How can I set the value and the datatype when creating a new SqlParameter?

Question

I have my code setting up a parameter for QuestionsCount and then for Questions:

    parameterList.Add(new SqlParameter("@QuestionsCount", chunkSize));

    var p = new SqlParameter("@Questions", questions);
    p.TypeName = "dbo.QuestionList";
    parameterList.Add(p);

Is there a way that I can combine the last three lines and create a new SqlParameter with the typeName. I was looking at the definitions but cannot find one that takes the value (questions) and the TypeName "dbo.QuestionList".

I find that table valued parameters require (relatively) verbose setup before you can use them, so I just wrote an extension method: AddWithValue<T>(this SqlParameterCollection that, string parameterName, string typeName, IEnumerable<T> rows) that handles setting the type etc. plus empty IEnumerable<T>s get passed down as null (why that's a requirement I have no idea...) — ta.speot.is
– ta.speot.is, Commented Jul 20, 2014 at 7:20

marc_s · Accepted Answer · 2014-07-20 09:00:11Z

3

There are quite a few overloads for the constructor for SqlParameter, some of which allow you to specify a datatype and optionally a length:

var p = new SqlParameter("@Questions", SqlDbType.VarChar, 100);
p.Value = ":.......";
parameterList.Add(p);

Check out the MSDN documentation on SqlParameter for details.

answered Jul 20, 2014 at 9:00

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Comments

Dan Guzman · Accepted Answer · 2014-07-20 12:33:17Z

1

One method is with an initializer to supply property values not available in the constructor overloads:

parameterList.Add(new SqlParameter( "@Questions", SqlDbType.Structured ) { TypeName = "dbo.QuestionList", Value = questions });

answered Jul 20, 2014 at 12:33

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Collectives™ on Stack Overflow

How can I set the value and the datatype when creating a new SqlParameter?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related