linq to sql 扩展方法

浏览数：45 / 时间：2015年06月12日

老赵的博客：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();
    }

}