C H A P T E R 3 Creating the Product Catalog: Part I E-Commerce

1. C H A P T E R 3 Creating the Product Catalog: Part I E-Commerce Hassanin M. Al-Barhamtoshy hassanin@kau.edu.sa

2. How This Chapter Is Structured The main topics we’ll touch on in this chapter are: • Analyzing the structure of the product catalog and the functionality it should support • Creating the database structures for the catalog and the data tier of the catalog • Implementing the business tier objects required to make the catalog run, and putting a basic but functional error-handling strategy in place • Implementing a functional UI for the product catalog

3. What Does a Product Catalog Look Like? • The store has a number of departments. • Each department will contain a number of categories. • Each category can then have any number of products attached to it.

4. Graphical User Interface Designing • Accessibility: • Standard GUI • Blind Users (Color Blind) • Movement Difficulties • Sign Language Interface • Braille (Active Content – no Images) • Localization and Globalization • Multi Languages • Navigation

5. Installing the Software • Visual Web Developer 2005 • http://lab.msdn.microsoft.com/express/ • SQL Server 2005 • SQL Server Express Manager • Check to install SQL Server 2005 Express Edition. • IIS 5.x • Go to the Control Panel and select the Add or Remove Programs icon.

6. Previewing the Product Catalog • In the figure, you can see the BalloonShop front page and four of its featured products.

7. Previewing the Product Catalog In the figure, you see the page that will appear when the “Anniversary

8. Previewing the Product Catalog In the figure, you can see how that page appears when selecting the “Birthdays” category. Also note the paging controls, which appear in any product listings that contain more than an established number of products.

9. Previewing the Product Catalog • In any page that displays products, you can click the name or the picture of a product to view its product details page (see the figure). In later chapters, you’ll add more functionality to this page, such as product recommendations.

10. Roadmap for This Chapter • The following figure previews what you’ll create at each tier in this chapter to achieve a functional departments list.

11. Roadmap for This Chapter • To implement the departments list, you’ll start with the database and make your way to the presentation tier: 1. You’ll create the Department table in the database. This table will store data regarding the store’s departments. Before adding this table, you’ll learn the basic concepts of working with relational databases. 2. You’ll add the GetDepartments stored procedure to the database, which (like all the other stored procedures you’ll write) is logically located in the data tier part of the application. At this step, you’ll learn how to speak with relational databases using SQL. 3. You’ll create the business tier components of the departments list. You’ll learn how to communicate with the database by calling the stored procedure and sending the results to the presentation tier. 4. Finally, you’ll implement the DepartmentsList.ascx Web User Control to display a dynamic list of departments for your visitor, which is the goal of this chapter.

12. Storing Catalog Information Understanding Data Tables This section is a quick database lesson that covers the essential information you need to know to design simple data tables. We’ll briefly discuss the main parts that make up a database table: • Primary keys • Unique columns • SQL Server data types • Nullable columns and default values • Identity columns • Indexes

13. The Department Table • The database element of the product catalog is composed of tables, table relationships, and stored procedures. Because this chapter only covers the departments list, you’ll only need to create one data table: the Department table. This table will store your departments’ data and is one of the simplest tables you’ll work with. • The table containing the departments’ data might look like the following figure.

14. The Department Table • Suppose you add another record to the Department table shown previously in the following figure, making it look like the table shown in previous figure. • An alternative solution, and usually the preferred one, is to have an additional column in the table, called an ID column, to act as its primary key. With an ID column, the Department table would look like as in the following figure.

15. Designing the Department Table

16. Creating the Department Table • Using the Database Explorer window in Visual Web Developer, open the BalloonShop data connection that you created in the previous Chapter. Remember, if Database Explorer is not visible, activate it using View ➤ Database Explorer or by using the default shortcutCtrl+Alt+S. • Expand the BalloonShop database connection node, right-click the Tables node, and select Add New Tablefrom the context menu. Alternatively, after connecting to the database, you can choose Data ➤ Add New ➤ Table. • A form appears where you can add columns to the new table. Using this form, add three columns, with the properties described in the Table.

