C# Run a procedure without specifying a parameter name

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.

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

command.Parame开发者_如何学Pythonters.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);

The name of the parameter in the stored procedure may change from CustomerID to 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.

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.

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
}

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:

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

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

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

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

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.