--Create DataBasecreate database BulkTestDB;gouse BulkTestDB;go--Create TableCreate table BulkTestTable(Id int primary key,UserName nvarchar(32),Pwd varchar(16))go--Create Table ValuedCREATE TYPE BulkUdt AS TABLE  (Id int,   UserName nvarchar(32),   Pwd varchar(16))

            Stopwatch sw = new Stopwatch();            SqlConnection sqlConn = new SqlConnection(                ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);//连接数据库            SqlCommand sqlComm = new SqlCommand();            sqlComm.CommandText = string.Format("insert into BulkTestTable(Id,UserName,Pwd)values(@p0,@p1,@p2)");//参数化SQL            sqlComm.Parameters.Add("@p0", SqlDbType.Int);            sqlComm.Parameters.Add("@p1", SqlDbType.NVarChar);            sqlComm.Parameters.Add("@p2", SqlDbType.VarChar);            sqlComm.CommandType = CommandType.Text;            sqlComm.Connection = sqlConn;            sqlConn.Open();            try            {                //循环插入100万条数据，每次插入10万条，插入10次。                for (int multiply = 0; multiply < 10; multiply++)                {                    for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)                    {                        sqlComm.Parameters["@p0"].Value = http://www.mamicode.com/count;"@p1"].Value = http://www.mamicode.com/string.Format("User-{0}", count * multiply);                        sqlComm.Parameters["@p2"].Value = http://www.mamicode.com/string.Format("Pwd-{0}", count * multiply);                        sw.Start();                        sqlComm.ExecuteNonQuery();                        sw.Stop();                    }                    //每插入10万条数据后，显示此次插入所用时间                    Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));                }            }            catch (Exception ex)            {                throw ex;            }            finally            {                sqlConn.Close();            }            Console.ReadLine();

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

public static void TableValuedToDB(DataTable dt){    SqlConnection sqlConn = new SqlConnection(      ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);    const string TSqlStatement =     "insert into BulkTestTable (Id,UserName,Pwd)" +     " SELECT nc.Id, nc.UserName,nc.Pwd" +     " FROM @NewBulkTestTvp AS nc";    SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);    SqlParameter catParam = cmd.Parameters.AddWithValue("@NewBulkTestTvp", dt);    catParam.SqlDbType = SqlDbType.Structured;    //表值参数的名字叫BulkUdt，在上面的建立测试环境的SQL中有。    catParam.TypeName = "dbo.BulkUdt";    try    {      sqlConn.Open();      if (dt != null && dt.Rows.Count != 0)      {          cmd.ExecuteNonQuery();      }    }    catch (Exception ex)    {      throw ex;    }    finally    {      sqlConn.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 < 10; multiply++)    {        DataTable dt = TableValued.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();        TableValued.TableValuedToDB(dt);        sw.Stop();        Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));    }    Console.ReadLine();}