linq to sql 擴充方法

標籤：des   style   blog   http   color   io   os   ar   for   

老趙的部落格：http://blog.zhaojie.me/2008/02/using-translate-method-and-modify-command-text-before-query-in-linq-to-sql.html

                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);    }}

View Code

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();    }}

View Code

 

linq to sql 擴充方法