连接字符串:server=.;uid=sa;pwd=haosql;database=SQLSUGAR4XTEST
如果ORM连接不上数据库:
请使用原生写法测试:new SqlConnection("字符串").Open(); 原生写法通过ORM就能用
new SqlConnection(db.CurrentConfig.ConnectionString).Open()//原生进行测试 是否是 SqlSUgar问题
如果是偶发情况:那么就使用SqlSugarScope 单例模式保证线程安全或者排查线程问题
nolcok nolcok nolcok nolcok , 脏读
//单个设置 无锁 db.Queryable<Order>().With(SqlWith.NoLock).ToList() //全局设置 var db= new SqlSugarClient(new ConnectionConfig() { DbType = SqlSugar.DbType.SqlServer, ConnectionString = Config.ConnectionString, IsAutoCloseConnection = true, MoreSettings=new ConnMoreSettings() { IsWithNoLockQuery=true//看这里 }); //全局设置禁用自动Nolock,只针对当前查询禁用 db.Queryable<Order>().With(SqlWith.Null).ToList() //全局设置禁用自动Nolock,只针对当前上下文禁用 db.CurrentConnectionConfig.MoreSettings.IsWithNoLockQuery = false;
db.Queryable<Order>().With("with(indexname, NoLock)").ToList()
建议用高版本的SqlServer数据库
原理:ORM默认字符串都是nvarchar参数的方式传到数据库
问题:特殊情况会影响索引,导致性能失效,并不是所有SQL都有影响
方案:
//1通过特性指定类型 [SugarColumn(SqlParameterDbType =System.Data.DbType.AnsiString)] public string name { get; set; } //2. 全局操作 (适合所有表都没有nvarchar) DbType = SqlSugar.DbType.Oracle, ConnectionString = Config.ConnectionString, InitKeyType = InitKeyType.Attribute, MoreSettings=new ConnMoreSettings() { DisableNvarchar=true//添加这一行 ,将参数全部转成varchar模式 } //3. 指定当方法 (在当前方法加上这行,这个方法里面会生效) db.CurrentConnectionConfig.MoreSettings=new MoreSettings(){DisableNvarchar=true}; //4. 指定具体代码 (具体的条件会生效) Where(it=>it.Name==SqlFunc.ToVarchar("张"))//函数只能用到参数上面,字段要走索引禁止用函数 //5. 原生SQL用法 var name=new SugarParameter("@name","haha",System.Data.DbType.AnsiString)
实体定义
public class UnitGe { [SugarColumn(ColumnDataType = "geometry")] public string geometry1 { get; set; } }
代码
db.Insertable(new UnitGe() { geometry1 = "POINT (20 180)" }).ExecuteCommand(); var gelist=db.Queryable<UnitGe>().Select(it=>new { geometry1 = it.geometry1.ToString()}).ToList();
完整用例
//SqlSugar中用法 var s = new SugarParameter("@p", dt); s.TypeName = "dtTableName"; //等同于原生SQL //sqlParameter.TypeName = "dtTableName"; //sqlParameter.SqlDbType = SqlDbType.Structured;
对于这版本请使用 .net framework版本SqlSugar ,如果用低版本SqlSugar InitKey=InitKey.Attribute要设置
功能除了CodeFirst 和 分页其他功能应该没问题
DbType = SqlSugar.DbType.SqlServer, ConnectionString = Config.ConnectionString, IsAutoCloseConnection = true, MoreSettings=new ConnMoreSettings() { SqlServerCodeFirstNvarchar= true,//建表字符串默认Nvarchar }
安装一个sqlserver tls1.2补丁包(SQLServer2008R2-KB4057113-x64)就解决了
The negotiated TLS 1.0 is an insecure protocol and is supported for backward compatibility only. The recommended protocol version is TLS 1.2 and later.
解决方案:
安装一个sqlserver tls1.2补丁包 或者 升级SqlServer
Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) ---> System.ComponentModel.Win32Exception (0x80090325): The certificate chain was issued by an authority that is not trusted.
1. 在客户端环境中安装目标 SQL Server 的 TLS/SSL 证书。 如果需要加密,将对其进行验证。
2.(不太安全)在连接字符串中设置“TrustServerCertificate=true”属性。
SqlServer中指定索引
db.Queryable<T>.With("WITH(INDEX(index_name))").ToList();
2016 © donet5.comApache Licence 2.0