在"中，在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
    
      dics)
    

{

int lastId = 0;

string keyStr = string.Empty;//拼接键

string valStr = string.Empty;//拼接变量

int index = 0;//索引

try

{

foreach (KeyValuePair
    
      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
    
      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
    
      data = new Dictionary
     
      ();
     
    

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

}

AddProductByDic

@using (Html.BeginForm("AddProductByDic", "Test", new { @id = "addForm" }, FormMethod.Post))

{

@Html.AntiForgeryToken()

ProductVm

@Html.ValidationSummary(true)

@Html.LabelFor(model => model.Name, new { @class = "control-label col-md-2" })

@Html.EditorFor(model => model.Name)

@Html.ValidationMessageFor(model => model.Name)

@Html.LabelFor(model => model.Quantity, new { @class = "control-label col-md-2" })

@Html.EditorFor(model => model.Quantity)

@Html.ValidationMessageFor(model => model.Quantity)

@Html.LabelFor(model => model.Price, new { @class = "control-label col-md-2" })

@Html.EditorFor(model => model.Price)

@Html.ValidationMessageFor(model => model.Price)

}

@Html.ActionLink("Back to List", "Index")