插入写入

Question

代码：

SqlConnection sqlc = new SqlConnection(
                        "Data Source=" + Environment.MachineName + @"\SQLEXPRESS;" +
                        "Integrated security=true;" +
                        "database=someDB");

SqlCommand sqlcmd;
string tmp = string.Empty;

for(int i = 0; i < 100000; i++)
{
    tmp += "inserto into [db].[Files](...) values (...);"
}

sqlcmd = new SqlCommand(tmp, sqlc);
try { sqlc.Open(); sqlcmd.ExecuteNonQuety(); } cathc{}

仅插入< 1000 次写入 如何写全部100000次写入？可能会销毁并创建 sqlcmd？

Answer 1

您可能应该将命令分成更小的命令。假设每个 SqlCommand 插入 500 条记录，直到插入所有记录为止。

int totalRecordsToWrite = 100000;
int maxRecordsPerCommand = 500;

SqlConnection sqlc = new SqlConnection(
                        "Data Source=" + Environment.MachineName + @"\SQLEXPRESS;" +
                        "Integrated security=true;" +
                        "database=someDB");

int currentRecord = 0;

while (currentRecord < totalRecordsToWrite)
{
    SqlCommand sqlcmd;
    string tmp = string.Empty;

    for(int j = 0; j < maxRecordsPerCommand; j++)
    {
        currentRecord++;

        if (currentRecord >= totalRecordsToWrite)
          break;

        // Insert record "currentRecord" of the 100000 here.

        tmp += "inserto into [db].[Files](...) values (...);"
    }

    using (sqlcmd = new SqlCommand(tmp, sqlc))
    {
       try { sqlc.Open(); sqlcmd.ExecuteNonQuety(); } catch{}
    }
}