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

user1447679

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.

Shep

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?

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

How can I write this CSS more efficiently?

From Dev

How Can I Rewrite This More Efficiently

From Dev

How can I write this CSS more efficiently?

From Dev

How can I use the OUT parameter from a SQL Server stored procedure in a vb.net code

From Dev

How can I use the OUT parameter from a SQL Server stored procedure in a vb.net code

From Dev

How can I Setup and Manage Several SQL Server Scripts to use in VB.NET?

From Dev

How to deal with DBNull in DataContractSerializer?

From Dev

How to handle DBNull? VB.NET

From Dev

How can we deal with large number in vb.net

From Dev

How do I deal with special characters in vb.net?

From Dev

How do I deal with special characters in vb.net?

From Dev

How can I run SQL Server using VB as a ASPX?

From Dev

How can I make large IN clauses more efficient in SQL Server?

From Dev

How can I make large IN clauses more efficient in SQL Server?

From Dev

How can I use VB.NET to configure a DataAdapter to use Sql Server Stored Procedures for Select, Update, Insert and Delete commands?

From Dev

VB.Net DataTable.Select specific value or DBNull

From Dev

How can I use find command more efficiently?

From Dev

How can I more efficiently search a large list in python?

From Dev

How can I use find command more efficiently?

From Dev

How can I more efficiently render Mathjax content?

From Dev

How can I more efficiently arrange my AXML layout?

From Dev

JSON return from a web API can be double or long, how do I most efficiently deal with this in Java?

From Dev

I can't connect to my SQL Server database Using VB.NET

From Dev

How can I add string value and integer value into listbox in vb.net?

From Dev

How can I set the value of a SQL Server column to True / False?

From Dev

How can I get the count depending on the column value in SQL Server

From Dev

How to make an installer in VB.NET which can install SQL Server Express Edition?

From Dev

Replacing a value with NULL in a table in SQL Server using vb.net

From Dev

How can I detect the cell value changed of a specific datagridview column? - VB.NET

Related Related

  1. 1

    How can I write this CSS more efficiently?

  2. 2

    How Can I Rewrite This More Efficiently

  3. 3

    How can I write this CSS more efficiently?

  4. 4

    How can I use the OUT parameter from a SQL Server stored procedure in a vb.net code

  5. 5

    How can I use the OUT parameter from a SQL Server stored procedure in a vb.net code

  6. 6

    How can I Setup and Manage Several SQL Server Scripts to use in VB.NET?

  7. 7

    How to deal with DBNull in DataContractSerializer?

  8. 8

    How to handle DBNull? VB.NET

  9. 9

    How can we deal with large number in vb.net

  10. 10

    How do I deal with special characters in vb.net?

  11. 11

    How do I deal with special characters in vb.net?

  12. 12

    How can I run SQL Server using VB as a ASPX?

  13. 13

    How can I make large IN clauses more efficient in SQL Server?

  14. 14

    How can I make large IN clauses more efficient in SQL Server?

  15. 15

    How can I use VB.NET to configure a DataAdapter to use Sql Server Stored Procedures for Select, Update, Insert and Delete commands?

  16. 16

    VB.Net DataTable.Select specific value or DBNull

  17. 17

    How can I use find command more efficiently?

  18. 18

    How can I more efficiently search a large list in python?

  19. 19

    How can I use find command more efficiently?

  20. 20

    How can I more efficiently render Mathjax content?

  21. 21

    How can I more efficiently arrange my AXML layout?

  22. 22

    JSON return from a web API can be double or long, how do I most efficiently deal with this in Java?

  23. 23

    I can't connect to my SQL Server database Using VB.NET

  24. 24

    How can I add string value and integer value into listbox in vb.net?

  25. 25

    How can I set the value of a SQL Server column to True / False?

  26. 26

    How can I get the count depending on the column value in SQL Server

  27. 27

    How to make an installer in VB.NET which can install SQL Server Express Edition?

  28. 28

    Replacing a value with NULL in a table in SQL Server using vb.net

  29. 29

    How can I detect the cell value changed of a specific datagridview column? - VB.NET

HotTag

Archive