【C#-批量插入数据到数据库】DataTable数据批量插入数据的库三种方法：SqlCommand.EcecuteNonQurery()，SqlDataAdapter.Update(DataTable) ，SqlBulkCopy.WriteToServer(Datatable)

第一种方法：使用SqlCommand.EcecuteNonQurery()  效率最慢

第二种方法：使用SqlDataAdapter.Update(DataTable)   效率次之

第三种方法：使用SqlBulkCopy.WriteToServer(Datatable)  效率最快

        static void Main(string[] args)
        {
            string connString = "SERVER=.;DATABASE=Test;UID=sa;PWD=F123456789f";

            //生成DataTable表
            DataTable dt = new DataTable();
            dt.Columns.Add("CompanyCode");
            dt.Columns.Add("CompanyName");
            dt.Columns.Add("Address");
            dt.Columns.Add("Owner");
            dt.Columns.Add("Memo");
            for (int i = 0; i < 20000; i++)
            {
                DataRow row = dt.NewRow();
                row["CompanyName"] = "companyname" + string.Format("{0:00000}", i);
                row["CompanyCode"] = "companycode" + string.Format("{0:00000}", i);
                row["Address"] = "address" + string.Format("{0:00000}", i);
                row["Owner"] = "owner" + string.Format("{0:00000}", i);
                row["Memo"] = "memo" + string.Format("{0:00000}", i);
                dt.Rows.Add(row);
            }


            //第一种方法：使用SqlCommand.EcecuteNonQurery()方法插入
            foreach (DataRow row in dt.Rows)
            {
                string sql = "insert into Mytable(CompanyName,CompanyCode,Address,Owner,Memo) values("
                      + "'" + row["CompanyName"].ToString() + "'" + ","
                      + "'" + row["CompanyCode"].ToString() + "'" + ","
                      + "'" + row["Address"].ToString() + "'" + ","
                      + "'" + row["Owner"].ToString() + "'" + ","
                      + "'" + row["Memo"].ToString() + "'" + ")";
                using (SqlConnection sqlcon = new SqlConnection(connString))
                {
                    sqlcon.Open();
                    SqlCommand sqlcmd = new SqlCommand(sql, sqlcon);
                    sqlcmd.ExecuteNonQuery(); sqlcon.Close();
                }

            }
 
            //第二种方法：使用SqlDataAdapter.Update(DataTable)
            SqlCommand insertcmd = new SqlCommand("insert into Mytable(CompanyName,CompanyCode,Address,Owner,Memo) values(@CompanyName,@CompanyCode,@Address,@Owner,@Memo)", new SqlConnection(connString));
            insertcmd.Parameters.Add("@CompanyName", SqlDbType.NChar, 50, "CompanyName");
            insertcmd.Parameters.Add("@CompanyCode", SqlDbType.NChar, 50, "CompanyCode");
            insertcmd.Parameters.Add("@Address", SqlDbType.NChar, 50, "Address");
            insertcmd.Parameters.Add("@Owner", SqlDbType.NChar, 50, "Owner");
            insertcmd.Parameters.Add("@Memo", SqlDbType.NChar, 50, "Memo");
            SqlDataAdapter sqlda = new SqlDataAdapter();
            sqlda.InsertCommand = insertcmd;
            sqlda.Update(dt);


            //第三种方法：使用SqlBulkCopy.WriteToServer(Datatable)
            SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connString, SqlBulkCopyOptions.UseInternalTransaction);
            sqlbulkcopy.DestinationTableName = "Mytable"; //目标表，即数据要插入到哪个表去
            sqlbulkcopy.ColumnMappings.Add("CompanyName","CompanyName"); //数据源列名与目标表的属性的映射关系   数据源是DataTable，目标表即数据库表
            sqlbulkcopy.ColumnMappings.Add("CompanyCode", "CompanyCode");
            sqlbulkcopy.ColumnMappings.Add("Address", "Address");
            sqlbulkcopy.ColumnMappings.Add("Memo", "Memo");
            sqlbulkcopy.ColumnMappings.Add("Owner", "Owner");
            sqlbulkcopy.WriteToServer(dt); //数据源数据写入目标表

        }