在DataTable中实现增、删、改以及批量插入数据到数据库

using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; public partial class FILEManage_Controls_XCPEditControl : System.Web.UI.UserControl { DataTable dt = new DataTable(); string sQDGuid = ""; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { ViewState["LPGuid"] = Guid.NewGuid().ToString(); if (ViewState["DataTable"] != null) { this.GridView1.DataSource = (DataTable)ViewState["DataTable"]; this.GridView1.DataKeyNames = new string[] { "LQ_LQ_ID" }; this.GridView1.DataBind(); dt = (DataTable)ViewState["DataTable"]; } } if (ViewState["DataTable"] != null) { this.GridView1.DataKeyNames = new string[] { "LQ_LQ_ID" }; dt = (DataTable)ViewState["DataTable"]; } } /// <summary> /// 批量插入数据 /// </summary> private void InsertData() { string sConn = ConfigurationManager.AppSettings["TQNPOAConnectionString"]; string sSelect = "select LQ_LQ_ID,LQ_LP_ID,LQ_名称,LQ_数量,LQ_接收单位,LQ_事由 from LP_宣传品清单"; string sInsert = "insert into LP_宣传品清单(LQ_LQ_ID,LQ_LP_ID,LQ_名称,LQ_数量,LQ_接收单位,LQ_事由) values (@LQ_LQ_ID,@LQ_LP_ID,@LQ_名称,@LQ_数量,@LQ_接收单位,@LQ_事由)"; SqlConnection con = new SqlConnection(sConn); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = new SqlCommand(sSelect,con); da.InsertCommand = new SqlCommand(sInsert,con); da.InsertCommand.Parameters.Add("@LQ_LQ_ID", SqlDbType.VarChar, 100, "LQ_LQ_ID"); da.InsertCommand.Parameters.Add("@LQ_LP_ID", SqlDbType.VarChar, 100, "LQ_LP_ID"); da.InsertCommand.Parameters.Add("@LQ_名称", SqlDbType.VarChar, 50, "LQ_名称"); da.InsertCommand.Parameters.Add("@LQ_数量", SqlDbType.Int, 4, "LQ_数量"); da.InsertCommand.Parameters.Add("@LQ_接收单位", SqlDbType.VarChar, 80, "LQ_接收单位"); da.InsertCommand.Parameters.Add("@LQ_事由", SqlDbType.VarChar, 200, "LQ_事由"); da.InsertCommand.UpdatedRowSource = UpdateRowSource.None; da.UpdateBatchSize = 0; DataTable dtInsert = new DataTable(); da.Fill(dtInsert); for (int i = 0; i < dt.Rows.Count; i++) { DataRow row = dtInsert.NewRow(); row[0] = dt.Rows[i]["LQ_LQ_ID"]; row[1] = dt.Rows[i]["LQ_LP_ID"]; row[2] = dt.Rows[i]["LQ_名称"]; row[3] = dt.Rows[i]["LQ_数量"]; row[4] = dt.Rows[i]["LQ_接收单位"]; row[5] = dt.Rows[i]["LQ_事由"]; dtInsert.Rows.Add(row); } da.Update(dtInsert); da.Dispose(); dtInsert.Dispose(); con.Close(); } protected void lnkbtnAdd_Click(object sender, EventArgs e) { sQDGuid = Guid.NewGuid().ToString(); //生成GUID if (ViewState["DataTable"] == null) { DataColumn dc1 = new DataColumn(); dc1.Caption = "ID"; dc1.ColumnName = "LQ_LQ_ID"; dc1.DataType = System.Type.GetType("System.String"); dt.Columns.Add(dc1); DataColumn dc2 = new DataColumn(); dc2.Caption = "ID"; dc2.ColumnName = "LQ_LP_ID"; dc2.DataType = System.Type.GetType("System.String"); dt.Columns.Add(dc2); DataColumn dc3 = new DataColumn(); dc3.Caption = "礼品名称"; dc3.ColumnName = "LQ_名称"; dc3.DataType = System.Type.GetType("System.String"); dt.Columns.Add(dc3); DataColumn dc4 = new DataColumn(); dc4.Caption = "礼品数量"; dc4.ColumnName = "LQ_数量"; dc4.DataType = System.Type.GetType("System.String"); dt.Columns.Add(dc4); DataColumn dc5 = new DataColumn(); dc5.Caption = "接收单位"; dc5.ColumnName = "LQ_接收单位"; dc5.DataType = System.Type.GetType("System.String"); dt.Columns.Add(dc5); DataColumn dc6 = new DataColumn(); dc6.Caption = "事由"; dc6.ColumnName = "LQ_事由"; dc6.DataType = System.Type.GetType("System.String"); dt.Columns.Add(dc6); } DataRow row = dt.NewRow(); row[0] = sQDGuid; row[1] = ViewState["LPGuid"].ToString(); row[2] = ((TextBox)this.FormView1.FindControl("txtLPName")).Text; row[3] = ((TextBox)this.FormView1.FindControl("txtLPNum")).Text; row[4] = ((TextBox)this.FormView1.FindControl("txtLPAccept")).Text; row[5] = ((TextBox)this.FormView1.FindControl("txtRemark")).Text; dt.Rows.Add(row); DataColumn [] col = new DataColumn[1]; col[0] = dt.Columns["LQ_LQ_ID"]; dt.PrimaryKey = col; //设置生成表的主键 //this.GridView1.EditIndex = this.GridView1.Rows.Count; ViewState["DataTable"] = dt; this.GridView1.DataSource = dt; this.GridView1.DataBind(); } protected void btnAdd_Click(object sender, EventArgs e) { InsertData(); } protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) { this.GridView1.EditIndex = -1; DataRow row = dt.Rows.Find(this.GridView1.DataKeys[e.RowIndex].Value); row.BeginEdit(); row["LQ_名称"] = ((TextBox)this.GridView1.Rows[e.RowIndex].FindControl("TextBox1")).Text; row["LQ_数量"] = ((TextBox)this.GridView1.Rows[e.RowIndex].FindControl("TextBox2")).Text; row["LQ_接收单位"] = ((TextBox)this.GridView1.Rows[e.RowIndex].FindControl("TextBox3")).Text; row["LQ_事由"] = ((TextBox)this.GridView1.Rows[e.RowIndex].FindControl("TextBox4")).Text; row.EndEdit(); if (ViewState["DataTable"] != null) { this.GridView1.DataSource = (DataTable)ViewState["DataTable"]; this.GridView1.DataBind(); } } protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e) { dt.Rows.RemoveAt(e.RowIndex); if (ViewState["DataTable"] != null) { this.GridView1.DataSource = (DataTable)ViewState["DataTable"]; this.GridView1.DataBind(); } } protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) { this.GridView1.EditIndex = e.NewEditIndex; if (ViewState["DataTable"] != null) { this.GridView1.DataSource = (DataTable)ViewState["DataTable"]; this.GridView1.DataBind(); } } protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) { this.GridView1.EditIndex = -1; if (ViewState["DataTable"] != null) { this.GridView1.DataSource = (DataTable)ViewState["DataTable"]; this.GridView1.DataBind(); } } }