using System; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Security.Cryptography; using System.Web; using System.Web.Security; using System.Diagnostics; using System.Globalization; /*===================================================================== File: CustomersDB.cs for Adventure Works Cycles Storefront Sample Summary: Middle tier component for manipulating customer information. Date: June 16, 2003 --------------------------------------------------------------------- This file is part of the Microsoft SQL Server Code Samples. Copyright (C) Microsoft Corporation. All rights reserved. This source code is intended only as a supplement to Microsoft Development Tools and/or on-line documentation. See these other materials for detailed information regarding Microsoft code samples. THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. ======================================================= */ namespace Microsoft.Samples.SqlServer { //******************************************************* // // CustomerDetails Class // // A simple data class that encapsulates details about // a particular customer inside the AdventureWorks // database. // // //******************************************************* public class CustomerDetails { private String customerID; private String firstName; private String lastName; private String email; private String passwordHash; private String passwordSalt; private int emailPromotion; // The fields above are exposed publically as properties // There is nothing fancy going on here, it is just better // practice to not expose public fields. This helps avoid binary // incompatabilities if the public fields were to have to // be changed to properties later. public String CustomerID { get { return customerID; } set { customerID = value; } } public String FirstName { get { return firstName; } set { firstName = value; } } public String LastName { get { return lastName; } set { lastName = value; } } public String Email { get { return email; } set { email = value; } } public String PasswordHash { get { return passwordHash; } set { passwordHash = value; } } public String PasswordSalt { get { return passwordSalt; } set { passwordSalt = value; } } public int EmailPromotion { get { return emailPromotion; } set { emailPromotion = value; } } public CustomerDetails(String customerID, String firstName, String lastName, String email, String passwordHash, String passwordSalt, int emailPromotion) { this.customerID = customerID; this.firstName = firstName; this.lastName = lastName; this.email = email; this.passwordHash = passwordHash; this.passwordSalt = passwordSalt; this.emailPromotion = emailPromotion; } } public class CustomerAddress { private int addressID = -1; private String line1 = ""; private String line2 = ""; private String city = ""; private int stateProvinceID = -1; private String stateProvinceName = ""; private String postalCode = ""; public CustomerAddress() { } public CustomerAddress(int addressID, String line1, String line2, String city, int stateProvinceID, String stateProvinceName, String postalCode) { this.addressID = addressID; this.line1 = line1; this.line2 = line2; this.city = city; this.stateProvinceID = stateProvinceID; this.stateProvinceName = stateProvinceName; this.postalCode = postalCode; } // The fields above are exposed publically as properties // There is nothing fancy going on here, it is just better // practice to not expose public fields. This helps avoid binary // incompatabilities if the public fields were to have to // be changed to properties later. public int AddressID { get { return addressID; } set { addressID = value; } } public String Line1 { get { return line1; } set { line1 = value; } } public String Line2 { get { return line2; } set { line2 = value; } } public String City { get { return city; } set { city = value; } } public int StateProvinceID { get { return stateProvinceID; } set { stateProvinceID = value; } } public String StateProvinceName { get { return stateProvinceName; } set { StateProvinceName = value; } } public String PostalCode { get { return postalCode; } set { postalCode = value; } } public void FillAddress (DataTable dt) { if (dt.Rows.Count > 0) { DataRow dr = dt.Rows[0]; addressID = (int)dr["AddressID"]; line1 = (String)dr["AddressLine1"]; line2 = (String)dr["AddressLine2"]; city = (String)dr["City"]; stateProvinceID = (int)dr["StateProvinceID"]; stateProvinceName = (String)dr["StateProvinceName"]; postalCode = (String)dr["postalCode"]; } } } public class CustomerAddresses { private CustomerAddress billingAddress; private CustomerAddress shippingAddress; public CustomerAddresses(CustomerAddress billingAddress, CustomerAddress shippingAddress) { this.billingAddress = billingAddress; this.shippingAddress = shippingAddress; } public CustomerAddress BillingAddress { get { return billingAddress; } set { billingAddress = value; } } public CustomerAddress ShippingAddress { get { return shippingAddress; } set { shippingAddress = value; } } } //******************************************************* // // CustomersDB Class // // Business/Data Logic Class that encapsulates all data // logic necessary to add/login/query customers within // the AdventureWorks database. // //******************************************************* public class CustomersDB { private static string CreateSalt(int size) { // Generate a cryptographic random number using the cryptographic // service provider RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider(); byte[] buff = new byte[size]; rng.GetBytes(buff); // Return a Base64 string representation of the random number return Convert.ToBase64String(buff); } public static string CreatePasswordHash(string pwd, string salt) { string saltAndPwd = String.Concat(pwd, salt); string hashedPwd = FormsAuthentication.HashPasswordForStoringInConfigFile( saltAndPwd, "SHA1"); return hashedPwd; } //******************************************************* // // 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="usp_CustomerDetail.htm" style="color:green">usp_CustomerDetail Stored Procedure</a> // //******************************************************* public CustomerDetails GetCustomerDetails(String customerID) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection( Properties.Settings.Default.ConnectionString); SqlCommand myCommand = new SqlCommand("usp_CustomerDetail", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterCustomerID = new SqlParameter("@CustomerID", SqlDbType.Int, 4); parameterCustomerID.Value = Int32.Parse(customerID, CultureInfo.InvariantCulture); myCommand.Parameters.Add(parameterCustomerID); SqlParameter parameterFirstName = new SqlParameter("@FirstName", SqlDbType.NVarChar, 50); parameterFirstName.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterFirstName); SqlParameter parameterLastName = new SqlParameter("@LastName", SqlDbType.NVarChar, 50); parameterLastName.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterLastName); SqlParameter parameterEmail = new SqlParameter("@Email", SqlDbType.NVarChar, 50); parameterEmail.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterEmail); SqlParameter parameterPasswordHash = new SqlParameter("@PasswordHash", SqlDbType.VarChar, 40); parameterPasswordHash.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterPasswordHash); SqlParameter parameterPasswordSalt = new SqlParameter("@PasswordSalt", SqlDbType.VarChar, 10); parameterPasswordSalt.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterPasswordSalt); SqlParameter parameterEmailPromotion = new SqlParameter( "@EmailPromotion", SqlDbType.Int); parameterEmailPromotion.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterEmailPromotion); try { myConnection.Open(); myCommand.ExecuteNonQuery(); } finally { myConnection.Close(); } // Create CustomerDetails class and populate it. CustomerDetails myCustomerDetails = new CustomerDetails(customerID, (string)parameterFirstName.Value, (string)parameterLastName.Value, (string)parameterEmail.Value, (string)parameterPasswordHash.Value, (string)parameterPasswordSalt.Value, (parameterEmailPromotion.Value is int) ? (int)parameterEmailPromotion. Value : 0); return myCustomerDetails; } //******************************************************* // // CustomersDB.AddCustomer() Method <a name="AddCustomer"></a> // // The AddCustomer method inserts a new customer record // into the 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. Exceptions should // be caught by the caller. // Other relevant sources: // + <a href="usp_CustomerAdd.htm" style="color:green">usp_CustomerAdd Stored Procedure</a> // //******************************************************* public String AddCustomer(string firstName, string lastName, string email, string password) { if (GetCustomerID(email) != 0) throw new UserAlreadyExistsException(string.Format(CultureInfo.InvariantCulture, "The email address {0} is already in use", email)); String passwordSalt = CustomersDB.CreateSalt(5); String passwordHash = CreatePasswordHash(password, passwordSalt); // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString); SqlCommand myCommand = new SqlCommand("usp_CustomerAdd", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterFirstName = new SqlParameter("@FirstName", SqlDbType.NVarChar, 50); parameterFirstName.Value = firstName; myCommand.Parameters.Add(parameterFirstName); SqlParameter parameterLastName = new SqlParameter("@LastName", SqlDbType.NVarChar, 50); parameterLastName.Value = lastName; myCommand.Parameters.Add(parameterLastName); SqlParameter parameterEmail = new SqlParameter("@Email", SqlDbType.NVarChar, 50); parameterEmail.Value = email; myCommand.Parameters.Add(parameterEmail); SqlParameter parameterPasswordHash = new SqlParameter("@PasswordHash", SqlDbType.VarChar, 40); parameterPasswordHash.Value = passwordHash; myCommand.Parameters.Add(parameterPasswordHash); SqlParameter parameterPasswordSalt = new SqlParameter("@PasswordSalt", SqlDbType.VarChar, 10); parameterPasswordSalt.Value = passwordSalt; myCommand.Parameters.Add(parameterPasswordSalt); SqlParameter parameterCustomerID = new SqlParameter("@CustomerID", SqlDbType.Int, 4); parameterCustomerID.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterCustomerID); try { myConnection.Open(); myCommand.ExecuteNonQuery(); // Calculate the CustomerID using Output Param from SPROC int customerId = (int)parameterCustomerID.Value; return customerId.ToString(CultureInfo.InvariantCulture); } finally { if (myConnection.State != ConnectionState.Closed) myConnection.Close(); } } public void AddCustomerSurvey(int customerID, string survey) { // Create Instance of Connection and Command Object using (SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString)) { SqlCommand myCommand = new SqlCommand("usp_CustomerAddSurvey", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterCustomerID = new SqlParameter( "@CustomerID", SqlDbType.Int); parameterCustomerID.Value = customerID; myCommand.Parameters.Add(parameterCustomerID); SqlParameter parameterSurvey = new SqlParameter( "@CustomerSurvey", SqlDbType.NVarChar, 2048); parameterSurvey.Value = survey; myCommand.Parameters.Add(parameterSurvey); myConnection.Open(); myCommand.ExecuteNonQuery(); } } public void UpdateCustomer(int customerID, string firstName, string lastName, string email, string password, string passwordSalt, int emailPromotion) { String passwordHash = (password.Equals(String.Empty)) ? "" : CreatePasswordHash(password, passwordSalt); // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection( Properties.Settings.Default.ConnectionString); SqlCommand myCommand = new SqlCommand("usp_CustomerUpdate", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterCustomerID = new SqlParameter("@CustomerID", SqlDbType.Int, 4); parameterCustomerID.Value = customerID; myCommand.Parameters.Add(parameterCustomerID); SqlParameter parameterFirstName = new SqlParameter("@FirstName", SqlDbType. NVarChar, 50); parameterFirstName.Value = firstName; myCommand.Parameters.Add(parameterFirstName); SqlParameter parameterLastName = new SqlParameter("@LastName", SqlDbType. NVarChar, 50); parameterLastName.Value = lastName; myCommand.Parameters.Add(parameterLastName); SqlParameter parameterEmail = new SqlParameter("@Email", SqlDbType.NVarChar, 50); parameterEmail.Value = email; myCommand.Parameters.Add(parameterEmail); SqlParameter parameterPasswordHash = new SqlParameter( "@PasswordHash", SqlDbType.VarChar, 40); parameterPasswordHash.Value = passwordHash; myCommand.Parameters.Add(parameterPasswordHash); SqlParameter parameterEmailPromotion = new SqlParameter( "@EmailPromotion", SqlDbType.Int); parameterEmailPromotion.Value = emailPromotion; myCommand.Parameters.Add(parameterEmailPromotion); try { myConnection.Open(); myCommand.ExecuteNonQuery(); } finally { if (myConnection.State != ConnectionState.Closed) myConnection.Close(); } } private int GetCustomerID(string email) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString); SqlCommand myCommand = new SqlCommand("usp_CustomerLogin", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterEmail = new SqlParameter("@Email", SqlDbType.NVarChar, 50); parameterEmail.Value = email; myCommand.Parameters.Add(parameterEmail); SqlParameter parameterCustomerID = new SqlParameter("@CustomerID", SqlDbType.Int, 4); parameterCustomerID.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterCustomerID); try { // Open the connection and execute the Command myConnection.Open(); myCommand.ExecuteNonQuery(); return (int)(parameterCustomerID.Value); } finally { if (myConnection.State != ConnectionState.Closed) { myConnection.Close(); } } } //******************************************************* // // CustomersDB.Login() Method <a name="Login"></a> // // The Login method validates a email/password pair // against credentials stored in the 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="usp_CustomerLogin.htm" style="color:green">usp_CustomerLogin Stored Procedure</a> // //******************************************************* public CustomerDetails Login(string email, string password) { int customerId = GetCustomerID(email); if (customerId == 0) { return null; } else { //Lookup information (including password hash and password salt) about the specified user CustomerDetails result = GetCustomerDetails(customerId.ToString(CultureInfo.InvariantCulture)); // Now take the salt and the password entered by the user // and concatenate them together. string passwordAndSalt = String.Concat(password, result.PasswordSalt); // Now hash them string hashedPasswordAndSalt = FormsAuthentication.HashPasswordForStoringInConfigFile( passwordAndSalt, "SHA1"); // Now verify them. if (hashedPasswordAndSalt.Equals(result.PasswordHash)) return result; else return null; } } //******************************************************* // // CustomersDB.GetAllStateProvince() Method <a name="GetAllStateProvince"></a> // // The GetAllStateProvince method returns // a DataTable containing a list of all // State/Provicences registered in the database. // // Other relevant sources: // + <a href="usp_CustomerAllStateProvince" style="color:green">CustomerAllStateProvince Stored Procedure</a> // //******************************************************* public DataTable GetAllStateProvince() { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString); SqlCommand myCommand = myConnection.CreateCommand(); myCommand.CommandText = "usp_CustomerAllStateProvince"; // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; DataTable result = new DataTable(); result.Locale = CultureInfo.InvariantCulture; SqlDataAdapter sda = new SqlDataAdapter(myCommand); sda.Fill(result); return result; } //******************************************************* // // CustomersDB.GetCustomerDefaultAddresses() Method <a name="GetCustomerDefaultAddresses"></a> // // The GetCustomerDefaultAddresses method returns // a DataSet containing two tables (default billing and shipping address tables, each with // one row). // // Other relevant sources: // + <a href="usp_CustomerDefaultAddresses" style="color:green">usp_CustomerDefaultAddresses Stored Procedure</a> // //******************************************************* public CustomerAddresses GetCustomerDefaultAddresses(int customerID) { SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString); SqlCommand myCommand = myConnection.CreateCommand(); myCommand.CommandText = "usp_CustomerDefaultAddresses"; SqlParameter customerIDParameter = new SqlParameter("@CustomerID", SqlDbType.Int); myCommand.Parameters.Add(customerIDParameter); customerIDParameter.Value = customerID; // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; DataSet ds = new DataSet("Default Addresses"); ds.Locale = CultureInfo.InvariantCulture; SqlDataAdapter sda = new SqlDataAdapter(myCommand); sda.Fill(ds); CustomerAddresses result = new CustomerAddresses(new CustomerAddress(), new CustomerAddress()); if (ds.Tables.Count > 0) { result.BillingAddress.FillAddress(ds.Tables[0]); if (ds.Tables.Count > 1) result.ShippingAddress.FillAddress(ds.Tables[1]); } return result; } public int AccessCustomerAddress(int customerID, String addressTypeName, CustomerAddress customerAddress) { using (SqlConnection myConnection = new SqlConnection( Properties.Settings.Default.ConnectionString)) { SqlCommand myCommand = myConnection.CreateCommand(); myCommand.CommandText = "usp_CustomerAccessAddress"; // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; SqlParameter parameterCustomerID = new SqlParameter( "@CustomerID", SqlDbType.Int, 4); myCommand.Parameters.Add(parameterCustomerID); parameterCustomerID.Value = customerID; SqlParameter parameterAddressTypeName = new SqlParameter( "@AddressTypeName", SqlDbType.NVarChar, 50); myCommand.Parameters.Add(parameterAddressTypeName); parameterAddressTypeName.Value = addressTypeName; SqlParameter parameterAddressLine1 = new SqlParameter( "@AddressLine1", SqlDbType.NVarChar, 60); myCommand.Parameters.Add(parameterAddressLine1); parameterAddressLine1.Value = customerAddress.Line1; SqlParameter parameterAddressLine2 = new SqlParameter( "@AddressLine2", SqlDbType.NVarChar, 60); myCommand.Parameters.Add(parameterAddressLine2); parameterAddressLine2.Value = customerAddress.Line2; SqlParameter parameterCity = new SqlParameter("@City", SqlDbType. NVarChar, 30); myCommand.Parameters.Add(parameterCity); parameterCity.Value = customerAddress.City; SqlParameter parameterStateProvinceID = new SqlParameter( "@StateProvinceID", SqlDbType.Int, 4); myCommand.Parameters.Add(parameterStateProvinceID); parameterStateProvinceID.Value = customerAddress. StateProvinceID; SqlParameter parameterPostalCode = new SqlParameter( "@PostalCode", SqlDbType.NVarChar, 15); myCommand.Parameters.Add(parameterPostalCode); parameterPostalCode.Value = customerAddress.PostalCode; SqlParameter parameterAddressID = new SqlParameter("@AddressID", SqlDbType. Int, 4); myCommand.Parameters.Add(parameterAddressID); parameterAddressID.Direction = ParameterDirection.Output; myConnection.Open(); myCommand.ExecuteNonQuery(); return (int)parameterAddressID.Value; } } } }