在DataTable中實現增、刪、改以及批量插入資料到資料庫

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