哪种方法可以更好地将数据传递给程序？ [英] Which is a better approach to pass data to procedures?

问题描述

有些情况下，我已经将数据作为各种参数传递给过程，甚至我已经通过为整个数据创建单个XML参数将数据传递给过程。



所以我想知道在以不同方式传递各种参数或将数据作为单个XML元素传递之间将数据传递给过程的更好方法是什么？

将数据作为XML传递的情况比传递参数更有优势吗？



我尝试过的事情：



在以不同方式传递各种参数或将数据作为单个XML元素传递中，哪种方法可以将数据传递给过程？

以XML格式传递数据的情况优于传递参数？

There have been instances where I've passed data to procedures as various parameters and even I've passed data to the procedure by creating a single XML parameter for whole data to send to procedure.

So I want to know which is a better approach of passing data to procedure among "passing various parameters differently" or "passing data as a single XML element"?;
Situations where passing data as XML provides advantage over passing parameters?

What I have tried:

Which is a better approach of passing data to procedure among "passing various parameters differently" or "passing data as a single XML element"?;
Situations where passing data as XML provides advantage over passing parameters?

推荐答案

引用：

我想知道哪个是将数据传递给过程的更好方法

I want to know which is a better approach of passing data to procedure

SQ L·作为避免 SQL注入的参数[ ^ ]。

我想说制作一个集中数据访问的对象（因此可以更容易在单元测试中进行测试和模拟）并且在那个中进行平均参数检查，转义序列检查然后作为参数传递，你应该有一些皮带和吊带，可以优化你的数据连接使用等。

Id's say make an object to centralize data access (as such can more easily be tested and mocked out in unit tests) and in that one have your average parameter checking, escape sequence checking and then pass as parameter and you should bee sort of belt and suspenders and can optimize your data connections usage etc.

using (var cn = GetConnection())
        {
            using(var cmd = new SqlCommand("spMyProcedure", cn))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@id", invoiceNumber);
                cmd.Parameters.AddWithValue("@comment", FlareOnBadChars(comment));
                cmd.Parameters.AddWithValue("@note", FlareOnBadChars(note));
                var affect = (int)cmd.ExecuteScalar();

                if (affect != 1)
                    throw new DataConcernsException(string.Format("Expected to insert data but failed with data: '{0}','{1}','{2}'", invoiceNumber, comment, note));
            }
        } //Implicitly dispose already calls close on your connection

a不错的插件是检查转义序列的字符串类型参数，如下面的简化版...你真的不是想要从参数绑定或sql中得到奇怪的异常......如果发生这种事情，更好地确切地知道出了什么问题

a nice addon is to check your string type parameters for escape sequence like the simplified one below ... you don't really want odd exceptions from parameter binding or sql ... much better to know exactly what's wrong if this thing occurs

private string FlareOnBadChars(string source)
       {
           if (!Regex.IsMatch(source, @"\A[^\\]+\Z"))  //TODO: add other things that you don't like before the ]
               throw new DataConcernsException("That's not a nice string");
           return source;
        }

作为替代方案，请考虑使用类似实体的存储库框架框架，这将为您处理参数处理，你不能避免这样做，所以说;）

As an alternative consider using a repository framework like entity framework, this will take care of parameter handling for you and you cannot avoid doing it right so to say ;)

唯一正确的答案是，它取决于。 :)



如果您将一组值传递给存储过程，那么传递单个参数通常会更好。只有当您需要传递超过允许的最大参数数量时，才需要不同的解决方案。但由于每个程序的限制是 2100 ，如果你到达它，你迫切需要重新评估你的数据库设计！



如果您将多个值传递给存储过程，则有几个选项：

• 对于数字列表，您可以将它们作为单个分隔的字符串，并使用 STRING_SPLIT [ ^ ]或自定义字符串拆分函数来提取值列表;
• 您可以使用XML参数，将值存储为子元素：
  
  使用XML将列表作为参数传递到SQL Server中 - 简单谈话 [ ^ ]
  
  如文章所示，性能下降得非常快大型列表。
• 如果您使用的是SQL Server 2008或更高版本，则可以使用表值参数：
  
  表值参数| Microsoft Docs [ ^ ]
  
  使用SQL Server的表值参数 - Brent OzarUnlimited® [ ^ ]

The only correct answer is, "it depends". :)

If you're passing a single set of values to a stored procedure, then it's usually better to pass individual parameters. The only time you'd need a different solution would be if you needed to pass more than the maximum number of parameters allowed. But since that limit is 2100 per procedure, if you ever reach it, you urgently need to re-evaluate your database design!

If you're passing multiple values to a stored procedure, you have several options:

• For lists of numbers, you might be able to pass them as a single delimited string, and use STRING_SPLIT[^] or a custom string splitting function to extract the list of values;
• You can use an XML parameter, with the values stored as child elements:
  Using XML to pass lists as parameters in SQL Server - Simple Talk[^]
  As that article shows, the performance degrades quite rapidly for large lists.
• If you're using SQL Server 2008 or later, you can use a Table Valued Parameter:
  Table-Valued Parameters | Microsoft Docs[^]
  Using SQL Server's Table Valued Parameters - Brent Ozar Unlimited®[^]

这篇关于哪种方法可以更好地将数据传递给程序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！