Series

Introduction

An introduction into defensive programming for read rows and column information from a SQL-Server database using base exception and connection classes.

Problem

For one of many reasons, you need to retrieve data from a database where there were either no constraints in an existing .NET solution or users have full reign over adding, editing and removing data outside of an existing .NET solution. 

Preliminary actions

  • Discovery is the first step which means talking to business to learn the business rules and constraints against the data.
  • Work with the database administrator to figure out the table relations, relationships along with are there any stored procedures, triggers and functions for the database.
  • Work out a plan using SQL statements to figure out which records are invalid and provide a view to business so they can make decisions on how they would like to handle this data. In many cases, this is not an easy process as many times business is limited on how much time they can give to IT.
  • Once the above has been worked out it's time to write SQL statements to rectify the bad data which may also involve writing code in Visual Studio to assist with fixing the bad data. Always make a backup of the database before starting this part of the process.
  • Work out a plan with business for them to get at their data which does not compromise the integrity of the database. This may mean allowing them to create a local copy of the data which never is posted back to the production database.

What to avoid

Looking at this issue as code can fix the problem. Many developers will fall prey to "this can be fixed by writing a bunch of code" while this does not address the initial problem of how users can modify data at will. 

Moving forward

No matter how well data clean goes there will be issues with bad data that has been missed. This means we need to be defensive, assume there is still bad data.

The first step is building a solid structure which means a way to handle exceptions. For this, you will need a class such as the one shown next. 

Imports System.Data.SqlClient
 
Public Class BaseExceptionsHandler
 
 Protected mHasException As Boolean
 ''' <summary>
 ''' Indicate the last operation thrown an
 ''' exception or not
 ''' </summary>
 ''' <returns></returns>
 Public ReadOnly Property HasException() As Boolean
 Get
 Return mHasException
 End Get
 End Property
 Protected mHasSqlException As Boolean
 ''' <summary>
 ''' Indicate the last operation thrown an
 ''' exception or not
 ''' </summary>
 ''' <returns></returns>
 Public ReadOnly Property HasSqlException() As Boolean
 Get
 Return mHasSqlException
 End Get
 End Property
 Protected mSqlException As SqlException
 Public ReadOnly Property LastSqlException() As SqlException
 Get
 Return mSqlException
 End Get
 End Property
 Protected mLastException As Exception
 ''' <summary>
 ''' Provides access to the last exception thrown
 ''' </summary>
 ''' <returns></returns>
 Public ReadOnly Property LastException() As Exception
 Get
 Return mLastException
 End Get
 End Property
 ''' <summary>
 ''' If you don't need the entire exception as in
 ''' LastException this provides just the text of the exception
 ''' </summary>
 ''' <returns></returns>
 Public ReadOnly Property LastExceptionMessage As String
 Get
 Return mLastException.Message
 End Get
 End Property
 ''' <summary>
 ''' Indicate for return of a function if there was an
 ''' exception thrown or not.
 ''' </summary>
 ''' <returns></returns>
 Public ReadOnly Property IsSuccessFul As Boolean
 Get
 Return Not mHasException OrElse Not mHasSqlException
 End Get
 End Property
End Class

The exception class above is implemented in a data connection class.

