Inserting a piece of data into SQL Server uses the INSERT statement, but if you want to bulk insert a bunch of data, looping through insert is inefficient and results in a system performance problem with SQL
The following are the two bulk data insertion methods supported by SQL Server: Bulk and table-valued parameters (table-valued Parameters).
The main idea of the bulk method is to insert the data in the table into the database by sqlbulkcopy the data in the table at the client, and then using the
The code is as follows:
public static void Bulktodb (DataTable dt)
{
SqlConnection sqlconn = new SqlConnection (
configurationmanager.connectionstrings["ConnStr"]. ConnectionString);
SqlBulkCopy bulkcopy = new SqlBulkCopy (sqlconn);
Bulkcopy.destinationtablename = "Bulktesttable";
bulkcopy.batchsize = dt. Rows.Count;
Try
{
Sqlconn.open ();
if (dt! = null && dt. Rows.Count! = 0)
Bulkcopy.writetoserver (DT);
}
catch (Exception ex)
{
Throw ex;
}
Finally
{
Sqlconn.close ();
if (bulkcopy! = null)
Bulkcopy.close ();
}
}
public static DataTable GetTableSchema ()
{
DataTable dt = new DataTable ();
Dt. Columns.addrange (New datacolumn[]{
New DataColumn ("Id", typeof (int)),
New DataColumn ("UserName", typeof (String)),
New DataColumn ("Pwd", typeof (String))});
return DT;
}
static void Main (string[] args)
{
Stopwatch SW = new Stopwatch ();
for (int multiply = 0; multiply < multiply++)
{
DataTable dt = Bulk.gettableschema ();
for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)
{
DataRow r = dt. NewRow ();
R[0] = count;
R[1] = string. Format ("user-{0}", Count * multiply);
R[2] = string. Format ("pwd-{0}", Count * multiply);
Dt. Rows.Add (R);
}
Sw. Start ();
BULK.BULKTODB (DT);
Sw. Stop ();
Console.WriteLine (String. Format ("Elapsed time is {0} Milliseconds", SW. Elapsedmilliseconds));
}
Console.ReadLine ();
}
- Reprinted from: http://www.aspnetjia.com/Cont-133.html
Database inserts 10w data at once, how to insert efficiency fast