ASP. net mvc uses a stored procedure to add and modify data operations in batches. asp. netmvc

ASP. net mvc uses a stored procedure to add and modify data operations in batches. asp. netmvc

The Entity Framework is used for database interaction. In the code, lamda expressions and linq are used directly to operate on the database. In the middle, the programmer saves the time for database access code, programmers can directly focus on writing the business logic layer. However, it is difficult to query or modify complex table link associations. Generally, you can use EF to execute SQL statements or "stored procedures", especially for complex Batch Tasks. Of course, you can also use ADO. NET at the bottom of MVC, which is not mentioned here. How to batch? Here we will talk about how to use the stored procedure in EF to add and modify data in batches.

The requirement is as follows: You need to batch add and modify the number of projection tasks in the product category. The quantity is updated once a month, and the value is 0 at the beginning of the month. After adding and modifying tasks is displayed in the form, that is, adding and modifying tasks are all on the same page.

Ideas:The front-end uses a form to dynamically read the category and load it to the page using viewbag. If the number of tasks for the current month has been added, it is read and displayed on the form, which can be modified, otherwise, the number of tasks in the current month is added. A problem occurred when submitting the form. How can I post the category number to the background? I tried to add a hidden field with the value "Type | Category Number ", when the data is retrieved in the background, the system determines whether the data contains the Type to determine whether it is a category number. Then, the system reads data cyclically using split ('|') [1.

How do I transfer data to a database? I store the data in the datatable, then use EF to execute the stored procedure, and upload the datatable to the database as a parameter.

How does the database handle this datatable? Processing with Custom Data Types

Code steps:

Code

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 =>. MKBTID, B => int. parse (B. ID), (a, B) => new {. MKBTID, B. ID, B. text,. taskNum }). select (s => new ViewsModel {ID = s. MKBTID. toString (), Text = s. text, TaskNum = s. taskNum. toString ()});} // if data is associated with else {var rel2 = markeType. select (s => new ViewsModel {ID = s. ID, Text = s. text, TaskNum = ""}). toList (); // directly return the form ViewBag. datas = rel2;} return View ();}

Initially, you want to directly return the object. Foreground traversal of the result is not supported. Therefore, you can create an object 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 | @ modelMarke Ts. ID "/> <! -- Hide a form --> </div>}

Controller post submission form

[HttpPost] public ActionResult MarketTaskAdd (string type) {var strform = Request. Form; int userId = adminUser! = Null? AdminUser. userID: 0; // The creator or modifier 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]) ;}// Cyclic 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); // batch Add to datatable} var rel = new MarketTaskProgressProvider (). marketTaskAddOrEdit (userId, dt); // call the method if (rel) ViewBag. js = "<script> alert ('Operation successful! '); 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 ();}}

Method for submitting to database:

Public bool MarketTaskAddOrEdit (int userId, DataTable dt) {using (DssEntity entity = new DssEntity () // using {SqlParameter p = new SqlParameter ("@ CreatedUser ", dbType. int32); p. value = userId; SqlParameter p1 = new SqlParameter ("@ tableMarketTask", DbType. object); p1.Value = dt; p1.TypeName = "tableMarketTask"; // parameter processing. It seems that the user-defined function must be named var rel = entity. database. executeSqlCommand ("EXEC [dbo]. [PR_MarketTaskAddorEdit] @ CreatedUser, @ tableMarketTask ", p, p1); // ef executes the Stored Procedure return rel> 0 ;}}

Database

First, create a custom type as needed, as shown below:

-- Create the data typeCREATE TYPE [dbo]. [tableMarketTask] as table ([MKBTID] [varchar] (50) not null, -- serving category [TaskNum] [varchar] (50) not null -- serving task quantity)

You can also use the SQL server tool to manually create

The second is the storage creation process.

Create procedure PR_MarketTaskAddorEdit @ CreatedUser INT, @ tableMarketTask readonly -- a parameter of the custom type, which must be added with readonly. As declare @ TempCreatedUser int if exists (select top 1 * FROM MarketMonthTask t where Months = MONTH (GETDATE ())) -- if the current MONTH exists, modify begin select 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 @ tableMarketTask end else -- or directly insert begin insert into MarketMonthTask (MKBTID, TaskNum, Months, CreatedUser) select mkbtid, TaskNum, MONTH (GETDATE ()), @ CreatedUser FROM @ tableMarketTask END

User-Defined types can be queried by themselves like tables, which is very convenient. Custom functions are not easy to debug. EF directly calls stored procedures and does not support user-defined functions.

The above is a small series of ASP.. net mvc uses the stored procedure to add and modify data in batches. I hope it will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!