在"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>