Geek asks and answers

Array of Dimensional / Typed SqlParameters

I am trying to create a dbTyped array and a SqlParameters size. This works fine, but causes the code to change in 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 the best way to do this?

+5
source share
4 answers

You can use initialifier 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 can 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 can even write an extension method on SqlParameter:

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

Then use it as follows:

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)
};
+11
source

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

For instance:

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

etc...
0

? : , , "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();

.

0

you can add the value back as follows:

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

More articles:


All Articles