LINQ to SQL extension methods

Lao Zhao's Blog: 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 extension method

 Public Static classdatacontextexentions {/// <summary>        ///Open Connection/// </summary>        /// <param name= "DataContext" ></param>        Private Static voidOpenConnection ( ThisDataContext DataContext) {            if(DataContext.Connection.State = =connectionstate.closed) {dataContext.Connection.Open (); }        }        /// <summary>        ///extending the ExecuteQuery method/// </summary>        /// <typeparam name= "T" ></typeparam>        /// <param name= "DataContext" ></param>        /// <param name= "Query" ></param>        /// <param name= "Withnolock" ></param>        /// <returns></returns>         Public StaticList<t> executequery<t> ( ThisDataContext DataContext, IQueryable query,BOOLWithnolock) {DbCommand command=datacontext.getcommand (query, Withnolock);            Datacontext.openconnection (); using(DbDataReader reader =command. ExecuteReader ()) {returnDatacontext.translate<t>(reader).            ToList (); }        }        /// <summary>        ///Extending the ExecuteQuery Method 2/// </summary>        /// <typeparam name= "T" ></typeparam>        /// <param name= "DataContext" ></param>        /// <param name= "Query" ></param>        /// <param name= "Withnolock" ></param>        /// <returns></returns>         Public StaticList<t> executequery<t> ( ThisDataContext DataContext, IQueryable query) {DbCommand command=datacontext.getcommand (query);            Datacontext.openconnection (); using(DbDataReader reader =command. ExecuteReader ()) {returnDatacontext.translate<t>(reader).            ToList (); }        }        /// <summary>        ///extend the Getcommend method to allow setting Withnolick/// </summary>        /// <param name= "DataContext" ></param>        /// <param name= "Query" ></param>        /// <param name= "Withnolock" ></param>        /// <returns></returns>        Private StaticSqlCommand GetCommand ( ThisDataContext DataContext, IQueryable query,BOOLWithnolock) {SqlCommand command=(SqlCommand) datacontext.getcommand (query); if(withnolock) {Command.commandtext=Addwithnolock (Command.commandtext); }            returncommand; }        /// <summary>        ///modify the SQL statement to with NOLOCK/// </summary>        /// <param name= "Cmdtext" ></param>        /// <returns></returns>        Private Static stringAddwithnolock (stringCmdtext) {IEnumerable<Match> matches =s_withnolockregex.matches (Cmdtext). Cast<Match>()                . OrderByDescending (M=M.index); foreach(Match minchmatches) {                intSplitindex = M.index +m.value.length; Cmdtext=cmdtext.substring (0, Splitindex) +"With (NOLOCK)"+cmdtext.substring (Splitindex); }            returnCmdtext; }        Private StaticRegex S_withnolockregex =NewRegex (@"(] as [td+])", regexoptions.compiled); }}

View Code

2, DataContext extension method support paging

/// <summary>///DataContext Extension Methods/// </summary> Public Static classdatacontextextends{/// <summary>    ///executequery method extension to convert an object to an entity in Redader mode/// </summary>    /// <typeparam name= "T" ></typeparam>    /// <param name= "DataContext" ></param>    /// <param name= "Query" ></param>    /// <returns></returns>     Public StaticList<t> executequery<t> ( ThisDataContext DataContext, iqueryable<Object>query) {        returnExecutequery<t> (dataContext, query,1, query. Cast<t>().    Count ()); }    /// <summary>    ///executequery Method Extension, which represents the paging/// </summary>    /// <typeparam name= "T" ></typeparam>    /// <param name= "DataContext" ></param>    /// <param name= "Query" ></param>    /// <param name= "PageIndex" ></param>    /// <param name= "pageSize" ></param>    /// <returns></returns>     Public StaticList<t> executequery<t> ( ThisDataContext DataContext, iqueryable<Object> Query,intPageIndex,intpageSize) {        intTotal =query.        Count (); intTotalPages = 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 ()) {returnDatacontext.translate<t>(reader).        ToList (); }    }    Private Static voidOpenConnection ( ThisDataContext DataContext) {        if(DataContext.Connection.State = =connectionstate.closed) DataContext.Connection.Open (); }}

View Code

LINQ to SQL extension methods