使用存储过程在数据库中插入列表记录

Question

我有一个使用存储过程插入SQL Server数据库的记录列表。目前我是这样做的，但还有更好的方法吗？

我在应用程序的高峰时段每秒插入100-200行。存储过程只获取值并插入新行

    public void InsertRecords(List<stRecord> records)
    {
        foreach (var item in records)
        {
            if (CheckforDuplicateRecord(item) == false)
            {
                using (con = new SqlConnection(connectionString))
                {
                    con.Open();
                    SqlCommand cmd = new    SqlCommand(StoredProcedures.Service_Insert_record.ToString(), con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@item1", SqlDbType.NChar);
                    cmd.Parameters.Add("@item2", SqlDbType.NChar);
                    cmd.Parameters[0].Value = item.localUsername;
                    cmd.Parameters[1].Value = item.BetfairUsername;

                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception exp)
                    {
                        throw exp;
                    }
                }
            }
        }
    }

Answer 1

这正是表值参数的用途-您可以一次传递列表。

在SQL Server中：

CREATE TYPE dbo.Usernames AS TABLE
(
  localUsername   NVARCHAR(32),
  BetfairUsername NVARCHAR(32)
);
GO
CREATE PROCEDURE dbo.Service_Insert_MultipleRows
  @u AS dbo.Usernames READONLY
AS
BEGIN
  SET NOCOUNT ON;
  INSERT INTO dbo.DestinationTable(localUsername, BetfairUsername)
    SELECT localUsername, BetfairUsername FROM @u;
END
GO

现在使用C#：

DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("localUsername"));
tvp.Columns.Add(new DataColumn("BetfairUsername"));
foreach(var item in records)
{ 
    tvp.Rows.Add(item.localUsername, item.BetfairUsername); 
}
using (con)
{
    SqlCommand cmd = new SqlCommand("Service_Insert_MultipleRows", con);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@u", tvp);
    tvparam.SqlDbType = SqlDbType.Structured;
    con.Open();
    cmd.ExecuteNonQuery();
}

Answer 2

您可以做一件显而易见的事情：在方法开始时只创建一次SqlCommand-一遍又一遍地创建它毫无意义！

public void InsertRecords(List<stRecord> records)
{
   using (con = new SqlConnection(connectionString))
   using (SqlCommand cmd = new SqlCommand(StoredProcedures.Service_Insert_record.ToString(), con))
   {
       cmd.CommandType = CommandType.StoredProcedure;
       // are those paramters *REALLY* just 1 character long?? 
       // that's what you have now, with the way you define it!
       cmd.Parameters.Add("@item1", SqlDbType.NChar);
       cmd.Parameters.Add("@item2", SqlDbType.NChar);
       // otherwise, you need to define the LENGTH of the NCHAR parameter!
       // cmd.Parameters.Add("@item2", SqlDbType.NChar, 15);
       foreach (var item in records)
       {
           if (CheckforDuplicateRecord(item) == false)
           {
              cmd.Parameters["@item1"].Value = item.localUsername;
              cmd.Parameters["@item2"].Value = item.BetfairUsername;
              try
              {
                 con.Open();
                 cmd.ExecuteNonQuery();
                 con.Close();
              }
              catch (Exception exp)
              {
                 throw;
              }
            }
       }
    }