1. On CustomersDAL.vb, type the following:
Imports System.Data.OleDb
Public Class CustomersDAL
Dim db As OledbConnection
Public Sub New()
db = dbs.Connect
End Sub
Public Function GetCustomer(ByVal id As Integer) As Customers
Dim sql As String
sql = "SELECT * FROM Customers WHERE id=" & id & ""
Dim cmd As New OleDbCommand(sql, db)
Dim rdr As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.SingleRow)
rdr.Read()
Return customer(rdr)
End Function
Private Function customer(ByVal dtr As Object) 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
Public Function CreateUpdateSQLDeductCreditLimit(ByVal id As Integer, ByVal totalamount As Decimal) As String
Dim sql As String
sql = "UPDATE Customers SET creditlimit=creditlimit-'" & totalamount & "'" _
& "WHERE id=" & id & ""
Return sql
End Function
End Class
2. On EmployeesDAL.vb, type the following:
Imports System.Data.OleDb
Public Class EmployeesDAL
Dim db As OledbConnection
Public Sub New()
db = dbs.Connect
End Sub
Public Function GetEmployee(ByVal id As Integer) As Employees
Dim sql As String
sql = "SELECT * FROM Employees WHERE id=" & id & ""
Dim cmd As New OleDbCommand(sql, db)
Dim rdr As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.SingleRow)
rdr.Read()
Return Employee(rdr)
End Function
Private Function Employee(ByVal dtr As Object) As Employees
Dim e As New Employees
e.ID = dtr("id")
e.Name = dtr("name")
e.Position = dtr("position")
Return e
End Function
End Class
3. On GeneratorsDAL.vb, type the following:
Imports System.Data.OleDb
Public Class GeneratorsDAL
Private db As New OleDbConnection
Public Sub New()
db = dbs.Connect
End Sub
Public Function GetOrderID() As Integer
Dim sql As String
sql = "SELECT orderid FROM generators"
Dim cmd = New OleDbCommand(sql, db)
Return CInt(cmd.ExecuteScalar.ToString)
End Function
Public Function CreateUpdateSQLOrderID() As String
Return "UPDATE generators set orderid=orderid+1"
End Function
End Class
4. On OrderDetailsDAL.vb, type the following:
Imports System.Data.OleDb
Public Class OrderDetailsDAL
Public Function CreateInsertSQL(ByVal OrderDetail As OrderDetails) As String
Dim sql As String
sql = "INSERT INTO OrderDetails" _
& "(orderid,prodid,price,qtyordered,amount,status)VALUES(" _
& "'" & OrderDetail.Orderid & "','" & OrderDetail.Prodid & "'," _
& "'" & OrderDetail.Price & "','" & OrderDetail.Qtyordered & "'," _
& "'" & OrderDetail.Amount & "','open')"
Return sql
End Function
End Class
5. On OrdersDAL.vb, type the following:
Imports System.Data.OleDb
Public Class OrdersDAL
Public Function CreateInsertSQL(ByVal Order As Orders) As String
Dim sql As String
sql = "INSERT INTO Orders" _
& "(id,custid,empid,orderdate,totalamount,status)VALUES(" _
& "'" & Order.ID & "','" & Order.Custid & "'," _
& "'" & Order.Empid & "','" & Order.Orderdate & "'," _
& "'" & Order.Totalamount & "','open')"
Return sql
End Function
End Class
6. On ProductsDAL.vb, type the following:
Imports System.Data.OleDb
Public Class ProductsDAL
Dim db As OleDbConnection
Public Sub New()
db = dbs.Connect
End Sub
Public Function GetProduct(ByVal id As Integer) As Products
Dim sql As String
sql = "SELECT * FROM Products WHERE id=" & id & ""
Dim cmd As New OleDbCommand(sql, db)
Dim rdr As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.SingleRow)
rdr.Read()
Return Product(rdr)
End Function
Private Function Product(ByVal dtr As Object) As Products
Dim p As New Products
p.ID = dtr("id")
p.Description = dtr("description")
p.Untmsr = dtr("untmsr")
p.Price = dtr("price")
p.Qtyonhand = dtr("qtyonhand")
Return p
End Function
Public Function CreateUpdateSQLDeductInventory(ByVal pid As Integer, ByVal qtyordered As Decimal) As String
Dim sql As String
sql = "UPDATE Products SET qtyonhand=qtyonhand-'" & qtyordered & "'" _
& "WHERE id=" & pid & ""
Return sql
End Function
End Class
No comments:
Post a Comment