EF Architecture ~ EF6-DbCommand interceptors for database read and write separation ~ Continue ~ Add transaction mechanism

Back to Catalog

In the last lecture, we briefly introduced the DbCommand interceptor to realize the read and write separation of SQL Server in an EF environment, which is only the simplest implementation, and if there is a transaction situation, there will be some problems, because in the interceptor we manually opened the connection link, In the case of a Web request, if one of your variables uses the read library and the write library, it will lead to the SPID on the SQL Server side (System process ID, which may be the ID of a database into the program in Id,sqlserver) change , and for this change, the original local transaction will automatically be promoted to distributed transactions, the MSDTC does not understand the students, may see my related articles, so, we use interception to achieve read and write separation, in the program, you read and write the storage object to be defined separately, cannot be shared, and the storage object you write in the transaction must use the same data context!

When you do what I say, the local transaction will not be promoted to MSDTC.

Today I was optimizing the DbCommand interceptor, sharing the code below, so the test is not a real project code

    /// <summary>    ///SQL command Blocker/// </summary>     Public classSqlcommandinterceptor:dbcommandinterceptor {/// <summary>        ///read the library, from the library cluster, write the library without setting the default EF framework ./// </summary>        stringReadconn = system.configuration.configurationmanager.appsettings["READDB"] ??string.        Empty; Private stringGetreadconn () {varReadarr = Readconn.split (New Char[] {','}, Stringsplitoptions.removeemptyentries); varResultconn =string.            Empty; if(Readarr! =NULL&&Readarr.any ()) {Resultconn= Readarr[convert.toint32 (Math.floor (Double)NewRandom (). Next (0, Readarr.length))]; }            returnResultconn; }        //Update,delete generated by LINQ to entity         Public Override voidnonqueryexecuting (DbCommand command, dbcommandinterceptioncontext<int>Interceptioncontext) {            Base. Nonqueryexecuting (command, Interceptioncontext);//Update,delete and other write operations go directly to the main library        }        /// <summary>        ///executes the SQL statement and returns the first row in the first column, not found returns null if the value in the database is null, returns DBNull.Value/// </summary>        /// <param name= "command" ></param>        /// <param name= "Interceptioncontext" ></param>         Public Override voidscalarexecuting (DbCommand command, Dbcommandinterceptioncontext<Object>Interceptioncontext) {            if(!string. Isnullorwhitespace (Getreadconn ()))//if the read-write separation is configured, the implementation            {                if(!command.commandtext.startswith ("Insert", Stringcomparison.invariantcultureignorecase)) {command.                    Connection.close (); Command. Connection.connectionstring=Getreadconn (); Command.                Connection.Open (); }            }            Base.        Scalarexecuting (command, Interceptioncontext); }        /// <summary>        ///Select,insert generated by LINQ to entity///triggered before sending to SQL Server///WARNING: dbcommand.transaction is null in the SELECT statement, and EF adds a dbcommand.transaction for each insert to wrap/// </summary>        /// <param name= "command" ></param>        /// <param name= "Interceptioncontext" ></param>         Public Override voidreaderexecuting (DbCommand command, dbcommandinterceptioncontext<dbdatareader>Interceptioncontext) {            if(!string. Isnullorwhitespace (Getreadconn ()))//if the read-write separation is configured, the implementation            {                if(!command.commandtext.startswith ("Insert", Stringcomparison.invariantcultureignorecase)) {command.                    Connection.close (); Command. Connection.connectionstring=Getreadconn (); Command.                Connection.Open (); }            }            Base.        readerexecuted (command, Interceptioncontext); }        /// <summary>        ///triggered after sending to SQL Server/// </summary>        /// <param name= "command" ></param>        /// <param name= "Interceptioncontext" ></param>         Public Override voidreaderexecuted (DbCommand command, dbcommandinterceptioncontext<dbdatareader>Interceptioncontext) {            Base.        readerexecuted (command, Interceptioncontext); }    }

Run the program to set up some test code

  PublicActionResult Index () {Iunitofwork db=Newbackgroundentities (); IRepository<WebManageUsers> Readuser =NewBackgroundrepositorybase<webmanageusers>(); varA = Readuser.getmodel (). ToList ();//Read Library            using(vartrans =NewTransactionScope ())//Transaction Write Library{irepository<WebManageUsers> Userwrite =NewBackgroundrepositorybase<webmanageusers>(DB); IRepository<WebManageMenus> Menuwrite =NewBackgroundrepositorybase<webmanagemenus>(DB); varentity =Newwebmanageusers {websystemid=0, CreateDate=DateTime.Now, DepartmentID=3, Description="", Email="", LoginName="Test", Mobile="", Operator="", Password="", Realname="Test", Status=1, Updatedate=DateTime.Now,}; varEntity2 =NewWebmanagemenus {parentid=1, about="", Linkurl="", MenuLevel=1, MenuName="Test", Operator="", Sortnumber=1, Status=1, Updatedate=DateTime.Now,};                Userwrite.insert (entity);                Menuwrite.insert (Entity2);            Trans.complete (); }            returnView (a); }

View Code

The final result is what we want, here is a point, storage stride Read and write separation without database pressure this piece of consideration, just random to access a read library.

Back to Catalog

EF Architecture ~ EF6-DbCommand interceptors for database read and write separation ~ Continue ~ Add transaction mechanism