sqlCommand object and adding parameters using loop

Question

We can clear all the text boxes in a form using foreach without typing codes for each text box to clear them.

com.Parameters.Add(new SqlParameter("@",objPlayrInjr_P));

using (SqlCommand com = new SqlCommand())
{
    com.CommandText = "SPname";
    com.Connection = connection object;
    com.CommandType = CommandType.StoredProcedure;

    com.Parameters.Add(new SqlParameter("@SqlParameterName",objectName.propertyName));
}

I need a method to add sqlParameters to the Command object through a loop, like how I did when clearing text boxes. So no need to write code for each and every statement, it will be done by the loop.

to go through a loop, you need a list of parameter names and property values. — decyclone, Aug 16 '12 at 14:50
I would recommend just using a standard approach to adding parameters. Keep them in the data layer/classes. I do not think you will get much of a time saving from trying to supply a list of parameters in or marking up your domain objects with parameter names. — Trotts, Aug 16 '12 at 15:15
My first question is what have you tried? Please show code examples of what you have attempted. If you haven't tried anything - then please use "The Google" to assist you. We are all more than happy to help - but please "Help us help you". — tsells, Aug 16 '12 at 16:51

Eric J. · Answer 1 · 2012-08-16T16:37:57.577

3

One approach is to put your parameter names and values in a Dictionary<string, object> and then

foreach (KeyValuePair<string, object> param in params)
{
    com.Parameters.AddWithValue(param.Key, param.Value);
}

edited Aug 16 '12 at 16:37

answered Aug 16 '12 at 14:47

Eric J.

147,927
63
340
553

Would you recommend doing this though? Your form model would consist entirely of a Dictionary of arbitrary values. – Trotts Aug 16 '12 at 14:53
What form model? There's no context given for the problem, other than he wants to be able to set SqlParameter's in a loop. The only reference to "form" is that one can use a foreach on a form to clear text boxes. I do exactly this when setting up complex queries based on user input (e.g. user can select 1..N keys that become part of an IN clause). – Eric J. Aug 16 '12 at 15:18

score 3 · Answer 2 · answered Aug 16 '12 at 15:02

Here is something using annotations:

Annotation for marking a property with parameter name

[AttributeUsage(AttributeTargets.Property)]
public class ParameterAttribute : Attribute
{
    public string ParameterName { get; private set; }

    public ParameterAttribute(string parameterName)
    {
        ParameterName = parameterName;
    }
}

Example class

public class Person
{
    [Parameter("FirstName")]
    public string FirstName { get; set; }

    [Parameter("LastName")]
    public string LastName { get; set; }

    [Parameter("EmailAddress")]
    public string Email { get; set; }
}

Usage

SqlCommand command = new SqlCommand();

Person person = new Person()
{
    FirstName = "John",
    LastName = "Doe",
    Email = "johndoe@domain.com"
};

foreach (var pi in person.GetType().GetProperties())
{
    var attribute = (ParameterAttribute)pi.GetCustomAttributes(typeof(ParameterAttribute), false).FirstOrDefault();

    if (attribute != null)
    {
        command.Parameters.AddWithValue(string.Format("@{0}", attribute.ParameterName), pi.GetValue(person, null));
    }
}

sqlCommand object and adding parameters using loop

2 Answers2

Annotation for marking a property with parameter name

Example class

Usage