customersdb.vb

visual studio.net 学习资料

Imports System
Imports System.Data
Imports System.Data.SQL

Namespace Conference

    '*******************************************************
    '
    ' CustomerDetails Class
    '
    ' A simple data Class that encapsulates details about
    ' a particular customer inside the Conference Customer 
    ' database.
    '
    '*******************************************************

    Public Class CustomerDetails 
    
        Public FullName As String
        Public Email As String
        Public Password As String
        
    End Class

    '*******************************************************
    '
    ' CustomersDB Class
    '
    ' Business/Data Logic Class that encapsulates all data
    ' logic necessary to add/login/query customers within
    ' the Conference Customer database.
    '
    '*******************************************************

    Public Class CustomersDB 
  
        '*******************************************************
        '
        ' CustomersDB.GetCustomerDetails() Method <a name="GetCustomerDetails"></a>
        '
        ' The GetCustomerDetails method returns a CustomerDetails
        ' struct that contains information about a specific
        ' customer (name, email, password, etc).
        '
        ' Other relevant sources:  
        '     + <a href="srcview.aspx?path=../Login.src&file=docs/CustomerDetail.htm" style="color:green">CustomerDetail Stored Procedure</a>
        '
        '*******************************************************
  
        Public Function GetCustomerDetails(customerID As String) As CustomerDetails 

            ' Create Instance of Connection and Command Object
            Dim myConnection As SQLConnection = new SQLConnection(ConferenceDB.ConnectionString)
            Dim myCommand As SQLCommand = new SQLCommand("CustomerDetail", myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterCustomerID As SQLParameter = new SQLParameter("@CustomerID", SQLDataType.Int, 4)
            parameterCustomerID.Value = Int32.Parse(customerID)
            myCommand.Parameters.Add(parameterCustomerID)

            Dim parameterFullName As SQLParameter = new SQLParameter("@FullName", SQLDataType.NChar, 50)
            parameterFullName.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterFullName)

            Dim parameterEmail As SQLParameter = new SQLParameter("@Email", SQLDataType.NChar, 50)
            parameterEmail.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterEmail)

            Dim parameterPassword As SQLParameter = new SQLParameter("@Password", SQLDataType.NChar, 50)
            parameterPassword.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterPassword)

            Try 
                ' Open the connection and execute the Command
                myConnection.Open()
                myCommand.Execute()
            Catch e As Exception
                ' An error occurred, pass the exception up
                throw e
            Finally 
                ' Close the Connection
                If myConnection.State = DBObjectState.Open then
                    myConnection.Close()
                End If
            End Try

            ' Create CustomerDetails Struct
            Dim myCustomerDetails As CustomerDetails= new CustomerDetails()

            ' Populate Struct Imports Output Params from SPROC
            myCustomerDetails.FullName = CStr(parameterFullName.Value)
            myCustomerDetails.Password = CStr(parameterPassword.Value)
            myCustomerDetails.Email = CStr(parameterEmail.Value)
            
            Return myCustomerDetails
            
        End Function

        '*******************************************************
        '
        ' CustomersDB.AddCustomer() Method <a name="AddCustomer"></a>
        '
        ' The AddCustomer method inserts a new customer record
        ' into the customers database.  A unique "CustomerId"
        ' key is then returned from the method.  This can be 
        ' used later to place orders, track shopping carts,
        ' etc within the ecommerce system.
        '
        ' Other relevant sources:  
        '     + <a href="srcview.aspx?path=../Register.src&file=docs/CustomerAdd.htm" style="color:green">CustomerAdd Stored Procedure</a>
        '
        '*******************************************************

        Public Function AddCustomer(fullName As String, email As String, password As String) As String

            ' Create Instance of Connection and Command Object
            Dim myConnection As SQLConnection = new SQLConnection(ConferenceDB.ConnectionString)
            Dim myCommand As SQLCommand = new SQLCommand("CustomerAdd", myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterFullName As SQLParameter = new SQLParameter("@FullName", SQLDataType.NChar, 50)
            parameterFullName.Value = fullName
            myCommand.Parameters.Add(parameterFullName)

            Dim parameterEmail As SQLParameter = new SQLParameter("@Email", SQLDataType.NChar, 50)
            parameterEmail.Value = email
            myCommand.Parameters.Add(parameterEmail)

            Dim parameterPassword As SQLParameter = new SQLParameter("@Password", SQLDataType.NChar, 50)
            parameterPassword.Value = password
            myCommand.Parameters.Add(parameterPassword)

            Dim parameterCustomerID As SQLParameter = new SQLParameter("@CustomerID", SQLDataType.Int, 4)
            parameterCustomerID.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterCustomerID)

            Try 
                ' Open the connection and execute the Command
                myConnection.Open()
                myCommand.Execute()
            Catch e As Exception
                ' An error occurred, pass the exception up
                throw e
            Finally 
                ' Close the Connection
                If myConnection.State = DBObjectState.Open then
                    myConnection.Close()
                End If
            End Try
  
            ' Return the CustomerID Imports Output Param from SPROC
            Dim customerId as Integer
            customerId = CInt(parameterCustomerID.Value)
            
            Return customerId.ToString()
            
        End Function

        '*******************************************************
        '
        ' CustomersDB.Login() Method <a name="Login"></a>
        '
        ' The Login method validates a email/password pair
        ' against credentials stored in the customers database.
        ' If the email/password pair is valid, the method returns
        ' the "CustomerId" number of the customer.  Otherwise
        ' it will throw an exception.
        '
        ' Other relevant sources:  
        '     + <a href="srcview.aspx?path=../Login.src&file=docs/CustomerLogin.htm" style="color:green">CustomerLogin Stored Procedure</a>
        '
        '*******************************************************

        Public Function Login(email As String, password As String) As String

            ' Create Instance of Connection and Command Object
            Dim myConnection As SQLConnection = new SQLConnection(ConferenceDB.ConnectionString)
            Dim myCommand As SQLCommand = new SQLCommand("CustomerLogin", myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterEmail As SQLParameter = new SQLParameter("@Email", SQLDataType.NChar, 50)
            parameterEmail.Value = email
            myCommand.Parameters.Add(parameterEmail)

            Dim parameterPassword As SQLParameter = new SQLParameter("@Password", SQLDataType.NChar, 50)
            parameterPassword.Value = password
            myCommand.Parameters.Add(parameterPassword)
            
            Dim parameterCustomerID As SQLParameter = new SQLParameter("@CustomerID", SQLDataType.Int, 4)
            parameterCustomerID.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterCustomerID)

            Try 
                ' Open the connection and execute the Command
                myConnection.Open()
                myCommand.Execute()
            Catch e As Exception
                ' An error occurred, pass the exception up
                throw e
            Finally 
                ' Close the Connection
                If myConnection.State = DBObjectState.Open then
                    myConnection.Close()
                End If
            End Try
  
            Dim customerId As Integer = CInt(parameterCustomerID.Value)

            If customerId = 0 Then
                Return ""
            Else
                Return customerId.ToString()
            End If
  
        End Function     

    End Class

End Namespace