Solved

SqlTypes and DBNull

Posted on 2007-11-27
8
530 Views
Last Modified: 2008-02-01
Hi experts!

This is just a simple design question. I'm working on a DAL. I've got a class that reads a row of data from the database and stores each column as it's own member variable / property. You can modify most of the properties and then call a "Save" method which writes the object properties back to the DB.

Anyhow, what is the best way to preserve DBNulls. If the value is DBNull I want to keep that value in a variable but also be able to set it to a value of it's SqlType. I thought using the SqlTypes would allow for this but they don't. I hope that's clear. Many thanks for looking!

Here's the error which kind of shows what I'm going for.

Error      53      Cannot implicitly convert type 'System.DBNull' to 'System.Data.SqlTypes.SqlChars'      

Thanks!!!

private SqlChars mRefID = DBNull.Value;

public SqlChars RefID

{

	get { return mRefID; }

	internal set { mRefID = value; }

}

Open in new window

0
Comment
Question by:CoconutTelegraph
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 22

Accepted Solution

by:
JimBrandley earned 300 total points
Comment Utility
I ran into the same problem in mine. If I read a null, I just leave that column out of the update statement. It can be a pain in the neck to manage, but it works.

Jim
0
 
LVL 2

Assisted Solution

by:ileanarc
ileanarc earned 100 total points
Comment Utility
How about setting your variable to Null?
private SqlChars mRefID = SqlChars.Null;
0
 
LVL 7

Assisted Solution

by:prosh0t
prosh0t earned 100 total points
Comment Utility
Hi!  I'm a little curious why you aren't using a typed dataset for this situation.  It does all of that for you, and can be automatically created by simply opening the connection to your db in server explorer, and dragging the desired tables over into the designer.
0
 

Author Comment

by:CoconutTelegraph
Comment Utility
JimBrandley - Thanks for the suggestion. I think that's what I'll have to end up doing. I'm also curious, do you use SqlTypes for your property types in your class or do you use their corresponding .NET types? Is there any advantage in using one over the other.

prosh0t - Interesting suggestion but I'm not sure that would work for my situation because I retrieve all of my data from Stored Procedures not tables. Any thoughts there?

Thanks y'all! I really appreciate it.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 7

Expert Comment

by:prosh0t
Comment Utility
hmm.. in that case you would create the typed datasets by hand in teh designer yourself to correspond to the tables the SP's return.  Depending on the # of SP's you have it might not be worth it.  You're probably best off sticking to your original solution unless you have only a few SP's that each return many columns.
0
 
LVL 22

Expert Comment

by:JimBrandley
Comment Utility
We store data in our business objects in native .Net Types. I have parameter builders that keep the conversion in one place. Here are examples for both Oracle and SQL Server.

Jim

        // Create a varchar parameter

        public IDbDataParameter CreateVarcharParameter(string name, int length, string fieldValue, ParameterDirection direction)

        {

            OracleParameter OracleParam = new OracleParameter();
 

	   OracleParam.OracleDbType = OracleDbType.Varchar2;

            OracleParam.Value = fieldValue;

            OracleParam.ParameterName = name;

            OracleParam.Size = length;

            OracleParam.Direction = direction;

            return OracleParam;

        }
 

        public IDbDataParameter CreateVarcharParameter(string name, int length, string fieldValue, ParameterDirection direction)

        {

            SqlParameter SqlParam = new SqlParameter();
 

            SqlParam.ParameterName = "@" + name;

            SqlParam.Size = length;

            SqlParam.SqlDbType = SqlDbType.VarChar;

            SqlParam.Value = fieldValue;

            SqlParam.Direction = direction;

            return SqlParam;

        }

Open in new window

0
 

Author Comment

by:CoconutTelegraph
Comment Utility
Ok, thanks fellas!! This gives me some good ideas to go on and I should be able to figure out a nice solution.

Have a great day!
0
 
LVL 22

Expert Comment

by:JimBrandley
Comment Utility
My pleasure. Good luck.

Jim
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now