N Tier layer questions

[jamie_pattison]

I created a website using the following structure:

Class Project - Called DataAccessLayer > Added a Dataset > Add a tableAdapter and Datatable with a query called GetcustomersByID(ID)
Class Project - Called BusinessLayer > Created some code to call into the DataAccessLayer and return the results in a CustomerDataTable for the query GetcustomersByID(ID)
Web Project - Added a reference to the BusinessLayer.

From this point i can add an ObjectDataSource and tie it to the Business Layer and call the appropriate method (in this example GetCustomersByID(ID)).

I then wanted to add an extra layer where i was hoping to load all customer data into a customer object. So i add another class called Customers and add all the fields as properties (CustomerID, FirstName, Surname, AddressOne etc).

How could i load all the details from the BusinessLayer into this object, so i could write code such as

Dim myCustomer as Customer
....
...... Some code to get the data and load it into the Customer object.

If myCustomer.Firstname = "Bob" Then
....
End If

Thanks

 

[IanRyder]

Hi,

Have a quick look at this Class structure that I sometimes use to extract information I need from a Database. In this case, I have created two methods to return either a List of all Employees or a Single Employee as I need.

Notice that I have used the Shared keyword on the methods so that I do not need to create a new instance of the class before I can access its methods.

You can also see that I have coded the connection to the Data source within the class but in your case you would just access your Data and Business layers as you need to get your information.

Imports System.Data.SqlClient
 
Public Class Form1
 
  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    'show a single employee name
    MsgBox(Employee.GetEmployeeByName("Davolio").ToString)
 
    'show all employee names
    For Each curEmployee As Employee In Employee.GetAllEmployees
      MsgBox(curEmployee.ToString)
    Next
 
    'save all employee names in a list
    Dim myEmployees As List(Of Employee) = Employee.GetAllEmployees
  End Sub
End Class
 
Public Class Employee
  Const myConnectionString As String = "Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True"
 
  Public Property FirstName As String
  Public Property LastName As String
  Public Property BirthDate As Date
 
  Public Overrides Function ToString() As String
    Return FirstName & " " & LastName
  End Function
 
  Public Shared Function GetAllEmployees() As List(Of Employee)
    Const mySQLString As String = "Select FirstName, LastName, BirthDate FROM Employees"
    Dim myReader As SqlDataReader
    Dim myEmployeeList As New List(Of Employee)
 
    Using sqlConn As New SqlConnection(myConnectionString)
      Using myCmnd As New SqlCommand(mySQLString)
        Try
          sqlConn.Open()
          myCmnd.Connection = sqlConn
          myReader = myCmnd.ExecuteReader
          While myReader.Read
            Dim myEmployee As New Employee
            With myEmployee
              .FirstName = If(Not IsDBNull(myReader("FirstName")), myReader("FirstName"), String.Empty).ToString
              .LastName = If(Not IsDBNull(myReader("LastName")), myReader("LastName"), String.Empty).ToString
              .BirthDate = CDate(If(Not IsDBNull(myReader("BirthDate")), myReader("BirthDate"), Nothing))
            End With
            myEmployeeList.Add(myEmployee)
          End While
        Catch sqlEX As SqlException
          Throw New Exception(sqlEX.Message, sqlEX.InnerException)
        Catch ex As Exception
          Throw New Exception(ex.Message, ex.InnerException)
        Finally
          If sqlConn.State = ConnectionState.Open Then
            sqlConn.Close()
          End If
        End Try
      End Using
    End Using
    Return myEmployeeList
  End Function
 
  Public Shared Function GetEmployeeByName(ByVal EmployeeName As String) As Employee
    Const mySQLString As String = "Select FirstName, LastName, BirthDate FROM Employees WHERE LastName =@LastName"
    Dim myReader As SqlDataReader
    Dim myEmployee As New Employee
 
    Using sqlConn As New SqlConnection(myConnectionString)
      Using myCmnd As New SqlCommand(mySQLString)
        Try
          sqlConn.Open()
          With myCmnd
            .Connection = sqlConn
            .Parameters.AddWithValue("@LastName", EmployeeName)
          End With
          myReader = myCmnd.ExecuteReader
          While myReader.Read
            With myEmployee
              .FirstName = If(Not IsDBNull(myReader("FirstName")), myReader("FirstName"), String.Empty).ToString
              .LastName = If(Not IsDBNull(myReader("LastName")), myReader("LastName"), String.Empty).ToString
              .BirthDate = CDate(If(Not IsDBNull(myReader("BirthDate")), myReader("BirthDate"), Nothing))
            End With
          End While
        Catch sqlEX As SqlException
          Throw New Exception(sqlEX.Message, sqlEX.InnerException)
        Catch ex As Exception
          Throw New Exception(ex.Message, ex.InnerException)
        Finally
          If sqlConn.State = ConnectionState.Open Then
            sqlConn.Close()
          End If
        End Try
      End Using
    End Using
    Return myEmployee
  End Function
End Class

Hope that helps.

Cheers,

Ian

 

[IanRyder]

Hi,

I should have added a word of caution here when using this kind of structure. Consider what would happen if you called:-

MsgBox(Employee.GetEmployeeByName("Davolio").ToString & " - " & Employee.GetEmployeeByName("Davolio").BirthDate)

Cheers,

Ian

 

[jamie_pattison]

Hi,

I should have added a word of caution here when using this kind of structure. Consider what would happen if you called:-

MsgBox(Employee.GetEmployeeByName("Davolio").ToString & " - " & Employee.GetEmployeeByName("Davolio").BirthDate)

Cheers,

Ian

Thanks Ian. The way i read the code i think it would make two calls to get two details as opposed to making one call loading the object with the data and then using that object?

 

[IanRyder]

Correct.

Hope it helps.

Cheers,

Ian