Click here to Skip to main content
16,017,899 members
Articles / Database Development
Tip/Trick

Making It Easier to Replace Null with DBNull.Value

Rate me:
Please Sign up or sign in to vote.
4.78/5 (15 votes)
16 Mar 2023CPOL2 min read 33.7K   13   14
Function to help convert null to DBNull.Value for inserting in database
I often have the need to convert a null to a DBNull.Value for inserting into database, this function helps.

Introduction

Just a quick tip on a function that will handle all nullable types and make it easier to insert DBNull.Value into the database.

Background

I posted a similar method a long while back in The Weird & the Wonderful and I needed it again today. However, today I made it better, by making it generic -- take all nullable types.

Using the Code

Here's the (Updated) Method

A commentor mentioned that I could still use ?? Operator. 
I forgot, I could use the coalescence operator in the actual method so the method becomes only one line.  Conciseness FTW!! For The Win!!

private Object convertDbNull<T>(T origValue){
  if (origValue == null){
   return System.DBNull.Value;
  }
  return origValue;
}

C#
private Object convertDbNull<T>(T origValue){
  return (Object)origValue ?? System.DBNull.Value;
}

Phew...this is the most time I've ever spent on a little tip/trick.  😆

Here's How You Can Use it

C#
command.Parameters.AddWithValue("$screenName", convertDBNull(task.screenName));

Now if the value of task.screenName == null, then it will be converted to DBNull.Value.
However, if the value isn't null, then the value will be returned and inserted into the database.

Template Code: Generics

Also, since the method takes any nullable type (Strings are nullable by default) like Int32? or bool? then the method will work with the type.

No more annoyances and worries about converting null to DBNull.Value.

Update - Someone Asked About ?? Operator

I had someone post a comment asking why the following wouldn't work:

C#
command.Parameters.AddWithValue("$screenName", task.screenName ?? DBNull.Value);

That code uses the null coalescing operator and it would be a very nice short-hand.

That is the exact code that I had hoped would work also. Alas, it does not compile.

Do you know why? Can you see why that code does not compile? It's not immediately obvious and you get a somewhat cryptic error that looks like:

Operator '??' cannot be applied to operands of type 'string' and 'DBNull'

It's attempting to indicate that you are comparing two different types (a string and a dbnull) and the compiler cannot do that on the null coalescing operator.

You can fix that code by casting the String as an object with the following code:

C#
command.Parameters.AddWithValue("$screenName", (object) task.screenName ?? DBNull.Value);

However, that is what my method does and again the method handles all types.

Maybe there were others who thought they could use the coalescing operator here too, and they were the ones who down-voted this article? Not sure, but this should clear this up.

History

  • 9th March, 2023: Updated with info about using coalescing operator
  • 26th February, 2023: First publication

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) RADDev Publishing
United States United States
"Everything should be made as simple as possible, but not simpler."

Comments and Discussions

 
Questiondon't reinvent the wheel Pin
jabbath22-Mar-23 2:17
jabbath22-Mar-23 2:17 
GeneralRe: don't reinvent the wheel Pin
the Kris24-Mar-23 9:30
the Kris24-Mar-23 9:30 
I'm a little confused here. Your code samples don't do at all what the OP is trying to achieve.
Questionare you sure that you cannot use coalesce? Pin
Anibal_Ven15-Mar-23 1:03
Anibal_Ven15-Mar-23 1:03 
AnswerRe: are you sure that you cannot use coalesce? Pin
raddevus15-Mar-23 3:44
mvaraddevus15-Mar-23 3:44 
QuestionDate parameters Pin
Ezz Khayyat12-Mar-23 21:46
professionalEzz Khayyat12-Mar-23 21:46 
QuestionWhy not use the coalescence operator? Pin
Alberto Nuti9-Mar-23 0:16
Alberto Nuti9-Mar-23 0:16 
AnswerRe: Why not use the coalescence operator? Pin
raddevus9-Mar-23 1:56
mvaraddevus9-Mar-23 1:56 
GeneralRe: Why not use the coalescence operator? Pin
Right_Said_Fred10-Mar-23 12:26
Right_Said_Fred10-Mar-23 12:26 
GeneralRe: Why not use the coalescence operator? Pin
raddevus11-Mar-23 5:41
mvaraddevus11-Mar-23 5:41 
GeneralRe: Why not use the coalescence operator? Pin
Right_Said_Fred15-Mar-23 19:38
Right_Said_Fred15-Mar-23 19:38 
GeneralRe: Why not use the coalescence operator? Pin
raddevus16-Mar-23 2:09
mvaraddevus16-Mar-23 2:09 
AnswerRe: Why not use the coalescence operator? Pin
raymond Hastie10-Mar-23 6:42
raymond Hastie10-Mar-23 6:42 
QuestionMake it fluent Pin
sorint_de@yahoo.com27-Feb-23 2:14
sorint_de@yahoo.com27-Feb-23 2:14 
AnswerRe: Make it fluent Pin
raddevus27-Feb-23 3:53
mvaraddevus27-Feb-23 3:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.