Imports ExceptionsLibrary
''' <summary>
''' Pre-configure connection string using a default server
''' and catalog. In a normal solution this is all that should
''' be required but if at run time the connection needs to
''' change you can change the server and catalog in the class
''' which inherits from this class.
'''
''' BaseExceptionsHandler provides properties which any
''' class inherits from BaseSqlServerConnections can set
''' a property then when the caller method throws an exception
''' you can mark a property that an exception occured
''' then in the high level caller (e.g. in a form) can
''' ask "was there an exception" and if so get the exception
''' via LastException property in BaseExceptionsHandler.
''' </summary>
Public Class BaseSqlServerConnections
 Inherits BaseExceptionsHandler
 
 ''' <summary>
 ''' This points to your database server
 ''' </summary>
 Protected DatabaseServer As String = "KARENS-PC"
 ''' <summary>
 ''' Name of database containing required tables
 ''' </summary>
 Protected DefaultCatalog As String = ""
 Public ReadOnly Property ConnectionString As String
 Get
 Return $"Data Source={DatabaseServer};" &
 $"Initial Catalog={DefaultCatalog};Integrated Security=True"
 End Get
 End Property
 
End Class

Then in your data class implement BaseSqlServerConnections which provides access to use the properties in BaseExceptionHandler class

Imports System.Data.SqlClient
Public Class DataOperations
 Inherits BaseSqlServerConnections
 Public Sub SetConnectionString(ByVal pDatabaseServer As String, ByVal pDefaultCatalog As String)
 DatabaseServer = pDatabaseServer
 DefaultCatalog = pDefaultCatalog
 End Sub
 
End Class

Reading data defensively

Let's first look at reading data incorrectly. In the following code block. There is exception handling but no assertion in regards to checking data types when using a DataReader nor is there an assertion for null data. We have slightly tighter code yet fails to do anything more. 
Public Function GetCustomerDoneWrong(ByVal pIdentifier As Integer) As Customer
 mHasException = False
 
 Dim customer As Customer = Nothing
 
 Dim selectStatement As String = "SELECT FirstName,LastName,Address" &
 ",City,State,ZipCode,JoinDate,Pin,Balance " &
 "FROM dbo.Customer WHERE Id = @Id"
 Try
 Using cn = New SqlConnection(ConnectionString)
 Using cmd = New SqlCommand() With {.Connection = cn, .CommandText = selectStatement}
 cmd.Parameters.AddWithValue("@Id", pIdentifier)
 cn.Open()
 
 Dim reader As SqlDataReader = cmd.ExecuteReader
 
 If reader.HasRows Then
 
 reader.Read()
 customer = New Customer
 
 customer.Id = pIdentifier
 customer.FirstName = reader.GetString(0)
 customer.LastName = reader.GetString(1)
 customer.Address = reader.GetString(2)
 customer.City = reader.GetString(3)
 customer.State = reader.GetString(4)
 customer.ZipCode = reader.GetString(5)
 customer.JoinDate = reader.GetDateTime(6)
 customer.Pin = reader.GetInt32(7)
 customer.Balance = reader.GetDouble(8)
 
 End If
 End Using
 End Using
 Catch ex As Exception
 mHasException = True
 mLastException = ex
 End Try
 
 Return customer
 
End Function

In the following improved code language, extension methods are used.

The extensions test for null values and if the value has the proper type e.g. we expected a date, check to ensure the value is not null and is a date.

Imports System.Data.OleDb
Imports System.Data.SqlClient
 
Public Module LanguageExtensions
 <Runtime.CompilerServices.Extension>
 Public Iterator Function [Select](Of T)(
 ByVal reader As SqlDataReader,
 ByVal projection As Func(Of SqlDataReader, T)) As IEnumerable(Of T)
 
 Do While reader.Read()
 Yield projection(reader)
 Loop
 
 End Function
 <Runtime.CompilerServices.Extension>
 Public Function ExecuteReaderAsync(ByVal source As SqlCommand) As Task(Of SqlDataReader)
 
 Return Task(Of SqlDataReader).Factory.
 FromAsync(New Func(Of AsyncCallback, Object, IAsyncResult)(AddressOf source.BeginExecuteReader),
 New Func(Of IAsyncResult, SqlDataReader)(AddressOf source.EndExecuteReader), Nothing)
 
 End Function
 
 <Runtime.CompilerServices.Extension>
 Public Iterator Function [Select](Of T)(
 ByVal reader As OleDbDataReader,
 ByVal projection As Func(Of OleDbDataReader, T)) As IEnumerable(Of T)
 
 Do While reader.Read()
 Yield projection(reader)
 Loop
 
 End Function
 ''' <summary>
 '''  Gets the record value casted as int or 0.
 ''' </summary>
 ''' <param name = "pReader">The data reader.</param>
 ''' <param name = "pField">The name of the record field.</param>
 ''' <returns>The record value</returns>
 <Runtime.CompilerServices.Extension>
 Public Function GetInt32Safe(ByVal pReader As IDataReader, ByVal pField As String) As Integer
 Return pReader.GetInt32Safe(pField, 0)
 End Function
 
 ''' <summary>
 '''  Gets the record value casted as int or the specified default value.
 ''' </summary>
 ''' <param name = "pReader">The data reader.</param>
 ''' <param name = "pField">The name of the record field.</param>
 ''' <param name = "pDefaultValue">The default value.</param>
 ''' <returns>The record value</returns>
 <Runtime.CompilerServices.Extension>
 Public Function GetInt32Safe(ByVal pReader As IDataReader, ByVal pField As String, ByVal pDefaultValue As Integer) As Integer
 
 Dim value = pReader(pField)
 Return (If(TypeOf value Is Integer, CInt(Fix(value)), pDefaultValue))
 
 End Function
 ''' <summary>
 ''' Gets the record value casted as decimal or 0.
 ''' </summary>
 ''' <param name = "pReader">The data reader.</param>
 ''' <param name = "pField">The name of the record field.</param>
 ''' <returns>The record value</returns>
 <Runtime.CompilerServices.Extension>
 Public Function GetDoubleSafe(ByVal pReader As IDataReader, ByVal pField As String) As Double
 Return pReader.GetDoubleSafe(pField, 0)
 End Function
 
 ''' <summary>
 ''' Gets the record value casted as double or the specified default value.
 ''' </summary>
 ''' <param name = "pReader">The data reader.</param>
 ''' <param name = "pField">The name of the record field.</param>
 ''' <param name = "pDefaultValue">The default value.</param>
 ''' <returns>The record value</returns>
 <Runtime.CompilerServices.Extension>
 Public Function GetDoubleSafe(
 ByVal pReader As IDataReader,
 ByVal pField As String,
 ByVal pDefaultValue As Long) As Double
 
 Dim value = pReader(pField)
 Return (If(TypeOf value Is Double, CDbl(value), pDefaultValue))
 
 End Function
 ''' <summary>
 '''  Gets the record value casted as DateTime or DateTime.MinValue.
 ''' </summary>
 ''' <param name = "pReader">The data reader.</param>
 ''' <param name = "pField">The name of the record field.</param>
 ''' <returns>The record value</returns>
 <Runtime.CompilerServices.Extension>
 Public Function GetDateTimeSafe(ByVal pReader As IDataReader, ByVal pField As String) As Date
 
 Return pReader.GetDateTimeSafe(pField, Date.MinValue)
 
 End Function
 
 ''' <summary>
 ''' Gets the record value casted as DateTime or the specified default value.
 ''' </summary>
 ''' <param name = "pReader">The data reader.</param>
 ''' <param name = "pField">The name of the record field.</param>
 ''' <param name = "pDefaultValue">The default value.</param>
 ''' <returns>The record value</returns>
 <Runtime.CompilerServices.Extension>
 Public Function GetDateTimeSafe(ByVal pReader As IDataReader, ByVal pField As String,
 ByVal pDefaultValue As Date) As Date
 
 Dim value = pReader(pField)
 Return (If(TypeOf value Is Date, CDate(value), pDefaultValue))
 
 End Function
 <Runtime.CompilerServices.Extension>
 Public Function GetStringSafe(ByVal pReader As IDataReader, ByVal pField As String) As String
 
 Return If(TypeOf pReader(pField) Is DBNull, Nothing, pReader(pField).ToString())
 
 End Function
 
