How to Create 3 Tier Vb.Net Desktop Application

3 Tiers are 

1. DLL (Data Logic Layer)
2. BLL (Business Logic Layer )
3. UI/UX (User Interface)

DLL Code

DataAccess.vb Code

#Region "Data Access <<Imported NameSpaces>>"
Imports System.Data.Odbc
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Xml
Imports System.Data.Common
Imports System.Text
Imports System.Configuration
#End Region
#Region "Data Access <<Namespace, Class, Functions, Proeprties, etc.>>"
Namespace Kcon.Practices.PrivateLibrary.DataLib
    Public Class DataAccess
#Region "Private Variables"
        Private databaseProviderFactory As DbProviderFactory
        Private databaseConnection As DbConnection
        Private connectionString As String
        Private providerName As String
        Private databaseTransaction As DbTransaction
        Private transactionIsolationLevel As IsolationLevel
       
#End Region
#Region "Private subroutine/Functions"
        Private Sub GetConnectionInfoFromConfig()
            Try
               Dim settings As ConnectionStringSettingsCollection = ConfigurationManager.ConnectionStrings
                If Not settings Is Nothing Then
                    If settings.Count > 0 Then
                        For Each cs As ConnectionStringSettings In settings
                            connectionString = (cs.ConnectionString)
                            providerName = cs.ProviderName
                            'Exit For
                        Next
                    End If
                End If

            Catch ex As Exception
                Throw ex
            End Try
        End Sub
        Private Sub GetFactory()
            Try
                databaseProviderFactory = DbProviderFactories.GetFactory(providerName)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
        Private Function GetCommand(ByVal requestParameter As Request) As DbCommand
            Dim requestParameterInformation As Request.ParameterInformation = Nothing
            Dim databaseCommand As DbCommand = Nothing
            Try
                databaseCommand = databaseProviderFactory.CreateCommand
                databaseCommand.CommandText = requestParameter.CommandText
                databaseCommand.CommandType = requestParameter.CommandType

                If requestParameter.Parameters.Count > 0 Then
                    For Each requestParameterInformation In requestParameter.Parameters
                        databaseCommand.Parameters.Add(GetParameter(requestParameterInformation))
                    Next
                End If
                Return databaseCommand
            Catch ex As Exception
                Throw ex
            End Try
        End Function
        Private Function GetParameter(ByVal requestParameterInformation As Request.ParameterInformation) As DbParameter
            Dim databaseParameter As DbParameter = Nothing
            Try
                databaseParameter = databaseProviderFactory.CreateParameter
                databaseParameter.ParameterName = requestParameterInformation.ParameterName
                databaseParameter.Value = requestParameterInformation.ParameterValue
                databaseParameter.DbType = requestParameterInformation.ParameterType
                databaseParameter.Direction = requestParameterInformation.ParameterDirection
                Return databaseParameter
            Catch ex As Exception
                Throw ex
            End Try
        End Function
        Private Sub GetConnection()
            Try
                If databaseConnection Is Nothing Then
                    databaseConnection = databaseProviderFactory.CreateConnection
                End If
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
        Private Sub OpenConnection()
            Try
                databaseConnection.ConnectionString = connectionString
                If databaseConnection.State = ConnectionState.Closed Then
                    databaseConnection.Open()
                End If
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
        Private Sub CloseConnection()
            If Not databaseConnection Is Nothing Then
                databaseConnection.Close()
                databaseConnection.Dispose()
                databaseConnection = Nothing
            End If
        End Sub
        Private Function GetAdapter() As DbDataAdapter
            Try
                Return databaseProviderFactory.CreateDataAdapter()
            Catch ex As Exception
                Throw ex
            End Try
        End Function
        Private Sub GetTransaction(ByVal transIsolationLevel As IsolationLevel)
            Try
                If databaseTransaction Is Nothing Then
                    databaseTransaction = databaseConnection.BeginTransaction(transIsolationLevel)
                End If
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
        Private Sub DisposeTransaction()
            Try
                databaseTransaction.Dispose()
                databaseTransaction = Nothing
                CloseConnection()
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
#End Region
#Region "Public subroutine/Functions"
        Public Function ExecuteDataReader(ByVal requestParameter As Request) As DbDataReader
            Dim databaseCommand As DbCommand = Nothing
            Try
                GetConnectionInfoFromConfig()
                GetFactory()
                databaseCommand = GetCommand(requestParameter)
                GetConnection()
                databaseCommand.Connection = databaseConnection
                OpenConnection()
                Return databaseCommand.ExecuteReader(CommandBehavior.CloseConnection)
            Catch ex As Exception
                If databaseConnection.State <> ConnectionState.Closed Then
                    CloseConnection()
                End If
                Throw ex
            Finally
                If Not databaseCommand Is Nothing Then
                    databaseCommand.Dispose()
                    databaseCommand = Nothing
                End If
                If Not databaseProviderFactory Is Nothing Then
                    databaseProviderFactory = Nothing
                End If
            End Try
        End Function
        Public Function ExecuteDataTable(ByVal requestParameter As Request) As DataTable
            Dim databaseCommand As DbCommand = Nothing
            Dim databaseDataAdpter As DbDataAdapter = Nothing
            Dim finalDatatable As DataTable = Nothing
            Try
                GetConnectionInfoFromConfig()
                GetFactory()
                databaseCommand = GetCommand(requestParameter)
                databaseDataAdpter = GetAdapter()

                GetConnection()
                databaseCommand.Connection = databaseConnection
                databaseDataAdpter.SelectCommand = databaseCommand
                OpenConnection()

                finalDatatable = New DataTable
                databaseDataAdpter.Fill(finalDatatable)

                CloseConnection()

                If requestParameter.TableName <> "" Then
                    finalDatatable.TableName = requestParameter.TableName
                End If
                Return finalDatatable
            Catch ex As Exception
                Throw ex
            Finally
                If databaseConnection.State <> ConnectionState.Closed Then
                    CloseConnection()
                End If
                If Not databaseCommand Is Nothing Then
                    databaseCommand.Dispose()
                    databaseCommand = Nothing
                End If
                If Not databaseDataAdpter Is Nothing Then
                    databaseDataAdpter = Nothing
                End If
                If Not finalDatatable Is Nothing Then
                    finalDatatable.Dispose()
                    finalDatatable = Nothing
                End If
                If Not databaseProviderFactory Is Nothing Then
                    databaseProviderFactory = Nothing
                End If
            End Try
        End Function
        Public Function ExecuteDataSet(ByVal requestParameter As Request) As DataSet
            Dim databaseCommand As DbCommand = Nothing
            Dim databaseDataAdpter As DbDataAdapter = Nothing
            Dim finalDataSet As DataSet = Nothing
            Try
                GetConnectionInfoFromConfig()
                GetFactory()
                databaseCommand = GetCommand(requestParameter)
                databaseDataAdpter = GetAdapter()

                GetConnection()
                databaseCommand.Connection = databaseConnection
                databaseDataAdpter.SelectCommand = databaseCommand
                OpenConnection()

                finalDataSet = New DataSet
                databaseDataAdpter.Fill(finalDataSet)

                CloseConnection()


                Return finalDataSet
            Catch ex As Exception
                Throw ex
            Finally
                If databaseConnection.State <> ConnectionState.Closed Then
                    CloseConnection()
                End If
                If Not databaseCommand Is Nothing Then
                    databaseCommand.Dispose()
                    databaseCommand = Nothing
                End If
                If Not databaseDataAdpter Is Nothing Then
                    databaseDataAdpter = Nothing
                End If
                If Not finalDataSet Is Nothing Then
                    finalDataSet.Dispose()
                    finalDataSet = Nothing
                End If
                If Not databaseProviderFactory Is Nothing Then
                    databaseProviderFactory = Nothing
                End If
            End Try
        End Function
        Public Function ExecuteDataSet(ByVal requestParameter As Request(), ByVal relation As Relation()) As DataSet
            Dim finalDataSet As DataSet = Nothing
            Try
                Dim i As Int16
                If Not requestParameter Is Nothing Then
                    GetConnectionInfoFromConfig()
                    GetFactory()
                    GetConnection()
                    OpenConnection()
                    finalDataSet = New DataSet
                    For i = 0 To requestParameter.Length - 1
                        Dim databaseCommand As DbCommand = Nothing
                        Dim databaseDataAdpter As DbDataAdapter = Nothing
                        Dim request As Request = Nothing
                        Try
                            request = requestParameter(i)
                            databaseCommand = GetCommand(request)
                            databaseDataAdpter = GetAdapter()

                            databaseCommand.Connection = databaseConnection
                            databaseDataAdpter.SelectCommand = databaseCommand

                            databaseDataAdpter.Fill(finalDataSet)
                            If request.TableName <> "" Then
                                finalDataSet.Tables(i).TableName = request.TableName
                            End If
                        Catch ex As Exception
                            Throw ex
                        Finally
                            If Not databaseCommand Is Nothing Then
                                databaseCommand.Dispose()
                                databaseCommand = Nothing
                            End If
                            If Not databaseDataAdpter Is Nothing Then
                                databaseDataAdpter = Nothing
                            End If
                            If Not request Is Nothing Then
                                request = Nothing
                            End If
                        End Try
                    Next
                    CloseConnection()
                End If

                'Code to add relationships

                If Not relation Is Nothing Then
                    For i = 0 To relation.Length - 1
                        Dim relationInformation As Relation = Nothing
                        Try
                            relationInformation = relation(i)

                            If relationInformation.ParentCoulmnName.Length = relationInformation.ChildCoulmnName.Length Then
                                Dim databaseRelation As DataRelation = Nothing
                                Try
                                    Dim parentDataColumn() As DataColumn
                                    Dim childDataColumn() As DataColumn
                                    Dim j As Integer
                                    ReDim parentDataColumn(relationInformation.ParentCoulmnName.Length - 1)
                                    For j = 0 To relationInformation.ParentCoulmnName.Length - 1
                                        parentDataColumn(j) = finalDataSet.Tables(relationInformation.ParentTableName).Columns(relationInformation.ParentCoulmnName(j))
                                    Next

                                    ReDim childDataColumn(relationInformation.ChildCoulmnName.Length - 1)
                                    For j = 0 To relationInformation.ChildCoulmnName.Length - 1
                                        childDataColumn(j) = finalDataSet.Tables(relationInformation.ChildTableName).Columns(relationInformation.ChildCoulmnName(j))
                                    Next

                                    databaseRelation = New DataRelation(relationInformation.RelationName, parentDataColumn, childDataColumn)
                                    databaseRelation.Nested = True
                                    finalDataSet.Relations.Add(databaseRelation)
                                Catch ex As Exception
                                    Throw ex
                                Finally
                                    If Not databaseRelation Is Nothing Then
                                        databaseRelation = Nothing
                                    End If
                                End Try

                            End If
                        Catch ex As Exception
                            Throw ex
                        Finally
                            If Not relationInformation Is Nothing Then
                                relationInformation = Nothing
                            End If
                        End Try
                    Next
                End If
                Return finalDataSet
            Catch ex As Exception
                Throw ex
            Finally
                If databaseConnection.State <> ConnectionState.Closed Then
                    CloseConnection()
                End If
                If Not databaseProviderFactory Is Nothing Then
                    databaseProviderFactory = Nothing
                End If
            End Try
        End Function
        Public Function ExecuteXMLDocument(ByVal requestParameter As Request(), ByVal relation As Relation(), Optional ByVal docTitle As String = "") As XmlDocument
            Dim finalDataSet As DataSet = Nothing
            Dim xmlDoc As XmlDocument = Nothing
            Try
                finalDataSet = ExecuteDataSet(requestParameter, relation)
                If docTitle <> "" Then
                    finalDataSet.DataSetName = docTitle
                End If
                xmlDoc.LoadXml(finalDataSet.GetXml)
                Return xmlDoc
            Catch ex As Exception
                Throw ex
            Finally
                If Not finalDataSet Is Nothing Then
                    finalDataSet.Dispose()
                    finalDataSet = Nothing
                End If
                If Not xmlDoc Is Nothing Then
                    xmlDoc = Nothing
                End If
            End Try
        End Function
        Public Function ExecuteXMLDocument(ByVal requestParameter As Request) As XmlDocument
            Dim finalDataSet As DataSet = Nothing
            Dim xmlDoc As XmlDocument = Nothing
            Try
                finalDataSet = ExecuteDataSet(requestParameter)
                xmlDoc.LoadXml(finalDataSet.GetXml)
                Return xmlDoc
            Catch ex As Exception
                Throw ex
            Finally
                If Not finalDataSet Is Nothing Then
                    finalDataSet.Dispose()
                    finalDataSet = Nothing
                End If
                If Not xmlDoc Is Nothing Then
                    xmlDoc = Nothing
                End If
            End Try
        End Function
        Public Function ExecuteScalar(ByVal requestParameter As Request) As Object
            Dim databaseCommand As DbCommand = Nothing
            Try
                GetConnectionInfoFromConfig()
                GetFactory()
                databaseCommand = GetCommand(requestParameter)
                GetConnection()
                databaseCommand.Connection = databaseConnection
                OpenConnection()
                Return databaseCommand.ExecuteScalar()
            Catch ex As Exception
                Throw ex
            Finally
                If databaseConnection.State <> ConnectionState.Closed Then
                    CloseConnection()
                End If
                If Not databaseCommand Is Nothing Then
                    databaseCommand.Dispose()
                    databaseCommand = Nothing
                End If
                If Not databaseProviderFactory Is Nothing Then
                    databaseProviderFactory = Nothing
                End If
            End Try
        End Function
        Public Function ExecuteNonQueryReturnValues(ByVal requestParameter As Request) As ArrayList
            Dim databaseCommand As DbCommand = Nothing
            Dim outputParameter As ArrayList = Nothing
            Try
                If IsInTransaction() = False Then
                    GetConnectionInfoFromConfig()
                    GetFactory()
                    GetConnection()
                End If
                databaseCommand = GetCommand(requestParameter)
                databaseCommand.Connection = databaseConnection
                If IsInTransaction() = False Then
                    OpenConnection()
                Else
                    databaseCommand.Transaction = databaseTransaction
                End If
                databaseCommand.ExecuteNonQuery()
                Dim iParam As DbParameter
                outputParameter = New ArrayList
                For Each iParam In databaseCommand.Parameters
                    If iParam.Direction = ParameterDirection.Output Or iParam.Direction = ParameterDirection.InputOutput Or iParam.Direction = ParameterDirection.ReturnValue Then
                        outputParameter.Add(iParam.Value)
                    End If
                Next
                Return outputParameter
            Catch ex As Exception
                Throw ex
            Finally
                If IsInTransaction() = False Then
                    If databaseConnection.State <> ConnectionState.Closed Then
                        CloseConnection()
                    End If
                End If
                If Not databaseCommand Is Nothing Then
                    databaseCommand.Dispose()
                    databaseCommand = Nothing
                End If
                If IsInTransaction() = False Then
                    If Not databaseProviderFactory Is Nothing Then
                        databaseProviderFactory = Nothing
                    End If
                End If
            End Try
        End Function
        Public Function ExecuteNonQueryRecordsAffacted(ByVal requestParameter As Request) As Integer
            Dim databaseCommand As DbCommand = Nothing
            Try
                If IsInTransaction() = False Then
                    GetConnectionInfoFromConfig()
                    GetFactory()
                    GetConnection()
                End If
                databaseCommand = GetCommand(requestParameter)
                databaseCommand.Connection = databaseConnection
                If IsInTransaction() = False Then
                    OpenConnection()
                Else
                    databaseCommand.Transaction = databaseTransaction
                End If
                Return databaseCommand.ExecuteNonQuery()
            Catch ex As Exception
                Throw ex
            Finally
                If IsInTransaction() = False Then
                    If databaseConnection.State <> ConnectionState.Closed Then
                        CloseConnection()
                    End If
                End If
                If Not databaseCommand Is Nothing Then
                    databaseCommand.Dispose()
                    databaseCommand = Nothing
                End If
                If IsInTransaction() = False Then
                    If Not databaseProviderFactory Is Nothing Then
                        databaseProviderFactory = Nothing
                    End If
                End If
            End Try
        End Function
        Public Function IsInTransaction() As Boolean
            Try
                If databaseTransaction Is Nothing Then
                    Return False
                Else
                    Return True
                End If
            Catch ex As Exception
                Throw ex
            End Try
        End Function
        Public Sub BeginTransaction(ByVal transIsolationLevel As IsolationLevel)
            Try
                GetConnectionInfoFromConfig()
                GetFactory()
                GetConnection()
                OpenConnection()
                GetTransaction(transIsolationLevel)
            Catch ex As Exception
                If databaseConnection.State <> ConnectionState.Closed Then
                    CloseConnection()
                End If
                Throw ex
            End Try
        End Sub
        Public Sub CommitTransaction()
            Try
                If IsInTransaction() Then
                    databaseTransaction.Commit()
                    DisposeTransaction()
                End If
            Catch ex As Exception
                If databaseConnection.State <> ConnectionState.Closed Then
                    CloseConnection()
                End If
                Throw ex
            End Try
        End Sub
        Public Sub RollbackTransaction()
            Try
                If IsInTransaction() Then
                    databaseTransaction.Rollback()
                    DisposeTransaction()
                End If
            Catch ex As Exception
                If databaseConnection.State <> ConnectionState.Closed Then
                    CloseConnection()
                End If
                Throw ex
            End Try
        End Sub
