c datatable导入mysql_在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"];

}

}

///

/// 批量插入数据

///

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

}

}

}

分享到：

2010-06-12 11:16

浏览 891

评论