CHAPTER 3 ■ CREATING THE PRODUCT CATALOG: PART I
49
featured products in the department page, in part because the complete list would be too long.
The text above the list of featured products is the description for the selected department,
which means you’ll need to store in the database both a name and a description for each
department.
In this page, when a particular category from the categories list is selected, all of its prod-
ucts are listed, along with updated title and description text. In Figure 3-3, 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.
Figure 3-3. The “Birthdays” category
In any page that displays products, you can click the name or the picture of a product to
view its product details page (see Figure 3-4). In later chapters, you’ll add more functionality to
this page, such as product recommendations.
Darie-Watson_4681C03.fm Page 49 Thursday, September 15, 2005 5:42 AM
50
CHAPTER 3
■ CREATING THE PRODUCT CATALOG: PART I
Figure 3-4. The product details page
Roadmap for This Chapter
We’ll cover a lot of ground in this chapter. To make sure you don’t get lost on the way, let’s have
a look at the big picture.
The departments list will be the first dynamically generated data in your site, as the names
of the departments will be extracted from the database. We cover just the creation of the
department list in this chapter, in the form of a Web User Control, because we’ll also take a
closer look at the mechanism that makes the control work. After you understand what happens
behind the list of departments, you’ll quickly implement the other components in Chapter 4.
In Chapter 2, we discussed the three-tiered architecture that you’ll use to implement the
Web Application. The product catalog part of the site makes no exception to the rule, and its
components (including the departments list) will be spread over the three logical layers. Figure 3-5
previews what you’ll create at each tier in this chapter to achieve a functional departments list.

of databases, but these solutions are generally not suited for applications like BalloonShop, so we won’t cover
them in this book. However, it’s good to know there are options.
Although this is not a book about databases or relational database design, you’ll learn all
you need to know to understand the product catalog and make it work. For more information
about database programming using SQL Server, you should read an SQL Server book such as
Beginning SQL Server 2005 Programming (Wiley, 2005).
Essentially, a relational database is made up of data tables and the relationships that exist
between them. Because in this chapter you’ll work with a single data table, we’ll cover only the
database theory that applies to the table as a separate, individual database item. In the next
chapter, when you add the other tables to the picture, we’ll take a closer look at more theory
behind relational databases by analyzing how the tables relate to each other and how SQL
Server helps you deal with these relationships.
■Note In a real world situation, you would probably design the whole database (or at least all the tables
relevant to the feature you build) from the start. However, we chose to split the development over two chapters
to maintain a better balance of theory and practice.
So, let’s start with a little bit of theory, after which you’ll create the Department data table
and the rest of the required components.
Darie-Watson_4681C03.fm Page 52 Thursday, September 15, 2005 5:42 AM
CHAPTER 3 ■ CREATING THE PRODUCT CATALOG: PART I
53
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
■Note If you have enough experience with SQL Server, you might want to skip this section and go directly

uniquely identifiable. For example, suppose you add another record to the Department table
shown previously in Figure 3-6, making it look like the table shown in Figure 3-7.
Figure 3-7. Two departments with the same name
Now look at this table, and tell me the description of the “Balloons for Children” depart-
ment. Yep, we have a problem! The problem arises because there are two departments with
this name. If you queried the table using the Name column and wanted to add new products to
the “Balloons for Children” department, to change the department’s name, or to do literally
anything, you would get two results!
To solve this problem, you use a primary key, which allows you to uniquely identify a
specific row out of many rows. Technically, a PRIMARY KEY is a constraint applied on a table
column that guarantees that the column will have unique values across the table.
■Note Applying a PRIMARY KEY constraint on a field also generates a unique index by default. Indexes are
objects that improve the performance of many database operations, speeding up your Web Application (you’ll
learn more about indexes a bit later).
A table can have a single PRIMARY KEY constraint, which can be composed of one or more
columns. Note that the primary key is not a column itself; instead, it’s a constraint that applies
to one or more of the existing columns. Constraints are rules that apply to data tables and make
up part of the data integrity rules of the database. The database takes care of its own integrity
and makes sure these rules aren’t broken. If, for example, you try to add two identical values for
a column that has a PRIMARY KEY constraint, the database will refuse the operation and generate
an error. We’ll do some experiments later in this chapter to show this.
Darie-Watson_4681C03.fm Page 54 Thursday, September 15, 2005 5:42 AM
CHAPTER 3 ■ CREATING THE PRODUCT CATALOG: PART I
55
■Note Although a PRIMARY KEY is not a column, but a constraint that applies to that column, from now
on, for the sake of simplicity, when we refer to primary key, we’ll be talking about the column that has the
PRIMARY KEY constraint applied to it.
Back to the example, setting the Name column as the primary key of the Department table
would solve the problem because two departments would not be allowed to have the same
name. If Name is the primary key of the Department table, searching for a row with a specific Name