#End Region
    End Class
End Namespace
#End Region








GeneralModule.vb Code



#Region "General Module <<Imported NameSpaces>>"
Imports System.Security.Cryptography
Imports System.Text
#End Region
#Region "General Module <<Namespace, Class, Functions, Proeprties, etc.>>"
Namespace Kcon.Practices.PrivateLibrary.DataLib
    Public Module GeneralModule
#Region "Public subroutine/Functions"
        Public Function EncryptData(ByVal value As String) As String
            Dim shaM As New SHA1Managed
            Convert.ToBase64String(shaM.ComputeHash(Encoding.ASCII.GetBytes(value)))
            Dim encodeData() As Byte = ASCIIEncoding.ASCII.GetBytes(value)
            Dim encodeDataValue As String = Convert.ToBase64String(encodeData)
            EncryptData = encodeDataValue
        End Function
        Public Function DecryptData(ByVal value As String) As String
            Dim decodeData() As Byte = Convert.FromBase64String(value)
            Dim decodeDataValue As String = ASCIIEncoding.ASCII.GetString(decodeData)
            DecryptData = decodeDataValue
        End Function
#End Region
    End Module
End Namespace
#End Region


Relation.vb Code

#Region "Relation <<Namespace, Class, Functions, Proeprties, etc.>>"
Namespace Kcon.Practices.PrivateLibrary.DataLib
    Public Class Relation
