Microsoft .Net – OleDbDataReader – Null Values

 

Background

A few days ago I found myself reviewing entries in a table and discovered records completely missing in some cases.

TroubleShooting

Source

As I dug in further traced the issue back to a source data source.

SQL

Here is the SQL that gathers metrics for each file


select

      fileid as Fileid,

      groupid as FileGroup,

      size/8 as TotalExtents,

      fileproperty(name,'SpaceUsed')/8 as UsedExtents,

      name as Name,

      filename as FileName	

from dbo.sysfiles
														  where groupid  0
														  order by 1

Issue

Issue – Immediate

The problem manifests itself in cases where we use partial database restores.

For skipped filegroups, here is what is returned :-

  1. dbo.sysfiles
    • size
      • size is 0
    • fileproperty(name,’SpaceUsed’)
      • null

Issue – Long Term

  1. Unfortunately, I did not check the code against what I will generously call “edge cases“.
  2. Due to the two lacks, foresight and testing, my exception handling code was stretched too far
    • It should have been localized

Remediation

Outline

  1. Get Column Position
    • GetOrdinal
  2. Is Column Data Nullable
    • IsDBNull
      • If record/column is null
        • Set Value to default
      • If not null
        • Set value to read record’s column value

C#

C# Code snippet:-


int    iColumnDefault =-1;
String strColumn_UsedExtents = "UsedExtents";
int    iColumn_UsedExtents   = iColumnDefault;

private double EXTENT_SIZE_IN_SQL2K = (64 * 1.000) / (1024.000);

int   dblSizeofExtents = EXTENT_SIZE_IN_SQL2K;

totalAllocation
	= Int32.Parse
		(
			objDBReaderClient["TotalExtents"].ToString()
		)
		* dblSizeofExtents;

//Get Column Position ( Ordinal )
if (iColumn_UsedExtents == iColumnDefault)
{
      iColumn_UsedExtents =
        objDBReaderClient.GetOrdinal(strColumn_UsedExtents);

}

//SqlDataReader.IsDBNull(Int32) Method
if (objDBReaderClient.IsDBNull(iColumn_UsedExtents))
{
	usedAllocation =0;
}
else
{

	usedAllocation
		= Int32.Parse
			(
				objDBReaderClient["UsedExtents"].ToString()
			) * dblSizeofExtents;

}			

References

  1. Microsoft
    • SQL Docs
      • sys.sysfiles
        • Docs / SQL / Reference / System compatibility views / sys.sysfiles
          Link
    • System.Data.SqlClient
      • GetOrdinal
        • Docs / .NET / .NET API browser / System.Data.SqlClient / SqlDataReader / Methods / GetOrdinal
          Link
      • IsDBNull
        • Docs / .NET / .NET API browser / System.Data.SqlClient / SqlDataReader / Methods / IsDBNull
          Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s