C#环路和质量插入 [英] C# loops and mass insert
问题描述
后续code将在我的数据库中插入一些值。它得到6个随机值,放东西在一个数组,然后将其插入到数据库中。
The follow code will insert some values in my database. It gets 6 random values, puts the stuff in an array and then inserts it in the database.
public void LottoTest(object sender, EventArgs e)
{
Dictionary<int, int> numbers = new Dictionary<int, int>();
Random generator = new Random();
while (numbers.Count < 6)
{
numbers[generator.Next(1, 49)] = 1;
}
string[] lotto = numbers.Keys.OrderBy(n => n).Select(s => s.ToString()).ToArray();
foreach (String _str in lotto)
{
Response.Write(_str);
Response.Write(",");
}
var connectionstring = "Server=C;Database=lotto;User Id=lottoadmin;Password=password;";
using (var con = new SqlConnection(connectionstring)) // Create connection with automatic disposal
{
con.Open();
using (var tran = con.BeginTransaction()) // Open a transaction
{
// Create command with parameters (DO NOT PUT VALUES IN LINE!!!!!)
string sql =
"insert into CustomerSelections(val1,val2,val3,val4,val5,val6) values (@val1,@val2,@val3,@val4,@val5,@val6)";
var cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("val1", lotto[0]);
cmd.Parameters.AddWithValue("val2", lotto[1]);
cmd.Parameters.AddWithValue("val3", lotto[2]);
cmd.Parameters.AddWithValue("val4", lotto[3]);
cmd.Parameters.AddWithValue("val5", lotto[4]);
cmd.Parameters.AddWithValue("val6", lotto[5]);
cmd.Transaction = tran;
cmd.ExecuteNonQuery(); // Insert Record
tran.Commit(); // commit transaction
Response.Write("<br />");
Response.Write("<br />");
Response.Write("Ticket has been registered!");
}
}
}
什么是循环的最佳方式,并插入MASS条目到数据库中。比方说,通过C#100000条记录?我希望能够用我的方法来生成随机数和利用,我有太多的插件。
What is the best way to loop and insert MASS entries into the database. Lets say, 100,000 records via C#? I want to be able to generate the random numbers by my method and utilize the insert which i have too..
推荐答案
对于真正大规模的插入, SqlBulkCopy的
是你的朋友。最简单的,但低效的方式做,这是刚刚填补了数据表
与数据,并抛出,在 SqlBulkCopy的
,但它可以通过欺骗的的IDataReader
完成快两倍(相信我,我已经超时的话)。我最近搬到这个code到 FastMember 为了方便,所以你可以这样做:
For true large scale inserts, SqlBulkCopy
is your friend. The easy but inefficient way to do this is just to fill a DataTable
with the data, and throw that at SqlBulkCopy
, but it can be done twice as fast (trust me, I've timed it) by spoofing an IDataReader
. I recently moved this code into FastMember for convenience, so you can just do something like:
class YourDataType {
public int val1 {get;set;}
public string val2 {get;set;}
... etc
public DateTime val6 {get;set;}
}
然后创建一个迭代器块的(即非缓冲只转发读者):
then create an iterator block (i.e. a non-buffered forwards only reader):
public IEnumerable<YourDataType> InventSomeData(int count) {
for(int i = 0 ; i < count ; i++) {
var obj = new YourDataType {
... initialize your random per row values here...
}
yield return obj;
}
}
然后
var data = InventSomeData(1000000);
using(var bcp = new SqlBulkCopy(connection))
using(var reader = ObjectReader.Create(data))
{ // note that you can be more selective with the column map
bcp.DestinationTableName = "CustomerSelections";
bcp.WriteToServer(reader);
}
这篇关于C#环路和质量插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!