C# 向 sqlparameter 类添加更多参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11044214/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Adding more number of parameters to sqlparameter class
提问by user1435482
I have to call a stored procedure but i am having more number of parameters is there any simple way to do this? or simply adding every parameter to sqlparameter class?? like below
我必须调用一个存储过程，但我有更多的参数有什么简单的方法可以做到这一点？还是简单地将每个参数添加到 sqlparameter 类？像下面
SqlCommand command = new SqlCommand("inserting", con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@Firstname", SqlDbType.NVarChar).Value = TextBox1.Text;
回答by Jon Senchyna
Be aware that Paramters.Addhas an overloadthat takes in a string and a DbType, so you don't have to call the Parameter constructor. You could replace the line you are currently using to add a new parameter:
请注意，它Paramters.Add有一个接受字符串和 DbType的重载，因此您不必调用 Parameter 构造函数。您可以替换当前用于添加新参数的行：
command.Parameters.Add(new SqlParameter("@Firstname", SqlDbType.NVarChar)).Value = TextBox1.Text;
with the following shorter (but functionally equivalent) line:
使用以下较短（但功能相同）的行：
command.Parameters.Add("@Firstname", SqlDbType.NVarChar).Value = TextBox1.Text;
If you want to add more parameters, you would simply add them to the Parametersproperty of your command, like so:
如果要添加更多参数，只需将它们添加到命令的Parameters属性中，如下所示：
SqlCommand command = new SqlCommand("inserting", con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Firstname", SqlDbType.NVarChar).Value = TextBox1.Text;
command.Parameters.Add("@Lastname", SqlDbType.NVarChar).Value = TextBox2.Text;
Aside from that, have you tried using Parameters.AddWithValue? You can use this if the data type of your column maps to the type of your value in C#. You can find a mapping of C# to SQL Server data typse here.
除此之外，您是否尝试过使用Parameters.AddWithValue？如果列的数据类型映射到 C# 中的值类型，则可以使用它。您可以在此处找到 C# 到 SQL Server 数据类型的映射。
You would use it like so:
你会像这样使用它：
// Assume your sproc has a parameter named @Age that is a SqlInt32 type
int age = 5;
// Since age is a C# int (Int32), AddWithValue will automatically set
// the DbType of our new paramter to SqlInt32.
command.Parameters.AddWithValue("@Age", 5);
If you need to specify the SqlDbType, AddWithValue returns the parameter you just added, so it's as simple as adding an extra statement to set the DbType propertyat the end, although at this point, you're better off just using the original .Add function and setting the value.
如果需要指定 SqlDbType，AddWithValue 会返回刚刚添加的参数，因此就像在末尾添加额外的语句来设置DbType 属性一样简单，尽管此时，您最好只使用原始的 .Add功能和设置值。
command.Parameters.AddWithValue("@Firstname", TextBox1.Text).DbType = SqlDbType.NVarChar;
回答by Damith
You can use dapper-dot-net
您可以使用dapper-dot-net
sample code:
示例代码：
var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });
Insert example:
插入示例：
connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)",
new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }
).IsEqualTo(3); // 3 rows inserted: "1,1", "2,2" and "3,3"
回答by Adeel Ahmed
Use Array of type SqlParameter and insert that into SqlCommand
使用 SqlParameter 类型的数组并将其插入到 SqlCommand
SqlCommand Comm = new SqlCommand("Command text", new SqlConnection("Connection String");
SqlParameter[] param = {new SqlParameter("@Name","Value"),
new SqlParameter("@Name","Value"),
........
};
Comm.Parameters.AddRange(param);
回答by marc_s
Just call the command.Parameters.Addmethod multiple times:
只需command.Parameters.Add多次调用该方法：
SqlCommand command = new SqlCommand("inserting", con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Firstname", SqlDbType.NVarChar, 100).Value = TextBox1.Text;
command.Parameters.Add("@Lastname", SqlDbType.NVarChar, 100).Value = TextBox2.Text;
command.Parameters.Add("@City", SqlDbType.NVarChar, 100).Value = TextBox3.Text;
command.Parameters.Add("@ID", SqlDbType.Int).Value = Convert.ToInt32(TextBox4.Text);
....... and so on .....
回答by Chagbert
The command.Parameters.Addis deprecated. Rather use command.Parameters.AddWithValue.
For this, you would call it many times for each parameter.
在command.Parameters.Add已被弃用。而是使用command.Parameters.AddWithValue. 为此，您将为每个参数多次调用它。
回答by Sachin Verma
// Mention size of the nvarchar column , here i give 500 , you can use its length for @Firstname as you mention in database according to your database
SqlCommand command = new SqlCommand("inserting", con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@Firstname", SqlDbType.NVarChar,500).Value = TextBox1.Text;
