トランザクション操作を有効にし、sqlite、mssqlなどのデータベースの一括挿入や更新にかかる時間の問題を解決します.
27524 ワード
private void button1_Click(object sender, EventArgs e)
{
//Sqlite
DateTime starttime = DateTime.Now;
using (SQLiteConnection con = new SQLiteConnection(connStr))
{
con.Open();
DbTransaction trans = con.BeginTransaction();//
SQLiteCommand cmd = new SQLiteCommand(con);
try
{
cmd.CommandText = "INSERT INTO MyTable(username,useraddr,userage) VALUES(@a,@b,@c)";
for (int n = 0; n < 100000; n++)
{
cmd.Parameters.Add(new SQLiteParameter("@a", DbType.String)); //MySql MySqlDbType.String
cmd.Parameters.Add(new SQLiteParameter("@b", DbType.String)); //MySql MySql.Data.dll
cmd.Parameters.Add(new SQLiteParameter("@c", DbType.String));
cmd.Parameters["@a"].Value = " " + n;
cmd.Parameters["@b"].Value = " " + n;
cmd.Parameters["@c"].Value = 10 + n;
cmd.ExecuteNonQuery();
}
trans.Commit();//
DateTime endtime = DateTime.Now;
MessageBox.Show(" ， " + (endtime - starttime).TotalMilliseconds);
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
//MySql
try
{
using (MySqlConnection con = new MySqlConnection(MySqlStr))
{
con.Open();
DbTransaction trans = con.BeginTransaction();//
#region
// Sqlite
for (int i = 0; i < count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
string sqlDelete = "delete from packect where proimei=@d";
MySqlCommand cmd1 = new MySqlCommand(sqlDelete, con);
cmd1.Parameters.Add(new MySqlParameter("@d", MySqlDbType.String));
cmd1.Parameters["@d"].Value = dr["proimei"];
cmd1.ExecuteNonQuery();
}
#endregion
#region
// Sqlite
for (int i = 0; i < count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
string sqlInsert = "insert into packect(proimei, prokg, proadddate) values(@a,@b,@c)";
MySqlCommand cmd2 = new MySqlCommand(sqlInsert, con);
cmd2.Parameters.Add(new MySqlParameter("@a", MySqlDbType.String));
cmd2.Parameters.Add(new MySqlParameter("@b", MySqlDbType.Decimal));
cmd2.Parameters.Add(new MySqlParameter("@c", MySqlDbType.DateTime));
cmd2.Parameters["@a"].Value = dr["proimei"];
cmd2.Parameters["@b"].Value = dr["prokg"];
cmd2.Parameters["@c"].Value = dr["proadddate"];
cmd2.ExecuteNonQuery();
}
#endregion
trans.Commit();//
DateTime endtime = DateTime.Now;
MessageBox.Show(" ， " + (endtime - starttime).TotalMilliseconds + " ！");
//
SQLiteHelper.ExecuteNonQuery(connStr, "delete from table1", CommandType.Text);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
/*
DataTable dt = new DataTable();
// ，
dt.Columns.Add("username");
dt.Columns.Add("useraddr");
dt.Columns.Add("userage");
for (int i = 0; i < 10000; i++)
{
dt.Rows.Add(" " + i, " " + i, i);
}
DateTime starttime = DateTime.Now;
SqlBulkCopyInsert(dt, "mytable");
DateTime endtime = DateTime.Now;
Response.Write(" ， " + (endtime - starttime).TotalSeconds + " ");
*/
/// <summary>
/// SqlBulkCopy ， SQLServer,
/// </summary>
/// <param name="table"> DataTable, ， </param>
/// <param name="tableName"> </param>
public void SqlBulkCopyInsert(DataTable table, string tableName)
{
SqlBulkCopy sbc = new SqlBulkCopy(MSCL.Until.GetApp("SqlConnStr"));
sbc.DestinationTableName = tableName;
for (int i = 0; i < table.Columns.Count; i++)
{
sbc.ColumnMappings.Add(table.Columns[i].ColumnName, table.Columns[i].ColumnName);
}
sbc.WriteToServer(table);
}
/// <summary>
/// SqlBulkCopy ， SQLServer,
/// ，
/// </summary>
/// <param name="table"> DataTable, ， </param>
/// <param name="tableName"> </param>
/// <param name="columns"> </param>
public void SqlBulkCopyInsert(DataTable table, string tableName, string[] columns)
{
SqlBulkCopy sbc = new SqlBulkCopy(" ");
sbc.DestinationTableName = tableName;
foreach (string col in columns)
{
sbc.ColumnMappings.Add(col, col);
}
sbc.WriteToServer(table);
}
/// <summary>
/// ,Connection/Command/DataAdapter
///
/// </summary>
/// <param name="ds"> </param>
/// <returns> </returns>
public int MultyInsert(DataSet ds)
{
int result = 0;
IDbConnection con = new OracleConnection(" ");
con.Open();
IDbCommand cmd = new OracleCommand();
cmd.CommandText = "Insert into Member(UserName,Password) values(@name,@password)";
IDbDataParameter namePar = cmd.CreateParameter();
namePar.ParameterName = "@name";
namePar.SourceColumn = "UserName";
namePar.SourceVersion = DataRowVersion.Original;
namePar.DbType = DbType.String;
cmd.Parameters.Add(namePar);
IDbDataParameter passPar = cmd.CreateParameter();
passPar.ParameterName = "@pass";
passPar.DbType = DbType.String;
passPar.SourceColumn = "Password";
passPar.SourceVersion = DataRowVersion.Original;
cmd.Parameters.Add(passPar);
IDbDataAdapter adpt = new OracleDataAdapter();
adpt.InsertCommand = cmd;
try
{
result = adpt.Update(ds);
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
return result;
}
/*
* Oracle
*/
////
//using System.Data;
//using System.Data.OracleClient;
//using Oracle.DataAccess.Client; //Oracle ： $Oracle $/bin/Oracle.DataAccess.dll
//
string connectStr = "User Id=scott;Password=tiger;Data Source=";
OracleConnection conn = new OracleConnection(connectStr);
OracleCommand command = new OracleCommand();
command.Connection = conn;
// ， ，
//
int recc = 10000000;
command.ArrayBindCount = recc;
// , , ,
// , ,
command.CommandText = "insert into dept values(:deptno, :deptname, :loc)";
conn.Open();
// , ,
int[] deptNo = new int[recc];
string[] dname = new string[recc];
string[] loc = new string[recc];
// , ,
// ,
OracleParameter deptNoParam = new OracleParameter("deptno", OracleType.Int32);
deptNoParam.Direction = ParameterDirection.Input;
deptNoParam.Value = deptNo;
command.Parameters.Add(deptNoParam);
OracleParameter deptNameParam = new OracleParameter("deptname", OracleType.VarChar);
deptNameParam.Direction = ParameterDirection.Input;
deptNameParam.Value = dname; command.Parameters.Add(deptNameParam);
OracleParameter deptLocParam = new OracleParameter("loc", OracleType.VarChar);
deptLocParam.Direction = ParameterDirection.Input;
deptLocParam.Value = loc;
command.Parameters.Add(deptLocParam);
// , , SQL
for (int i = 0; i < recc; i++)
{
deptNo[i] = i;
dname[i] = i.ToString();
loc[i] = i.ToString();
}
// SQL,
command.ExecuteNonQuery();