End Module

Using the extension methods

Public Function TypicalGetCustomersWithNullsChecks() As List(Of Customer)
 mHasException = False
 
 Dim customerList = New List(Of Customer)
 
 Dim selectStatement As String = "SELECT Id,FirstName,LastName,Address" &
 ",City,State,ZipCode,JoinDate,Pin,Balance " &
 "FROM dbo.Customer"
 
 Try
 Using cn = New SqlConnection(ConnectionString)
 Using cmd = New SqlCommand() With {.Connection = cn, .CommandText = selectStatement}
 cn.Open()
 Dim reader As SqlDataReader = cmd.ExecuteReader
 While reader.Read
 customerList.Add(New Customer With
 {
 .Id = reader.GetInt32(0),
 .FirstName = reader.GetStringSafe("FirstName"),
 .LastName = reader.GetStringSafe("LastName"),
 .Address = reader.GetStringSafe("Address"),
 .City = reader.GetStringSafe("City"),
 .State = reader.GetStringSafe("State"),
 .ZipCode = reader.GetStringSafe("ZipCode"),
 .JoinDate = reader.GetDateTimeSafe("JoinDate"),
 .Pin = reader.GetInt32Safe("Pin"),
 .Balance = reader.GetDoubleSafe("Balance")
 })
 End While
 End Using
 End Using
 Catch ex As Exception
 mHasException = True
 mLastException = ex
 End Try
 
 Return customerList
 
End Function

Let's improve on the last example. The following private method which is in the same data class cleans up the read data method and uses the language extensions above.

