c# - 应该多久关闭/打开一次连接？

Question

我正在从 C# 逐行写入 SQL 服务器上的两个表。

我的 C# 应用程序将参数传递给 2 个存储过程，每个存储过程都将行插入表中。

每次调用存储过程时，我都会打开然后关闭连接。

我需要将大约 100m 行写入数据库。

我是否应该在每次调用存储过程时关闭和打开连接？

这是我正在做的一个例子:

public static void Insert_TestResults(TestResults testresults)
        {
            try
            {
                DbConnection cn = GetConnection2();
                cn.Open();

                // stored procedure
                DbCommand cmd = GetStoredProcCommand(cn, "Insert_TestResults");
                DbParameter param;

                param = CreateInParameter("TestName", DbType.String);
                param.Value = testresults.TestName;
                cmd.Parameters.Add(param);


                if (testresults.Result != -9999999999M)
                {
                    param = CreateInParameter("Result", DbType.Decimal);
                    param.Value = testresults.Result;
                    cmd.Parameters.Add(param);
                }


                param = CreateInParameter("NonNumericResult", DbType.String);
                param.Value = testresults.NonNumericResult;
                cmd.Parameters.Add(param);

                param = CreateInParameter("QuickLabDumpID", DbType.Int32);
                param.Value = testresults.QuickLabDumpID;
                cmd.Parameters.Add(param);
                // execute
                cmd.ExecuteNonQuery();

                if (cn.State == ConnectionState.Open)
                    cn.Close();

            }
            catch (Exception e)
            {

                throw e;
            }

        }

这是服务器上的存储过程:

USE [SalesDWH]
GO
/****** Object:  StoredProcedure [dbo].[Insert_TestResults]    Script Date: 12/26/2011 10:45:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Insert_TestResults]
    -- Add the parameters for the stored procedure here

    @TestName varchar (500),
    @Result decimal (18,4)=null,
    @NonNumericResult varchar (50)=null, 
    @QuickLabDumpid int

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

INSERT INTO [SalesDWH].[dbo].[TestResults]
           ([TestName]
           ,[Result]
           ,nonnumericresult
           ,[QuickLabDumpid])
     VALUES
           (@TestName,@Result,@nonnumericresult,@QuickLabDumpID)


END

对于大约 1 亿行，大约需要 3 天。这对我来说似乎太慢了。我该怎么做才能加快速度？多次打开/关闭连接的标准是什么？

Answer 1

给你多一个选择。 .NET 框架有 SqlBulkCopy从 2.0 开始上课。您必须做对的主要事情是确保 DataTable架构与您的表匹配。在您的测试用例中，是这样的:

private void _initDataTable() {
  dt = new DataTable();
  dt.Columns.Add(new DataColumn()  {
    DataType = Type.GetType("System.String"), 
    ColumnName = "TestName"
  });
  dt.Columns.Add(new DataColumn()  {
    DataType = Type.GetType("System.Decimal"), 
    ColumnName = "Result"
  });
  dt.Columns.Add(new DataColumn()  {
    DataType = Type.GetType("System.String"), 
    ColumnName = "NonNumericResult"
  });
  dt.Columns.Add(new DataColumn()  {
    DataType = Type.GetType("System.Int32"), 
    ColumnName = "QuickLabDumpid"
  });
}

数据访问代码如下所示:

private void _insertData() {
  using (var c = new SqlConnection(CS)) {
    c.Open();
    using (var trans = c.BeginTransaction()) {
      try {
        using (var bc = new SqlBulkCopy(
          c, SqlBulkCopyOptions.TableLock, trans))
        {
          bc.DestinationTableName = "dbo.Insert_TestResults";
          bc.WriteToServer(dt);
        }
        trans.Commit();
      }
      catch (Exception e) {
        trans.Rollback();
        throw;
      }
    }
  }
}  

测试了 1000 万条这样的记录:

private void _fillDataTable() {
  int batchToInsert = 1000000;
  int numberOfTimes = 10;
  int recordCounter = 1;
  for (int i = 0; i < numberOfTimes; ++i) {
    for (int j = 0; j < batchToInsert; j++) {
      var row = dt.NewRow();
      row[0] = string.Format("TestName{0}", recordCounter);
      row[1] = (decimal) i;
      row[2] = string.Format("NonNumericResult{0}", recordCounter);
      row[3] = i;
      dt.Rows.Add(row);
      recordCounter += 1;
    }
    _insertData();
    dt.Clear();
  }
}

在我的开发机器上花了两分半钟多一点。您可能想要尝试一次批处理多少条记录。 (不像上面的测试用例那样是 100 万)很明显，你将 10 倍以上的数据放入表中(猜测你的实时数据会更大)，但我非常怀疑这种方法会3 天:)

无论您决定使用哪种方法，祝您好运。

编辑:如果不是很明显，我忘了提及 - 因为您在设置 DestinationTableName 属性时指定了表名，所以这就是您所需要的 - 没有存储过程或任何其他 SQL 语句。