Solved

SqlTypes and DBNull

Posted on 2007-11-27
8
541 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 300 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 100 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 100 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

617 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