#Region "Private Variables"
        Private str_parentCoulmnName As String()
        Private str_childCoulmnName As String()
        Private str_parentTableName As String
        Private str_childTableName As String
        Private str_relationName As String
#End Region
#Region "Public Properties"
        Public Property ParentCoulmnName() As String()
            Get
                ParentCoulmnName = str_parentCoulmnName
            End Get
            Set(ByVal value As String())
                str_parentCoulmnName = value
            End Set
        End Property
        Public Property ChildCoulmnName() As String()
            Get
                ChildCoulmnName = str_childCoulmnName
            End Get
            Set(ByVal value As String())
                str_childCoulmnName = value
            End Set
        End Property
        Public Property ParentTableName() As String
            Get
                ParentTableName = str_parentTableName
            End Get
            Set(ByVal value As String)
                str_parentTableName = value
            End Set
        End Property
        Public Property ChildTableName() As String
            Get
                ChildTableName = str_childTableName
            End Get
            Set(ByVal value As String)
                str_childTableName = value
            End Set
        End Property
        Public Property RelationName() As String
            Get
                RelationName = str_relationName
            End Get
            Set(ByVal value As String)
                str_relationName = value
            End Set
        End Property
#End Region
    End Class
End Namespace
#End Region


Request.vb Code

Namespace Kcon.Practices.PrivateLibrary.DataLib
    Public Class Request