similar to the PRIMARY KEY constraint because it doesn’t allow duplicate data in a column. Still,
there are differences. Although there is only one PRIMARY KEY constraint per table, you are allowed
to have as many UNIQUE constraints as you like.
Columns with the UNIQUE constraint are useful when you already have a primary key, but
you still have columns for which you want to have unique values. You can set Name to be unique
in the Department table if you want to forbid repeating values, when the DepartmentID column
is the primary key. (We won’t use the UNIQUE constraint in this book, but we mention it here for
completeness.) We decided to allow identical department names because only site administra-
tors will have the privileges to modify or change department data.
The facts that you need to remember about UNIQUE constraints are
•The UNIQUE constraint forbids having identical values on the field.
• You can have more that one UNIQUE field in a data table.
• Unlike with primary keys, a UNIQUE constraint can’t apply to more than one field.
•A UNIQUE field is allowed to accept NULL values, in which case it can only accept one
NULL value.
• Indexes are automatically created on UNIQUE and PRIMARY KEY columns.
Columns and Data Types
Each column in a table has a particular data type. By looking at the previously shown Figure 3-8
with the Department table, it’s clear that DepartmentID has a numeric data type, whereas Name
and Description contain text.
It’s important to consider the many data types that SQL Server supports so that you can
make correct decisions concerning how to create your tables. Table 3-1 isn’t an exhaustive list
of SQL Server data types, but it focuses on the main types you might come across in your project.
Refer to SQL Server 2005 Books Online, which can be freely accessed and downloaded from
for a more detailed list.
■Note Table 3-1 was created with SQL Server 2005 in mind, but these data types exist in SQL Server 2000
as well, and even SQL Server 7 comes close. The differences between SQL Server versions are reflected in
details such as the maximum size for character data.
To keep the table short, under the Data Type heading we’ve listed only the most frequently
used types, while similar data types are explained under the Description and Notes heading.

identifiers using other methods, but it’s good to know there
are options.
VarChar, NVarChar Variable Stores variable-length character data. NVarChar stores Unicode
data with a maximum length of 4,000 characters and VarChar
non-Unicode data with a maximum length of 8,000 characters.
This data type is best used for storing short strings (note their
length limitation) without fixed lengths.
Char, NChar Fixed Stores fixed-length character data. Values shorter than the
declared size are padded with spaces. NChar is the Unicode
version and goes to a maximum of 4,000 characters, whereas
Char can store 8,000 characters. When the size of the strings
to be stored is fixed, it’s more efficient to use Char rather than
VarChar.
Text, NText Fixed Stores large character data. NText is the Unicode version and
has a maximum size of 1,073,741,823 characters. Text has
double this maximum size. Using these data types can slow
down the database, and it’s generally recommended to use
Char, VarChar, NChar, or NVarChar instead. When adding Text
or NText fields, their length is fixed to 16, which represents the
size of the pointer that references the location where the actual
text is stored, and not the size of the text itself. The Text data
type can be used to store large character data such as para-
graphs, long product descriptions, and so on. We won’t use
this data type in this book.
Darie-Watson_4681C03.fm Page 57 Thursday, September 15, 2005 5:42 AM
58
CHAPTER 3
■ CREATING THE PRODUCT CATALOG: PART I
■Note The names of the SQL Server 2005 data types are not case sensitive, and most programmers write
them either in full uppercase or lowercase. We’ve cased them properly in the table for readability.

