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 · Accepted Answer · 2013-03-14 11:31:20Z

1

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.

edited Mar 14, 2013 at 11:31

answered Mar 7, 2013 at 4:35

Dustin Kingen

21.4k7 gold badges55 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

donubas Over a year ago

Hi Romoku, very interesting code. how do you implement this method into your code?

Dustin Kingen Over a year ago

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.

mellamokb · Accepted Answer · 2013-03-07 03:22:25Z

0

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);

edited Mar 7, 2013 at 3:22

answered Mar 7, 2013 at 3:16

mellamokb

56.8k12 gold badges111 silver badges138 bronze badges

Comments

Iswanto San · Accepted Answer · 2013-03-07 03:24:12Z

0

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);

answered Mar 7, 2013 at 3:24

Iswanto San

18.6k13 gold badges60 silver badges79 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related