C H A P T E R 3

1
C H A P T E R 3 Creating the Product Catalog
Part I E-Commerce
Hassanin M. Al-Barhamtoshy hassanin_at_kau.edu.sa
2
How This Chapter Is Structured

• The main topics well 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, youll add more functionality to this
  page, such as product recommendations.

10
Roadmap for This Chapter

• The following figure previews what youll create
  at each tier in this chapter to achieve a
  functional departments list.

11
Roadmap for This Chapter

• To implement the departments list, youll start
  with the database and make your way to the
  presentation tier
• 1. Youll create the Department table in the
  database. This table will store data regarding
  the stores departments. Before adding this
  table, youll learn the basic concepts of working
  with relational databases.
• 2. Youll add the GetDepartments stored procedure
  to the database, which (like all the other stored
  procedures youll write) is logically located in
  the data tier part of the application. At this
  step, youll learn how to speak with relational
  databases using SQL.
• 3. Youll create the business tier components of
  the departments list. Youll learn how to
  communicate with the database by calling the
  stored procedure and sending the results to the
  presentation tier.
• 4. Finally, youll 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. Well 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, youll only need to
  create one data table the Department table. This
  table will store your departments data and is
  one of the simplest tables youll 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

1. 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 shortcut CtrlAltS.
2. Expand the BalloonShop database connection node,
   right-click the Tables node, and select Add New
   Table from the context menu. Alternatively, after
   connecting to the database, you can choose Data ?
   Add New ? Table.
3. 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 CtrlS or select File ? Save Table1. When
asked, type Department 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 ltcolumn listgt FROM lttable
name(s)gt WHERE ltrestrictive conditiongt
20
Communicating with the Database
The simplest SELECT command you can execute on
your BalloonShop database is SELECT FROM
Department
SELECT DepartmentID, Name, Description FROM
Department SELECT Name FROM Department WHERE
DepartmentID 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 lttable namegt (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 lttable
namegt SET ltcolumn namegt ltnew valuegt , ltcolumn
namegt ltnew valuegt ... WHERE ltrestrictive
conditiongt 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 lttable namegt WHERE
ltrestrictive conditiongt 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, youll 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 CREATE PROCEDURE ltprocedure namegt AS
ltstored procedure codegt
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 CtrlS to save the stored procedure. Unlike
  with the tables, you wont be asked for a name
  because the database already knows that youre
  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 its 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 applications business logic.
• For the business tier of the departments list,
  youll implement three classes
• GenericDataAccess implements common functionality
  that youll 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 youre
  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)\SqlE
  xpress "
• "User IDhassanin PasswordCS483"
• "DatabaseBalloonShop"
• // Open the connection
• connection.Open()

33
Issuing Commands and Executing Stored Procedures
Creating an SqlCommand Object // Create the
command object SqlCommand command new
SqlCommand() command.Connection
connection command.CommandText
"GetDepartments" command.CommandType
CommandType. StoredProcedure
This is equivalent to // Create the command
object SqlCommand command new
SqlCommand("GetDepartments", connection) command.
CommandType CommandType.StoredProcedure
34
Executing the Command and Closing the Connection

• Heres 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.SqlCl
ient") // Create the connection
object DbConnection conn factory.CreateConnectio
n() // 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.
• Utilities contains 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 ltconfiguration xmlns"http//schemas.microso
ft.com/.NetConfiguration/v2.0"gt ltappSettings/gt ltco
nnectionStringsgt ltadd name"BalloonShopConnection"
connectionString"Server (local)\SqlExpress Int
egrated SecurityTrueDatabaseBalloonShop" provid
erName"System.Data.SqlClient"/gt lt/connectionStrin
gsgt ltsystem.webgt lt!--
41
Exercise Implementing the Data Access Code
2. Add the other necessary configuration data
under the ltappSettingsgt node in web.config, as
shown here ltappSettingsgt ltadd key"MailServer"
value"localhost" /gt ltadd key"EnableErrorLogEmail
" value"true" /gt ltadd key"ErrorLogEmail"
value"errors_at_yourballoonshopxyz.com"
/gt lt/appSettingsgt
42
Exercise Implementing the Data Access Code
3. Right-click the projects 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. Youll 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 dbPro
viderName 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 "Enab
leErrorLogEmail")
46
Exercise Implementing the Data Access Code

• // Returns the email address where to send error
  reports
• public static string ErrorLogEmail
• get
• return ConfigurationManager.AppSettings"ErrorLogE
  mail"
• 7. Right-click the projects 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. Youll be asked about
  adding the class into the App_Code folder. Choose
  Yes.
