2

I was just wondering if there is a way to execute a stored procedure with out naming the parameters. Meaning that C# resolves the parameters in the order they're declared within the stored procedure. 

public static DataTable GetRelatedResources(string StoredProcedure, object[] Parameters)
{
   var Results = new DataTable();

   try
   {
        using (SqlConnection conn = new SqlConnection())
        {
            using (SqlCommand cmd = new SqlCommand(ConfigurationManager.ConnectionStrings["MK3Entities"].ConnectionString))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = StoredProcedure;

                if (Parameters!= null)
                {
                   foreach(var Param in Parameters)
                   { 
                       // I Want To Do something like this
                       cmd.Parameters.AddWithValue(Param);
                   }
                }

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(Results);
            }
        }
    }
    catch (Exception ex)
    {
       MMSLogger.Instance.WriteToLog("Exception Executing Stored Procedure:" + ex.Message);
    }

    return Results;
}
Andrey Korneyev
  • 26,353
  • 15
  • 70
  • 71
johnny 5
  • 19,893
  • 50
  • 121
  • 195
  • No; but you can call `DeriveParameters` to have the required parameters loaded dynamically (Or build an `EXEC` string manually) – Alex K. Oct 28 '14 at 14:48
  • 2
    Your question sounds like is there a way to shoot in my foot? Why? When passing parameters to stored procedure you know for which parameter you're passing data, why not send the name along with it? – Sriram Sakthivel Oct 28 '14 at 14:52
  • Ordinal parameter passing isn't generally desirable, and can cause a lot of [issues like this](http://stackoverflow.com/a/26547211/314291). If you really have to do this, then you can use [OleDb](http://stackoverflow.com/q/2407685/314291) instead of `SqlClient` – StuartLC Oct 28 '14 at 14:53

3 Answers3

3

As per MSDN:

The ParameterName is specified in the form @paramname. You must set ParameterName before executing a SqlCommand that relies on parameters.

Another MSDN article for SqlCommand:

Nameless, also called ordinal, parameters are not supported by the .NET Framework Data Provider for SQL Server.

So answer is no, there is no way to execute a stored procedure without naming the parameters.

Andrey Korneyev
  • 26,353
  • 15
  • 70
  • 71
3

Do you mean some thing like derived parameter. This MSDN article gave an overview:

Parameters can also be derived from a stored procedure using the DbCommandBuilder class. Both the SqlCommandBuilder and OleDbCommandBuilder classes provide a static method, DeriveParameters, which automatically populates the parameters collection of a command object that uses parameter information from a stored procedure. Note that DeriveParameters overwrites any existing parameter information for the command.

A nother solution would be to get the parameter of the stored procedure from the sql-database and than set them in your code. But this need one extra query. To get the parameter use:

select * from dbo.parameters where specific_name='procedure-name'

But in any case, you have to use parameter-names.

BendEg
  • 20,098
  • 17
  • 57
  • 131
3

Execute a command instead, and pass in parameters '@p1', '@p2' etc:

cmd.CommandType = CommandType.Text;
cmd.CommandText = 'exec ' + StoredProcedure;

int i=0;
string comma = ' ';
foreach(var Param in Parameters)
   {
   var paramName = String.Format("@P{0}", i);
   cmd.CommandText += comma + paramName;
   cmd.Parameters.AddWithValue(paramName, Param);
   ++i;
   comma = ', ';
   }

Be aware that AddwithValue is huge performance antipattern. See How Data Access Code Affects Database Performance

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569