Using Dapper ORM in ASP.NET Web Forms (Visual Basic.NET)

Hi,

This is a conversion of this post Using Dapper ORM in ASP.NET WebForm to VB.NET language.
Customer.vb

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

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

Imports System.Data.SqlClient
Imports Dapper

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

Imports System.Collections.Generic
Imports System.Linq
Imports System.Web

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

Screenshot



The source code is available for download in Github Repository.

Note: When running this project, download the create table script here and then change the target database in the script. Then run the script using SQL Server query window.
That's it.. :-)