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

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


 

在"ASP.NET MVC与Sql Server建立连接"中,与Sql Server建立了连接。本篇实践向Sql Server中插入数据。

 

在数据库帮助类中增加插入数据的方法。

 

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

 

创建一个对应数据库Product的视图模型。   

 

  public class ProductVm
  {
    [Required(ErrorMessage="必填")]
    [StringLength(16)]
    public string Name { get; set; }
    [Required(ErrorMessage = "必填")]
    [StringLength(16)]
    public string Quantity { get; set; }
    [Required(ErrorMessage = "必填")]
    [StringLength(16)]
    public string Price { get; set; }
  }

 

在TestController中增加一个处理添加数据的2个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("连接失败");
      }
    }
    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 View();
      }
      else
      {
        return View(productVm);
      }
    }
  }

 

在对应的Test/AddProduct视图中:

 

@model Portal.Models.ProductVm
@{
  ViewBag.Title = "AddProduct";
  Layout = "~/Views/Shared/_Layout.cshtml";
}
<h2>创建产品</h2>
@using (Html.BeginForm("AddProduct", "Test", new { @id = "addForm" }, FormMethod.Post))
{
  @Html.AntiForgeryToken()
  
  <div class="form-horizontal">
    @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>