.NET: Defensive data programming (part 1)
Series
Introduction
An introduction into defensive programming for reading 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 to 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
Note: The above classes can be replaced with the following NuGet Package
> Install-Package BaseConnectionLibrary -Version 1.0.3
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
''' <summary>
''' By default we have the catalog set to use
''' </summary>
Public Sub New()
DefaultCatalog = "TipsExample"
End Sub
''' <summary>
''' Provides a way to override setting the
''' server and default catalog
''' </summary>
''' <param name="pDatabaseServer"></param>
''' <param name="pDefaultCatalog"></param>
Public Sub SetConnectionString(
pDatabaseServer As String,
pDefaultCatalog As String)
DatabaseServer = pDatabaseServer
DefaultCatalog = pDefaultCatalog
End Sub
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 a slightly tighter code yet fails to do anything more.
Public Function GetCustomerDoneWrong(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}
cmd.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)(
reader As SqlDataReader,
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(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)(
reader As OleDbDataReader,
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 cast 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(pReader As IDataReader, pField As String) As Integer
Return pReader.GetInt32Safe(pField, 0)
End Function
''' <summary>
''' Gets the record value cast 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(
pReader As IDataReader,
pField As String,
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(pReader As IDataReader, pField As String) As Double
Return pReader.GetDoubleSafe(pField, 0)
End Function
''' <summary>
''' Gets the record value cast 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(
pReader As IDataReader,
pField As String,
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 cast 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(pReader As IDataReader, 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(
pReader As IDataReader,
pField As String,
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(
pReader As IDataReader,
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}
cmd.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(
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 GetCustomerWithNullChecksWithAlternateBuilder(
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}
cmd.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 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 errorMessage As String =
"Data is Null. This method or property " &
"cannot be called on Null values."
Dim ops As New DataOperations
Dim customerList As List(Of Customer) = ops.TypicalGetCustomersWithNullsUnChecked
Assert.IsTrue(customerList.Count < 5000,
"Expected less than 5000 records")
Assert.IsTrue(ops.LastException.Message = errorMessage,
"Unexpected exception message")
End Sub
<TestMethod> Public Sub GetAllCustomersWithNullChecks()
Dim ops As New DataOperations
Dim customerList As List(Of Customer) = ops.TypicalGetCustomersWithNullsChecks
Assert.IsTrue(customerList.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>
''' Validate 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.GetCustomerWithNullChecksWithAlternateBuilder(45)
Assert.IsTrue(customer.Balance = -1,
"Expected -1 balance")
End Sub
''' <summary>
''' Validate 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.GetCustomerWithNullChecksWithAlternateBuilder(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 extensions worked for null strings
''' </summary>
<TestMethod> Public Sub GetSingleCustomerWithNullStringValues()
Dim ops As New DataOperations
Dim customer = ops.GetCustomerWithNullChecksWithAlternateBuilder(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.GetCustomerWithNullChecksWithAlternateBuilder(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 GitHub repository 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.