ASP.NET MVC uses stored procedures to add and modify data operations in batches

This article mainly introduces the method of batch adding and modifying data in ASP.NET MVC using stored procedures. It is very good and has reference value. Friends in need can refer to

Using Entity Framework for database interaction. Directly use lambda expressions and linq to operate the database in the code, which saves programmers the coding time for database access. Programmers can directly focus on writing the business logic layer. However, it is more laborious to query or modify more complex table relationships. The usual method that can be adopted is to use EF to execute SQL statements or "stored procedures", especially to execute complex batch tasks. Of course, you can also use ADO.NET at the bottom of MVC, which I won't go into here. How to make batches? Here we talk about using stored procedures to add and modify data in batches under EF.

The requirements are as follows: the amount of delivery tasks for product categories needs to be added and modified in batches. It is updated once a month and resets to 0 at the beginning of the month. After being added, it will be displayed in the form, that is, the addition and modification are all on one page.

Idea: The front-end first uses a form to dynamically read the category, and uses viewbag to dynamically load it into the page. If there is already After adding the number of tasks for the current month, it will be read and displayed on the form, which can be modified. Otherwise, the number of tasks for the current month will be newly added. When submitting the form, a problem arose. How to post the category number to the backend? I thought of a way, that is, add a hidden field with the value "Type|Category Number". The backend gets the data to determine whether it contains Type. is the category number, then use split('|')[1] to read it in a loop.

How to pass it to the database? I save the data into the datatable, then use EF to execute the stored procedure, and pass the datatable to the database as parameters for processing.

How does the database handle this datatable? Processing

Code steps with custom data types:

Code aspects

Controller display Dynamic form

public ActionResult MarketTaskAdd()
  {
   var markeType = new MarketDataProvider().GetBTIDData().Where(a=>a.ID!="0");//读取类别
   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()}); }//如果有数据关联数据
   else
   {
    var rel2 = markeType.Select(s => new ViewsModel{ ID = s.ID, Text = s.Text, TaskNum="" }).ToList();//直接返回表单
    ViewBag.datas = rel2;
   }
   return View();
  }

At first I wanted to return object directly, but the foreground traversal was not supported, so I created a new entity class ViewsModel.

View page

@foreach (var modelMarkets in ViewBag.datas)
       {
        <p class="row" style="margin-top:10px">
         <p class="col-md-4 text-right"><span class="red">*</span> @modelMarkets.Text </p>
         <p 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" /><!--隐藏表单-->
         </p>
        </p>
       }

Controller post submission form

[HttpPost]
  public ActionResult MarketTaskAdd(string type)
  {
   var strform = Request.Form;
   int userId = adminUser!=null?adminUser.UserID:0;//创建人或者修改人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(&#39;|&#39;)[1]); }
    else
    { temp2.Add(strform[i]); }//循环分解表单
   }
   for (int i = 0; i < temp1.Count; i++)
   {
    DataRow dr = dt.NewRow();
    dr[0] = temp1[i];
    dr[1] = temp2[i];
    dt.Rows.Add(dr);//批量添加到datatable
   }
   var rel = new MarketTaskProgressProvider().MarketTaskAddOrEdit(userId,dt);//调用方法
   if(rel)
     ViewBag.js = "<script>alert(&#39;操作成功！&#39;);window.location.href=&#39;/MarketTaskProgress/MarketTaskAdd&#39;;</script>";
   else
    ViewBag.js = "<script>alert(&#39;操作失败！&#39;);window.location.href=&#39;/MarketTaskProgress/MarketTaskAdd&#39;;</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
   {
    SqlParameter p = new SqlParameter("@CreatedUser",DbType.Int32);
    p.Value = userId;
    SqlParameter p1 = new SqlParameter("@tableMarketTask",DbType.Object);
    p1.Value = dt;
    p1.TypeName = "tableMarketTask";//参数处理，貌似自定义函数必须加这个函数名称
    var rel = entity.Database.ExecuteSqlCommand("EXEC[dbo].[PR_MarketTaskAddorEdit] @CreatedUser,@tableMarketTask", p,p1);//ef执行存储过程
    return rel > 0;
   }
  }

##Database aspect

First create a custom type according to the situation, as follows

-- Create the data type
CREATE TYPE [dbo].[tableMarketTask] AS TABLE(
 [MKBTID] [varchar](50) NOT NULL,--投放类别
 [TaskNum] [varchar](50) NOT NULL--投放任务数量
)

　You can also use the sql server tool to manually create a new one

The second is to build a stored procedure

CREATE PROCEDURE PR_MarketTaskAddorEdit
 @CreatedUser INT,
 @tableMarketTask tableMarketTask readonly --自定义类型的参数，必须加readonly。
AS
 DECLARE @TempCreatedUser INT
 IF EXISTS(SELECT TOP 1 * FROM MarketMonthTask T WHERE Months=MONTH(GETDATE()))--当月存在的话就修改
 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--或者直接插入
 BEGIN
  INSERT INTO MarketMonthTask(MKBTID,TaskNum,Months,CreatedUser) SELECT MKBTID,TaskNum,MONTH(GETDATE()),@CreatedUser FROM @tableMarketTask
 END

Custom types can be queried by themselves like tables, which is very convenient. Custom functions are not easy to debug. EF does not support custom functions when calling stored procedures directly.

The above is the ASP.NET MVC batch adding and modifying data operation using stored procedures introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will promptly Reply to everyone. I would also like to thank you all for your support of the PHP Chinese website!

For more articles related to ASP.NET MVC using stored procedures to add and modify data in batches, please pay attention to the PHP Chinese website!