the product images in the file system.
Table 3-1. SQL Server 2005 Data Types (Continued)
Data Type Size in Bytes Description and Notes
Darie-Watson_4681C03.fm Page 58 Thursday, September 15, 2005 5:42 AM
CHAPTER 3 ■ CREATING THE PRODUCT CATALOG: PART I
59
Nullable Columns and Default Values
Observe the Allow Nulls column in the design window of the Department table—some fields
have this check box checked, but others don’t. If the check box is checked, the column is allowed
to store the NULL value.
The best and shortest definition for NULL is “undefined.” In your Department table, only
DepartmentID and Name are required, so Description is optional—meaning that you are allowed
to add a new department without supplying a description for it. If you add a new row of data
without supplying a value for columns that allow nulls, NULL is automatically supplied for them.
Especially for character data, a subtle difference exists between the NULL value and an
“empty” value. If you add a product with an empty string for its description, this means that
you actually set a value for its description; it’s an empty string, not an undefined (NULL) value.
The primary key field never allows NULL values. For the other columns, it’s up to you to
decide which fields are required and which are not.
In some cases, instead of allowing NULLs, you’ll prefer to specify default values. This way, if
the value is unspecified when creating a new row, it will be supplied with the default value. The
default value can be a literal value (such as 0 for a Salary column or "Unknown" for a Description
column), or it can be a system value (such as the GETDATE function, which returns the current
date). In Chapter 10, you’ll have a column named DateCreated, which can be set to have the
default value supplied by the GETDATE function.
Identity Columns
Identity columns are “auto-numbered” columns. This behavior is similar to AutoNumber columns
in Microsoft Access. When a column is set as an identity column, SQL Server automatically
provides values for it when inserting new records into the table; by default, the database doesn’t
permit manually specified values for identity columns.

tions. Indexes increase the speed of search operations, but slow down insert, delete, and update
operations. Usually, the gains of using indexes considerably outweigh the drawbacks.
On a table, you can create one or more indexes, with each index working on one column
or on a set of columns. When a table is indexed on a specific column, its rows are either indexed
or physically arranged based on the values of that column and of the type of index. This makes
search operations on that column very fast. If, for example, an index exists on DepartmentID,
and then you do a search for department 934, the search is performed very quickly. Adding or
updating new rows is a bit slower because the index must be actualized (or the table rows
rearranged) each time these operations occur.
You should keep the following in mind about indexes:
• Indexes greatly increase search operations on the database, but they slow down operations
that change the database (delete, update, and insert operations).
• Having too many indexes can slow down the general performance of the database. The
general rule is to set indexes on columns frequently used in WHERE, ORDER BY, and GROUP BY
clauses, used in table joins, or having foreign-key relationships with other tables.
• By default, indexes are automatically created on primary key and unique table columns.
You can use dedicated tools to test the performance of a database under stress conditions
with and without particular indexes; in fact, a serious database administrator will want to make
some of these tests before deciding on a wining combination for indexes. You can also use the
Database Tuning Advisor that can be accessed through SQL Server Management Studio (this
doesn’t ship with the Express Edition, however). Consult a specialized SQL Server book for
more details on these subjects.
In your application, you’ll rely on the indexes that are automatically created on the
primary key columns, which is a safe combination for our kind of web site.
Darie-Watson_4681C03.fm Page 60 Thursday, September 15, 2005 5:42 AM
CHAPTER 3 ■ CREATING THE PRODUCT CATALOG: PART I
61
Creating the Department Table
You created the BalloonShop database in Chapter 2. In the following exercise, you’ll add the
Department table to it.

Save Table1. When asked, type Department for the table name.
5. 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. (Alternatively, you can choose Database ➤ Show Table Data after selecting the table in Database
Explorer.) 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.
6. Add two departments, as shown in Figure 3-11.
Figure 3-11. Adding two sample rows to the Department table
Darie-Watson_4681C03.fm Page 62 Thursday, September 15, 2005 5:42 AM
8213592a117456a340854d18cee57603
CHAPTER 3 ■ CREATING THE PRODUCT CATALOG: PART I
63
■Note To ensure consistency with the scripts in the Source Code area on the Apress web site (and to make
your life easier), make sure the department IDs are 1 and 2, as shown in Figure 3-11. Because DepartmentID
is an identity column, an ID value is generated only once, even if you remove records from the table in the
meantime. The only way to reset the identity values generator is to delete and re-create the table, or to truncate
the table. The easiest way to truncate the table is to start SQL Server Express Manager, log in to your local
SQL Server Express Instance (by default, named localhost\SqlExpress), and execute the following
SQL commands:
USE BalloonShop
TRUNCATE TABLE Department
How It Works: The Database Table
You have just created your first database table! You also set a primary key, set an identity column, and then filled the
table with some data. As you can see, as soon as you have a clear idea about the structure of a table, Visual Web
Developer and SQL Server make it very easy to implement.
Let’s continue by learning how to programmatically access and manipulate this data with SQL code.

