插入写入

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{}
    }
}