public void FastInsertMany(DataTable dt)
{
Open(connStr);
using (DbTransaction dbTrans = conn.BeginTransaction())
{ using (DbCommand cmd = conn.CreateCommand())
{ cmd.CommandText = "INSERT INTO tb_gk_gqtz VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; DbParameter Field1 = null;
DataRow dr = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
for (int j = 0; j < dt.Columns.Count; j++)
{
Field1 = cmd.CreateParameter();
Field1.Value = dr[j];
cmd.Parameters.Add(Field1);
//Field1 = null;
}
cmd.ExecuteNonQuery(); } } dbTrans.Commit(); } }
插入一千条数据就很慢了,请问如何优化
{
Open(connStr);
using (DbTransaction dbTrans = conn.BeginTransaction())
{ using (DbCommand cmd = conn.CreateCommand())
{ cmd.CommandText = "INSERT INTO tb_gk_gqtz VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; DbParameter Field1 = null;
DataRow dr = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
for (int j = 0; j < dt.Columns.Count; j++)
{
Field1 = cmd.CreateParameter();
Field1.Value = dr[j];
cmd.Parameters.Add(Field1);
//Field1 = null;
}
cmd.ExecuteNonQuery(); } } dbTrans.Commit(); } }
插入一千条数据就很慢了,请问如何优化
实现起来并不算复杂:DateTime begin = DateTime.Now;
string connectionString = ......;
using(SqlConnection conn = new SqlConnection(connectionString))...{
conn.Open();
SqlDataAdapter sd = new SqlDataAdapter();
sd.SelectCommand = new SqlCommand("select devid,data_time,data_value from CurrentTest", conn);
sd.InsertCommand = new SqlCommand("insert into CurrentTest (devid,data_time,data_value) "
+ " values (@devid,@data_time,@data_value);", conn);
sd.InsertCommand.Parameters.Add("@devid", SqlDbType.Char, 18, "devid");
sd.InsertCommand.Parameters.Add("@data_time", SqlDbType.Char, 19, "data_time");
sd.InsertCommand.Parameters.Add("@data_value", SqlDbType.Int, 8, "data_value");
sd.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
sd.UpdateBatchSize = 0; DataSet dataset = new DataSet();
sd.Fill(dataset);
Random r = new Random(1000);
for (int i = 0; i < 100000; i++) ...{
object[] row = ...{"DEVID"+i,DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),r.Next(1,1000) };
dataset.Tables[0].Rows.Add(row);
if (i % 300 == 0) ...{
sd.Update(dataset.Tables[0]);
dataset.Tables[0].Clear();
}
}
sd.Update(dataset.Tables[0]);
dataset.Tables[0].Clear();
sd.Dispose();
dataset.Dispose();
conn.Close();
}
5s or 50s?