Array of Sized/Typed SqlParameters

Question

I'm trying to create an array of dbTyped and sized SqlParameters. This works fine but results in changing code both places if I need another column.

SqlParameter[] parameters = {
                                  new SqlParameter("@first_name", SqlDbType.VarChar, 50),
                                  new SqlParameter("@last_name", SqlDbType.VarChar, 50),
                                  new SqlParameter("@middle_name", SqlDbType.VarChar, 50),
                                  new SqlParameter("@empid", SqlDbType.Int)
                            };
parameters[0].Value = to.FirstName;
parameters[1].Value = to.LastName;
parameters[2].Value = to.MiddleName;
parameters[3].Value = to.EmpId;

What is a better way of doing this?

Jon Skeet · Accepted Answer · 2012-08-02 18:04:08Z

19

You can use object initializer expressions:

SqlParameter[] parameters =
{    
  new SqlParameter("@first_name", SqlDbType.VarChar, 50) { Value = to.FirstName },
  new SqlParameter("@last_name", SqlDbType.VarChar, 50) { Value = to.LastName },
  new SqlParameter("@middle_name", SqlDbType.VarChar, 50) { Value = to.MiddleName },
  new SqlParameter("@empid", SqlDbType.Int) { Value = to.EmpId }
};

You could also create a list in the same way, which is often preferred:

List<SqlParameter> parameters = new List<SqlParameter>
{    
  new SqlParameter("@first_name", SqlDbType.VarChar, 50) { Value = to.FirstName },
  new SqlParameter("@last_name", SqlDbType.VarChar, 50) { Value = to.LastName },
  new SqlParameter("@middle_name", SqlDbType.VarChar, 50) { Value = to.MiddleName },
  new SqlParameter("@empid", SqlDbType.Int) { Value = to.EmpId }
};

Or you could even write an extension method on SqlParameter:

public static SqlParameter WithValue(this SqlParameter parameter, object value)
{
    parameter.Value = value;
    return parameter;
}

Then use it like this:

List<SqlParameter> parameters = new List<SqlParameter>
{    
  new SqlParameter("@first_name", SqlDbType.VarChar, 50).WithValue(to.FirstName),
  new SqlParameter("@last_name", SqlDbType.VarChar, 50).WithValue = to.LastName)
  new SqlParameter("@middle_name", SqlDbType.VarChar, 50).WithValue(to.MiddleName),
  new SqlParameter("@empid", SqlDbType.Int).WithValue(to.EmpId)
};

answered Aug 2, 2012 at 18:04

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

5 Comments

Jason Eades Over a year ago

Exactly what I was looking for. I've explored option 3 but didn't like what I came up with. Thanks!

Rahul Nikate Over a year ago

@Jon Skeet I found your post very helpful but currently I'm working on Framework 2.0. So it is showing error like "Feature Object Initializer cannot be used because it is not a part of the ISO-2 C# language specification. Sorry rightnow its not possible to upgrade project. So is there any efficient way for this.

Jon Skeet Over a year ago

@RahulNikate: Just because you're using .NET 2.0 doesn't mean you can't use newer features of C#. For example, you could use Visual Studio 2013 and still target .NET 2.0. If you limit yourself to C# 2.0 you'll find an awful lot of code on Stack Overflow posts doesn't work for you, and it's not practical to provide alternatives all over the place.

Rahul Nikate Over a year ago

@JonSkeet Currently I'm using Visual Studio 2013 only but project i'm working is having .Net Framework 2.0. Could you please tell me how can i use advance features. I didn't understand clearly. Thanks very much

Jon Skeet Over a year ago

@RahulNikate: The error message you're getting suggests that your project is set to build using a very old language version. Look in the project properties, Build, Advanced and set the language version to the default instead. You don't need to use a later version of .NET in order to use collection initializers.

competent_tech · Accepted Answer · 2012-08-02 18:09:04Z

0

You can use the constructor overload that allows you to specify the value as a parameter.

For example:

SqlParameter[] parameters = {
                              new SqlParameter("@first_name", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Current, to.FirstName),

etc...

answered Aug 2, 2012 at 18:09

competent_tech

45.1k12 gold badges96 silver badges114 bronze badges

Comments

Marc Gravell · Accepted Answer · 2012-08-02 18:30:17Z

0

A better way? Sure: let a tool help you, for example "dapper":

var rows = conn.Query<YourType>(@" your tsql ",
    new { first_name = to.FirstName,
          middle_name = to.MiddleName,
          last_name = to.LastName,
          empid = to.EmpId }).ToList();

This then handles all the parameterisation and materialization for you.

answered Aug 2, 2012 at 18:30

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Comments

Stig · Accepted Answer · 2012-08-02 18:02:14Z

-2

you can add the value back like this:

 new SqlParameter("@first_name", SqlDbType.VarChar, 50).value = to.FirstName

answered Aug 2, 2012 at 18:02

Stig

1,32316 silver badges22 bronze badges

2 Comments

Jon Skeet Over a year ago

No you can't - because then the value of that expression is to.FirstName, not the SqlParameter.

Jon Skeet Over a year ago

In an array creation expression? It's more common to have command.AddParameter(...).Value = "foo" which is entirely different.

Collectives™ on Stack Overflow

Array of Sized/Typed SqlParameters

4 Answers 4

5 Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related