ADO.NET – Best Practices for Data Access

This article will first talk about top 5 best practices you should use while accessing database and then demonstrate how to properly use SqlConnection, SqlCommand and SqlDataAdapter objects while working with data using stored procedure or inline SQL for optimal performance. 
 

5 Best Practices

1. Use the Appropriate Data-Access Object – Always use the DataReader's streaming data access for read-only data retrieval operations. Use the DataSet object for data update operations only if you need to perform the updates in disconnected mode. Use the DataView object when you want to work with filtered views of a larger DataSet object. The DataView object provides many of the benefits of the DataSet object, but without as much overhead.
2. Use Stored Procedures, Not Embedded T-SQL: Stored procedures execute much faster than T-SQL statements because they are precompiled on the database server and are reusable. Not only does T-SQL execute more slowly than a stored procedure, but you also can introduce parsing errors into the T-SQL statement, which in turn generates runtime errors. Always execute stored procedures, and use input parameters (preferably with strong typing). Also, avoid using the * wildcard in SQL queries; always specify the exact fields you want to extract.
3. Use SQL Data Types With SQL Server: The .NET Framework and SQL Server use different data types that do not always convert with each other. In addition, the SqlDataReader class provides typed access methods that map retrieved field values into the appropriate structure automatically. Always use typed access methods when retrieving SQL Server data to avoid type-conversion errors.
4. Use Connection Pooling: Always use connection pooling. The SQL Server managed provider supports connection pooling by default, with little effort required on your part. The most work you must do is modifying the connection string to override default settings for connection pooling parameters.
5. Use Centralized Data-Access Functions: Always centralize your data-access functions in a dedicated class file. This lets you maintain your database code in one central location, which makes it easier to write and maintain. A data-access class implements wrapper functions for basic data-access operations, including executing a stored procedure and returning either a DataReader, DataSet, XmlReader, or no return value at all. These functions should be using good programming sense such as proper connection handing, garbage collection, exception handling etc. You can also use Microsoft Enterprise Library, now available on http://www.codeplex.com/entlib and version 3.0 is just ready for release. For more in-depth understanding, you can read “Understanding and Optimizing ADO.NET Performance”  power point presentation by Gert E.R. Drapers, Software Architect at Microsoft Corporation.

Following is an example of Centralized Data Access Class. Connections, exceptions and garbage collection handling are being done through “Using” Statement. These methods will report any exceptions back to the caller, which can be presented in more elegant way in Business Layer or Presentation Layer.  .NET 2.0 framework introduced "Using" keyword in VB.NET. Behind the scenes, the Visual Basic complier translates a Using block into Try…Finally block, exactly as you do manually and any exceptions will be reported back to caller, and then disposes the object with End Using statement. So database connection will be automatically closed once the SqlConnection object gets disposed by End Using Statement, hence no need to explicitly close the connection as it is already done for you in orderly manner.

'--------- Class: DataAccess -----------------------------------------------------------

Option Explicit On

Option Strict On

Imports System.Data

Imports System.Data.SqlClient

Imports System.Web.Configuration

 

''' <summary>

''' DataAccess Class

''' </summary>

''' <remarks></remarks>

Public Class DataAccess

  

#Region "Constructor"

    Private Sub New()

 

    End Sub

#End Region

 

