使用许多参数调用存储过程的最佳代码是什么

I have realized that I have a very large method, which creates SqlParameter objects and adds them to SqlCommand (cmd). For instance:

我已经意识到我有一个非常大的方法,它创建SqlParameter对象并将它们添加到SqlCommand(cmd)。例如:

SqlParameter itemType = new SqlParameter
{
    ParameterName = "ItemType",
    Direction = ParameterDirection.Input,
    SqlDbType = SqlDbType.Int,
    Value = (int)item.ItemType
};

cmd.Parameters.Add(itemType);

A stored procedure has a lot of parameters by design and the design cannot be changed.

存储过程通过设计具有许多参数,并且设计不能改变。

I do not like to have several pagedowns of code to create the parameters. It is hard to read/support. Of course I can use regions here, but the question is about the code.

我不喜欢有几个页面下拉代码来创建参数。很难阅读/支持。当然我可以在这里使用区域,但问题是关于代码。

Do you have any ideas how can I improve my code?

您有什么想法我如何改进我的代码?

Currently, I see only one way here: I need to use custom attributes to specify parameter name, direction and db type for each item property. In this case, I need to use reflection and I am not sure that it is the best choice.

目前,我只看到一种方法:我需要使用自定义属性为每个项属性指定参数名称,方向和数据库类型。在这种情况下,我需要使用反射,我不确定它是最好的选择。

That do you think?

那你觉得呢?

Thank you.

4 个解决方案

#1

Another way to do this is to make the SqlCommand a member field of the class, and have a member method to initialize it.

另一种方法是使SqlCommand成为该类的成员字段,并有一个成员方法来初始化它。

This is similar to the code that Visual Studio generates when you create a Component, then drag a SqlCommand onto the design surface. If you configure the SqlCommand with a parameterized query or stored procedure with parameters, then it will generate code to create the SqlParameter objects and add them to the Parameters property.

这类似于Visual Studio在创建Component时生成的代码,然后将SqlCommand拖到设计图面上。如果使用带参数的参数化查询或存储过程配置SqlCommand,则它将生成用于创建SqlParameter对象的代码并将它们添加到Parameters属性。

To use thie SqlCommand later in your code, you would do:

要在代码中稍后使用SqlCommand,您可以:

m_Command.Parameters["@ParamName"].Value = value;

#2

Erland Sommerkog's article Arrays and Lists in SQL Server 2005 "describes a number of different ways to do this, both good and bad."

Erland Sommerkog的文章“SQL Server 2005中的数组和列表”描述了许多不同的方法,包括好的和坏的。

Duplicate of: Since there is no Sqlserver array parameter, what’s the best way to proceed?

重复:由于没有Sqlserver数组参数,最好的方法是什么?

#3

May be I understood your question wrongly, but why not just abstract away the code that instantiates the parameter and adds it to a command object to a helper function?

可能是我错误地理解了你的问题,但为什么不抽象出实例化参数的代码并将其添加到辅助函数的命令对象中呢?

Something like this...

像这样......

AddSqlParam("ParamName1",SqlDbType.Int,val1,ParameterDirection.Input, command); AddSqlParam("ParamName2",SqlDbType.String,val2,ParameterDirection.Input, command);

AddSqlParam(“ParamName1”,SqlDbType.Int,val1,ParameterDirection.Input,command); AddSqlParam(“ParamName2”,SqlDbType.String,val2,ParameterDirection.Input,command);

You can further refactor this by having commonly used parameters into little helper functions. For example most databases have an ID column.

您可以通过将常用参数放入小辅助函数中来进一步重构。例如,大多数数据库都有一个ID列。

So you could have a method like AddIdParam(command);

所以你可以有像AddIdParam(command)这样的方法;

I hope I conveyed my point.

我希望我转达了我的观点。

#4

This is a little bit more condensed format you could use

这是一个你可以使用的更简洁的格式

SqlCommand command = new SqlCommand();

command.Parameters.Add(new SqlParameter("@param1", SqlDbType.NVarChar, 255));
command.Parameters[command.Parameters.Count - 1].Value = "value1";

command.Parameters.Add(new SqlParameter("@param2", SqlDbType.NVarChar, 255));
command.Parameters[command.Parameters.Count - 1].Value = "value2";

command.Parameters.Add(new SqlParameter("@param3", SqlDbType.NVarChar, 255));
command.Parameters[command.Parameters.Count - 1].Value = "value3";

#1