sqlSugar 简单封装及使用案例

SqlSugar; using System; using System.Linq; namespace AppSugarContext { public class SugarDbContext { /// 获取连接字符串 // private static string Connection = ConfigCommon.Get("WuAnDBContext"); private static string Connection = "Data Source=58.211.23.172;Initial Catalog=WuAnDBPrd2;Password=mk2XA5+#;User ID=sa;"; public SugarDbContext() { Db = new SqlSugarClient(new ConnectionConfig() { ConnectionString = Connection, DbType = DbType.SqlServer, InitKeyType = InitKeyType.Attribute,//从特性读取主键和自增列信息 IsAutoCloseConnection = true,//开启自动释放模式和EF原理一样我就不多解释了 }); //调式代码 用来打印SQL Db.Aop.OnLogExecuting = (sql, pars) => { Console.WriteLine(sql + "\r\n" + Db.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value))); Console.WriteLine(); }; } //注意：不能写成静态的 public SqlSugarClient Db;//用来处理事务多表查询和复杂的操作 } } View Code

需要引入Sugar和Json 两个包

 然后，我们封装sqlSuagr的一些常用方法，如下：

using appDataInterface;
using appModel;
using AppSugarContext;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace appDataService
{
    public class DataRepository
    {
        public static SugarDbContext sugar = new SugarDbContext();
        public static string NewGuid()
        {
            return Guid.NewGuid().ToString("N");
        }
        /// <summary>
        /// 获取返回的列表
        /// </summary>
        /// <typeparam name="U"></typeparam>
        /// <param name="sql"></param>
        /// <param name="orderby"></param>
        /// <returns></returns>
        public static List<U> GetListBySql<U>(string sql, string orderby = "")
            where U : class, new()
        {
            List<U> result = null;
            using (var db = sugar.Db)
            {
                if (string.IsNullOrEmpty(orderby))
                {
                    result = db.SqlQueryable<U>(sql).ToList();
                }
                else
                {
                    result = db.SqlQueryable<U>(sql).OrderBy(orderby).ToList();
                }
            }
            return result;
        }
        /// <summary>
        /// 获取返回的列表-参数化
        /// </summary>
        /// <typeparam name="U"></typeparam>
        /// <param name="sql"></param>
        /// <param name="where"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static List<U> GetListBySql<U>(string sql, string where, object parameters)
            where U : class, new()
        {
            List<U> result = null;
            using (var db = sugar.Db)
            {
                result = db.SqlQueryable<U>(sql).Where(where, parameters).ToList();
            }
            return result;
        }

        /// <summary>
        /// 获取DbSet 第一行
        /// </summary>
        /// <typeparam name="U"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static U GetOneBySql<U>(string sql)
            where U : class, new()
        {
            U result = null;
            using (var db = sugar.Db)
            {
                result = db.SqlQueryable<U>(sql).First();
            }
            return result;
        }
        /// <summary>
        /// 获取第一行第一列的值 并转化为Int
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int GetInt(string sql)
        {
            using (var db = sugar.Db)
            {
                return db.Ado.GetInt(sql);
            }
        }
        /// <summary>
        /// 获取第一行第一列的值 并转化为Double
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static double GetDouble(string sql)
        {
            using (var db = sugar.Db)
            {
                return db.Ado.GetDouble(sql);
            }
        }
        /// <summary>
        /// SQL 分页
        /// </summary>
        /// <typeparam name="E">返回值对象</typeparam>
        /// <typeparam name="U">查询类，继承自PaginationModel</typeparam>
        /// <param name="sql">sql</param>
        /// <param name="OrderBy">排序</param>
        /// <param name="u">查询对象，继承自PaginationModel</param>
        /// <returns></returns>
        public static PaginationListModel<E> PageQuery<E, U>(string sql, string OrderBy, U u)
            where U : PaginationModel
            where E : class, new()
        {
            var db = sugar.Db;
            int total = 0;
            List<E> list = null;
            if (string.IsNullOrEmpty(OrderBy))
            {
                list = db.SqlQueryable<E>(sql).ToPageList(u.pageNumber, u.pageSize, ref total);
            }
            else
            {
                list = db.SqlQueryable<E>(sql).OrderBy(OrderBy).ToPageList(u.pageNumber, u.pageSize, ref total);
            }
            return new PaginationListModel<E>()
            {
                data = list,
                pagination = new BasePaginationModel()
                {
                    pageNumber = u.pageNumber,
                    pageSize = u.pageSize,
                    total = total
                }
            };
        }
        /// <summary>
        ///  SQL 分页 参数化
        /// </summary>
        /// <typeparam name="E"></typeparam>
        /// <typeparam name="U"></typeparam>
        /// <param name="sql"></param>
        /// <param name="OrderBy"></param>
        /// <param name="u"></param>
        /// <param name="where"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static PaginationListModel<E> PageQuery<E, U>(string sql, string OrderBy, U u, string where, object parameters)
            where U : PaginationModel
            where E : class, new()
        {
            if (parameters == null)
            {
                return PageQuery<E, U>(sql, OrderBy, u);
            }

            var db = sugar.Db;
            int total = 0;
            List<E> list = null;
            if (string.IsNullOrEmpty(OrderBy))
            {
                list = db.SqlQueryable<E>(sql).Where(where, parameters).ToPageList(u.pageNumber, u.pageSize, ref total);
            }
            else
            {
                list = db.SqlQueryable<E>(sql).Where(where, parameters).OrderBy(OrderBy).ToPageList(u.pageNumber, u.pageSize, ref total);
            }
            return new PaginationListModel<E>()
            {
                data = list,
                pagination = new BasePaginationModel()
                {
                    pageNumber = u.pageNumber,
                    pageSize = u.pageSize,
                    total = total
                }
            };
        }
        /// <summary>
        /// 执行Sql 查询单个实体
        /// </summary>
        /// <typeparam name="E"></typeparam>
        /// <typeparam name="U"></typeparam>
        /// <param name="sql"></param>
        /// <param name="OrderBy"></param>
        /// <param name="u"></param>
        /// <returns></returns>
        public static E PageOne<E>(string sql)
            where E : class, new()
        {
            var db = sugar.Db;
            var one = db.SqlQueryable<E>(sql).ToList().FirstOrDefault();
            return one;
        }

        /// <summary>
        /// 查询结果List的第一条记录
        /// </summary>
        /// <typeparam name="E"></typeparam>
        /// <param name="sql"></param>
        /// <param name="where"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static E PageOne<E>(string sql, string where, object parameters)
           where E : class, new()
        {
            if (parameters == null)
            {
                return PageOne<E>(sql);
            }

            var db = sugar.Db;
            var one = db.SqlQueryable<E>(sql).Where(where, parameters).ToList().FirstOrDefault();
            return one;
        }


        /// <summary>
        /// 第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, object parameters = null)
        {
            using (var db = sugar.Db)
            {
                return db.Ado.GetScalar(sql, parameters);
            }
        }

        /// <summary>
        /// 第一行第一列
        /// </summary>
        public static object ExecuteScalar(string sql)
        {
            using (var db = sugar.Db)
            {
                return db.Ado.GetScalar(sql);
            }
        }
        /// <summary>
        /// 第一行第一列    -    异步
        /// </summary>
        public static async Task<object> ExecuteScalarAsync(string sql, object parameters = null)
        {
            using (var db = sugar.Db)
            {
                return await db.Ado.GetScalarAsync(sql, parameters);
            }
        }
        /// <summary>
        /// 第一行第一列    -    异步
        /// </summary>
        public static async Task<object> ExecuteScalarAsync(string sql)
        {
            using (var db = sugar.Db)
            {
                return await db.Ado.GetScalarAsync(sql);
            }
        }
       
        public static E GetOneBySql<E>(string sql, object parameters = null)
            where E : class
        {
            using (var db = sugar.Db)
            {
                return db.Ado.SqlQuerySingle<E>(sql, parameters);
            }

        }
        /// <summary>
        /// 第一行第一列    -    异步
        /// </summary>
        public static async Task<E> GetOneBySqlAsync<E>(string sql, object parameters = null)
          where E : class
        {
            using (var db = sugar.Db)
            {
                return await db.Ado.SqlQuerySingleAsync<E>(sql, parameters);
            }

        }

        public static List<E> GetBySql<E>(string sql, object parameters = null)
            where E : class
        {
            using (var db = sugar.Db)
            {
                return db.Ado.SqlQuery<E>(sql, parameters);
            }

        }

        public static async Task<List<E>> GetBySqlAsync<E>(string sql, object parameters = null)
            where E : class
        {
            using (var db = sugar.Db)
            {
                return await db.Ado.SqlQueryAsync<E>(sql, parameters);
            }
        }
    }
}

View Code

引用：System.Data.SqlClient

调用我们封装的方法，如下：

        public GetEvseInfoModel GetEvseInfo()
        {
            string sql = string.Format(@"select *
from  A left join  B
on A.GroupID=B.uid
where   EVSENo =‘{0}‘", "11212174714143316"); 
            var Info = DataRepository.PageOne<GetEvseInfoModel>(sql);
            return Info;
        }

        public PaginationListModel<DeliveryCarEntitys> GetCarEntities(SearchCarParam searchaParam)
        {
            string sql = string.Format(@"SELECT
*
FROM [dbo].[DeliveryCar] d WHERE 1=1
");
            return DataRepository.PageQuery<DeliveryCarEntitys, SearchCarParam>(sql, " AddTime desc", searchaParam);
        }

        public PaginationListModel<DeliveryCarEntitys> GetCarEntities_param(SearchCarParam searchaParam)
        {
            string sql = string.Format(@"SELECT
*
FROM [dbo].[DeliveryCar] d 
");
            var where = "1=1 and FrameNo = @FrameNo";
            var para = new
            {
                FrameNo = "112" //
            };
            return DataRepository.PageQuery<DeliveryCarEntitys, SearchCarParam>(sql, "AddTime desc", searchaParam, where, para);
        }

        public async Task<int> GetCabinetCount(SearchStatistic data, CurrentUserData CurrentUser)
        {
            string sql = @"select count(1) from  A 
";
            #region 数据权限
            if (!CurrentUser.IsAdmin)
            {
                sql += " and B.SystemID=@UserSystemID";
            }
            #endregion
            if (!string.IsNullOrEmpty(data.systemId))
            {
                sql += " and B.SystemID=@SystemID";
            }
            if (data.GroupId.HasValue)
            {
                sql += " and A.GroupId=@GroupId";
            }
            if (!string.IsNullOrEmpty(data.StationStoreId))
            {
                sql += "  and (A.StationID=@StationStoreId or A.StoreId=@StationStoreId)";
            }
            var para = new
            {
                UserSystemID = CurrentUser.userLoginToken.SystemID,
                SystemID = data.systemId,
                GroupId = data.GroupId,
                StationStoreId = data.StationStoreId

            };
            var Obj = await ExecuteScalarAsync(sql, para);
            if (Obj != null)
                return Convert.ToInt32(Obj);
            return 0;

        }

        public DeliveryCarEntity GetDetail(string uid)
        {
            var sql = $@"select {GetFieldsStr("c")},s.GroupId as StoreGroupId from {nameof(DeliveryCar)} as c with(nolock) 
                        left join {nameof(SysStoreInfo)} as s with(nolock) on s.uid = c.StoreId 
                        where c.uid = @uid";
            return GetOneBySql<DeliveryCarEntity>(sql, new { uid });
        }

        public async Task<ChangeApiPageInfo> GetFirstPageBatteryInfo(CurrentWeChatUser CurrentUser)
        {
            var bol = context.Cmcustomer.Any(A => A.Uid == CurrentUser.customerId && A.HasAgreed);
            if (!bol)
            {
                return new ChangeApiPageInfo() { HasAgreed = false };
            }
            string sql = @"select * from  baty 
inner  join  mat on mat.Skuno=baty.MaterialNo
left join  cus on cus.Uid= baty.customerId
where CustomerID=@CustomerID ";
            var paras = new { CustomerID = CurrentUser.customerId };
            return await GetOneBySqlAsync<ChangeApiPageInfo>(sql, paras);
        }

        public bool HasIot(string batteryNo)
        {
            var one = PageOne<GenericObject<bool>>($@"select b.BatteryNo,
m.HasIoT
from BaseBattery b
inner join MaterialBattery m on b.MaterialNo=m.SKUNo", "BatteryNo=@BatteryNo", new { BatteryNo = batteryNo });
            return one != null && one.Value;
        }

View Code

@天才卧龙的博客

sqlSugar 简单封装及使用案例

标签：image   convert   iot   appdata   sep   reg   工作人员   怎么   模式