#Region "Private Variables"
        Private commandTypeValue As CommandType
        Private commandTextVaule As String = ""
        Private collectionparameters As New Collection
        Private tableNameValue As String = ""
#End Region
#Region "Public Properties"
        Public Property CommandType() As CommandType
            Get
                CommandType = commandTypeValue
            End Get
            Set(ByVal value As CommandType)
                commandTypeValue = value
            End Set
        End Property
        Public Property CommandText() As String
            Get
                CommandText = commandTextVaule
            End Get
            Set(ByVal value As String)
                commandTextVaule = value
            End Set
        End Property
        Public Property Parameters() As Collection
            Get
                Parameters = collectionparameters
            End Get
            Set(ByVal value As Collection)
                collectionparameters = value
            End Set
        End Property
        Public Property TableName() As String
            Get
                TableName = tableNameValue
            End Get
            Set(ByVal value As String)
                tableNameValue = value
            End Set
        End Property
#End Region
#Region "Parameter Information Class"
        Public Class ParameterInformation
#Region "Private Variables"
            Private str_parameterName As String
            Private str_parameterValue As Object
            Private str_parameterType As String
            Private str_parameterDirection As String
#End Region
#Region "Public Properties"
            Public Property ParameterName() As String
                Get
                    ParameterName = str_parameterName
                End Get
                Set(ByVal value As String)
                    str_parameterName = value
                End Set
            End Property
            Public Property ParameterValue() As Object
                Get
                    ParameterValue = str_parameterValue
                End Get
                Set(ByVal value As Object)
                    str_parameterValue = value
                End Set
            End Property
            Public Property ParameterType() As String
                Get
                    ParameterType = str_parameterType
                End Get
                Set(ByVal value As String)
                    str_parameterType = value
                End Set
            End Property
            Public Property ParameterDirection() As String
                Get
                    ParameterDirection = str_parameterDirection
                End Get
                Set(ByVal value As String)
                    str_parameterDirection = value
                End Set
            End Property

#End Region
        End Class
#End Region
    End Class
End Namespace
#End Region

For BLL Code Refer Our Next Article By Click Here