Private Shared instance As New DataAccess

    ''' <summary>

    ''' GetCustomerDataSet1 - Use of SqlDataAdpater and SqlConnection along with Stored procedure

    ''' and sql parameters to Return a DataSet. This option is good, if you

    ''' are returning multiple datatable in dataset.

    ''' </summary>

    ''' <param name="custID"></param>

    ''' <returns>Customer's DataSet with table name Customer</returns>

    ''' <remarks>when Fill method is called,SqlDataAdpater Opens

    ''' and Closes Connection internally, so no need to open connection explicitly.

    ''' You can open the connection explicitly if you will be using the same connection

    ''' for multiple DataTables on same SQLDataAdpater

    ''' </remarks>

    Public Shared Function GetCustomerDataSet1(ByVal custID As Int32) As DataSet

        Dim resultDataSet As New DataSet("Customers")

        Using connection As New SqlConnection(ConnectionString)

            Using dataAdapter As New SqlDataAdapter("dbo.Usp_GetCustomer", connection)

                With dataAdapter

                    .SelectCommand.CommandType = CommandType.StoredProcedure ' Always mention it for stored proc

                    .SelectCommand.CommandTimeout = 60 ' you can change based on the need

                    .SelectCommand.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID

                    .Fill(resultDataSet, "Customer")

                End With

            End Using

        End Using

        Return resultDataSet

    End Function

 

    ''' <summary>

    ''' GetCustomerDataSet2 - Use of SqlDataAdpater, SqlConnection and SqlCommand along with Stored procedure

    ''' and sql parameters to Return a DataSet. This option is good, if you

    ''' are returning multiple datatable in a dataset.

    ''' </summary>

    ''' <param name="custID"></param>

    ''' <returns>Customer's DataSet with table name Customer</returns>

    ''' <remarks>when Fill method is called,SqlDataAdpater Opens

    ''' and Closes Connection internally, so no need to open connection explicitly.

    ''' You can open the connection explicitly if you will be using the same connection

    ''' for multiple DataTables on same SQLDataAdpater

    ''' </remarks>

    Public Shared Function GetCustomerDataSet2(ByVal custID As Int32) As DataSet

        Dim dataAdapter As New SqlDataAdapter

        Dim resultDataSet As New DataSet("Customers")

 

        Using connection As New SqlConnection(ConnectionString)

            Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)

                With command

                    .CommandType = CommandType.StoredProcedure ' Always mention it for stored proc

                    .CommandTimeout = 60 ' you can change based on the need

                    .Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID

                    dataAdapter.SelectCommand = command

                    dataAdapter.Fill(resultDataSet, "Customer")

                End With

                dataAdapter = Nothing

            End Using

        End Using

        Return resultDataSet

    End Function

 

    ''' <summary>

    ''' GetCustomerDataTable1 - Use of SqlDataAdpater and SqlConnection along with Stored procedure and sql parameters

    ''' to Return a DataTable,If you are expecting data from only one table, datatable is best option.

    ''' </summary>

    ''' <param name="custID"></param>

    ''' <returns>Customer's DataTable with table name Customer</returns>

    ''' <remarks>SqlDataAdpater Opens and Closes Connection internally when Fill method is called

    ''' </remarks>

    Public Shared Function GetCustomerDataTable1(ByVal custID As Int32) As DataTable

        Dim resultDataTable As New DataTable("Customer")

        Using connection As New SqlConnection(ConnectionString)

            Using dataAdapter As New SqlDataAdapter("dbo.Usp_GetCustomer", connection)

                With dataAdapter

                    .SelectCommand.CommandType = CommandType.StoredProcedure ' Always mention it for stored proc

                    .SelectCommand.CommandTimeout = 60 ' you can change based on the need

                    .SelectCommand.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID

                    .Fill(resultDataTable)

                End With

            End Using

        End Using

        Return resultDataTable

    End Function

 

    ''' <summary>

    ''' GetCustomerDataTable2 - Use of SqlDataReader, SqlConnection and SqlCommand along with Stored procedure

    ''' and sql parameters to Return a DataTable, If you are expecting data from only one

    ''' table, datatable is best option.

    ''' </summary>

    ''' <param name="custID"></param>

    ''' <returns>Customer's DataTable with table name Customer</returns>

    ''' <remarks>SqlDataReader requires an open connection

    ''' </remarks>

    Public Shared Function GetCustomerDataTable2(ByVal custID As Int32) As DataTable

        Dim resultDataTable As New DataTable("Customer")

        Using connection As New SqlConnection(ConnectionString)

            Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)

                With command

                    .CommandType = CommandType.StoredProcedure

                    .CommandTimeout = 60

                    .Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID

                    .Connection.Open()

                    Using reader As SqlDataReader = .ExecuteReader(CommandBehavior.CloseConnection)

                       resultDataTable.Load(reader)

                    End Using

                End With

            End Using

        End Using

        Return resultDataTable

    End Function

 

    ''' <summary>

    ''' GetCustomer - Use of SqlDataReader, SqlConnection and SqlCommand along with Stored procedure

    ''' with Sql Parameter to Return a Customer record. This examples reads the data in most efficient way

    ''' based on ordinal number rather column name. It also avoids boxing/unboxing which happends during

    ''' reading data on coulmn name and converting into proper datatype.

    ''' </summary>

    ''' <param name="custID"></param>

    ''' <returns>Customer</returns>

    ''' <remarks>You should always read data based on ordinal number and avoid boxing/unboxing.</remarks>

    Public Shared Function GetCustomer(ByVal custID As Int32) As DTOCustomer

        Dim record As New DTOCustomer

        Using connection As New SqlConnection(ConnectionString)

            Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)

                With command

                    .CommandType = CommandType.StoredProcedure

                    .CommandTimeout = 60 ' you can change based on the need

                    .Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID

                    .Connection.Open()

                End With

 

                Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)

                    Dim custIDOrdinal As Integer = reader.GetOrdinal("Cust_ID")

                    Dim custNameOrdinal As Integer = reader.GetOrdinal("Cust_Name")

                    Dim custDOBOrdinal As Integer = reader.GetOrdinal("Cust_DOB")

                    Dim custAddressOrdinal As Integer = reader.GetOrdinal("Cust_Address")

                    Dim custDateCreatedOrdinal As Integer = reader.GetOrdinal("Date_Created")

                    Dim custDateModifiedOrdinal As Integer = reader.GetOrdinal("Date_Modified")

 

                    If reader.Read Then

                        'Option -1 **** Based on Column ordinal number - More efficient code

                        record.CustID = reader.GetInt32(custIDOrdinal) ' Cust ID is Primary Key so it can not be NULL

                        If Not reader.IsDBNull(custNameOrdinal) Then record.CustName = reader.GetString(custNameOrdinal)

                        If Not reader.IsDBNull(custDOBOrdinal) Then record.CustDOB = reader.GetDateTime(custDOBOrdinal)

                        If Not reader.IsDBNull(custAddressOrdinal) Then record.CustAddress = reader.GetString(custAddressOrdinal)

                        If Not reader.IsDBNull(custDateCreatedOrdinal) Then record.DateCreated = reader.GetDateTime(custDateCreatedOrdinal)

                        If Not reader.IsDBNull(custDateModifiedOrdinal) Then record.DateModified = reader.GetDateTime(custDateModifiedOrdinal)

 

                        'Option -2 *****Based on column name - Less efficient code THUS COMMENTED

                        'record.CustID = CInt(reader.Item("Cust_ID"))

                        'record.CustName = reader.Item("Cust_Name").ToString

                        'If Not IsDBNull(reader.Item("Cust_DOB")) Then record.CustDOB = CDate(reader.Item("Cust_DOB"))

                        'record.CustAddress = reader.Item("Cust_Address").ToString

                        'If Not IsDBNull(reader.Item("Date_Created")) Then record.DateCreated = CDate(reader.Item("Date_Created"))

                        'If Not IsDBNull(reader.Item("Date_Modified")) Then record.DateModified = CDate(reader.Item("Date_Modified"))

                    End If

                End Using

            End Using

        End Using

        Return record

    End Function

 

    ''' <summary>

    ''' InsertTestData - Use of Inline SQL Statement with parameterized Query for Improved performance

    ''' and importantce of Prepare method of SqlCommand Object

    ''' </summary>

    ''' <param name="custName"></param>

    ''' <param name="custDOB"></param>

    ''' <param name="custAddress"></param>

    ''' <returns>No of rows affected</returns>

    ''' <remarks></remarks>

    Public Shared Function InsertTestData(ByVal custName As String, ByVal custDOB As DateTime, ByVal custAddress As String) As Int32

        Dim sqlString As String = "INSERT dbo.tbl_Customer(Cust_Name,Cust_DOB,Cust_Address) VALUES (@Name,@DOB,@Address)"

        Using conConnection As New SqlConnection(ConnectionString)

            Using cmdCommand As New SqlCommand(sqlString, conConnection)

                With cmdCommand

                    .CommandType = CommandType.Text

                    .CommandTimeout = 60 ' you can change based on the need

                    .Parameters.Add(New SqlParameter("@Name", SqlDbType.VarChar, 50)).Value = custName

                    .Parameters.Add(New SqlParameter("@DOB", SqlDbType.DateTime)).Value = custDOB

                    .Parameters.Add(New SqlParameter("@Address", SqlDbType.VarChar, 100)).Value = custAddress

                    .Connection.Open()

                    .Prepare() ' Important for the performance of inline SQL

                    Return .ExecuteNonQuery()

                End With

            End Using

        End Using

    End Function

 

    ''' <summary>

    ''' InsertCustomer - Inserts a new customer

    ''' </summary>

    ''' <param name="custName"></param>

    ''' <param name="custDOB"></param>

    ''' <param name="custAddress"></param>

    ''' <returns>Auto Generated Cust ID</returns>

    ''' <remarks>It also use the Return Value Paramater</remarks>

    Public Shared Function InsertCustomer(ByVal custName As String, ByVal custDOB As DateTime, _

                                          ByVal custAddress As String) As Int32

        Using connection As New SqlConnection(ConnectionString)

            Using command As New SqlCommand("dbo.Usp_InsertCustomer", connection)

                With command

                    .CommandType = CommandType.StoredProcedure

                    .CommandTimeout = 60 ' The value you can change based on the need

                    .Parameters.Add(New SqlParameter("@Return", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue

                    .Parameters.Add(New SqlParameter("@CustName", SqlDbType.VarChar, 50)).Value = custName

                    .Parameters.Add(New SqlParameter("@CustDOB", SqlDbType.DateTime)).Value = custDOB

                    .Parameters.Add(New SqlParameter("@CustAddress", SqlDbType.VarChar, 100)).Value = custAddress

                    .Connection.Open()

                    .ExecuteNonQuery()

                    Return CInt(.Parameters("@Return").Value)

                End With

            End Using

        End Using

    End Function

 

    ''' <summary>

    ''' UpdateCustomer - Updates a Customer Record based on Cust ID

    ''' </summary>

    ''' <param name="custID"></param>

    ''' <param name="custName"></param>

    ''' <param name="custAddress"></param>

    ''' <returns>If Updated Successully then passed Cust ID else -1 if failed</returns>

    ''' <remarks></remarks>

    Public Shared Function UpdateCustomer(ByVal custID As Int32, ByVal custName As String, ByVal custDOB As DateTime, _

                                          ByVal custAddress As String) As Int32

        Using connection As New SqlConnection(ConnectionString)

            Using command As New SqlCommand("dbo.Usp_UpdateCustomer", connection)

                With command

                    .CommandType = CommandType.StoredProcedure

                    .CommandTimeout = 60 ' The value you can change based on the need

                    .Parameters.Add(New SqlParameter("@Return", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue

                    .Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID

                    .Parameters.Add(New SqlParameter("@CustName", SqlDbType.VarChar, 50)).Value = custName

                    .Parameters.Add(New SqlParameter("@CustDOB", SqlDbType.DateTime)).Value = custDOB

                    .Parameters.Add(New SqlParameter("@CustAddress", SqlDbType.VarChar, 100)).Value = custAddress

                    .Connection.Open()

                    .ExecuteNonQuery()

                    Return CInt(.Parameters("@Return").Value)

                End With

            End Using

        End Using

    End Function

 

    ''' <summary>

    ''' DeleteCustomer - Deletes a Customer record on Cust ID

    ''' </summary>

    ''' <param name="custID">integer</param>

    ''' <returns>If Deleted Successully then passed Cust ID else -1 if failed</returns>

    ''' <remarks></remarks>

    Public Shared Function DeleteCustomer(ByVal custID As Int32) As Int32

        Using connection As New SqlConnection(ConnectionString)

            Using command As New SqlCommand("dbo.Usp_DeleteCustomer", connection)

                With command

                    .CommandType = CommandType.StoredProcedure

                    .CommandTimeout = 60 ' The value you can change based on the need

                    .Parameters.Add(New SqlParameter("@Return", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue

                    .Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID

                    .Connection.Open()

                    .ExecuteNonQuery()

                    Return CInt(.Parameters("@Return").Value) ' If fails then -1 else passed cust id

                End With

            End Using

        End Using

    End Function

 

    ' The following 5 methods you should use only in exceptional case

    ''' <summary>

    ''' GetResultAsDataReader- A generic method to return a SqlDataReader to calling method.

    ''' </summary>

    ''' <param name="sqlString"></param>

    ''' <returns>SqlDataReader</returns>

    ''' <remarks>It is very important to close SqlDataReader after its use in calling method</remarks>

    Public Shared Function GetResultAsDataReader(ByVal sqlString As String) As SqlDataReader

        Dim connection As New SqlConnection(ConnectionString)

        Using command As New SqlCommand(sqlString, connection)

            With command

                .CommandType = CommandType.Text

                .CommandTimeout = 60

                .Connection.Open()

                Return .ExecuteReader(CommandBehavior.CloseConnection)

            End With

        End Using

    End Function

 

    ''' <summary>

    ''' GetResultAsDataTable - A generic method to return a datatable on a SQL String

    ''' </summary>

    ''' <param name="sqlString"></param>

    ''' <returns>DataTable</returns>

    ''' <remarks>SQL Statement must be returning one result set.

    ''' Inline SQL Statements are slower then Stored Procedure

    ''' You should avoid using this unless you have exceptional case

    ''' </remarks>

    Public Shared Function GetResultAsDataTable(ByVal sqlString As String) As DataTable

        Dim resultTable As New DataTable

        Using connection As New SqlConnection(ConnectionString)

            Using command As New SqlCommand(sqlString, connection)

                With command

                    .CommandType = CommandType.Text

                    .CommandTimeout = 60

                    .Connection.Open()

                    Using reader As SqlDataReader = .ExecuteReader(CommandBehavior.CloseConnection)

                        If reader.HasRows Then resultTable.Load(reader)

                    End Using

                End With

            End Using

        End Using

        Return resultTable

    End Function

 

    ''' <summary>

    ''' GetResultAsDataSet - A Generic Method to return a dataset on a SQL String

    ''' </summary>

    ''' <param name="sqlString"></param>

    ''' <returns>DataSet</returns>

    ''' <remarks>Inline SQL Statements are slower then Stored Procedure

    ''' Inline SQL Statements are slower then Stored Procedure

    ''' You should avoid using this unless you have exceptional case

    ''' </remarks>

    Public Shared Function GetResultAsDataSet(ByVal sqlString As String) As DataSet

        Dim resultDataSet As New DataSet

        Using connection As New SqlConnection(ConnectionString)

            Using dataAdapter As New SqlDataAdapter(sqlString, connection)

                With dataAdapter

                    .SelectCommand.CommandType = CommandType.Text

                    .SelectCommand.CommandTimeout = 60

                    .Fill(resultDataSet)

                End With

                Return resultDataSet

            End Using

        End Using

    End Function

 

    ''' <summary>

    ''' RunSQLStatement - A generic method to execute a sql statement

    ''' </summary>

    ''' <param name="sqlString"></param>

    ''' <returns>No of rows affected</returns>

    ''' <remarks>Inline SQL Statements are slower then Stored Procedure

    ''' You should avoid using this unless you have exceptional case

    ''' </remarks>

    Public Shared Function RunSQLStatement(ByVal sqlString As String) As Int32

        Using connection As New SqlConnection(ConnectionString)

            Using dataAdapter As New SqlDataAdapter(sqlString, connection)

                With dataAdapter

                    .SelectCommand.CommandType = CommandType.Text

                    .SelectCommand.CommandTimeout = 60

                    .SelectCommand.Connection.Open()

                    Return (.SelectCommand.ExecuteNonQuery())

                End With

            End Using

        End Using

    End Function

 

    ''' <summary>

    ''' ConnectionString - A generic Property to get connection string

    ''' </summary>

    ''' <value>none</value>

    ''' <returns>connection string</returns>

    ''' <remarks>One place to make any change in connection string call</remarks>

    Public Shared ReadOnly Property ConnectionString() As String

        Get

            Try

                Return WebConfigurationManager.ConnectionStrings.Item("ExampleDB").ConnectionString

                'In ASP.NET Use ConfigurationManager instead of WebConfigurationManager

            Catch exc As Exception

                Return ""

            End Try

        End Get

    End Property

 

    ''' <summary>

    ''' CloseConnection- A generic method to close SqlConnection

    ''' </summary>

    ''' <param name="connection">SqlConnection</param>

    ''' <remarks></remarks>

    Public Shared Sub CloseConnection(ByRef connection As SqlConnection)

        If Not connection Is Nothing AndAlso connection.State = ConnectionState.Open Then connection.Close()

        connection = Nothing

    End Sub

 

    ''' <summary>

    ''' CloseDataReader - A generic method to close SqlDataReaer

    ''' </summary>

    ''' <param name="reader">SqlDataReaer</param>

    ''' <remarks></remarks>

    Public Shared Sub CloseDataReader(ByRef reader As SqlClient.SqlDataReader)

        If Not reader Is Nothing AndAlso Not reader.IsClosed Then reader.Close()

        reader = Nothing

    End Sub

 

End Class

 

'-----Class: DTOCustomer ------------------------------

Option Strict On

Option Explicit On

 

Public Class DTOCustomer

 

    Private _custID As Integer = 0

    Private _custName As String = String.Empty

    Private _custDOB As DateTime = DateTime.MinValue

    Private _custAddress As String = String.Empty

    Private _dateCreated As DateTime = DateTime.Now

    Private _dateModified As DateTime = DateTime.Now

 

#Region "Constructor"

    Public Sub New()

 

    End Sub

#End Region

 

#Region "Properties"

 

    Public Property CustID() As Integer

        Get

            Return _custID

        End Get

 

        Set(ByVal value As Int32)

            _custID = value

        End Set

    End Property

 

    Public Property CustName() As String

        Get

            Return _custName

        End Get

        Set(ByVal value As String)

            _custName = value

        End Set

    End Property

 

    Public Property CustDOB() As DateTime

        Get

            Return _custDOB

        End Get

        Set(ByVal value As DateTime)

            _custDOB = value

        End Set

    End Property

 

    Public Property CustAddress() As String

        Get

            Return _custAddress

        End Get

        Set(ByVal value As String)

            _custAddress = value

        End Set

    End Property

 

    Public Property DateCreated() As DateTime

        Get

            Return _dateCreated

        End Get

        Set(ByVal value As DateTime)

            _dateCreated = value

        End Set

    End Property

 

    Public Property DateModified() As DateTime

        Get

            Return _dateModified

        End Get

        Set(ByVal value As DateTime)

            _dateModified = value

        End Set

    End Property

 

#End Region

 

End Class

 

‘---Uses of Above Method and Class

 

Private Sub GetCustomerDataSet(ByVal custID As Int32)

        Dim dstRecord As New DataSet

        Try

            dstRecord = DataAccess.GetCustomerDataSet1(custID)

            'dstRecord = DataAccess.GetCustomerDataSet2(custID)

            Call ReadFromCustomerDataTable(dstRecord.Tables("Customer"))

 

        Catch ex As Exception

            MsgBox(ex.Message)

        Finally

            dstRecord = Nothing

        End Try

    End Sub

 

    Private Sub GetCustomerDataTable(ByVal custID As Int32)

        Dim record As New DataTable

        Try

            record = DataAccess.GetCustomerDataTable1(custID)

            'record = DataAccess.GetCustomerDataTable2(custID)

            Call ReadFromCustomerDataTable(record)

 

        Catch ex As Exception

            MsgBox(ex.Message)

        Finally

            record = Nothing

        End Try

    End Sub

 

    Private Sub GetCustomer(ByVal custID As Int32)

        Dim record As New DTOCustomer

        Try

            record = DataAccess.GetCustomer(custID)

            If Not record Is Nothing Then

                Me.txtCustName.Text = record.CustName

                Me.txtCustDOB.Text = record.CustDOB

                Me.txtCustAdd.Text = record.CustAddress

            Else

                Me.txtCustName.Text = ""

                Me.txtCustDOB.Text = ""

                Me.txtCustAdd.Text = ""

            End If

        Catch ex As Exception

            MsgBox(ex.Message)

        Finally

            record = Nothing

        End Try

    End Sub

 

    Private Sub ReadCustomerFromDataReader(ByVal custID As Integer)

        Dim reader As SqlClient.SqlDataReader = Nothing

        Try

            reader = DataAccess.GetResultAsDataReader("dbo.Usp_GetCustomer " & custID.ToString)

            Dim custNameOrdinal As Integer = reader.GetOrdinal("Cust_Name")

            Dim custAddressOrdinal As Integer = reader.GetOrdinal("Cust_Address")

            Dim custDOBOrdinal As Integer = reader.GetOrdinal("Cust_DOB")

 

            If reader.Read Then

                If Not reader.IsDBNull(custNameOrdinal) Then Me.txtCustName.Text = reader.GetString(custNameOrdinal)

                If Not reader.IsDBNull(custAddressOrdinal) Then Me.txtCustAdd.Text = reader.GetString(custAddressOrdinal)

                If Not reader.IsDBNull(custDOBOrdinal) Then Me.txtCustDOB.Text = reader.GetDateTime(custDOBOrdinal).ToShortDateString

            Else

                Me.txtCustName.Text = ""

                Me.txtCustDOB.Text = ""

                Me.txtCustAdd.Text = ""

            End If

        Catch ex As Exception

            MsgBox(ex.Message)

        Finally

            DataAccess.CloseDataReader(reader)

        End Try

    End Sub

 

    ''' <summary>

    ''' Different options to read data from data table

    ''' </summary>

    ''' <param name="custDataTable"></param>

    ''' <remarks>If you have only one row, Option 1 is Ok</remarks>

    Private Sub ReadFromCustomerDataTable(ByVal custDataTable As DataTable)

        Me.txtCustName.Text = ""

        Me.txtCustDOB.Text = ""

        Me.txtCustAdd.Text = ""

 

        ''--Option 1 - Slowest -- but easy to code

        If custDataTable.Rows.Count > 0 Then

            If Not custDataTable.Rows(0).IsNull("Cust_Name") Then Me.txtCustName.Text = custDataTable.Rows(0).Item("Cust_Name").ToString

            If Not custDataTable.Rows(0).IsNull("Cust_DOB") Then Me.txtCustDOB.Text = CDate(custDataTable.Rows(0).Item("Cust_DOB")).ToShortDateString

            If Not custDataTable.Rows(0).IsNull("Cust_Address") Then Me.txtCustAdd.Text = custDataTable.Rows(0).Item("Cust_Address").ToString

 

        End If

 

        '--Option 2 - Good --

        'If custDataTable.Rows.Count > 0 Then

        '    Dim custNameDataColumn As DataColumn = custDataTable.Columns("Cust_Name")

        '    Dim custDOBDataColumn As DataColumn = custDataTable.Columns("Cust_DOB")

        '    Dim custAddDataColumn As DataColumn = custDataTable.Columns("Cust_Address")

        '    For Each rowOrder As DataRow In custDataTable.Rows

        '        If Not rowOrder.IsNull(custNameDataColumn) Then Me.txtCustName.Text = rowOrder.Item(custNameDataColumn).ToString

        '        If Not rowOrder.IsNull(custDOBDataColumn) Then Me.txtCustDOB.Text = CDate(rowOrder.Item(custDOBDataColumn)).ToShortDateString

        '        If Not rowOrder.IsNull(custAddDataColumn) Then Me.txtCustAdd.Text = rowOrder.Item(custAddDataColumn).ToString

        '    Next

        'End If

 

        '--Option 3 - Best

        'Using reader As DataTableReader = custDataTable.CreateDataReader

        '    Dim custNameOrdNo As Integer = reader.GetOrdinal("Cust_Name")

        '    Dim custDOBOrdNo As Integer = reader.GetOrdinal("Cust_DOB")

        '    Dim custAddOrdNo As Integer = reader.GetOrdinal("Cust_Address")

        '    If reader.Read Then

        '        If Not reader.IsDBNull(custNameOrdNo) Then Me.txtCustName.Text = reader.GetString(custNameOrdNo)

        '        If Not reader.IsDBNull(custDOBOrdNo) Then Me.txtCustDOB.Text = reader.GetDateTime(custDOBOrdNo).ToShortDateString

        '        If Not reader.IsDBNull(custAddOrdNo) Then Me.txtCustAdd.Text = reader.GetString(custAddOrdNo)

        '    End If

        'End Using

    End Sub