Steps:
1. Create CustomerRecordUI.vb[Design] as shown below:
Control-Name-Value
Form-CustomerRecordUI
Listview-lvwCustomers
View-Details
Gridlines-true
FullRowSelect-True
ContextMenuStrip-ContextMenuStrip1
Columns-(Refer to the image)
StatusStrip-stbCustomer
MenuStrip-mnuCustomer
MenuStripItem- RefreshToolStripMenuItem-Text-Refresh
ContextMenuStrip-ContextMenuStrip1-&Delete
2. On CustomerRecordUI.vb[Code], type the following:
Public Class CustomerRecordUI
Private Sub CustomerRecordUI_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
GetCustomers()
End Sub
Private Sub GetCustomers()
Dim customers As List(Of Customers)
customers = CustomerBAL.GetCustomers()
Dim row As Integer = 0
lvwCustomers.Items.Clear()
For Each customer In customers
lvwCustomers.Items.Add(customer.ID)
With lvwCustomers.Items(row).SubItems
.Add(customer.Name)
.Add(customer.Address)
.Add(customer.ContactNo)
.Add(Format(customer.CreditLimit, "#,##0.00"))
End With
row = row + 1
Next
lblCustomers.Text = "Total Customers: " & customers.Count
End Sub
Private Sub RefreshToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RefreshToolStripMenuItem.Click
GetCustomers()
End Sub
Private Sub DeleteToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteToolStripMenuItem.Click
Dim result As MsgBoxResult
result = MessageBox.Show(lvwCustomers.FocusedItem.SubItems(1).Text & " will be Deleted. Continue?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
If result = MsgBoxResult.Yes Then
CustomerBAL.Delete(CInt(lvwCustomers.FocusedItem.Text))
lvwCustomers.FocusedItem.Remove()
End If
End Sub
End Class
3. On Customers.vb class, type the following:
Public Class Customers
Private mID As Integer
Private mName As String
Private mAddress As String
Private mContactNo As String
Private mCreditLimit As Decimal
Property ID() As Integer
Get
Return mID
End Get
Set(ByVal value As Integer)
mID = value
End Set
End Property
Property Name() As String
Get
Return mName
End Get
Set(ByVal value As String)
mName = value
End Set
End Property
Property Address() As String
Get
Return mAddress
End Get
Set(ByVal value As String)
mAddress = value
End Set
End Property
Property ContactNo() As String
Get
Return mContactNo
End Get
Set(ByVal value As String)
mContactNo = value
End Set
End Property
Property CreditLimit() As Decimal
Get
Return mCreditLimit
End Get
Set(ByVal value As Decimal)
mCreditLimit = value
End Set
End Property
End Class
4. On CustomersBAL.vb class, type the following:
Imports MarioSoft.Sales.Customers
Imports MarioSoft.Sales.CustomersDAL
Public Class CustomerBAL
Public Shared Sub Delete(ByVal id As Integer)
Dim CustomerDAL As New CustomersDAL
CustomerDAL.Delete(id)
End Sub
Public Shared Function GetCustomers() As List(Of Customers)
Dim customerDAL As New CustomersDAL
Return customerDAL.GetCustomers()
End Function
End Class
5. On CustomersDAL.vb class, type the following:
Imports MarioSoft.Sales.Customers
Imports System.Data.OleDb
Public Class CustomersDAL
Dim db As OledbConnection
Public Sub New()
db = dbs.Connect
End Sub
Public Sub Delete(ByVal id As Integer)
Dim sql As String
sql = "DELETE FROM Customers where id=" & id & " "
Execute(sql)
End Sub
Private Sub Execute(ByVal sql As String)
db.Open()
Dim cmd = New OleDbCommand(sql, db)
cmd.ExecuteNonQuery()
db.Close()
End Sub
Public Function GetCustomers() As List(Of Customers)
Dim sql As String
sql = "SELECT * FROM Customers ORDER BY name"
Dim cmd As New OleDbCommand(sql, db)
Dim sda As New OleDbDataAdapter(cmd)
Dim dt As New DataTable
sda.Fill(dt)
Dim customers As New List(Of Customers)
For Each DataRow In dt.Rows
customers.Add(customer(DataRow))
Next
Return customers
End Function
Private Function customer(ByVal dtr As DataRow) As Customers
Dim c As New Customers
c.ID = dtr("id")
c.Name = dtr("name")
c.Address = dtr("address")
c.ContactNo = dtr("contactno")
c.CreditLimit = dtr("creditlimit")
Return c
End Function
End Class
6. On dbs.vb class, type the following:
Imports System.Data.OleDb
Public Class dbs
Public Shared Function Connect() As OledbConnection
Dim con As New OleDbConnection
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Application.StartupPath & "\Database\SALES.MDB"
Return con
End Function
End Class
7. Run the program
No comments:
Post a Comment