Working with Nullable Types in C# : Page 2

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 1. Sample Table: Notice that three of the columns in this table allow null values.

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

Executing the SQL script in Listing 1 creates the Patient table in your database (see Figure 1). 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. 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 (available with the downloadable code) that instantiates the PatientNullable and PatientNonNullable classes and calls the GetPatient() method of each class:

Previous Page

Next Page