C# SqlSugarClient分析一点

转载

mb5fe32930661bd 2019-09-03 15:55:00

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'

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。