在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();
        }
    }
}