Automatically convert .Net null to DBNull.Value?

Question

I've written a utility to handle adding values to data parameters for hand-rolling sql queries. Consumption looks like

utility.Add("SELECT * FROM MyTable WHERE MyColumn = {0}", myVariable, DBType.TheType);

or

utility.Add("UPDATE MyTable SET MyColumn = {0}", myVariable, DBType.TheType);

How should a null value for myVariable be handled?

All values should be fully trusted. The responsibility is solely upon the consumer. How would the consumer know that null would be handled anyway?
null can never work, so it should throw a NullArgumentException. Why go any further?
null should be automatically interpreted as DBNull.Value since it's the only viable solution. This is a utility, right? Make it utilizable and dry up some code!

Optional bonus question: If these arguments were made by three political candidates, what would their parties be? (Please state the home country of such parties)

Are you asking about null values returned from the query, or null values passed in as the value of myVariable? — Joe White
– Joe White, Commented Aug 16, 2011 at 23:09
I built a little sqlparameter wrapper, to handle null and other basic low-level validation so you can basically throw anyhting at it as value and it will end up as a perfeclty safe normalized data. I do translate nulls into dbnull.value — David
– David, Commented Aug 16, 2011 at 23:24
null is passed into the utility which creates a dbcommand with the command text and parameters — StarTrekRedneck
– StarTrekRedneck, Commented Aug 17, 2011 at 2:06
I added an additional use case to the question to further clarify. Thanks @Joe White. — StarTrekRedneck
– StarTrekRedneck, Commented Aug 17, 2011 at 12:38

FastAl · Accepted Answer · 2011-08-17 06:40:04Z

1

Rock the Null Coalescing Op:

In your add put another parm for the =... utility.Add("SELECT * FROM MyTable WHERE MyColumn {0}{1}", myVariable, DBType.TheType);

When you format the string in the Add method: ... = String.Format(sql,myVariable is null ? " IS " : " = ", myVariable ?? "Null"

You'll probably want to expound on that a bit, e.g., stick with on parameter and toss in a temp stringbuilder; might have to use a the ?/: construct for the type, too, wherever that's being used. Good luck.

answered Aug 17, 2011 at 6:40

FastAl

6,2772 gold badges41 silver badges62 bronze badges

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

Comments

Charles Graham · Accepted Answer · 2011-08-16 23:15:26Z

0

Null is a valid query parameter, you don't need to convert it to DBNull to use it in a query.

answered Aug 16, 2011 at 23:15

Charles Graham

1,1575 silver badges6 bronze badges

1 Comment

ta.speot.is Over a year ago

Yes but you can't use Column = NULL, it needs to be IS NULL. Which the String.Format example shown does not handle.

Collectives™ on Stack Overflow

Automatically convert .Net null to DBNull.Value?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related