How to create C# Method with sqlconnection executing stored procedure with Array of Parameters

Question

With the below code, I am executing the stored procedure named, "sp_InsertTradingAcctTransFront" with 4 parameters.

Now, instead of typing each of the parameter, i wanted to do it the parameter and field as Array.

Like:

  cmd.Parameters.Add(new SqlParameter("@sp parametername", param);

make note that param is an Array.

private void Methodname(SQlConn, param)
{
  //param as ARRAY of parameters

    cmd.CommandText = "sp_InsertTradingAcctTransFront";
    cmd.Parameters.Add(new SqlParameter("@mBatchName", mCollectionID));
    cmd.Parameters.Add(new SqlParameter("@mTATCash", mTATCash));
    cmd.Parameters.Add(new SqlParameter("@mTATradingOrdinary", mTATradingOrdinary));
    cmd.Parameters.Add(new SqlParameter("@mTATradingType", mTATradingType)); 
    cmd.ExecuteNonQuery();
    SQLConn.close()

}

Dustin Kingen · Answer 1 · 2013-03-14T11:31:20.243

I believe it would be easier to use a Dictionary(TKey, TElement)

private void QueryDatabase(string connectionString, string commandText, IDictionary<string, object> parameters)
{
    using(var connection = new SqlConnection(connectionString))
    using(var command = connection.CreateCommand())
    {
        command.CommandText = commandText;
        command.Parameters.AddRange(parameters.Select(l => new SqlParameter(l.Key, l.Value)));
        command.CommandType = CommandType.StoredProcedure;

        connection.Open()
        command.ExecuteNonQuery();
    }

}

edit:

public interface IParameterizable
{
    IEnumerable<SqlParameter> GetParameters();
}

public SqlParameterAttribute : Attribute
{
    public string Name { get; set; }
}

public class InsertTradingAcctTransFrontParameters : IParameterizable
{
    [SqlParameter( Name = "@mBatchName" )]
    public int CollectionId { get; set; }

    /* ... */

    IEnumerable<SqlParameter> GetParameters()
    {
        // Validation for properties, etc...
        if(0 > CollectionId) throw new MeaningfulException("CollectionId must be greater than 0");

        yield return new SqlParameter(GetParameterName("CollectionId"), CollectionId);
    }

    private string GetParameterName(string propertyName)
    {
        var attribute = GetType().GetProperty(propertyName).GetCustomAttributes(typeof(SqlParameterAttribute), false).SingleOrDefault();

        if(attribute == null) throw new NotImplementedException(string.Format("SqlParameter is not defined for {0}", propertyName);

        return ((SqlParameterAttribute)attribute).Name;
    }
}

Then you can change your query method:

private void QueryDatabase(string connectionString, string commandText, IParameterizable parameters)
{
    using(var connection = new SqlConnection(connectionString))
    using(var command = connection.CreateCommand())
    {
        command.CommandText = commandText;
        command.Parameters.AddRange(parameters.GetParameters());
        command.CommandType = CommandType.StoredProcedure;

        connection.Open()
        command.ExecuteNonQuery();
    }

}

At this point it's pretty reusable.

Hi Romoku, very interesting code. how do you implement this method into your code? — donubas, Mar 14 '13 at 09:57
I usually define a class to hold my parameters. If you wanted you could have a method on the class to create your parameters. Check my edit. — Dustin Kingen, Mar 14 '13 at 11:32

mellamokb · Answer 2 · 2013-03-07T03:22:25.730

Assuming that your code ensures the number of parameters is correct, you can refer to each item in the array using array indexing ([#]):

private void Methodname(string SQLConn, object[] param)
{
    ...
    cmd.Parameters.Add(new SqlParameter("@mBatchName", param[0]));
    cmd.Parameters.Add(new SqlParameter("@mTATCash", param[1]));
    cmd.Parameters.Add(new SqlParameter("@mTATradingOrdinary", param[2]));
    cmd.Parameters.Add(new SqlParameter("@mTATradingType", param[3]));

If you're talking about passing an array of SQLParameter objects, you can use the AddRange exposed by all collection types to append the entire contents of an array to the collection:

private void Methodname(string SQLConn, SQLParameter[] param)
{
    ...
    cmd.Parameters.AddRange(param);

score 0 · Answer 3 · answered Mar 07 '13 at 03:24

You can use SqlParameterCollection.AddRange Method.

Example:

Array a = Array.CreateInstance(typeof(SqlParameter), 4);
a.SetValue(new SqlParameter("@mBatchName", mCollectionID), 0);
a.SetValue(new SqlParameter("@mTATCash", mTATCash), 1);
a.SetValue(new SqlParameter("@mTATradingOrdinary", mTATradingOrdinary), 2);
a.SetValue(new SqlParameter("@mTATradingType", mTATradingType), 3);
cmd.Parameters.AddRange(a);

How to create C# Method with sqlconnection executing stored procedure with Array of Parameters

3 Answers3