Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

SqlTypes and DBNull

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
CoconutTelegraph
Asked:
CoconutTelegraph
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
JimBrandleyCommented:
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
 
ileanarcCommented:
How about setting your variable to Null?
private SqlChars mRefID = SqlChars.Null;
0
 
prosh0tCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
CoconutTelegraphAuthor Commented:
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
 
prosh0tCommented:
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
 
JimBrandleyCommented:
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
 
CoconutTelegraphAuthor Commented:
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
 
JimBrandleyCommented:
My pleasure. Good luck.

Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now