• Write the following code into Utilities.cs (note
  that weve 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 (BalloonShopConfigurat
ion.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.microsof
t.com/cdo/configuration/sendusername", "SmtpHostUs
erName") message.Fields.Add ("http//schemas.micr
osoft.com/cdo/configuration/sendpassword", "SmtpHo
stPassword") / // Send mail mailClient.Send(mail
Message)
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.ToLongDate
String() ", at " DateTime.Now.ToShortTimeStrin
g() // 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.EnableErro
rLogEmail)
50
Exercise Implementing the Data Access Code
if (BalloonShopConfiguration.EnableErrorLogEmail)
string from "noreply_at_cristiandarie.ro" string
to BalloonShopConfiguration.ErrorLogEmail stri
ng subject BalloonShopConfiguration.SiteName
" error report" string body errorMessage SendM
ail(from, to, subject, body)
51
Exercise Implementing the Data Access Code
10. Right-click the projects name in Solution
Explorer and choose Add New Item from the context
menu. Choose the Class template and set its name
to GenericDataAccess.cs. Click Add. Youll be
asked about adding the class into the App_Code
folder. Choose Yes. 11. Write the following code
into GenericDataAccess.cs using System using
System.Data using System.Data.Common using
System.Configuration public static class
GenericDataAccess // static constructor static
GenericDataAccess() // TODO Add constructor
logic here
52
Exercise Implementing the Data Access Code
// executes a command and returns the results as
a DataTable object public static DataTable
ExecuteSelectCommand(DbCommand command) // The
DataTable to be returned DataTable table //
Execute the command making sure the connection
gets closed in the end try // Open the data
connection command.Connection.Open() // Execute
the command and save the results in a
DataTable DbDataReader reader
command.ExecuteReader() table new
DataTable() table.Load(reader)
53
Exercise Implementing the Data Access Code
// Close the reader reader.Close() catch
(Exception ex) Utilities.LogError(ex)
throw ex finally // Close the
connection command.Connection.Close() return
table public static DbCommand
CreateCommand() // Obtain the database provider
name string dataProviderName BalloonShopConfigur
ation.DbProviderName
54
Exercise Implementing the Data Access Code
// Obtain the database connection string string
connectionString BalloonShopConfiguration.DbConn
ectionString // Create a new data provider
factory DbProviderFactory factory
DbProviderFactories. GetFactory(dataProviderName)
// Obtain a database specific connection
object DbConnection conn factory.CreateConnectio
n() // Set the connection string conn.ConnectionS
tring connectionString // Create a database
specific command object DbCommand comm
conn.CreateCommand() // Set the command type to
stored procedure comm.CommandType
CommandType.StoredProcedure // Return the
initialized command object return comm
55
Exercise Implementing the Data Access Code
12. In Solution Explorer, right-click on the
App_Code folder and choose Add New Item. Using
the window that appears, create a new class named
CatalogAccess (which would reside in a file named
CatalogAccess.cs). Add the new code to the
file using System using System.Data using
System.Data.Common public static class
CatalogAccess static CatalogAccess() //
TODO Add constructor logic here // Retrieve
the list of departments public static DataTable
GetDepartments() // get a configured DbCommand
object DbCommand comm GenericDataAccess.CreateCo
mmand() // set the stored procedure
name comm.CommandText "GetDepartments" //
execute the stored procedure and return the
results return GenericDataAccess.ExecuteSelectComm
and(comm)
56
How It Works The Business Tier

• The configuration file web.config is an external
  configuration XML file managed by ASP.NET. This
  powerful file can include many options regarding
  the applications security, performance,
  behavior, and so on.
• Saving data to web.config is beneficial because
  you can change it independently of your C code,
  which now doesnt need to be recompiled when you
  change the address of the mail server or the
  database connection string.
• Then, you added the BalloonShopConfiguration
  class, which is simply a collection of static
  properties that return data from web.config. The
  performance is improved as well because the class
  can cache the values read from web.config instead
  of reading them on every request.
• Next, you implemented the GenericDataAccess
  class, whose purpose is to store a series of
  common database access operations, to avoid
  typing it all over again in other places.
• CreateCommand creates a DbCommand object, sets
  some standard properties to it, and returns the
  configured object.
• ExecuteSelectCommand is essentially a wrapper for
  DbCommands ExecuteReader method, except it
  returns the results as a DataTable instead of a
  DataReader.

57
Understand the OOP Terminology
Understanding of basic OOP terminology-such as
classes, objects, constructors, methods,
properties, fields, instance members and static
members, public data and private data, and so
on-is an important prerequisite for this course.
These topics are covered in many articles on the
Internet, such as the ones you can find for free
download at http//www.cristiandarie.ro/downloads.
html .
58
How It Works The Business Tier

• Weve chosen to use static members mainly to
  improve performance. Because static classes and
  static members are initialized only once, they
  dont need to be reinstantiated each time a new
  visitor makes a new request instead, their
  global instances are used. In the presentation
  tier, youll display your list of departments
  with a call like this
• list.DataSource CatalogAccess.GetDepartments()
• If GetDepartments would have been an instance
  method, you would have needed to create a
  separate instance of the CatalogAccess class
  instead of using the static instance, which would
  have had, obviously, a bigger performance impact
• CatalogAccess catalogAccess new
  CatalogAccess()
• list.DataSource catalogAccess.GetDepartments()

59
Displaying the List of Departments

• Now that everything is in place in the other
  tiers, all you have to do is create the
  presentation tier partthe final goal youve been
  working toward from the beginning. As you saw in
  the figures at the beginning of this chapter, the
  departments list needs to look something like the
  following figure when the site is loaded in the
  web browser.
• Fortunately, the .NET Framework provides a few
  useful web controls that can help you solve this
  problem without writing too much code. For
  example, the DataList control can be set to
  simply take a DataTable object as input and
  generate content based on it.
• Before actually writing the user control, lets
  prepare the BalloonShop CSS file.

60
How It Works The Business Tier

• Weve chosen to use static members mainly to
  improve performance. Because static classes and
  static members are initialized only once, they
  dont need to be reinstantiated each time a new
  visitor makes a new request instead, their
  global instances are used. In the presentation
  tier, youll display your list of departments
  with a call like this
• list.DataSource CatalogAccess.GetDepartments()
• If GetDepartments would have been an instance
  method, you would have needed to create a
  separate instance of the CatalogAccess class
  instead of using the static instance, which would
  have had, obviously, a bigger performance impact
• CatalogAccess catalogAccess new
  CatalogAccess()
• list.DataSource catalogAccess.GetDepartments()

61
Downloading the Code

• The code for this book is available for download
  in the Source Code area of the Apress web site
  http//www.apress.com
• Unzip the file and open Welcome.html for
  installation details.

62
Quiz 1

• Design an interface for e-Learning Application,
  includes the following
• Faculty Name.
• Department Names.
• Courses Content.