Private Function CustomerBuilder1(ByVal pReader As SqlDataReader, Optional ByVal pIdentifier As Integer = 0) As Customer
 
 Dim Identifier As Integer = 0
 
 If pIdentifier > 0 Then
 Identifier = pIdentifier
 Else
 Identifier = Integer.Parse(pReader("id").ToString())
 End If
 
 Return New Customer With
 {
 .Id = Identifier,
 .FirstName = pReader.GetStringSafe("FirstName"),
 .LastName = pReader.GetStringSafe("LastName"),
 .Address = pReader.GetStringSafe("Address"),
 .City = pReader.GetStringSafe("City"),
 .State = pReader.GetStringSafe("State"),
 .ZipCode = pReader.GetStringSafe("ZipCode"),
 .JoinDate = pReader.GetDateTimeSafe("JoinDate", Now),
 .Pin = pReader.GetInt32Safe("Pin", 999),
 .Balance = pReader.GetDoubleSafe("Balance", -1)
 }
 
End Function

Calling the above method.

Public Function GetCustomerWithNullCheckesWithAlternateBuilder(ByVal pIdentifier As Integer) As Customer
 mHasException = False
 
 Dim customer As Customer = Nothing
 
 Dim selectStatement As String = "SELECT FirstName,LastName,Address" &
 ",City,State,ZipCode,JoinDate,Pin,Balance " &
 "FROM dbo.Customer WHERE Id = @Id"
 Try
 Using cn = New SqlConnection(ConnectionString)
 Using cmd = New SqlCommand() With {.Connection = cn, .CommandText = selectStatement}
 cmd.Parameters.AddWithValue("@Id", pIdentifier)
 cn.Open()
 
 Dim reader As SqlDataReader = cmd.ExecuteReader
 
 If reader.HasRows Then
 
 reader.Read()
 
 customer = CustomerBuilder1(reader, pIdentifier)
 
 End If
 End Using
 End Using
 Catch ex As Exception
 mHasException = True
 mLastException = ex
 End Try
 
 Return customer
 
End Function

Unit testing

When dealing with bad data as described above we need to make sure that our code works as expected. Many developers never even consider unit testing yet it's very important to do so. The following are examples of test methods.

