C # Run a procedure without specifying a parameter name

Question

How can I execute a stored procedure that takes parameters without specifying the name prameters? The parameter name in the stored procedure can change from CustomerID to CustID, so I do not want to change the code.

Instead of doing what is indicated 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 want to do something like this -

 command.Parameters.Add(startDate, customerID); 

Answer 1

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

Spank the person who does it.

Parameter names are your reliable way to identify a parameter. Another option is a sequence that seems much more flaky.

Answer 2

I do not think that you can create an SqlParameter object without specifying its name. However, you must use the DeriveParameters method ( see MSDN ) to get a set of parameters with names automatically retrieved from the SQL server.

Here you can find here . It looks something 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 } 

Answer 3

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

Answer 4

Using parameters Without parameters is only possible with OdbcCommand and OleDbCommand .

Answer 5

You can create an unnamed SQL parameter if you make its name empty or empty after adding it 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; 

Answer 6

You can use SQL exec , which does not ask for parameter names:

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

If your source source is unreliable, be sure to avoid single quotes in string parameters. This is done for stringParameter in the snippet above.