linq to sql 扩展方法
http://www.csharpwin.com/dotnetspace/9639r2943_2.shtml
1、DataContext扩展方法
public static class DataContextExentions { /// <summary> /// 打开连接 /// </summary> /// <param name="dataContext"></param> private static void OpenConnection(this DataContext dataContext) { if (dataContext.Connection.State == ConnectionState.Closed) { dataContext.Connection.Open(); } } /// <summary> /// 扩展ExecuteQuery方法 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dataContext"></param> /// <param name="query"></param> /// <param name="withNoLock"></param> /// <returns></returns> public static List<T> ExecuteQuery<T>(this DataContext dataContext, IQueryable query, bool withNoLock) { DbCommand command = dataContext.GetCommand(query, withNoLock); dataContext.OpenConnection(); using (DbDataReader reader = command.ExecuteReader()) { return dataContext.Translate<T>(reader).ToList(); } } /// <summary> /// 扩展ExecuteQuery方法2 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dataContext"></param> /// <param name="query"></param> /// <param name="withNoLock"></param> /// <returns></returns> public static List<T> ExecuteQuery<T>(this DataContext dataContext, IQueryable query) { DbCommand command = dataContext.GetCommand(query); dataContext.OpenConnection(); using (DbDataReader reader = command.ExecuteReader()) { return dataContext.Translate<T>(reader).ToList(); } } /// <summary> /// 扩展GetCommend方法,允许设置WithNoLick /// </summary> /// <param name="dataContext"></param> /// <param name="query"></param> /// <param name="withNoLock"></param> /// <returns></returns> private static SqlCommand GetCommand(this DataContext dataContext, IQueryable query, bool withNoLock) { SqlCommand command = (SqlCommand)dataContext.GetCommand(query); if (withNoLock) { command.CommandText = AddWithNoLock(command.CommandText); } return command; } /// <summary> /// 将Sql语句修改为with nolock /// </summary> /// <param name="cmdText"></param> /// <returns></returns> private static string AddWithNoLock(string cmdText) { IEnumerable<Match> matches = s_withNoLockRegex.Matches(cmdText).Cast<Match>() .OrderByDescending(m => m.Index); foreach (Match m in matches) { int splitIndex = m.Index + m.Value.Length; cmdText = cmdText.Substring(0, splitIndex) + " WITH (NOLOCK)" + cmdText.Substring(splitIndex); } return cmdText; } private static Regex s_withNoLockRegex = new Regex(@"(] AS [td+])", RegexOptions.Compiled); } }
2、DataContext扩展方法支持分页
/// <summary> /// DataContext扩展方法 /// </summary> public static class DataContextExtends { /// <summary> /// ExecuteQuery方法扩展,将对象以redader方式转换为实体 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dataContext"></param> /// <param name="query"></param> /// <returns></returns> public static List<T> ExecuteQuery<T>(this DataContext dataContext, IQueryable<object> query) { return ExecuteQuery<T>(dataContext, query, 1, query.Cast<T>().Count()); } /// <summary> /// ExecuteQuery方法扩展,代表分页的 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dataContext"></param> /// <param name="query"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public static List<T> ExecuteQuery<T>(this DataContext dataContext, IQueryable<object> query, int pageIndex, int pageSize) { int total = query.Count(); int totalPages = total / pageSize; if (total % pageSize > 0) totalPages++; if (pageIndex > totalPages) { pageIndex = totalPages; } if (pageIndex < 1) { pageIndex = 1; } query.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList(); DbCommand command = dataContext.GetCommand(query); dataContext.OpenConnection(); using (DbDataReader reader = command.ExecuteReader()) { return dataContext.Translate<T>(reader).ToList(); } } private static void OpenConnection(this DataContext dataContext) { if (dataContext.Connection.State == ConnectionState.Closed) dataContext.Connection.Open(); } }
作者:阿笨
【官方QQ一群:跟着阿笨一起玩NET(已满)】:422315558
【官方QQ二群:跟着阿笨一起玩C#(已满)】:574187616
【官方QQ三群:跟着阿笨一起玩ASP.NET(已满)】:967920586
【官方QQ四群:Asp.Net Core跨平台技术开发(可加入)】:829227829
【官方QQ五群:.NET Core跨平台开发技术(可加入)】:647639415
【网易云课堂】:https://study.163.com/provider/2544628/index.htm?share=2&shareId=2544628
【腾讯课堂】:https://abennet.ke.qq.com
【51CTO学院】:https://edu.51cto.com/sd/66c64
【微信公众号】:微信搜索:跟着阿笨一起玩NET