你的位置:首页 > ASP.net教程

[ASP.net教程]ASP.NET MVC与Sql Server交互,把字典数据插入数据库


 

在"ASP.NET MVC与Sql Server交互, 插入数据"中,在Controller中拼接sql语句。比如:

 

_db.InsertData("insert into Product(Name,quantity,Price) values('"+productVm.Name+"','"+productVm.Quantity+"','"+productVm.Price+"')");

 

在某些场景中需要把数据放在字典集合中,再插入到数据库。类似如下:

 

_db.InsertDataByDic("表名", 字典集合);

 

这样有更好的可读性。字典集合的键是表的字段们。

 

于是,可以把相对复杂的sql语句拼接放在了帮助类中。在帮助类中增加一个InsertDataByDic方法,该方法遍历字典集合拼接sql语句。

 

   public class SqlDB
  {
    protected SqlConnection conn;
    //打开连接
    public bool OpenConnection()
    {
      conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
      try
      {
        bool result = true;
        if (conn.State.ToString() != "Open")
        {
          conn.Open();
        }
        return result;
      }
      catch (SqlException ex)
      {
        return false;
      }
    }
    //关闭连接
    public bool CloseConnection()
    {
      try
      {
        conn.Close();
        return true;
      }
      catch (Exception ex)
      {
        return false;
      }
    }
    //插入数据
    public int InsertData(string sql)
    {
      int lastId = 0;
      //string query = sql + ";SELECT @@Identity;";
      try
      {
        if(conn.State.ToString()=="Open")
        {
          SqlCommand cmd = new SqlCommand(sql, conn);
          //cmd.ExecuteNonQuery();
          lastId = ToInt(cmd.ExecuteScalar());//返回第一行的第一列
        }
        return ToInt(lastId);
      }
      catch (Exception ex)
      {
        return 0;
      }
    }
    //转换成整型
    private int ToInt(object o)
    {
      try
      {
        return int.Parse(o.ToString());
      }
      catch (Exception ex)
      {
        return 0;
      }
    }
    //插入字典数据
    public int InsertDataByDic(string tableName, Dictionary<string,string> dics)
    {
      int lastId = 0;
      string keyStr = string.Empty;//拼接键
      string valStr = string.Empty;//拼接变量
      int index = 0;//索引
      try
      {
        foreach (KeyValuePair<string, string> item in dics)
        {
          index++;
          //第一次拼接前面逗号
          keyStr += (index != 1 ? "," : "") + "[" + item.Key + "]";
          valStr += (index != 1 ? "," : "") + "@" + item.Key;
        }
        //拼接sql语句
        string query = "insert into " + tableName + "(" + keyStr + ") values (" + valStr + ");SELECT @@Identity;";
        if (conn.State.ToString() == "Open")
        {
          SqlCommand cmd = new SqlCommand(query, conn);
          foreach (KeyValuePair<string, string> item in dics)
          {
            cmd.Parameters.AddWithValue("@" + item.Key, item.Value);
          }
          lastId = ToInt(cmd.ExecuteScalar());
        }
        return ToInt(lastId);
      }
      catch (Exception ex)
      {
        return 0;
      }
    }
  }

 

在TestController中增加2个名称为AddProductByDic的Action方法,把从前端获取到的视图模型中的数据赋值给字典集合。

 

 public class TestController : Controller
  {
    private SqlDB _db = new SqlDB();
    //
    // GET: /Test/
    public ActionResult Index()
    {
      bool r = _db.OpenConnection();
      if (r)
      {
        return Content("连接成功");
      }
      else
      {
        return Content("连接失败");
      }
    }
    //通过sql语句插入数据
    public ActionResult AddProduct()
    {
      return View();
    }
    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult AddProduct(ProductVm productVm)
    {
      if(ModelState.IsValid)
      {
        _db.OpenConnection();
        int result = _db.InsertData("insert into Product(Name,quantity,Price) values('"+productVm.Name+"','"+productVm.Quantity+"','"+productVm.Price+"')");
        if(result > 0)
        {
          ModelState.AddModelError("success", "创建成功");
        }
        else
        {
          ModelState.AddModelError("error", "创建失败");
        }
        _db.CloseConnection();
        return RedirectToAction("Index");
      }
      else
      {
        return View(productVm);
      }
    }
    //通过字典集合插入数据
    public ActionResult AddProductByDic()
    {
      return View();
    }
    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult AddProductByDic(ProductVm productVm)
    {
      int i = 0;
      if (ModelState.IsValid)
      {
        _db.OpenConnection();
        Dictionary<string, string> data = new Dictionary<string, string>();
        data["Name"] = productVm.Name;
        data["quantity"] = productVm.Quantity;
        data["Price"] = productVm.Price;
        i = _db.InsertDataByDic("Product", data);
        _db.CloseConnection();
        if(i>0)
        {
          return RedirectToAction("Index");
        }
        else
        {
          return View(productVm);
        }
      }
      else
      {
        return View(productVm);
      }
    }
  }

 

AddProductByDic.cshtml是一个强类型视图。

 

@model Portal.Models.ProductVm
@{
  ViewBag.Title = "AddProductByDic";
  Layout = "~/Views/Shared/_Layout.cshtml";
}
<h2>AddProductByDic</h2>
@using (Html.BeginForm("AddProductByDic", "Test", new { @id = "addForm" }, FormMethod.Post))
{
  @Html.AntiForgeryToken()
  
  <div class="form-horizontal">
    <h4>ProductVm</h4>
    <hr />
    @Html.ValidationSummary(true)
    <div class="form-group">
      @Html.LabelFor(model => model.Name, new { @class = "control-label col-md-2" })
      <div class="col-md-10">
        @Html.EditorFor(model => model.Name)
        @Html.ValidationMessageFor(model => model.Name)
      </div>
    </div>
    <div class="form-group">
      @Html.LabelFor(model => model.Quantity, new { @class = "control-label col-md-2" })
      <div class="col-md-10">
        @Html.EditorFor(model => model.Quantity)
        @Html.ValidationMessageFor(model => model.Quantity)
      </div>
    </div>
    <div class="form-group">
      @Html.LabelFor(model => model.Price, new { @class = "control-label col-md-2" })
      <div class="col-md-10">
        @Html.EditorFor(model => model.Price)
        @Html.ValidationMessageFor(model => model.Price)
      </div>
    </div>
    <div class="form-group">
      <div class="col-md-offset-2 col-md-10">
        <input type="submit" value="创建" class="btn btn-default" />
      </div>
    </div>
  </div>
}
<div>
  @Html.ActionLink("Back to List", "Index")
</div>