C# SqlSugarClient分析一点
public class UserManager : DbContext<User>{} UserManager userdb = new UserManager(); //创建对象 var user = userdb.GetFirst(u => u.UserName == userName && u.Password == pwd); //继承,可以使用父类方法GetFirst() public class DbContext<T> where T : class, new() { public SqlSugarClient Db;//用来处理事务多表查询和复杂的操作 public DbContext() { Db = new SqlSugarClient(new ConnectionConfig() { … }); } public virtual T GetFirst(Expression<Func<T, bool>> whereExpression) { return Db.Queryable<T>().First(whereExpression); } } SqlSugarClient Db对象下面的方法 public class SqlSugarClient : ISqlSugarClient, IDisposable, Itenant { public SqlSugarProvider Context => GetContext(); //public ISugarQueryable<T> Queryable<T>(); public ISugarQueryable<T> Queryable<T>() { return Context.Queryable<T>(); //返回的对象的类是继承于ISugarQueryable<T>接口 //所以可以继续点first方法 Db.Queryable<T>().First(whereExpression); } private SqlSugarProvider GetContext() { if (IsSameThreadAndShard()) { return SameThreadAndShard(); } if (IsNoSameThreadAndShard()) { return NoSameThreadAndShard(); } if (IsSynchronization()) { return Synchronization(); } return NoSameThread(); } } //接口 ; 继承并实现first方法 public interface ISugarQueryable<T> { T First(Expression<Func<T, bool>> expression); } // \.nuget\packages\sqlsugarcore\5.0.0.1\lib\netstandard2.0\SqlSugar.dll
cehsimodelManager abc = new cehsimodelManager(); abc.GetFirst(u => u.btnname == "admin" && u.btnid == "123465"); //c#
//sql exec sp_executesql N'SELECT * FROM (SELECT [btnname],[btnid],ROW_NUMBER() OVER(ORDER BY GETDATE() ) AS RowIndex FROM [cehsimodel] WHERE (( [btnname] = @btnname0 ) AND ( [btnid] = @btnid1 ))) T WHERE RowIndex BETWEEN 1 AND 1',N'@btnname0 nvarchar(4000),@btnid1 nvarchar(4000)',@btnname0=N'admin',@btnid1=N'123465' --[cehsimodel] 类的名称要和表名称一致
public class cehsimodelManager : DbContext<cehsimodel>
{}
//类
public class cehsimodel
{
public string btnname { get; set; }
public string btnid { get; set; }
}
List<Leave> list1; if (user_s[0].ReadLeave==null|| user_s[0].ReadLeave.Trim()==string.Empty) { user_s[0].ReadLeave = "0"; }
if (pid == 1) //查询未读取的 { list1 = Leavedb.GetPageList(d => !user_s[0].ReadLeave.Contains(d.LeaveId.ToString()), pageinfo, d => d.CreateTime, OrderByType.Asc); } else //读取的 { list1 = Leavedb.GetPageList(d => user_s[0].ReadLeave.Contains(d.LeaveId.ToString()), pageinfo, d => d.CreateTime, OrderByType.Asc); }
//sql语句查询
//if (pid == 1) //查询未读取的 //{ // sql = string.Format("select * from Leave where LeaveId not in ({0}) ", user_s[0].ReadLeave); // list1 = Leavedb.Db.SqlQueryable<Leave>(sql).ToList().OrderByDescending(n=>n.CreateTime).ToList(); //时间降序 //} //else //读取的 //{ // sql = string.Format("select * from Leave where LeaveId in ({0}) ", user_s[0].ReadLeave); // list1 = Leavedb.Db.SqlQueryable<Leave>(sql).ToList().OrderByDescending(n => n.CreateTime).ToList(); //}
--执行sql 语句
var t = exec_sql_list("UPDATE [dbo].[ColumnInfo] SET [IsDel] = 'false' ");
return Db.SqlQueryable<ColumnInfo>(sql).ToList();
--转换的sql语句
SELECT t.* FROM ( UPDATE [dbo].[ColumnInfo] SET [IsDel] = 'false' ) t
--执行更新集合
UPDATE S SET S.[IsDel]=T.[IsDel],S.[ColumnTitleContent]=T.[ColumnTitleContent],S.[Sort]=T.[Sort],S.[Remark]=T.[Remark],S.[CreateUserName]=T.[CreateUserName],S.[CreateRealName]=T.[CreateRealName],S.[CreateTime]=T.[CreateTime],S.[ModifyUserName]=T.[ModifyUserName],S.[ModifyRealName]=T.[ModifyRealName],S.[ModifyTime]=T.[ModifyTime] FROM [ColumnInfo] S INNER JOIN ( SELECT 0 AS [IsDel],N'4' AS [ColumnInfoId],N'测试' AS [ColumnTitleContent],N'1' AS [Sort],N'测试' AS [Remark],N'sysadmin' AS [CreateUserName],N'超级管理员' AS [CreateRealName],'2019-09-09 18:09:34.500' AS [CreateTime],NULL AS [ModifyUserName],NULL AS [ModifyRealName],NULL AS [ModifyTime] UNION ALL SELECT 0 AS [IsDel],N'5' AS [ColumnInfoId],N'2010测试' AS [ColumnTitleContent],N'1' AS [Sort],N'策划is' AS [Remark],N'sysadmin' AS [CreateUserName],N'超级管理员' AS [CreateRealName],'2019-09-10 08:17:01.013' AS [CreateTime],NULL AS [ModifyUserName],NULL AS [ModifyRealName],NULL AS [ModifyTime] UNION ALL SELECT 0 AS [IsDel],N'6' AS [ColumnInfoId],N'001' AS [ColumnTitleContent],N'1' AS [Sort],N'001' AS [Remark],N'sysadmin' AS [CreateUserName],N'超级管理员' AS [CreateRealName],'2019-09-10 08:17:25.087' AS [CreateTime],NULL AS [ModifyUserName],NULL AS [ModifyRealName],NULL AS [ModifyTime] UNION ALL SELECT 0 AS [IsDel],N'7' AS [ColumnInfoId],N'002' AS [ColumnTitleContent],N'1' AS [Sort],N'002' AS [Remark],N'sysadmin' AS [CreateUserName],N'超级管理员' AS [CreateRealName],'2019-09-10 08:17:31.547' AS [CreateTime],NULL AS [ModifyUserName],NULL AS [ModifyRealName],NULL AS [ModifyTime] UNION ALL SELECT 0 AS [IsDel],N'8' AS [ColumnInfoId],N'003' AS [ColumnTitleContent],N'1' AS [Sort],N'003' AS [Remark],N'sysadmin' AS [CreateUserName],N'超级管理员' AS [CreateRealName],'2019-09-10 08:17:37.523' AS [CreateTime],NULL AS [ModifyUserName],NULL AS [ModifyRealName],NULL AS [ModifyTime] UNION ALL SELECT 0 AS [IsDel],N'9' AS [ColumnInfoId],N'004' AS [ColumnTitleContent],N'1' AS [Sort],N'004' AS [Remark],N'sysadmin' AS [CreateUserName],N'超级管理员' AS [CreateRealName],'2019-09-10 08:17:44.583' AS [CreateTime],NULL AS [ModifyUserName],NULL AS [ModifyRealName],NULL AS [ModifyTime] UNION ALL SELECT 0 AS [IsDel],N'10' AS [ColumnInfoId],N'005' AS [ColumnTitleContent],N'1' AS [Sort],N'005' AS [Remark],N'sysadmin' AS [CreateUserName],N'超级管理员' AS [CreateRealName],'2019-09-10 08:17:53.443' AS [CreateTime],N'sysadmin' AS [ModifyUserName],N'超级管理员' AS [ModifyRealName],'2019-09-10 09:35:37.440' AS [ModifyTime] UNION ALL SELECT 0 AS [IsDel],N'11' AS [ColumnInfoId],N'006' AS [ColumnTitleContent],N'1' AS [Sort],N'006' AS [Remark],N'sysadmin' AS [CreateUserName],N'超级管理员' AS [CreateRealName],'2019-09-10 08:18:00.900' AS [CreateTime],NULL AS [ModifyUserName],NULL AS [ModifyRealName],NULL AS [ModifyTime] UNION ALL SELECT 0 AS [IsDel],N'12' AS [ColumnInfoId],N'007' AS [ColumnTitleContent],N'1' AS [Sort],N'007' AS [Remark],N'sysadmin' AS [CreateUserName],N'超级管理员' AS [CreateRealName],'2019-09-10 08:18:07.283' AS [CreateTime],N'sysadmin' AS [ModifyUserName],N'超级管理员' AS [ModifyRealName],'2019-09-10 09:31:48.427' AS [ModifyTime] ) T ON S.[ColumnInfoId]=T.[ColumnInfoId]
执行单个
exec sp_executesql N'UPDATE [ColumnInfo] SET [IsDel]=@IsDel,[ColumnTitleContent]=@ColumnTitleContent,[Sort]=@Sort,[Remark]=@Remark,[CreateUserName]=@CreateUserName,[CreateRealName]=@CreateRealName,[CreateTime]=@CreateTime,[ModifyUserName]=@ModifyUserName,[ModifyRealName]=@ModifyRealName,[ModifyTime]=@ModifyTime WHERE [ColumnInfoId]=@ColumnInfoId',N'@IsDel bit,@ColumnInfoId int,@ColumnTitleContent nvarchar(4000),@Sort int,@Remark nvarchar(4000),@CreateUserName nvarchar(4000),@CreateRealName nvarchar(4000),@CreateTime datetime,@ModifyUserName nvarchar(4000),@ModifyRealName nvarchar(4000),@ModifyTime datetime',@IsDel=1,@ColumnInfoId=9,@ColumnTitleContent=N'004',@Sort=1,@Remark=N'004',@CreateUserName=N'sysadmin',@CreateRealName=N'超级管理员',@CreateTime='2019-09-10 08:17:44.583',@ModifyUserName=N'sysadmin',@ModifyRealName=N'超级管理员',@ModifyTime='2019-09-10 10:20:20.240'
