通过一次数据库访问执行多个存储过程

Question

我有很多遗留数据访问代码，主要是 SqlCommand 和存储过程调用，我们用它们来执行大量插入语句到数据库中。 只要 SQL 服务器与应用程序位于同一台计算机上，就有可接受的性能，但现在我们正在尝试将一些数据移动到 SQL Azure。

问题是我们的代码为每条要插入的记录调用 SP，这会导致多次访问数据库，并且当不在同一服务器上时，需要一些时间。

var conn = new SqlConnection("connString")
var cmd = new SqlCommand(conn, "spMyStoreProc");
cmd.Params.Add("@a", SqlDbType.VarChar, 10);
cmd.Params.Add("@b", SqlDbType.Int);

using(conn)
{
 conn.Open();
 foreach(var rec in recordsToInsert)
 {
   cmd.Parameters["@a"].Value = rec.A;
   cmd.Parameters["@b"].Value = rec.B;
   cmd.ExecuteNonQuery();
 }
 conn.Close();
}

我已经尝试了上面的代码，无论是否有事务。

我还尝试使用“批处理”SQL 语句在每次访问服务器时执行多个 SP。 像这样：

var cmd = new SqlCommand(conn);
cmd.CommandText = "EXEC spMyStoreProc @a='a' @b=2; EXEC spMyStoreProc @a='b' @b=4;"

它极大地提高了操作的性能，但由于我有相当多的 SP，其中每个 SP 大约有 20-50 个参数，因此为该数据访问组件中的所有插入命令编写此代码变得非常乏味。

这是实现此目的的最佳方法吗？或者我可以以某种方式告诉 ADO.NET 我想批量执行我的调用（没有找到任何暗示其可能的内容，但觉得我至少应该问一下）以避免每个 SP 之间的网络延迟等称呼？

如果没有，没有人知道有什么好方法可以实现这一点，而不必“手工”编写它，并且由于它是遗留应用程序，我无法完全更改数据层。 是否有任何应用程序可以采用带参数的 SqlCommands 并生成它们将执行的 TQL？

提前致谢

Answer 1

您可能应该有一个存储过程，它调用所有其他存储过程 - 这可能是最少的工作量。因此，从代码中，您只调用存储过程一次......因此，鉴于它们是您每次传递的相同参数（因为您的代码似乎暗示了这一点），您基本上会做这样的事情：

CREATE PROCEDURE sp_RunBatch(@param1, @param2, etc [all the parameters you need])
AS

exec spMyStoreProc @a='a'
exec spMyStoreProc2 @b='b' 

这样做的优点是很多，其中一些是全部集中的，您甚至可以将它们全部包装在一个事务中，以免进行脏插入（假设它们都相互依赖）。

另外，如果您不想向每个 SP 传递 20/30 个参数，您可能需要为您可以传递的每组参数创建一个用户表定义的数据类型。这样每个 SP 就获得 1 或 2 个参数，代码变得更加简单和可读。

编辑：

这是用户定义的表类型的一个很好的参考：http:// msdn.microsoft.com/en-us/library/bb675163.aspx

这是将表值类型传递到 SQL Server 的方法：http://msdn.microsoft.com/en-us/library/bb675163.aspx

Answer 2

MR 方法的替代方法是将所有参数作为 XML 文档发送，然后解析 XML 文档以提取参数。这可能会稍微简化界面。

不过，当您讨论将所有命令链接到单个字符串中的可能性时，我认为您正在做一些事情。但不要手动构建它们，而是考虑构建 SqlCommand 对象的扩展方法，该方法返回单个字符串以供执行，利用 sp_executesql 语法，并在一次传递中执行整个字符串。

因此，您将有一个如下所示的循环，并且您将调用一个新的 ToInlineSql 扩展方法：

string sqlCommand = "";
foreach(var rec in recordsToInsert) 
{   cmd.Parameters["@a"].Value = rec.A;   
    cmd.Parameters["@b"].Value = rec.B;
    sqlCommand += cmd.ToInlineSql(); 
}
// execute sqlCommand 

ToInlineSql 扩展方法可能如下所示（peuso 代码，您必须添加某些内容，例如检查数据类型等上）[这里是 sp_executesql：

public static class SqlCmdExt 
{
      public static string ToInlineSql(this SqlCommand cmd)
      {
            string sql = "sp_executesql " + cmd.CommandText  ;
            foreach (SqlParameter p in cmd.Parameters)
            {
                  sql += ", @" + p.Name + " " + p.DataType.ToString() ;
                  sql += ", " + p.Name + " = " + p.Value;
            }
            sql += ";";
            return sql;
      }
}