import System; import System.Configuration; import System.Data; import System.Data.SqlClient; package IBuySpy { //******************************************************* // // CustomerDetails Class // // A simple data class that encapsulates details about // a particular customer inside the IBuySpy Customer // database. // //******************************************************* public class CustomerDetails { public var FullName : String; public var Email : String; public var Password : String; } //******************************************************* // // CustomersDB Class // // Business/Data Logic Class that encapsulates all data // logic necessary to add/login/query customers within // the IBuySpy Customer database. // //******************************************************* public class CustomersDB { //******************************************************* // // CustomersDB.GetCustomerDetails() Method // // The GetCustomerDetails method returns a CustomerDetails // struct that contains information about a specific // customer (name, email, password, etc). // // Other relevant sources: // + CustomerDetail Stored Procedure // //******************************************************* public function GetCustomerDetails(customerID: String) : CustomerDetails { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("CustomerDetail", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC var parameterCustomerID : SqlParameter = new SqlParameter("@CustomerID", SqlDbType.Int, 4); parameterCustomerID.Value = Int32.Parse(customerID); myCommand.Parameters.Add(parameterCustomerID); var parameterFullName : SqlParameter = new SqlParameter("@FullName", SqlDbType.NVarChar, 50); parameterFullName.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterFullName); var parameterEmail : SqlParameter = new SqlParameter("@Email", SqlDbType.NVarChar, 50); parameterEmail.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterEmail); var parameterPassword : SqlParameter = new SqlParameter("@Password", SqlDbType.NVarChar, 50); parameterPassword.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterPassword); myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); // Create CustomerDetails Struct var myCustomerDetails : CustomerDetails = new CustomerDetails(); // Populate Struct using Output Params from SPROC myCustomerDetails.FullName = parameterFullName.Value; myCustomerDetails.Password = parameterPassword.Value; myCustomerDetails.Email = parameterEmail.Value; return myCustomerDetails; } //******************************************************* // // CustomersDB.AddCustomer() Method // // 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: // + CustomerAdd Stored Procedure // //******************************************************* public function AddCustomer(fullName: String, email: String, password: String) : String { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("CustomerAdd", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC var parameterFullName : SqlParameter = new SqlParameter("@FullName", SqlDbType.NVarChar, 50); parameterFullName.Value = fullName; myCommand.Parameters.Add(parameterFullName); var parameterEmail : SqlParameter = new SqlParameter("@Email", SqlDbType.NVarChar, 50); parameterEmail.Value = email; myCommand.Parameters.Add(parameterEmail); var parameterPassword : SqlParameter = new SqlParameter("@Password", SqlDbType.NVarChar, 50); parameterPassword.Value = password; myCommand.Parameters.Add(parameterPassword); var parameterCustomerID : SqlParameter = new SqlParameter("@CustomerID", SqlDbType.Int, 4); parameterCustomerID.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterCustomerID); try { myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); // Calculate the CustomerID using Output Param from SPROC var customerId : int = parameterCustomerID.Value; return customerId.ToString(); } catch (e) { return String.Empty; } } //******************************************************* // // CustomersDB.Login() Method // // 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: // + CustomerLogin Stored Procedure // //******************************************************* public function Login(email: String, password : String) : String { // Create Instance of Connection and Command Object var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); var myCommand : SqlCommand = new SqlCommand("CustomerLogin", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC var parameterEmail : SqlParameter = new SqlParameter("@Email", SqlDbType.NVarChar, 50); parameterEmail.Value = email; myCommand.Parameters.Add(parameterEmail); var parameterPassword : SqlParameter = new SqlParameter("@Password", SqlDbType.NVarChar, 50); parameterPassword.Value = password; myCommand.Parameters.Add(parameterPassword); var parameterCustomerID : SqlParameter = new SqlParameter("@CustomerID", SqlDbType.Int, 4); parameterCustomerID.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterCustomerID); // Open the connection and execute the Command myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); var customerId : int = parameterCustomerID.Value; if (customerId == 0) { return null; } else { return customerId.ToString(); } } } }