Efficient way of providing SqlDbType?

Question

I have this working code below. However, I there is no SqlParameter constructor that takes name, value, and SqlDbType and I want to just provide the type for the date parameters. Is there a way to do that which does not require adding a lot of code?

SqlParameter[] parameters =  
{
    new SqlParameter("@START_ORDER", startOrder),
    new SqlParameter("@END_ORDER", endOrder), 
    new SqlParameter("@START_ITEM", startItem), 
    new SqlParameter("@END_ITEM", endItem), 
    new SqlParameter("@START_DUEDATE", dateFrom), 
    new SqlParameter("@END_DUEDATE", dateTo)
};

stakx - no longer contributing · Accepted Answer · 2015-05-18T21:18:01.220

6

new SqlParameter("@name", SqlDbType.Whatever) { Value = value }

There are constructor overloads that take all kinds of parameters, but they are perhaps not worth the hassle of specifying each and every argument. The above is probably as simple as it gets.

(Note that in some cases you might want to set SqlValue instead of Value.)

edited May 18 '15 at 21:18

answered May 18 '15 at 21:10

stakx - no longer contributing

83,039
20
168
268

First, thanks. Second, can you expand on when to (or not to) use `SqlValue`? – CodenameCain May 18 '15 at 21:22
1

You're welcome. Regarding the `SqlValue` bit, I'll only comment since it's mostly an aside. Basically it comes down to the fact that `SqlValue` and the various `Sql…` types allow you to be more precise about the exact value you want to send to SQL Server. Examples: **1.** You might want to avoid accuracy losses due to value conversion, e.g. with `SqlDateTime` vs. `DateTime`, which have different representations and precisions. **2.** The `Sql…` types all support `NULL`. **3.** Using `SqlDecimal` instead of `decimal` allows you to define precision & scale explicitly. – stakx - no longer contributing May 18 '15 at 21:57

score 1 · Answer 2 · answered May 18 '15 at 21:13

If you're doing a lot of manual ADO.NET, a good alternative might be to build a few helpers into a base class or utility class, which would also aid in ensuring you're passing in the right types:

public static SqlParameter CreateStringParam(string paramName,
    int length, string value) {}
public static SqlParameter CreateIntParam(string paramName,
    int? value) {}
// etc...

These helpers could help you out by converting null to DBNull.Value, and ensuring you remember about parameter size for strings and numbers.

At most you'd have to build a dozen or so, but more than likely you're only using a handful of data types, so you shouldn't need many of these.

Efficient way of providing SqlDbType?

2 Answers2