我有一個資料庫，共有50多個欄位，我準備將3條記錄依次插入到資料庫中，但是我下面的程式，連接了3次資料庫，執行速度太慢，我想改造成只連接一次資料庫，然后將3條記錄插入到資料庫中。
下面是我的原始程式代碼：
//插入第一條記錄
string conStr = "server=localhost;port=3306;user Id=root;password=8331; database=tp ;Allow User Variables=True";
MySqlConnection conn = new MySqlConnection(conStr);
try
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("insert into table1(id,d_date,d_time,td1,td2,td3,td4,td5,td6,td7,td8,td9,td10,td11,td12,td13,td14,td15,td16,td17,td18,td19,td20,td21,td22,td23,td24,td25,td26,td27,td28,td29,td30,td31,td32,td33,td34,td35,td36,td37,td38,td39,td40,pd1,pd2,pd3,pd4,pd5,pd6,pd7,pd8,pd9,pd10,a_press) VALUES(@id,@d_date,@d_time,@td1,@td2,@td3,@td4,@td5,@td6,@td7,@td8,@td9,@td10,@td11,@td12,@td13,@td14,@td15,@td16,@td17,@td18,@td19,@td20,@td21,@td22,@td23,@td24,@td25,@td26,@td27,@td28,@td29,@td30,@td31,@td32,@td33,@td34,@td35,@td36,@td37,@td38,@td39,@td40,@pd1,@pd2,@pd3,@pd4,@pd5,@pd6,@pd7,@pd8,@pd9,@pd10,@a_press);", conn);
cmd.Parameters.AddWithValue("@id", CableNo);
cmd.Parameters.AddWithValue("@d_date", dt);
cmd.Parameters.AddWithValue("@d_time", dt);
cmd.Parameters.AddWithValue("@td1", ftd[0]);
cmd.Parameters.AddWithValue("@td2", ftd[1]);
cmd.Parameters.AddWithValue("@td3", ftd[2]);
cmd.ExecuteNonQuery();
}
catch (Exception ex)//創建檢查Exception物件
{
MessageBox.Show(ex.Message);//輸出錯誤資訊
}
finally
{
conn.Close();//關閉連接
}
}
//插入第二條記錄
string conStr = "server=localhost;port=3306;user Id=root;password=8331; database=tp ;Allow User Variables=True";
MySqlConnection conn = new MySqlConnection(conStr);
try
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("insert into table1(id,d_date,d_time,td1,td2,td3,td4,td5,td6,td7,td8,td9,td10,td11,td12,td13,td14,td15,td16,td17,td18,td19,td20,td21,td22,td23,td24,td25,td26,td27,td28,td29,td30,td31,td32,td33,td34,td35,td36,td37,td38,td39,td40,pd1,pd2,pd3,pd4,pd5,pd6,pd7,pd8,pd9,pd10,a_press) VALUES(@id,@d_date,@d_time,@td1,@td2,@td3,@td4,@td5,@td6,@td7,@td8,@td9,@td10,@td11,@td12,@td13,@td14,@td15,@td16,@td17,@td18,@td19,@td20,@td21,@td22,@td23,@td24,@td25,@td26,@td27,@td28,@td29,@td30,@td31,@td32,@td33,@td34,@td35,@td36,@td37,@td38,@td39,@td40,@pd1,@pd2,@pd3,@pd4,@pd5,@pd6,@pd7,@pd8,@pd9,@pd10,@a_press);", conn);
cmd.Parameters.AddWithValue("@id", CableNo);
cmd.Parameters.AddWithValue("@d_date", dt);
cmd.Parameters.AddWithValue("@d_time", dt);
cmd.Parameters.AddWithValue("@td1", ftd[0]);
cmd.Parameters.AddWithValue("@td2", ftd[1]);
cmd.Parameters.AddWithValue("@td3", ftd[2]);
cmd.ExecuteNonQuery();
}
catch (Exception ex)//創建檢查Exception物件
{
MessageBox.Show(ex.Message);//輸出錯誤資訊
}
finally
{
conn.Close();//關閉連接
}
}
//插入第三條記錄
string conStr = "server=localhost;port=3306;user Id=root;password=8331; database=tp ;Allow User Variables=True";
MySqlConnection conn = new MySqlConnection(conStr);
try
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("insert into table1(id,d_date,d_time,td1,td2,td3,td4,td5,td6,td7,td8,td9,td10,td11,td12,td13,td14,td15,td16,td17,td18,td19,td20,td21,td22,td23,td24,td25,td26,td27,td28,td29,td30,td31,td32,td33,td34,td35,td36,td37,td38,td39,td40,pd1,pd2,pd3,pd4,pd5,pd6,pd7,pd8,pd9,pd10,a_press) VALUES(@id,@d_date,@d_time,@td1,@td2,@td3,@td4,@td5,@td6,@td7,@td8,@td9,@td10,@td11,@td12,@td13,@td14,@td15,@td16,@td17,@td18,@td19,@td20,@td21,@td22,@td23,@td24,@td25,@td26,@td27,@td28,@td29,@td30,@td31,@td32,@td33,@td34,@td35,@td36,@td37,@td38,@td39,@td40,@pd1,@pd2,@pd3,@pd4,@pd5,@pd6,@pd7,@pd8,@pd9,@pd10,@a_press);", conn);
cmd.Parameters.AddWithValue("@id", CableNo);
cmd.Parameters.AddWithValue("@d_date", dt);
cmd.Parameters.AddWithValue("@d_time", dt);
cmd.Parameters.AddWithValue("@td1", ftd[0]);
cmd.Parameters.AddWithValue("@td2", ftd[1]);
cmd.Parameters.AddWithValue("@td3", ftd[2]);
cmd.ExecuteNonQuery();
}
catch (Exception ex)//創建檢查Exception物件
{
MessageBox.Show(ex.Message);//輸出錯誤資訊
}
finally
{
conn.Close();//關閉連接
}
}
uj5u.com熱心網友回復：
打開連接-打開事務-插入資料-提交事務-關閉連接uj5u.com熱心網友回復：
一樓正解，connection.BeginTransaction()開啟事務即可。uj5u.com熱心網友回復：
能有代碼嗎？你說的太專業啦！
uj5u.com熱心網友回復：
大概這樣：
string conStr = "server=localhost;port=3306;user Id=root;password=8331; database=tp ;Allow User Variables=True";
MySqlConnection conn = new MySqlConnection(conStr);
MySqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
MySqlCommand cmd = new MySqlCommand("insert into table1(id,d_date,d_time,td1,td2,td3,td4,td5,td6,td7,td8,td9,td10,td11,td12,td13,td14,td15,td16,td17,td18,td19,td20,td21,td22,td23,td24,td25,td26,td27,td28,td29,td30,td31,td32,td33,td34,td35,td36,td37,td38,td39,td40,pd1,pd2,pd3,pd4,pd5,pd6,pd7,pd8,pd9,pd10,a_press) VALUES(@id,@d_date,@d_time,@td1,@td2,@td3,@td4,@td5,@td6,@td7,@td8,@td9,@td10,@td11,@td12,@td13,@td14,@td15,@td16,@td17,@td18,@td19,@td20,@td21,@td22,@td23,@td24,@td25,@td26,@td27,@td28,@td29,@td30,@td31,@td32,@td33,@td34,@td35,@td36,@td37,@td38,@td39,@td40,@pd1,@pd2,@pd3,@pd4,@pd5,@pd6,@pd7,@pd8,@pd9,@pd10,@a_press);", conn);
for (int i = 0; i < 3; i++)
{ //這里回圈執行，根據實際情況修改
cmd.Parameters.AddWithValue("@id", CableNo);
cmd.Parameters.AddWithValue("@d_date", dt);
cmd.Parameters.AddWithValue("@d_time", dt);
cmd.Parameters.AddWithValue("@td1", ftd[0]);
cmd.Parameters.AddWithValue("@td2", ftd[1]);
cmd.Parameters.AddWithValue("@td3", ftd[2]);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
tran.Commit();//提交事務
}
}
catch (Exception ex)//創建檢查Exception物件
{
MessageBox.Show(ex.Message);//輸出錯誤資訊
if (tran != null) tran.Rollback();
}
finally
{
if (tran != null)
tran.Dispose();
if (conn.State == ConnectionState.Open)
conn.Close();//關閉連接
}
uj5u.com熱心網友回復：
搞不懂，為啥不用EF物體框架。EF直接db.<Entity>.Add()就可以完成。
批量Insert，db.<Entity>.AddRange()
批量插入，用擴展插件：Z.EntityFramework.BulkInsert
db.BulkInsert(Entity)
uj5u.com熱心網友回復：
大概這樣吧
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/270131.html
標籤：C#