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

时间：2023-03-09 19:20:44
【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 = ; i < ; 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, , "CompanyName");

            insertcmd.Parameters.Add("@CompanyCode", SqlDbType.NChar, , "CompanyCode");

            insertcmd.Parameters.Add("@Address", SqlDbType.NChar, , "Address");

            insertcmd.Parameters.Add("@Owner", SqlDbType.NChar, , "Owner");

            insertcmd.Parameters.Add("@Memo", SqlDbType.NChar, , "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); //数据源数据写入目标表

        }

相关文章