?
Solved

SqlTypes and DBNull

Posted on 2007-11-27
8
Medium Priority
?
554 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 1200 total points
ID: 20363342
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 400 total points
ID: 20363366
How about setting your variable to Null?
private SqlChars mRefID = SqlChars.Null;
0
 
LVL 7

Assisted Solution

by:prosh0t
prosh0t earned 400 total points
ID: 20363569
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:CoconutTelegraph
ID: 20367337
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
 
LVL 7

Expert Comment

by:prosh0t
ID: 20367473
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
ID: 20367508
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
ID: 20367650
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
ID: 20367736
My pleasure. Good luck.

Jim
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
Suggested Courses
Course of the Month3 days, 7 hours left to enroll

601 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