person.vb

该系统是关于c2c电子商务系统 用asp.net技术 开发环境是vs2003+sqlserver2000

Imports System.Data.SqlClient

Namespace C2CSystem
    Public Class PersonDetails
        Public FamilyName As String
        Public GivenName As String
        Public EmailAddress As String
        Public Password As String
        Public StreetAddress1 As String
        Public StreetAddress2 As String
        Public City As String
        Public State As String
        Public PostalCode As String
        Public Country As String
        Public Active As Char
        Public LastLogin As String
        Public PersonID As Int32
    End Class

    Public Class Person

        Public Function GetPersonDetails(ByVal strEmail As String) As PersonDetails
            Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As SqlCommand = New SqlCommand("sp_person_sel", myConnection)

            myCommand.CommandType = CommandType.StoredProcedure

            Dim prmEmailID As SqlParameter = New SqlParameter("@email", SqlDbType.VarChar, 255)
            prmEmailID.Value = CStr(strEmail)
            myCommand.Parameters.Add(prmEmailID)

            Dim prmFamilyName As SqlParameter = New SqlParameter("@FamilyName", SqlDbType.VarChar, 255)
            prmFamilyName.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmFamilyName)

            Dim prmGivenName As SqlParameter = New SqlParameter("@GivenName", SqlDbType.VarChar, 255)
            prmGivenName.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmGivenName)

            Dim prmPassword As SqlParameter = New SqlParameter("@Password", SqlDbType.VarChar, 255)
            prmPassword.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmPassword)

            Dim prmAdd1 As SqlParameter = New SqlParameter("@add1", SqlDbType.VarChar, 255)
            prmAdd1.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmAdd1)

            Dim prmAdd2 As SqlParameter = New SqlParameter("@add2", SqlDbType.VarChar, 255)
            prmAdd2.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmAdd2)

            Dim prmCity As SqlParameter = New SqlParameter("@City", SqlDbType.VarChar, 255)
            prmCity.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmCity)

            Dim prmState As SqlParameter = New SqlParameter("@state", SqlDbType.VarChar, 255)
            prmState.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmState)

            Dim prmZip As SqlParameter = New SqlParameter("@zip", SqlDbType.VarChar, 10)
            prmZip.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmZip)

            Dim prmCountry As SqlParameter = New SqlParameter("@country", SqlDbType.VarChar, 255)
            prmCountry.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmCountry)

            Dim prmActive As SqlParameter = New SqlParameter("@active", SqlDbType.Char, 1)
            prmActive.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmActive)

            Dim prmLastLogin As SqlParameter = New SqlParameter("@lastlogin", SqlDbType.DateTime)
            prmLastLogin.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmLastLogin)

            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()

            Dim myPersonDetails As PersonDetails = New PersonDetails
            myPersonDetails.FamilyName = CStr(prmFamilyName.Value)
            myPersonDetails.GivenName = prmGivenName.Value
            myPersonDetails.FamilyName = CStr(prmFamilyName.Value)
            myPersonDetails.Password = CStr(prmPassword.Value)
            myPersonDetails.StreetAddress1 = CStr(prmAdd1.Value)
            myPersonDetails.StreetAddress2 = CStr(prmAdd2.Value)
            myPersonDetails.City = CStr(prmCity.Value)
            myPersonDetails.State = CStr(prmState.Value)
            myPersonDetails.PostalCode = CStr(prmZip.Value)
            myPersonDetails.Country = CStr(prmCountry.Value)
            myPersonDetails.Active = CStr(prmActive.Value)
            myPersonDetails.LastLogin = (prmLastLogin.Value)

            Return myPersonDetails

        End Function

        Public Function AddCustomer(ByVal familyname As String, _
                                    ByVal givenname As String, _
                                    ByVal emailaddress As String, _
                                    ByVal password As String, _
                                    ByVal add1 As String, _
                                    ByVal add2 As String, _
                                    ByVal city As String, _
                                    ByVal state As String, _
                                    ByVal zip As String, _
                                    ByVal country As String) As String

            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlCommand("sp_person_isp", myConnection)

            myCommand.CommandType = CommandType.StoredProcedure

            Dim prmEmail As New SqlParameter("@email", SqlDbType.VarChar, 255)
            prmEmail.Value = emailaddress
            myCommand.Parameters.Add(prmEmail)

            Dim prmgivenname As New SqlParameter("@givenname", SqlDbType.VarChar, 255)
            prmgivenname.Value = givenname
            myCommand.Parameters.Add(prmgivenname)

            Dim prmfamilyname As New SqlParameter("@familyname", SqlDbType.VarChar, 255)
            prmfamilyname.Value = familyname
            myCommand.Parameters.Add(prmfamilyname)

            Dim prmadd1 As New SqlParameter("@add1", SqlDbType.VarChar, 255)
            prmadd1.Value = add1
            myCommand.Parameters.Add(prmadd1)

            Dim prmadd2 As New SqlParameter("@add2", SqlDbType.VarChar, 255)
            prmadd2.Value = add2
            myCommand.Parameters.Add(prmadd2)

            Dim prmcity As New SqlParameter("@city", SqlDbType.VarChar, 255)
            prmcity.Value = city
            myCommand.Parameters.Add(prmcity)

            Dim prmstate As New SqlParameter("@state", SqlDbType.VarChar, 255)
            prmstate.Value = state
            myCommand.Parameters.Add(prmstate)

            Dim prmzip As New SqlParameter("@zip", SqlDbType.VarChar, 10)
            prmzip.Value = zip
            myCommand.Parameters.Add(prmzip)

            Dim prmcountry As New SqlParameter("@country", SqlDbType.VarChar, 255)
            prmcountry.Value = country
            myCommand.Parameters.Add(prmcountry)

            Dim prmPersonID As SqlParameter = New SqlParameter("@personid", SqlDbType.BigInt)
            prmPersonID.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmPersonID)

            Dim prmpwd As New SqlParameter("@pwd", SqlDbType.VarChar, 255)
            prmpwd.Value = password
            myCommand.Parameters.Add(prmpwd)

            Try
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                myConnection.Close()

                Return prmPersonID.Value
            Catch SQLexc As SqlException
                Return SQLexc.ToString()
            End Try
        End Function

        Public Function ModifyCustomer(ByVal familyname As String, _
                                    ByVal givenname As String, _
                                    ByVal emailaddress As String, _
                                    ByVal password As String, _
                                    ByVal add1 As String, _
                                    ByVal add2 As String, _
                                    ByVal city As String, _
                                    ByVal state As String, _
                                    ByVal zip As String, _
                                    ByVal country As String) As String

            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlCommand("sp_person_usp", myConnection)

            myCommand.CommandType = CommandType.StoredProcedure

            Dim prmEmail As New SqlParameter("@email", SqlDbType.VarChar, 255)
            prmEmail.Value = emailaddress
            myCommand.Parameters.Add(prmEmail)

            Dim prmgivenname As New SqlParameter("@givenname", SqlDbType.VarChar, 255)
            prmgivenname.Value = givenname
            myCommand.Parameters.Add(prmgivenname)

            Dim prmfamilyname As New SqlParameter("@familyname", SqlDbType.VarChar, 255)
            prmfamilyname.Value = familyname
            myCommand.Parameters.Add(prmfamilyname)

            Dim prmadd1 As New SqlParameter("@add1", SqlDbType.VarChar, 255)
            prmadd1.Value = add1
            myCommand.Parameters.Add(prmadd1)

            Dim prmadd2 As New SqlParameter("@add2", SqlDbType.VarChar, 255)
            prmadd2.Value = add2
            myCommand.Parameters.Add(prmadd2)

            Dim prmcity As New SqlParameter("@city", SqlDbType.VarChar, 255)
            prmcity.Value = city
            myCommand.Parameters.Add(prmcity)

            Dim prmstate As New SqlParameter("@state", SqlDbType.VarChar, 255)
            prmstate.Value = state
            myCommand.Parameters.Add(prmstate)

            Dim prmzip As New SqlParameter("@zip", SqlDbType.VarChar, 10)
            prmzip.Value = zip
            myCommand.Parameters.Add(prmzip)

            Dim prmcountry As New SqlParameter("@country", SqlDbType.VarChar, 255)
            prmcountry.Value = country
            myCommand.Parameters.Add(prmcountry)

            Dim prmpwd As New SqlParameter("@pwd", SqlDbType.VarChar, 255)
            prmpwd.Value = password
            myCommand.Parameters.Add(prmpwd)

            Try
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                myConnection.Close()

                Return "1"
            Catch SQLexc As SqlException
                Return SQLexc.ToString()
            End Try
        End Function

        Public Function Login(ByVal strEmail As String, ByVal strPassword As String) As PersonDetails
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlCommand("sp_login", myConnection)

            myCommand.CommandType = CommandType.StoredProcedure

            Dim prmEmail As New SqlParameter("@email", SqlDbType.VarChar, 255)
            prmEmail.Value = strEmail
            myCommand.Parameters.Add(prmEmail)

            Dim prmpwd As New SqlParameter("@pwd", SqlDbType.VarChar, 255)
            prmpwd.Value = strPassword
            myCommand.Parameters.Add(prmpwd)

            Dim prmGivenName As SqlParameter = New SqlParameter("@GivenName", SqlDbType.VarChar, 255)
            prmGivenName.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmGivenName)

            Dim prmPersonID As SqlParameter = New SqlParameter("@PersonID", SqlDbType.BigInt)
            prmPersonID.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmPersonID)

            Dim myPersonDetails As PersonDetails = New PersonDetails
            Try
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                myConnection.Close()

                myPersonDetails.GivenName = prmGivenName.Value
                myPersonDetails.PersonID = prmPersonID.Value

                Return myPersonDetails
            Catch SQLexc As SqlException
                myPersonDetails.GivenName = SQLexc.ToString()
                myPersonDetails.PersonID = 0
                Return myPersonDetails
            End Try
        End Function

        Public Function GetPersonByID(ByVal intPersonID As Int32) As PersonDetails
            Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As SqlCommand = New SqlCommand("sp_person_sel_by_id", myConnection)

            myCommand.CommandType = CommandType.StoredProcedure

            Dim prmBidID As SqlParameter = New SqlParameter("@bidid", SqlDbType.BigInt)
            prmBidID.Value = intPersonID
            myCommand.Parameters.Add(prmBidID)

            Dim prmGivenName As SqlParameter = New SqlParameter("@givenname", SqlDbType.VarChar, 255)
            prmGivenName.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmGivenName)

            Dim prmEmail As SqlParameter = New SqlParameter("@email", SqlDbType.VarChar, 255)
            prmEmail.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmEmail)

            Dim prmCity As SqlParameter = New SqlParameter("@city", SqlDbType.VarChar, 255)
            prmCity.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmCity)

            Dim prmState As SqlParameter = New SqlParameter("@state", SqlDbType.VarChar, 255)
            prmState.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmState)

            Dim prmCountry As SqlParameter = New SqlParameter("@country", SqlDbType.VarChar, 255)
            prmCountry.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(prmCountry)

            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()

            Dim myPersonDetails As PersonDetails = New PersonDetails
            myPersonDetails.GivenName = prmGivenName.Value
            myPersonDetails.EmailAddress = prmEmail.Value
            myPersonDetails.City = prmCity.Value
            myPersonDetails.State = prmState.Value
            myPersonDetails.Country = prmCountry.Value

            Return myPersonDetails
        End Function

    End Class

End Namespace