Communicating with the Database
Now that you have a table filled with data, let’s do something useful with it. The ultimate goal

• SQL queries created ad hoc in C# code are more vulnerable to SQL injection attacks,
which is a major security threat. Many Internet resources cover this security subject,
such as the article at />• This might be a matter of taste, but having the SQL logic separated from the C# code
keeps the C# code cleaner and easier to manage; it looks better to call the name of a
stored procedure than to join strings to create an SQL query to pass to the database.
Your goal for this section is to write the GetDepartments stored procedure, but first, let’s
take a quick look at SQL.
Speaking the Database Language
SQL (Structured Query Language) is the language used to communicate with modern Relational
Database Management Systems (RDBMS). Most database systems support a particular dialect
of SQL, such as T-SQL (Transact-SQL) for SQL Server and PL/SQL (Procedural Language extensions
to SQL) for Oracle. Because T-SQL is a big subject when analyzed in detail, we’ll briefly introduce it
and cover just enough so you’ll understand the code in your stored procedures.
■Tip If you’re interested in entering the world of SQL, we recommend another book we’ve authored called
The Programmer’s Guide to SQL (Apress, 2003). It covers the SQL standard and its dialects implemented in
SQL Server, Oracle, DB2, MySQL, and Access.
The basic and most important SQL commands are SELECT, INSERT, UPDATE, and DELETE. Their
names are self-explanatory, and they allow you to perform basic operations on the database.
You can use SQL Server Express Manager to test these commands with your newly created
Department table. Start SQL Server Express Manager, log in to your local SQL Server Express
Instance (by default, named localhost\SqlExpress), and then execute the following command
Darie-Watson_4681C03.fm Page 64 Thursday, September 15, 2005 5:42 AM
CHAPTER 3 ■ CREATING THE PRODUCT CATALOG: PART I
65
that connects you to the BalloonShop database (to execute the command, you can use the Execute
button on the toolbar, or choose Query ➤ Execute, or press the F5 shortcut key):
USE BalloonShop
After executing this command, you should get a “Command(s) completed successfully”
message. After you connect to the database, you’re ready to test the SQL commands you’re
about to learn.

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')
■Tip The INTO keyword is optional, but including it makes the statement easier to read.
We didn’t specify any value for the Description field because it was marked to allow NULLs
in the Department table. This is why you can omit specifying a value, if you want to. However,
the Name field is required, so if you tried, for example, to specify a description without specifying
a name, you would get an error:
INSERT INTO Department (Description) VALUES ('Some Description Here')
The error message specifies
.Net SqlClient Data Provider: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Name',
table 'balloonshop.dbo.Department'; column
does not allow nulls. INSERT fails.
The statement has been terminated.
Also note that you didn’t specify a DepartmentID. Because DepartmentID was set as an identity
column, you’re not allowed to manually specify values for this column. SQL Server can guarantee
this has unique values, but only if you don’t interfere with it.
So, if you can’t specify a value for DepartmentID, how can you determine which value was
automatically supplied by SQL Server? For this, you have a special variable named @@IDENTITY.
You can type its value by using the SELECT statement. The following two SQL commands add a
new record to Department and return the DepartmentID of the row just added:
Darie-Watson_4681C03.fm Page 66 Thursday, September 15, 2005 5:42 AM
CHAPTER 3 ■ CREATING THE PRODUCT CATALOG: PART I
67
INSERT INTO Department (Name) Values ('Some New Department')
SELECT @@IDENTITY

68
CHAPTER 3
■ CREATING THE PRODUCT CATALOG: PART I
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.
■Caution Unless you have a specific reason to do so, never ask for all columns (using the * wildcard) when
you only need a part of them. This generates more traffic and stress on the database server than necessary
and slows down performance. Moreover, even if you do need to ask for all columns in the table, it’s safer to
mention them explicitly to protect your application in case the number or order of columns changes in future.
Saving the Query As a Stored Procedure
As with data tables, after you know the structure, implementing the stored procedure is a piece
of cake. Now that you know the SQL code, the tools will help you save the query as a stored
procedure easily.
The syntax for creating a stored procedure that has no input or output parameters is
as follows:
CREATE PROCEDURE <procedure name>
AS
<stored procedure code>
If the procedure already exists and you just want to update its code, use ALTER PROCEDURE
instead of CREATE PROCEDURE.
Stored procedures can have input or output parameters. Because GetDepartments doesn’t
have any parameters, you don’t have to bother about them right now. You’ll learn how to use
input and output parameters in Chapter 4.
In the following exercise, you’ll add the GetDepartments stored procedure to your database.

