blocks|key|1037631|text|Erland+Sommerkog的文章Arrays+and+Lists+in+SQL+Server+2005“描述了许多不同的方法来做到这一点，包括好的和坏的。”|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1037632|的副本：Since+there+is+no+Sqlserver+array+parameter,+what’s+the+best+way+to+proceed?|style|BOLD|1037633|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sommarskog.se/arrays-in-sql-2005.html|1|https://stackoverflow.com/questions/1014571/since-there-is-no-sqlserver-array-parameter-whats-the-best-way-to-proceed^0|J|Z|0|0|1|27|4|24|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]]|C|$]]|$1|D|3|E|5|6|7|V|8|@$A|W|B|X|F|G]]|9|@$A|Y|B|Z|1|10]]|C|$]]|$1|H|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]|P|$5|K|L|M|C|$N|Q]]]]

Erland Sommerkog's article <a href="http://www.sommarskog.se/arrays-in-sql-2005.html" rel="nofollow noreferrer">Arrays and Lists in SQL Server 2005</a> "describes a number of different ways to do this, both good and bad."

Duplicate of: <a href="https://stackoverflow.com/questions/1014571/since-there-is-no-sqlserver-array-parameter-whats-the-best-way-to-proceed">Since there is no Sqlserver array parameter, what’s the best way to proceed?</a>

blocks|key|1383866|text|也许我误解了你的问题，但为什么不把实例化参数的代码抽象出来，并将其添加到命令对象的帮助函数中呢？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1383867|就像这样..。|1383868|AddSqlParam("ParamName1"，SqlDbType.Int，val1，ParameterDirection.Input，命令)；AddSqlParam("ParamName2"，SqlDbType.String，val2，ParameterDirection.Input，命令)；|1383869|您可以通过将常用参数转换为小帮助器函数来进一步重构。例如，大多数数据库都有一个ID列。|1383870|所以你可以有一个像AddIdParam(命令)这样的方法；|1383871|我希望我已经表达了我的观点。|1383872|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|P|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Q|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

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);

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

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

I hope I conveyed my point.

blocks|key|1381671|text|这是您可以使用的更精简的格式|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1381672|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";|code-block|syntax|javascript|1381673|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This is a little bit more condensed format you could use

<pre><code>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";
</code></pre>

blocks|key|82371|text|另一种方法是使SqlCommand成为类的成员字段，并使用成员方法对其进行初始化。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|82372|这类似于Visual+Studio在创建组件，然后将SqlCommand拖到设计图面上时生成的代码。如果使用参数化查询或带参数的存储过程配置SqlCommand，则它将生成代码来创建SqlParameter对象并将它们添加到parameters属性中。|82373|要在稍后的代码中使用此SqlCommand，您将执行以下操作：|82374|m_Command.Parameters["@ParamName"].Value+=+value;|code-block|syntax|javascript|82375|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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

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.

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

<pre><code>m_Command.Parameters["@ParamName"].Value = value;
</code></pre>

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

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

cmd.Parameters.Add(itemType);
</code></pre>

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.

What is the best code to call stored procedure with many parameters

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我已经意识到我有一个非常大的方法，它创建SqlParameter对象并将它们添加到SqlCommand (cmd)中。例如：SqlParameter itemType = new SqlParameter{    ParameterName = "ItemType",    Direction = ParameterD...

问调用具有多个参数的存储过程的最佳代码是什么
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问调用具有多个参数的存储过程的最佳代码是什么EN