ASP.NET Web Forms Project Using Dapper ORM (VB.NET)

This is the VB.NET version of this post ASP.NET-Web Forms Project Using Dapper ORM.
The approach is the same wherein you need to add the Dapper ORM to the project via NuGet. This project likewise does not
include any client or server validations so as to give emphasis on the CRUD functionality.

Customer.vb

Code:

---

Public Class Customer

    Public Property CustomerID() As Integer
        Get
            Return m_CustomerID
        End Get
        Set(value As Integer)
            m_CustomerID = Value
        End Set
    End Property
    Private m_CustomerID As Integer

    Public Property CompanyName() As String
        Get
            Return m_CompanyName
        End Get
        Set(value As String)
            m_CompanyName = Value
        End Set
    End Property
    Private m_CompanyName As String

    Public Property Address() As String
        Get
            Return m_Address
        End Get
        Set(value As String)
            m_Address = Value
        End Set
    End Property
    Private m_Address As String

    Public Property City() As String
        Get
            Return m_City
        End Get
        Set(value As String)
            m_City = Value
        End Set
    End Property
    Private m_City As String

    Public Property State() As String
        Get
            Return m_State
        End Get
        Set(value As String)
            m_State = Value
        End Set
    End Property
    Private m_State As String

    Public Property IntroDate() As DateTime
        Get
            Return m_IntroDate
        End Get
        Set(value As DateTime)
            m_IntroDate = Value
        End Set
    End Property
    Private m_IntroDate As DateTime

    Public Property CreditLimit() As Decimal
        Get
            Return m_CreditLimit
        End Get
        Set(value As Decimal)
            m_CreditLimit = Value
        End Set
    End Property
    Private m_CreditLimit As Decimal

End Class

---

ICustomerRepository.vb

Code:

---

Public Interface ICustomerRepository

    Function GetAll() As List(Of Customer)
    Function FindById(Id As Integer) As Customer
    Function AddCustomer(customer As Customer) As Boolean
    Function UpdateCustomer(customer As Customer) As Boolean
    Function DeleteCustomer(Id As Integer) As Boolean

End Interface

---

CustomerRepository.vb

Code:

---

Imports Dapper
Imports System.Data.SqlClient

Public Class CustomerRepository
    Implements ICustomerRepository

    Private _db As IDbConnection

    Public Sub New()
        _db = New SqlConnection(ConfigurationManager.ConnectionStrings("CustomerInformation").ConnectionString)
    End Sub

    Public Function GetAll() As List(Of Customer) Implements ICustomerRepository.GetAll
        Return Me._db.Query(Of Customer)("SELECT * From Customer;").ToList()
    End Function

    Public Function FindById(Id As Integer) As Customer Implements ICustomerRepository.FindById
        Return Me._db.Query("SELECT * FROM Customer WHERE CustomerID=@Id", New With { _
            Key .Id = Id _
        }).FirstOrDefault()
    End Function

    Public Function AddCustomer(customer As Customer) As Boolean Implements ICustomerRepository.AddCustomer
        Dim parameters As SqlParameter() = {
            New SqlParameter("@CompanyName", customer.CompanyName),
            New SqlParameter("@Address", customer.Address),
            New SqlParameter("@City", customer.City),
            New SqlParameter("@State", customer.State),
            New SqlParameter("@IntroDate", customer.IntroDate),
            New SqlParameter("@CreditLimit", customer.CreditLimit)}

        Dim query As String = "INSERT INTO Customer(CompanyName,Address,City,State,IntroDate,CreditLimit)" + " Values(@CompanyName,@Address,@City,@State,@IntroDate,@CreditLimit)"

        Dim args = New DynamicParameters()
        For Each p As SqlParameter In parameters
            args.Add(p.ParameterName, p.Value)
        Next

        Try
            Me._db.Query(Of Customer)(query, args).SingleOrDefault()
        Catch generatedExceptionName As Exception
            Return False
        End Try

        Return True
    End Function

    Public Function UpdateCustomer(customer As Customer) As Boolean Implements ICustomerRepository.UpdateCustomer
        Dim parameters As SqlParameter() = {
            New SqlParameter("@CustomerID", customer.CustomerID),
            New SqlParameter("@CompanyName", customer.CompanyName),
            New SqlParameter("@Address", customer.Address),
            New SqlParameter("@City", customer.City),
            New SqlParameter("@State", customer.State),
            New SqlParameter("@IntroDate", customer.IntroDate), _
            New SqlParameter("@CreditLimit", customer.CreditLimit)}

        Dim query As String = " UPDATE Customer SET CompanyName = @CompanyName,Address = @Address, " + " City = @City,State = @State,IntroDate = @IntroDate,CreditLimit = @CreditLimit" + " WHERE CustomerID = @CustomerID"

        Dim args = New DynamicParameters()
        For Each p As SqlParameter In parameters
            args.Add(p.ParameterName, p.Value)
        Next

        Try
            Me._db.Execute(query, args)
        Catch generatedExceptionName As Exception
            Return False
        End Try

        Return True
    End Function

    Public Function DeleteCustomer(Id As Integer) As Boolean Implements ICustomerRepository.DeleteCustomer
        Dim deletedCustomer As Integer = Me._db.Execute("DELETE FROM Customer WHERE CustomerID = @Id", New With { _
            Key .Id = Id _
        })
        Return deletedCustomer > 0
    End Function

End Class

---

CustomerService.vb

Code:

---

Public Class CustomerService
    Private _repository As ICustomerRepository

    Public Sub New()
        _repository = New CustomerRepository()
    End Sub

    Public Function GetAll() As List(Of Customer)
        Return _repository.GetAll()
    End Function

    Public Function FindById(Id As Integer) As Customer
        Return _repository.FindById(Id)
    End Function

    Public Function AddCustomer(customer As Customer) As Boolean
        Return _repository.AddCustomer(customer)
    End Function

    Public Function UpdateCustomer(customer As Customer) As Boolean
        Return _repository.UpdateCustomer(customer)
    End Function

    Public Function DeleteCustomer(Id As Integer) As Boolean
        Return _repository.DeleteCustomer(Id)
    End Function
End Class

---

Attachment 145207

The source code is available for download in Github ASP.NET-WebForm-Dapper-VB.NET.

Before running the program, download the create table script (CreateTableScript.txt) here and run it using SQL Server Query Window.
Make sure to change the target database in the script.

Regards,