17. Choosing Technologies and Tools: Using ASP.NET 2.0 After adding these fields, the form should look like in the following figure in Visual Studio. Press Ctrl+S or select File ➤ Save Table1. When asked, typeDepartment for the table name.

18. Choosing Technologies and Tools: Using SQL Server 2005 After creating the table in the database, you can open it to add some data. To open the Department table for editing, right-click it in Database Explorer and select Show Table Data from the context menu. Using the integrated editor, you can start adding rows. Because DepartmentID is an identity column, you cannot manually edit its data-SQL Server automatically fills this field, depending on the identity seed and identity increment values that you specified when creating the table. Add two departments, as shown in the following figure .

19. Communicating with the Database SELECT The SELECT statement is used to query the database and retrieve selected data that match the criteria you specify. Its basic structure is SELECT <column list> FROM <table name(s)> [WHERE <restrictive condition>]

20. Communicating with the Database The simplest SELECT command you can execute on your BalloonShop database is SELECT * FROMDepartment SELECTDepartmentID, Name, Description FROM Department SELECT Name FROM Department WHEREDepartmentID = 1

21. Communicating with the Database: SELECT

22. Communicating with the Database: INSERT The INSERT statement is used to insert or add a row of data into the table. Its syntax is as follows: INSERT [INTO] <table name> (column list) VALUES (column values) The following INSERT statement adds a department named “Mysterious Department” to the Department table: INSERT INTO Department (Name) VALUES ('Mysterious Department')

23. Communicating with the Database: UPDATE The UPDATE statement is used to modify existing data and has the following syntax: UPDATE <table name> SET <column name> = <new value> [, <column name> = <new value> ...] [WHERE <restrictive condition>] Ex: UPDATE Department SET Name='Cool Department' WHERE DepartmentID = 43

24. Communicating with the Database: DELETE The syntax of the DELETE command is actually very simple: DELETE [FROM] <table name> [WHERE <restrictive condition>] The FROM keyword is optional and can be omitted. We generally use it because it makes the query sound more like normal English. Most times, you’ll want to use the WHERE clause to delete a single row: DELETE FROM Department WHERE DepartmentID = 43

25. Creating Stored Procedures You need to create the GetDepartments stored procedure, which returns department information from the Department table. This stored procedure is part of the data tier and will be accessed from the business tier. The final goal is to have this data displayed in the user control. The SQL code that retrieves the necessary data and that you need to save to the database as the GetDepartments stored procedure is the following: SELECT DepartmentID, Name, Description FROM Department This command returns all the department information.

26. Saving the Query As a Stored Procedure The syntax for creating a stored procedure that has no input or output parameters is as follows: CREATEPROCEDURE<procedure name> AS<stored procedure code>

27. Exercise: Writing the Stored Procedure • Make sure the data connection to the BalloonShop database is expanded and selected in Database Explorer. Choose Data ➤ Add New ➤ Stored Procedure. Alternatively, you can right-click the Stored Procedures node in Server Explorer and select Add New Stored Procedure. • Replace the default text with your GetDepartments stored procedure: CREATE PROCEDURE GetDepartments AS SELECT DepartmentID, Name, Description FROM Department • Press Ctrl+S to save the stored procedure. Unlike with the tables, you won’t be asked for a name because the database already knows that you’re talking about the GetDepartments stored procedure. • Right Click at the stored procedure, and Click Execute

28. Exercise: Execute the Stored Procedure

29. Exercise: Execute the Stored Procedure 4. Now test your first stored procedure to see that it’s actually working. Navigate to the GetDepartments stored procedure node in Database Explorer and select Execute, as shown in the following Figure.

30. Adding Logic to the Site The business tier (or middle tier) is said to be the brains of the application because it manages the application’s business logic. For the business tier of the departments list, you’ll implement three classes: • GenericDataAccess implements common functionality that you’ll then reuse whenever you need to access the database. Having this kind of generic functionality packed in a separate class saves keystrokes and avoids bugs in the long run. • CatalogAccess contains product catalog specific functionality, such the GetDepartments method that will retrieve the list of departments from the database. • BalloonShopConfiguration and Utilities contain miscellaneous functionality such as sending emails, which will be reused in various places in BalloonShop.

