Solved

SqlTypes and DBNull

Posted on 2007-11-27
8
533 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
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

810 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