Search content within the blog

Monday, May 4, 2009

Implementing Nullable Types in C#

There may be situations where you might have to assign data from database columns containing null values to equivalent C# types. This section presents a simple application that will illustrate how to use nullable types to mitigate such problems.

Figure below shows the structure of a table called Patient that contains three nullable fields.



Executing the SQL script in Listing 1 creates the above mentioned Patient table in your database

Listing-1
T-SQL
Listing 1. Sample Table Script:
Run this SQL to create the sample table containing three columns that allow nulls.

CREATE TABLE [dbo].[PatientInfo](
[PatientID] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ContactPerson] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SSN] [int] NULL,
[BilledAmount] [int] NULL,
[Address] [varchar](50) COLLATE NOT NULL,
[Sex] [varchar](6) COLLATE NOT NULL,
[DateOfBirth] [datetime] NOT NULL,
[AdmissionDate] [datetime] NOT NULL,
[ReleaseDate] [datetime] NOT NULL,
CONSTRAINT [PK_PatientInfo] PRIMARY KEY CLUSTERED
(
[PatientID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO


You need some sample data, too; the script shown below inserts several records into the Patient table:
SET IDENTITY_INSERT [PatientInfo] ON

INSERT INTO [dbo].[PatientInfo] (
[PatientID], [Name], [ContactPerson], [SSN],
[BilledAmount], [Address], [Sex],
[DateOfBirth], [AdmissionDate],
[ReleaseDate])
VALUES (
1, 'Joydip', NULL, 4234234, NULL,
'Flat 20, Suvarna Aparments, Gachiboli, Hyderabad',
'Male', '19671102', '20070902', '20070910')

INSERT INTO [dbo].[PatientInfo] (
[PatientID], [Name], [ContactPerson], [SSN],
[BilledAmount], [Address], [Sex], [DateOfBirth],
[AdmissionDate], [ReleaseDate])
VALUES (
2, 'Vinay', NULL, NULL, 500,
'Saptagiri Towers, Begumpet, Secunderabad',
'Male', '19770808', '20070821', '20070910')

INSERT INTO [dbo].[PatientInfo] (
[PatientID], [Name], [ContactPerson], [SSN],
[BilledAmount], [Address], [Sex], [DateOfBirth],
[AdmissionDate], [ReleaseDate])
VALUES (
3, 'Sriram', NULL, NULL, NULL,
'2-4/A,Parklane Road, Hyderabad', 'Male',
'19790503', '20070608', '20070621')

INSERT INTO [dbo].[PatientInfo] (
[PatientID], [Name], [ContactPerson], [SSN],
[BilledAmount], [Address], [Sex], [DateOfBirth],
[AdmissionDate], [ReleaseDate])
VALUES (
4, 'Nageswar', 'Ashant', 83283493, 25000,
'3-6/W, Keys Marg, Secunderabad', 'Male',
'19670522', '20070406', '20070422')

SET IDENTITY_INSERT [PatientInfo] OFF

GO

The three nullable fields in the Patient table are ContactPerson, BilledAmount, and SSN; those fields may or may not have values.

A Simple Null-Aware Application
With the sample data available, here's a simple application that illustrates how and where you can use nullable types.

The application has two classes called PatientNullable and PatientNonNullable. These classes read data from the Patient table discussed in the preceding section, and use that data to populate class members that correspond to the table columns. Both classes have a GetPatient() method that accepts a PatientID parameter, queries the database for the patient with that PatientID, and populates the class instance with the retrieved data.

The difference between the two classes, of course, is that the PatientNonNullable class does not handle nullable types; therefore, it's prone to problems at run time if you attempt to populate it from from database columns that can contain null values. In contrast, the PatientNullable class does accept null types. The differences in the two GetPatient() methods should clarify where nullable types can help simplify your database application development

Listing 2 and Listing 3 show the full code for the PatientNullable and PatientNonNullable classes, respectively, but here's the relevant portion from the getPatient() methods in both classes.

Listing 2
Listing 2. PatientNonNullable Class:
This class does not handle null values.

public class PatientNonNullable
{
long patientid;
string name;
string contactperson;
long ssn;
int billedamount;
string address;
string sex;
DateTime dateofbirth;
DateTime admissiondate;
DateTime releasedate;

public PatientNonNullable()
{
}

public void GetPatient(long patientID)
{
string connectionString =
"Server=.;Database=Patient;Integrated Security=SSPI";
string queryString = String.Format(
"SELECT PatientID, Name, ContactPerson, SSN, " +
"BilledAmount, Address, Sex, DateOfBirth, " +
"AdmissionDate, ReleaseDate FROM PatientInfo " +
"WHERE PatientID = {0}", patientID);
using (SqlConnection dbConnection = new
SqlConnection(connectionString))
{
dbConnection.Open();
SqlCommand dbCommand = new SqlCommand(
queryString, dbConnection);
SqlDataReader dataReader = dbCommand.ExecuteReader();
if(dataReader.HasRows)
while (dataReader.Read())
{
patientid = Convert.ToInt32(
dataReader["PatientID"]);
name = dataReader["Name"].ToString();
contactperson = dataReader["ContactPerson"] ==
DBNull.Value ? null :
dataReader["ContactPerson"].ToString();
if (dataReader["SSN"] == DBNull.Value)
ssn = -1; //NULL replaced with -1
else
ssn = Convert.ToInt64(dataReader["SSN"]);
if (dataReader["BilledAmount"] == DBNull.Value)
billedamount = 0; //NULL Replaced with Zero
else
billedamount = Convert.ToInt32(
dataReader["BilledAmount"]);

address = dataReader["Address"].ToString();
sex = dataReader["Sex"].ToString();
dateofbirth = Convert.ToDateTime(
dataReader["DateOfBirth"]);
admissiondate = Convert.ToDateTime(
dataReader["AdmissionDate"]);
releasedate = Convert.ToDateTime(
dataReader["ReleaseDate"]);
}
}
}

public long PatientID { get { return patientid; } }
public string Name { get { return name; }}
public string ContactPerson { get { return contactperson; } }
public long SSN { get { return ssn; } }
public int BilledAmount { get { return billedamount; } }
public string Address { get { return address; } }
public string Sex { get { return sex; } }
public DateTime DateOfBirth { get { return dateofbirth; } }
public DateTime AdmissionDate { get { return admissiondate; } }
public DateTime ReleaseDate { get { return releasedate; } }
}

Listing-3
Listing 3. PatientNullable Class:
This class handles null database values using nullable types.

public class PatientNullable
{
long patientid;
string name;
string contactperson;
long? ssn;
int? billedamount;
string address;
string sex;
DateTime dateofbirth;
DateTime admissiondate;
DateTime releasedate;

public PatientNullable()
{
}

public void GetPatient(long patientID)
{
string connectionString =
"Server=.;Database=Patient;Integrated Security=SSPI";
string queryString = String.Format(
"SELECT PatientID, Name, ContactPerson, SSN, " +
"BilledAmount, Address, Sex, DateOfBirth, " +
"AdmissionDate, ReleaseDate " +
"FROM PatientInfo " +
"WHERE PatientID = {0}", patientID);
using (SqlConnection dbConnection = new
SqlConnection(connectionString))
{
dbConnection.Open();
SqlCommand dbCommand = new SqlCommand(
queryString, dbConnection);
SqlDataReader dataReader = dbCommand.ExecuteReader();
if (dataReader.HasRows)
while (dataReader.Read())
{
patientid = Convert.ToInt32(
dataReader["PatientID"]);
name = dataReader["Name"].ToString();
contactperson = dataReader["ContactPerson"] ==
DBNull.Value ? null :
dataReader["ContactPerson"].ToString();
ssn = dataReader["SSN"] ==
DBNull.Value ? (long?)null :
Convert.ToInt64(dataReader["SSN"]);
billedamount = dataReader["BilledAmount"] ==
DBNull.Value ? (int?)null :
Convert.ToInt32(dataReader["BilledAmount"]);
address = dataReader["Address"].ToString();
sex = dataReader["Sex"].ToString();
dateofbirth = Convert.ToDateTime(
dataReader["DateOfBirth"]);
admissiondate = Convert.ToDateTime(
dataReader["AdmissionDate"]);
releasedate = Convert.ToDateTime(
dataReader["ReleaseDate"]);
}
}
}

public long PatientID { get { return patientid; } }
public string Name { get { return name; } }
public string ContactPerson { get { return contactperson; } }
public long? SSN { get { return ssn; } }
public int? BilledAmount { get { return billedamount; } }
public string Address { get { return address; } }
public string Sex { get { return sex; } }
public DateTime DateOfBirth { get { return dateofbirth; } }
public DateTime AdmissionDate { get { return admissiondate; } }
public DateTime ReleaseDate { get { return releasedate; } }
}
The two fragments below assign values from from a DataReader containing query fields that might be null to the ContactPerson, BilledAmount, and SSN class properties.

Here's the PatientNonNullable.getPatient() code excerpt:

...
contactperson = dataReader["ContactPerson"] ==
DBNull.Value ? null :
dataReader["ContactPerson"].ToString();
if (dataReader["SSN"] == DBNull.Value)
ssn = -1; //NULL replaced with -1

else
ssn = Convert.ToInt64(dataReader["SSN"]);
if (dataReader["BilledAmount"] == DBNull.Value)
billedamount = 0; //NULL Replaced with Zero
else
billedamount = Convert.ToInt32(
dataReader["BilledAmount"]);
...

The ContactPerson string field has no problems with accepting null values, but when the SSN or BilledAmount database columns contain a nullable value, the PatientNonNullable class must substitute something to provide a valid value for the associated property's non-nullable value type.

In contrast, the PatientNullable class uses nullable types to overcome such problems. Here's the equivalent relevant portion of the PatientNullable.getPatient() method:

...
contactperson = dataReader["ContactPerson"] ==
DBNull.Value ? null :
dataReader["ContactPerson"].ToString();
ssn = dataReader["SSN"] ==
DBNull.Value ? (long?)null :
Convert.ToInt64(dataReader["SSN"]);
billedamount = dataReader["BilledAmount"] ==
DBNull.Value ? (int?)null :
Convert.ToInt32(dataReader["BilledAmount"]);
...


Note how the code ensures that nullable types used for properties containing null values get assigned without run-time exceptions, and without providing substitute values.

With those classes in place, Listing 4 shows the code for a simple console application that instantiates the PatientNullable and PatientNonNullable classes and calls the GetPatient() method of each class:

Listing -4
Listing 4. Simple Console Application:
This application retrieves a patient's data using both PatientNullable and PatientNonNullable and prints the results to the console.

class Program
{
static void Main(string[] args)
{
Program program = new Program();
program.GetNonNullablePatient(1);
program.GetNullablePatient(3);

Console.ReadKey();
}

public void GetNonNullablePatient(int patientID)
{
PatientNonNullable patientNonNull = new
PatientNonNullable();
patientNonNull.GetPatient(patientID);

Console.WriteLine("\n\n\nPatientID = {0}",
patientNonNull.PatientID);
Console.WriteLine("Name = {0}", patientNonNull.Name);

if (patientNonNullable.ContactPerson != null)
Console.WriteLine("ContactPerson = {0}",
patientNonNullable.ContactPerson);

Console.WriteLine("SSN = {0}", patientNonNull.SSN);
Console.WriteLine("BilledAmount = {0}",
patientNonNull.BilledAmount);
Console.WriteLine("Address = {0}", patientNonNull.Address);
Console.WriteLine("Sex = {0}", patientNonNull.Sex);
Console.WriteLine("DateOfBirth = {0}",
patientNonNull.DateOfBirth);
Console.WriteLine("AdmissionDate = {0}",
patientNonNull.AdmissionDate);
Console.WriteLine("ReleaseDate = {0}",
patientNonNull.ReleaseDate);
}

public void GetNullablePatient(int patientID)
{
PatientNullable patientNullable = new PatientNullable();
patientNullable.GetPatient(patientID);
Console.WriteLine("\nApplying Nullable Types:\n\nPatientID = {0}",
patientNullable.PatientID);
Console.WriteLine("Name = {0}", patientNullable.Name);
if (patientNullable.ContactPerson != null)
Console.WriteLine("ContactPerson = {0}",
patientNullable.ContactPerson);
if (patientNullable.SSN.HasValue)
{
long ssn = (long)patientNullable.SSN;
Console.WriteLine("SSN = {0}", ssn);
}

if (patientNullable.BilledAmount.HasValue)
{
int billedAmount = (int)patientNullable.BilledAmount;
Console.WriteLine("BilledAmount = {0}",
patientNullable.BilledAmount);
}
Console.WriteLine("Address = {0}", patientNullable.Address);
Console.WriteLine("Sex = {0}", patientNullable.Sex);
Console.WriteLine("DateOfBirth = {0}",
patientNullable.DateOfBirth);
Console.WriteLine("AdmissionDate = {0}",
patientNullable.AdmissionDate);
Console.WriteLine("ReleaseDate = {0}", patientNullable.ReleaseDate);
}
}

No comments:

Post a Comment