31. Connecting to SQL Server Each database operation always consists of three steps: 1. Open a connection to the SQL Server database. 2. Perform the needed operations with the database and get back the results. 3. Close the connection to the database. The class used to connect to SQL Server is SqlConnection. When creating a new database connection, you always need to specify at least three important pieces of data: • The name of the SQL Server instance you’re connecting to • The authentication information that will permit you to access the server • The database you want to work with

32. Connecting to SQL Server • The following code snippet demonstrates how to create and open a database connection: // Create the connection object SqlConnection connection = new SqlConnection(); // Set the connection string connection.ConnectionString = "Server=(local)\SqlExpress; " + "User ID=hassanin; Password=CS483;" + "Database=BalloonShop"; // Open the connection connection.Open();

33. Issuing Commands and Executing Stored Procedures Creating an SqlCommand Object // Create the command object SqlCommand command = newSqlCommand(); command.Connection = connection; command.CommandText = "GetDepartments"; command.CommandType = CommandType. StoredProcedure; This is equivalent to: // Create the command object SqlCommandcommand = newSqlCommand("GetDepartments", connection); command.CommandType = CommandType.StoredProcedure;

34. Executing the Command and Closing the Connection • Here’s a simple example of reading some records from the database and saving them to a DataTable: // Open the connection conn.Open(); // Create the SqlDataReader object by executing the command SqlDataReader reader = comm.ExecuteReader(); // Create a new DataTable and populate it from the SqlDataReader DataTable table = new DataTable(); table.Load(reader); // Close the reader and the connection reader.Close(); conn.Close();

35. Implementing Generic Data Access Code The .NET Framework ships with Managed Data Providers for SQL Server (System.Data.SqlClient namespaces), Oracle (System.Data.Oracle), OLE DB (System.Data.OleDb), and ODBC (System.Data.Odbc).

36. Create Database Exercise Before we continue, please: Create an Access table (for your personal information), and do the following: 1- Display the contents in the Web page(Master page). 2- Follow the steps to create Data List.

37. Implementing Generic Data Access Code // Create a new database provider factory DbProviderFactory factory =DbProviderFactories.GetFactory("System.Data.SqlClient"); // Create the connection object DbConnection conn = factory.CreateConnection(); // Initialize the connection string conn.ConnectionString = "... connection string ..."; // Create the command object and set its properties DbCommand comm = conn.CreateCommand(); comm.CommandText = "GetDepartments"; comm.CommandType = CommandType.StoredProcedure; // Open the connection conn.Open(); // Execute the command and save the results in a DataTable DbDataReader reader = comm.ExecuteReader(); DataTable table = new DataTable(); table.Load(reader); // Close the reader and the connection reader.Close(); conn.Close();

38. Sending Emails SMTP (Simple Mail Transfer Protocol) • The standard code that sends an email looks like the following code snippet (you need to replace the text in italics with your own data): // Configure mail client (may need additional code for // authenticated SMTP servers) SmtpClient smtpClient = new SmtpClient("SMTP server address"); // Create the mail message MailMessage mailMessage = new MailMessage("from", "to", "subject", "body"); // Send mail smtpClient.Send(mailMessage);

39. Writing the Business Tier Code You ‘ll add the following C# classes: GenericDataAccess contains the generic database access code, implementing basic error-handling and logging functionality. CatalogAccess contains the product catalog business logic. BalloonShopConfiguration provides easy access to various configuration settings (that are generally read from web.config), such as the database connection string, and so on. Utilitiescontains miscellaneous functionality such as sending emails, which will be used from various places in BalloonShop.

40. Exercise: Implementing the Data Access Code 1. Open the web.config configuration file (double-click on its name in Solution Explorer) and update the connectionStrings element like this: <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"> <appSettings/> <connectionStrings> <add name="BalloonShopConnection" connectionString="Server= (local)\SqlExpress; Integrated Security=True;Database=BalloonShop" providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> <!--

41. Exercise: Implementing the Data Access Code 2. Add the other necessary configuration data under the <appSettings> node in web.config, as shown here: <appSettings> <add key="MailServer" value="localhost" /> <add key="EnableErrorLogEmail" value="true" /> <add key="ErrorLogEmail" value="errors@yourballoonshopxyz.com" /> </appSettings>

