.net 大数据量的批量插入

概述使用Insert循环插入（每次一条） 使用BulkCopy写入； 1,创建一个DataTable dt2,using (SqlBulkCopy sbc = new SqlBulkCopy(目标库连接字符串))//目标库的链接字符串            {                sbc.BulkCopyTimeout = 600;                sbc.Batch

使用Insert循环插入（每次一条）

使用Bulkcopy写入；

1,创建一个Datatable dt2,using (sqlBulkcopy sbc = new sqlBulkcopy(目标库连接字符串))//目标库的链接字符串            {                sbc.BulkcopyTimeout = 600;                sbc.BatchSize = dt.Rows.Count;                sbc.Destinationtablename = "目标表名";//目标表                sbc.WritetoServer(dt);            }}

第二种写法            sqlDataReader reader =commandSourceData.ExecuteReader();                       using (sqlConnection destinationConnection = new sqlConnection(connectionString))            {                destinationConnection.open();                using (sqlBulkcopy bulkcopy =new sqlBulkcopy(destinationConnection))                {                    bulkcopy.Destinationtablename = "dbo.BulkcopyDemoMatchingColumns";                                        try                    {                                               bulkcopy.WritetoServer(reader);                    }                    catch (Exception ex)                    {                        Console.Writeline(ex.Message);                    }                    finally                    {                        // Close the sqlDataReader. The sqlBulkcopy                        // object is @R_419_6843@matically closed at the end                        // of the using block.                        reader.Close();                    }                }                               long countEnd = System.Convert.ToInt32(                    commandRowCount.ExecuteScalar());                Console.Writeline("Ending row count = {0}", countEnd);                Console.Writeline("{0} rows were added.", countEnd - countStart);                Console.Writeline("Press Enter to finish.");                Console.Readline();            }        }

使用表值参数写入。

     sqlParameter[] paramters = new sqlParameter[]                     {                         sqlParamHelper.MakeInParam("@dt",sqlDbType.Structured)                    };                DataSet ds = sqlHelper.ExecuteDataset(ComputingDB_ConnString, CommandType.StoredProcedure, "存储过程名", paramters);                string[] sqls = new string[]                {                    @" insert into table1                      select * from @dt                    ",               };                using (sqlConnection connection = new sqlConnection(StatDB_ConnString))                {                    connection.open();                    sqlTransaction trans = connection.BeginTransaction();                    try                    {                        string[] typenames = new string[] { "表类型名" };                        for (int i = 0; i < sqls.Length; i++)                        {                            paramters[0].Value = ds.tables[i];                            paramters[0].Typename = typenames[i];                            sqlHelper.ExecuteNonquery(trans, CommandType.Text, sqls[i], paramters);                        };                        trans.Commit();                    }                    catch (Exception ex)                    {                        trans.Rollback();                        throw;                    }                }

总结：Insert比较适合于少量数据的添加，如果是大批量的数据，只能考虑使用Bulkcopy或表值参数方式，后俩者相比于前者会有一个量级的提升，随着数据量的提升这个差别会越来越大

总结

以上是内存溢出为你收集整理的.net 大数据量的批量插入全部内容，希望文章能够帮你解决.net 大数据量的批量插入所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。