Vishwamohan

In this section, I will show you how to connect Data Access Layer(DAL) to previously designed database customer table in Part -1. But in order to access the database we will require connection string, so let's define the connection string called “LocalSqlServer” in web.config file.

NOTE: Considering it is a quick and dirty example, please keep in mind that DAL depends on customer object in this given example, so you will have to visit the Part 3 where Business Object is created, copy that code first. You will have to comment Data Access Layer calls in Customer class Temporarily, once your DAL methods is ready, you will have to uncomment them to make it work.

Web.Config

<connectionStrings> 

<remove name="LocalSqlServer" /> 

<add name="LocalSqlServer" connectionString="Data Source=Vishwa;Database=Example;User ID=eUser;Password=ePassword;" providerName="System.Data.SqlClient" /> 

</connectionStrings>

Data Access Layer Code:

DataAccess.vb: This class will be consumed by Customer class of Business Logic Layer (BLL) and allow to Get, Insert, Update and Delete customer record. Please note that there is a reference of BLL Namespace for Customer class in this class, which will described in BLL section (Part 3).

Option Explicit On

Option Strict On

Imports System.Data

Imports System.Data.SqlClient

Imports Vishwa.Example.Business

Namespace Vishwa.Example.Data

    Public Class DataAccess

        ' Author : Vishwa Mohan

        ' Date : 10/15/2006

        ' Class : Data Accesss Manager

       ‘ Design Pattern: Singleton

        ' Purpose: An Example to demonstrate Customer Data Access Management

        Private Shared instance As New DataAccess

#Region "--Customer Functions--- "

        Public Shared Function GetCustomers(ByVal custID As Integer) As Customer

            Dim custRecord As New Customer

            Using connection As New SqlConnection(GetConnectionString())

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

                    command.CommandType = CommandType.StoredProcedure

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

                    command.Connection.Open()

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

                        Dim tempCustomer As New Customer

                        custRecord.CustID = CInt(reader.Item("Cust_ID"))

                        custRecord.CustName = reader.Item("Cust_Name").ToString

                        custRecord.CustAddress = reader.Item("Cust_Address").ToString

                        custRecord.CustDOB = CDate(reader.Item("Cust_DOB"))

                        custRecord.DateCreated = CDate(reader.Item("Date_Created"))

                        custRecord.DateModified = CDate(reader.Item("Date_Modified"))

                    End Using

                End Using

            End Using

            Return custRecord

        End Function

        Public Shared Function GetAllCustomers() As Generic.List(Of Customer)

            Dim list As New Generic.List(Of Customer)()

            Using connection As New SqlConnection(GetConnectionString())

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

                    command.CommandType = CommandType.StoredProcedure

                    command.Connection.Open()

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

                        Do While (reader.Read())

                            Dim tempCustomer As New Customer

                            tempCustomer.CustID = CInt(reader.Item("Cust_ID"))

                            tempCustomer.CustName = reader.Item("Cust_Name").ToString

                            tempCustomer.CustAddress = reader.Item("Cust_Address").ToString

                            tempCustomer.CustDOB = CDate(reader.Item("Cust_DOB"))

                            tempCustomer.DateCreated = CDate(reader.Item("Date_Created"))

                            tempCustomer.DateModified = CDate(reader.Item("Date_Modified"))

                            list.Add(tempCustomer)

                        Loop

                    End Using

                End Using

            End Using

            Return list

        End Function

        Public Shared Function InsertCustomer(ByVal custInfo As Customer) As Integer

            Dim returnValue As Integer = -1

            Using connection As New SqlConnection(GetConnectionString())

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

                    With command

                        .CommandType = CommandType.StoredProcedure

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

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

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

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

                        .Connection.Open()

                        .ExecuteNonQuery()

                        returnValue = CInt(command.Parameters("@ReturnID").Value)

                    End With

                End Using

            End Using

            Return returnValue

        End Function

        Public Shared Function UpdateCustomer(ByVal custInfo As Customer) As Integer

            Dim returnValue As Integer = -1

            Using connection As New SqlConnection(GetConnectionString())

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

                    With command

                        .CommandType = CommandType.StoredProcedure

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

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

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

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

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

                        .Connection.Open()

                        .ExecuteNonQuery()

                        returnValue = CInt(command.Parameters("@ReturnID").Value)

                    End With

                End Using

            End Using

            Return returnValue

        End Function

        Public Shared Function DeleteCustomer(ByVal custInfo As Customer) As Integer

            Dim returnValue As Integer = -1

            Using connection As New SqlConnection(GetConnectionString())

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

                    With command

                        .CommandText = "Usp_DeleteCustomer"

                        .CommandType = CommandType.StoredProcedure

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

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

                        .Connection.Open()

                        .ExecuteNonQuery()

                        returnValue = CInt(command.Parameters("@ReturnID").Value)

                    End With

                End Using

            End Using

            Return returnValue

        End Function

#End Region

#Region "Common Connection Functions"

        Private Shared Function GetConnectionString() As String

            Try

                Return ConfigurationManager.ConnectionStrings.Item("LocalSqlServer").ConnectionString

            Catch exc As Exception

                Return ""

            End Try

        End Function

#End Region

#Region "Private Constructor"

        Private Sub New()

        End Sub

#End Region

    End Class

End Namespace

Now you can move on to Business Logic Layer in Part 3