C＃运行过程而不指定参数名称

How can I execute a stored procedure that takes in parameters without having to specify the prameters name? The name of the parameter in the stored procedure may change from CustomerID to CustID so I don't want to have to keep changing my code.

如何执行一个接收参数的存储过程而不必指定参数名称？存储过程中的参数名称可能会从CustomerID更改为CustID，因此我不想继续更改我的代码。

Rather than doing what is provided below where you specify the parameter name -

而不是执行下面提供的指定参数名称的内容 -

command.Parameters.Add("@dtStart", SqlDbType.DateTime);
command.Parameters["@dtStart"].Value = startDate;
command.Parameters.Add("@CustomerID", SqlDbType.NChar);
command.Parameters["@CustomerID"].Value = customerID;

I am looking to do something like this -

我希望做这样的事情 -

command.Parameters.Add(startDate, customerID);

6 个解决方案

#1

The name of the parameter in the stored procedure may change from CustomerID to CustID

存储过程中的参数名称可能会从CustomerID更改为CustID

Slap the person who does that.

扯掉那样做的人。

Parameter names are your reliable way of identifying a parameter. The other option is sequence, seems a lot more flaky.

参数名称是识别参数的可靠方法。另一种选择是序列，看起来更加片状。

#2

I don't think you can create a SqlParameter object without specifying its name. However, you should be able to use the DeriveParameters method (see MSDN) to get a collection of parameters with the names automatically retreived from the SQL server.

我不认为你可以创建一个SqlParameter对象而不指定其名称。但是，您应该能够使用DeriveParameters方法（请参阅MSDN）获取参数集合，其中的名称将自动从SQL Server中重新获取。

You can find an example here. It looks roughly like this:

你可以在这里找到一个例子。看起来大致如下：

SqlCommand command = // create a command for calling the stored procedure
SqlCommandBuilder.DeriveParameters(command);

// Now you can set values of parameters in a loop
for(int i = 0; i < command.Parameters.Length; i++) {
  var parameter = command.Parameters[i]
  // Set value of ith parameter
}

#3

Use Parameter Discovery, scroll down on: http://msdn.microsoft.com/en-us/library/ff664692(PandP.50).aspx

使用参数发现，向下滚动：http：//msdn.microsoft.com/en-us/library/ff664692 (PandP.50）.aspx

#4

Using Unnamed parameters is only possible with OdbcCommand and OleDbCommand object parameters.

只有OdbcCommand和OleDbCommand对象参数才能使用未命名参数。

#5

You can create a nameless SQL parameter if you force its name to null or empty after it's been added to the Parameters collection, something like this:

如果在将名称添加到Parameters集合后强制其名称为null或为空，则可以创建无名SQL参数，如下所示：

var par = cmd.CreateParameter();
par.Value = myValue;
cmd.Parameters.Add(par); // this will change the name to "ParameterX"
par.ParameterName = null;

#6

-2

You could use SQL's exec, which does not ask for parameter names:

您可以使用SQL的exec，它不要求参数名称：

command.CommandText = string.Format(
    "exec dbo.YourProcedure {0}, '{1}'",
    intParameter,
    stringParameter.Replace("'","''")
);
command.ExecuteNonQuery();

If your parameter source is untrustworthy, be sure to escape single quotes in string parameters. It's done for stringParameter in the snippet above.

如果参数源不可信，请确保在字符串参数中转义单引号。它是在上面的代码片段中为stringParameter完成的。

#1