42. Exercise: Implementing the Data Access Code 3. Right-click the project’s name in Solution Explorer and choose Add New Item from the context menu. 4. Choose the Class template, and set its name to ApplicationConfiguration.cs. Click Add. 5. You’ll be asked about adding the class into the App_Code folder. This is a special folder in ASP.NET 2.0. Choose Yes. 6. Modify the ApplicationConfiguration class like this:

43. Exercise: Implementing the Data Access Code using System; using System.Configuration; public static class BalloonShopConfiguration { // Caches the connection string private static string dbConnectionString; // Caches the data provider name private static string dbProviderName; static BalloonShopConfiguration() { dbConnectionString =ConfigurationManager.ConnectionStrings ["BalloonShopConnection"].ConnectionString; dbProviderName =ConfigurationManager.ConnectionStrings ["BalloonShopConnection"].ProviderName; }

44. Exercise: Implementing the Data Access Code // Returns the connection string for the BalloonShop database public static string DbConnectionString { get { return dbConnectionString; } } // Returns the data provider name public static string DbProviderName { get { return dbProviderName; } }

45. Exercise: Implementing the Data Access Code // Returns the address of the mail server public static string MailServer { get { return ConfigurationManager.AppSettings["MailServer"]; } } // Send error log emails? public static bool EnableErrorLogEmail { get { return bool.Parse(ConfigurationManager.AppSettings ["EnableErrorLogEmail"]); } }

46. Exercise: Implementing the Data Access Code // Returns the email address where to send error reports public static string ErrorLogEmail { get { return ConfigurationManager.AppSettings["ErrorLogEmail"]; } } } 7. Right-click the project’s name in Solution Explorer and choose Add New Item from the context menu. • Choose the Class template and set its name to Utilities.cs. Click Add. You’ll be asked about adding the class into the App_Code folder. Choose Yes. • Write the following code into Utilities.cs (note that we’ve removed the unnecessary using statements): using System; using System.Net.Mail;

47. Exercise: Implementing the Data Access Code public static class Utilities { static Utilities() { // TODO: Add constructor logic here } // Generic method for sending emails public static void SendMail(string from, string to, string subject, string body) { // Configure mail client (may need additional code for authenticated SMTP servers) SmtpClient mailClient = new SmtpClient (BalloonShopConfiguration.MailServer); // Create the mail message MailMessage mailMessage = new MailMessage(from, to, subject, body);

48. Exercise: Implementing the Data Access Code /* // For SMTP servers that require authentication message.Fields.Add ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate", 1); message.Fields.Add ("http://schemas.microsoft.com/cdo/configuration/sendusername", "SmtpHostUserName"); message.Fields.Add ("http://schemas.microsoft.com/cdo/configuration/sendpassword", "SmtpHostPassword"); */ // Send mail mailClient.Send(mailMessage); }

49. Exercise: Implementing the Data Access Code // Send error log mail public static void LogError(Exception ex) { // get the current date and time string dateTime = DateTime.Now.ToLongDateString() + ", at " + DateTime.Now.ToShortTimeString(); // stores the error message string errorMessage = "Exception generated on " + dateTime; // obtain the page that generated the error System.Web.HttpContext context = System.Web.HttpContext.Current; errorMessage += "\n\n Page location: " + context.Request.RawUrl; // build the error message errorMessage += "\n\n Message: " + ex.Message; errorMessage += "\n\n Source: " + ex.Source; errorMessage += "\n\n Method: " + ex.TargetSite; errorMessage += "\n\n Stack Trace: \n\n" + ex.StackTrace; // send error email in case the option is activated in Web.Config if (BalloonShopConfiguration.EnableErrorLogEmail) {

50. Exercise: Implementing the Data Access Code if (BalloonShopConfiguration.EnableErrorLogEmail) { string from = "noreply@cristiandarie.ro"; string to = BalloonShopConfiguration.ErrorLogEmail; string subject = BalloonShopConfiguration.SiteName + " error report"; string body = errorMessage; SendMail(from, to, subject, body); } } }