You’ve just finished coding the data tier part that reads the departments list!
The results in the Output window confirm your stored procedure works as expected. You can also test the stored
procedure by using SQL Express Manager and executing the stored procedure from there:
USE BalloonShop
EXEC GetDepartments
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. However, for simple tasks such as getting a list of departments
from the data tier, the business tier doesn’t have much logic to implement. It just requests the
data from the database and passes it to the presentation tier.
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.
In Chapter 4, you’ll keep adding methods to these classes to support the new pieces of
functionality.
Darie-Watson_4681C03.fm Page 70 Thursday, September 15, 2005 5:42 AM
CHAPTER 3 ■ CREATING THE PRODUCT CATALOG: PART I
71
Connecting to SQL Server
The main challenge is to understand how the code that accesses the database works. The .NET
technology that permits accessing a database from C# code is called ADO.NET. ADO.NET
groups all .NET classes that are related to database access. This is the most modern Microsoft
data-access technology, and it can be used from any .NET language.
ADO.NET is a complex subject that requires a separate book by itself, so we’ll cover just
enough to help you understand how your business tier works. For more information about

// Set the connection string
connection.ConnectionString = "Server=(local)\SqlExpress; " +
"User ID=johnny; Password=qwerty;" +
"Database=BalloonShop";
// Open the connection
connection.Open();
The code is fairly straightforward: you first create a SqlConnection object, then set its
ConnectionString property, and finally open the connection. A connection needs to be opened
before using it for any operations.
Understanding the connection string is important—if your program has problems
connecting to the database, these problems likely can be solved by “fixing” the connection
string (assuming that SQL Server is properly configured and that you actually have access to it).
The connection string contains the three important elements. The first is the name of the
SQL Server instance you’re connecting to. For the SQL Server 2005 Express Edition, the default
instance name is (local)\SqlExpress. You’ll want to change this if your SQL Server instance
has another name. You can use your computer name instead of (local). Of course, if you
connect to a remote SQL Server instance, you’ll need to specify the complete network path
instead of (local).
After specifying the server, you need to supply security information needed to log in to the
server. You can log in to SQL Server by either using SQL Server Authentication (in which case
you need to supply a SQL Server username and password as shown in the code snippet) or by
using Windows Authentication (also named Windows Integrated Security). With Windows
Integrated Security, you don’t have to supply a username and password because SQL Server
uses the Windows login information of the currently logged-in user.
To log in using Windows Authentication, you’ll need to supply Integrated Security=True
(or Integrated Security=SSPI) instead of User ID=username; Password=password. The final part
of the connection string specifies the database you’ll be working with.
Instead of setting the connection string after creating the SqlConnection object, you can
provide the connection string right when creating the SqlConnection object:
// Create the connection object and set the connection string

under the credentials of the logged-in user, making your life easier from this point of view (you don’t need to
set any security options, as your site will have full privileges to the BalloonShop database by default).
Alternative methods to solve the connection problem when you use IIS include enabling SQL Server
Authentication and using a user ID and password in the connection string, or using a technique called ASP.NET
impersonation, when the ASP.NET application is executed under the credentials of another Windows user
than ASPNET. However, we’ll not discuss the details of using these techniques here.
To enable the ASPNET account to access the BalloonShop database, you need to follow these steps:
1. Start SQL Express Manager, specify the SQL Server Instance name (localhost\SqlExpress by
default), and log in using Windows Authentication.
2. Use the sp_grantlogin stored procedure to add a Windows user account to the SQL Server database.
This command grants the ASPNET account the privilege to connect to SQL Server. Be sure to use the
name of your local machine instead of MachineName.
EXEC sp_grantlogin 'MachineName\ASPNET'
3. After giving the ASPNET account the privilege to connect to SQL Server, you need to give it the privilege
to access to the BalloonShop database:
USE BalloonShop
EXEC sp_grantdbaccess 'MachineName\ASPNET'
Darie-Watson_4681C03.fm Page 73 Thursday, September 15, 2005 5:42 AM