How can I more efficiently deal with DBNull.value in vb.net / SQL Server

Question

I have a class type object, such as:

Private Class Car
  Public Property Make As String
  Public Property Model As String
  Public Property Used As Boolean?
  Public Property Mileage As Integer?
  Public Property DateSold As Date?
End Class

I'm parsing a query string that's posted to my page that may or may not have key/value pairs for the above properties. For example, it may just be:

?Make=Toyota

I have a table in SQL Server based on the Public Properties you see above, and I'm allowing NULLS on all columns. (My real data has more fields, so this is for explanation purposes)

CREATE PROCEDURE InsertCar
  @Make varchar(25),
  @Model varchar(25),
  etc.

I'm not setting default values in the parameters in the stored procedure above. I want to know of a cleaner way to do this from code.

For example:

The data is posted to my page... I use httputility to parse it, and I can build a new object, such as:

Dim record As New Car
record.Make = Data("make")
record.Model = Data("model")
record.Used = Data("used")
record.Mileage = Data("mileage")
record.DateSold = Data("datesold")

Afterwards, I build some parameters for my stored procedure, but here's where I think I can improve:

Dim pars As New List(Of SqlParameter)
pars.Add(New SqlParameter("@Make", If(record.Make = Nothing, DBNull.Value, record.Make))
and so on...

So you can see my ternary operators for building the parameters. Is there a cleaner way, perhaps using default values or when the class is being put together to make this cleaner?

This is more of a precision and best practice question I think. Should I be assigning DBNull.Value to the public properties? Or is how I'm doing it sufficient? Is there a cleaner way to map nullable properties?

My values from the query string are parsed into a NameValueCollection, so is there an easier way to just iterate over the ones that exist, and if not exist in the class, automatically build the parameter with DBNull.Value?

I hope my question makes sense. With a lot of data it just seems ugly with all the if/then DBNull.value, etc. I feel like I could be saving myself a step somewhere along the way. The class object is helpful for organization and is utilized in other parts of the code, or else I'd probably just build the parameters and be done with it.

score 2 · Accepted Answer · edited May 23 '17 at 12:30

2

You can set default values in the stored procedure parameters.

CREATE PROCEDURE InsertCar
   @Make varchar(25) = null,
   @Model varchar(25) = null
   ...

And just pass your object, if it is null/missing the stored proc will default the missing paramater value to null

pars.Add(New SqlParameter("@Make", record.Make))

The SQL table also needs to accept nulls.

Or, use the vb.net IF() operator with 2 parameters

pars.Add(New SqlParameter("@Make", If(record.Make,DBNull.Value))

Is there a VB.NET equivalent for C#'s '??' operator?

edited May 23 '17 at 12:30

Community

1
1

answered Dec 21 '15 at 00:49

Shep

638
3
15

Thanks, aware of that though. I had more of a learning purpose in mind for better handling this all in vb.net, as mentioned in my question. – user1447679 Dec 21 '15 at 00:53
Possibly use the null coalescing operator: pars.Add(New SqlParameter("@Make", (object)record.Make ?? DBNull.Value)) – Shep Dec 21 '15 at 01:05
Never even seen that... will try that now! – user1447679 Dec 21 '15 at 01:06
Okay looks like that's the If() for vb.net. – user1447679 Dec 21 '15 at 01:12
The VB If() works the same way when only 2 parameters are specified. http://stackoverflow.com/questions/403445/is-there-a-vb-net-equivalent-for-cs-operator – Shep Dec 21 '15 at 01:16

How can I more efficiently deal with DBNull.value in vb.net / SQL Server

1 Answers1