Thursday, March 29, 2007

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 readUnderstanding 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

No comments: