Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SqlTypes and DBNull

Posted on 2007-11-27
8
Medium Priority
?
544 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

705 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