ASP. NET MVC uses stored procedures to add modified data in bulk

With the entity Framework for database interaction, in code directly with Lamda Expressions and LINQ to the database operations, the middle of the programmer to save the database access code time, the programmer can directly focus on the business logic layer of writing. However, it is more difficult to correlate queries or modifications for more complex table relationships. The usual way to do this is to use EF to execute SQL statements or "stored procedures", especially to perform complex batch tasks, and of course to use ADO at the bottom of MVC, which is not much to say. How to do batch? Here's how to add modified data in bulk using a stored procedure under EF.

The requirement is this: you need to batch add modified product categories to the task amount, updated monthly, the beginning of the month 0, added will be displayed in the form, that is, add the changes are in a page.

idea: the front-end first use form to read the category dynamically, with viewbag dynamic loading to the page, if you have added the number of tasks in the current month, read out to show to the form, you can modify, otherwise it is a new month to add the number of tasks. There is a problem when submitting the form, how to post the category number to the background, I think of a way, that is, add a hidden field, the value is "type| category number", background data is to determine whether to include the type to determine whether the category number, and then use Split (' | ') [1] Loop read.

How to pass it to the database? I put the data into a DataTable, and then I use EF to execute the stored procedure and pass the DataTable to the database processing in front of the parameters.

What does the database do with this DataTable? Handling with custom data types

Code steps:

code aspects

controller Display dynamic form

Public ActionResult Markettaskadd ()        {            var marketype = new Marketdataprovider (). Getbtiddata (). Where (a=>a.id!= "0");//Read class            var rel = new Markettaskprogressprovider (). Getmarketmonthtask ();            if (rel. Count () > 0)            {                Viewbag.datas = rel. Join (Marketype, a = A.mkbtid, b + = Int. Parse (b.ID), (A, b) = = new {A.mkbtid, b.id,b.text,a.tasknum}). Select (s=>new viewsmodel {id= s.mkbtid. ToString (), text=s.text,tasknum=s.tasknum.tostring ()}); }//If there is Data Association data            else            {                var rel2 = Marketype.select (s = = new viewsmodel{ID = s.id, Text = S.text, tasknum= ""} ). ToList ();//return directly to the form                Viewbag.datas = Rel2;            }            return View ();        }

Start to return to object directly, the result of the foreground traversal is not supported, so the new entity class Viewsmodel.

View Page

  @foreach (var modelmarkets in Viewbag.datas)                            {                                <div class= "Row" style= "margin-top:10px" >                                    <div class= "Col-md-4 text-right" ><span class= "Red" >*</span> @modelMarkets. Text </div>                                    <div class= "Col-md-8 text-left" >                                        <input name= "text| @modelMarkets. ID" class= "Form-control" style= "width:50%" Value= "@modelMarkets. Tasknum" type= "text"/>                                        <input type= "hidden"  name= "type| @modelMarkets. ID" Value = "type| @modelMarkets. ID"/><!--hidden form-                                    </div>                                </div>                            }

　　

Controller Post Submission Form

[HttpPost] public actionresult markettaskadd (string type) {var strform = Request.Form;            int userId = adminuser!=null?adminuser.userid:0;//Creator or modified person ID DataTable dt = new DataTable (); Dt.            Columns.Add ("Mkbtid", Type.GetType ("System.Int32")); Dt.            Columns.Add ("Tasknum", Type.GetType ("System.Int32"));            list<string> Temp1 = new list<string> ();            list<string> temp2 = new list<string> (); for (int i = 0; i < Strform. Count; i++) {if (Strform[i]. Contains ("type")) {Temp1. ADD (Strform[i]. Split (' | ') [1]); } else {Temp2. ADD (Strform[i]); }//cycle Decomposition form} for (int i = 0; i < Temp1. Count; i++) {DataRow dr = dt.                NewRow ();                Dr[0] = Temp1[i];                DR[1] = Temp2[i]; Dt. Rows.Add (DR);//Bulk Add to DataTable} var rel = NEW Markettaskprogressprovider (). Markettaskaddoredit (USERID,DT);//Call method if (rel) viewbag.js = "<script>alert (' Operation succeeded!            "Window.location.href= '/markettaskprogress/markettaskadd ';</script>"; else Viewbag.js = "<script>alert (' operation failed!            "Window.location.href= '/markettaskprogress/markettaskadd ';</script>";            list<viewsmodel> listtemp = new list<viewsmodel> ();            Listtemp.add (new Viewsmodel {ID = "", Text = "", Tasknum = ""             });            Viewbag.datas = listtemp;        return View (); }    }

Submit to Database method:

   public bool Markettaskaddoredit (int userid,datatable dt)        {            using (dssentity entity = new dssentity ())// Using the Using             {                SqlParameter p = new SqlParameter ("@CreatedUser", Dbtype.int32) is not recommended;                P.value = userId;                SqlParameter p1 = new SqlParameter ("@tableMarketTask", dbtype.object);                P1. Value = DT;                P1. TypeName = "Tablemarkettask";//parameter processing, it seems that the custom function must add this function name                var rel = entity. Database.executesqlcommand ("exec[dbo].[ Pr_markettaskaddoredit] @CreatedUser, @tableMarketTask ", P,P1)//ef Execute stored procedure                return rel > 0;            }                    }

Database aspects

First build the custom type according to the situation, as follows

--Create The data typecreate TYPE [dbo]. [Tablemarkettask] As TABLE ([Mkbtid] [varchar] () not NULL,--delivery category [Tasknum] [varchar] () not null--number of tasks served)

You can also use SQL Server tools to manually create new

The second is to build the stored procedure

CREATE PROCEDURE pr_markettaskaddoredit@createduser INT, @tableMarketTask tablemarkettask readonly-parameter of the custom type, ReadOnly must be added. Asdeclare @TempCreatedUser intif EXISTS (SELECT TOP 1 * from Marketmonthtask T WHERE months=month (GETDATE ()))--changes to be when the month exists Ginselect TOP 1 @TempCreatedUser =createduser from Marketmonthtask T WHERE months=month (GETDATE ()) DELETE from Marketmonthtask  WHERE Months=month (GETDATE ()) INSERT into  marketmonthtask (mkbtid,tasknum,months, Updateuser,createduser) SELECT Mkbtid,tasknum,month (GETDATE ()), @CreatedUser, @TempCreatedUser from @ Tablemarkettaskend    else--or direct insert    Begininsert into  marketmonthtask (mkbtid,tasknum,months,createduser) SELECT Mkbtid,tasknum,month (GETDATE ()), @CreatedUser from @tableMarketTask    

　　

Custom types can be easily queried as if they were tables. Custom functions are not easy to debug. The EF directly calls the stored procedure and does not support the custom function.

After testing, the perfect pass.

NOTE: Welcome reprint, please respect the original author, be sure to indicate the source and the following QR code.

ASP. NET MVC uses stored procedures to add modified data in bulk