需要引入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 工作人员 怎么 模式
查看更多关于sqlSugar 简单封装及使用案例的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did116723