Imports System.Text
Imports Microsoft.VisualStudio.TestTools.UnitTesting
Imports DatabackEndLibrary
 
 
<TestClass()> Public Class UnitTest1
 <TestMethod> Public Sub ReadCustomerView()
 
 Dim expectedRowCount As Integer = 5000
 
 Dim ops As New DataOperations
 Dim dt As DataTable = ops.CustomerView
 
 Assert.IsTrue(dt.Rows.Count > 0,
 $"Expected {expectedRowCount} rows")
 
 End Sub
 ''' <summary>
 ''' Make sure our get categories works as expected
 ''' </summary>
 <TestMethod()> Public Sub GetListOfCategories()
 
 Dim expectedCount As Integer = 16
 Dim ops As New DataOperations
 
 Dim results As List(Of Category) = ops.GetCategories
 
 Dim possibleMessage As String =
 If(ops.LastException IsNot Nothing, ops.LastException.Message, "")
 
 Assert.IsTrue(ops.IsSuccessFul,
 $"Operation throw an exception: {possibleMessage}")
 
 Assert.IsTrue(results.Count = expectedCount,
 $"Expected {expectedCount} category rows.")
 
 End Sub
 ''' <summary>
 ''' Test returning reference tables using NextResult off the
 ''' data reader.
 ''' </summary>
 <TestMethod> Public Sub GetReferenceTables()
 Dim ops As New DataOperations
 Dim results = ops.GetReferenceTables
 
 Assert.IsTrue(results.Catagories.Count = 16,
 "Expected 16 categories")
 
 Assert.IsTrue(results.Products.Count = 77,
 "Expected 77 products")
 
 End Sub
 <TestMethod> Public Sub GetStates()
 
 Dim expectedCount As Integer = 50
 Dim ops As New DataOperations
 
 Dim count As Integer = ops.GetStates.Count
 
 Assert.IsTrue(count = expectedCount,
 $"Expected {expectedCount} states")
 
 End Sub
 ''' <summary>
 ''' Bad test and bad code flow in GetCustomers1
 ''' </summary>
 <TestMethod> Public Sub GetSingleCustomerWhichPassesWithThrownException()
 
 Dim ops As New DataOperations
 Dim customer = ops.GetCustomerDoneWrong(3)
 
 Assert.IsTrue(customer IsNot Nothing,
 "Expected customer to be null")
 
 End Sub
 ''' <summary>
 ''' This test passes because the method TypicalGetCustomers
 ''' throw an exception. There is no assertion for null values
 ''' when obtaining data.
 ''' </summary>
 <TestMethod> Public Sub TryGetAllCustomersButWillFail()
 Dim errorMsg As String =
 "Data is Null. This method or property " &
 "cannot be called on Null values."
 
 Dim ops As New DataOperations
 Dim custList As List(Of Customer) = ops.TypicalGetCustomersWithNullsUnChecked
 
 Assert.IsTrue(custList.Count < 5000,
 "Expected less than 5000 records")
 
 Assert.IsTrue(ops.LastException.Message = errorMsg,
 "Unexpected exception message")
 End Sub
 <TestMethod> Public Sub GetAllCustomersWithNullChecks()
 Dim ops As New DataOperations
 Dim custList As List(Of Customer) = ops.TypicalGetCustomersWithNullsChecks
 
 Assert.IsTrue(custList.Count = 5000,
 "Expected 5000 records")
 
 End Sub
 ''' <summary>
 ''' For the record we fetch, there are several null values
 ''' where as coded we can check for the data having no value
 ''' </summary>
 <TestMethod> Public Sub GetSingleCustomerWithValidDataAndNullData()
 
 Dim ops As New DataOperations
 Dim customer = ops.GetCustomerWithNullCheckes(3)
 
 Assert.IsTrue(customer.JoinDate = DateTime.MinValue,
 "Expected JoinDate to be MinValue")
 
 End Sub
 ''' <summary>
 ''' Vaidate language extension method for double functions properly
 ''' and returns a value we supplied
 ''' </summary>
 <TestMethod> Public Sub GetSingleCustomerWithBuilderVerifyBalanceIsNegative()
 
 Dim ops As New DataOperations
 Dim customer = ops.GetCustomerWithNullCheckesWithAlternateBuilder(45)
 
 Assert.IsTrue(customer.Balance = -1,
 "Expected -1 balance")
 
 End Sub
 ''' <summary>
 ''' Vaidate language extension method for DateTime functions properly
 ''' and returns a value we supplied
 ''' </summary>
 <TestMethod> Public Sub GetSingleCustomerWithBuilderAndVerifyNullDateIsToday()
 
 Dim ops As New DataOperations
 Dim customer = ops.GetCustomerWithNullCheckesWithAlternateBuilder(7)
 
 Assert.IsTrue(customer.JoinDate.Date = Now.Date,
 "Expected JoinDate.Date to be Now.Date")
 
 End Sub
 ''' <summary>
 ''' Valid we could read the customer pin
 ''' </summary>
 <TestMethod> Public Sub GetSingleCustomerCheckPinValue()
 
 Dim pin As Integer = 269008
 
 Dim ops As New DataOperations
 Dim customer = ops.GetCustomerWithNullCheckes(4987)
 
 Assert.IsTrue(customer.Pin = pin,
 $"Expected pin to be {pin}")
 
 End Sub
 ''' <summary>
 ''' Validate language exensions worked for null strings
 ''' </summary>
 <TestMethod> Public Sub GetSingleCustomerWithNullStringValues()
 Dim ops As New DataOperations
 Dim customer = ops.GetCustomerWithNullCheckesWithAlternateBuilder(806)
 
 Assert.IsTrue(customer.FirstName Is Nothing,
 "Expected first name to be null")
 
 Assert.IsTrue(customer.LastName Is Nothing,
 "Expected last name to be null")
 
 Assert.IsTrue(customer.Address Is Nothing,
 "Expected address to be null")
 
 End Sub
 ''' <summary>
 ''' Validate the language extension for int works as we assign
 ''' a default value of 999 when Pin is null
 ''' </summary>
 <TestMethod> Public Sub GetSingleCustomerNullPin()
 
 Dim pin As Integer = 999
 Dim ops As New DataOperations
 Dim customer = ops.GetCustomerWithNullCheckesWithAlternateBuilder(859)
 
 Assert.IsTrue(customer.Pin = pin,
 $"Expected Pin to be {pin}")
 
 End Sub
 ''' <summary>
 ''' Determine we are properly handling passing in an
 ''' invalid catalog
 ''' </summary>
 <TestMethod> Public Sub BadConnection()
 
 Dim ops As New DataOperations
 ops.SetConnectionString("KARENS-PC", "NonExistingCatalog")
 Dim results As List(Of Category) = ops.GetCategories
 
 Assert.IsFalse(Not ops.IsSuccessFul,
 "Expected failure with non existing catalog")
 
 End Sub
 
End Class

Source code

See the following MSDN code sample done in VS2017 for a complete solution along with data scripts to create the database, tables, and data.

Building the solution

Final notes

Consider implementing a logging solution to your data operations which logs malformed data and if possible record primary keys, database and table names etc.  Logging could be writing to a text log file or to event logs along with sending an email to yourself, perhaps the DBA and if there is a service desk an email sent to them.