Transcript cs447 chap4

CREATING THE
PRODUCT CATALOG
PART 1
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.
ROADMAP FOR THIS CHAPTER
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.
STORING CATALOG INFORMATION
• 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
INDEXES
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.
• Indexes are automatically created on primary key and
unique table columns.
CREATING THE DEPARTMENT TABLE
Add two sample data
COMMUNICATING WITH THE
DATABASE
• The ultimate goal with the table is to get the list of
department names from the database using C#
code.
• Stored procedures are database objects that store
programs written in T-SQL. Much like normal
functions, stored procedures accept input and
output parameters and have return values.
STORED PROCEDURES ADV.
• Storing SQL code as a stored procedure usually results
in better performance because SQL Server generates
and caches the stored procedure execution plan
when it’s first executed.
• Using stored procedures allows for better
maintainability of the data access and manipulation
code, which is stored in a central place, and permits
easier implementation of the three-tier architecture
(the stored procedures forming the data tier).
STORED PROCEDURES ADV.
• Security can be better controlled because SQL Server
permits setting different security permissions for each
individual stored procedure.
• SQL queries created ad hoc in C# code are more
vulnerable to SQL injection attacks, which is a major
security threat.
• 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 a SQL query to pass to the database.
CREATE THE STORED PROCEDURE
CREATE PROCEDURE GetDepartments AS
SELECT DepartmentID, Name, Description
FROM Department
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.
CHOOSING TECHNOLOGIES AND
TOOLS
• No matter which architecture is chosen, a major
question that arises in every development project is
which technologies, programming languages, and
tools are going to be used, bearing in mind that
external requirements can seriously limit your
options.
• The system requirements and software requirements
stages in the software development process will
determine which technologies you must use for
creating the application.
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.
• 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.
CONT.
• 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
EXAMPLE
// Create the connection object
SqlConnection connection = new SqlConnection();
// Set the connection string
connection.ConnectionString = "Server=(local)\SqlExpress; " +
"User ID=balloonshop; Password=ecommerce;" +
"Database=BalloonShop";
// Open the connection
connection.Open();
Or
// Create the connection object and set the connection string
SqlConnection connection = new SqlConnection("... connection
string ...");
// Open the connection
connection.Open();
ISSUING COMMANDS AND
EXECUTING STORED PROCEDURES
// Create the command object
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "CatalogGetDepartments";
command.CommandType = CommandType.StoredProcedure;
Or
// Create the command object
SqlCommand command = new
SqlCommand("GetDepartments", connection);
command.CommandType = CommandType.StoredProcedure;
EXECUTING THE COMMAND AND
CLOSING THE CONNECTION
• Three Execute methods of the SqlCommand class:
• ExecuteNonQuery
• ExecuteScalar
• ExecuteReader
EXECUTENONQUERY
• is used to execute a SQL statement or stored
procedure that doesn’t return any records. Used
when executing operations that update, insert, or
delete information in the database.
• ExecuteNonQuery returns an integer value that
specifies how many rows were affected by the
connection.Open();
command.ExecuteNonQuery();
command.Close();
EXECUTESCALAR
• is like ExecuteNonQuery in that it returns a single
value, although it returns a value that has been
read from the database instead of the number of
affected rows. It is used in conjunction with SELECT
statements that select a single value.
• If SELECT returns more rows and/or more columns,
only the first column in the first row is returned.
• E.g: SELECT COUNT(*) FROM
EXECUTEREADER
• is used with SELECT statements that return multiple records
• ExecuteReader returns a SqlDataReader object, which
contains the results of the query.
• A SqlDataReader object reads and returns the results one by
one, in a forward-only and read-only manner. The good
news about the SqlDataReader is that it represents the
fastest way to read data from the database, and the bad
news is that it needs an open connection to operate—no
other database operations can be performed on that
connection until the reader is closed.
• Another solution is to load all the data returned by the
SqlDataReader into a DataTable object which will allow you
to close the database connection very quickly.
EXAMPLE
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();
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 = "CatalogGetDepartments";
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();
CATCHING AND HANDLING
EXCEPTIONS
• The general strategy to deal with runtime exceptions is as
follows:
• If the error is not critical, deal with it in code, allowing the
code to continue executing
normally, and the visitor will never know an error happened.
• If the error is critical, handle it partially with code to reduce the
negative effects as much
as possible, and then let the error propagate to the presentation
tier that will show the
visitor a nice-looking “Houston, we have a problem” page.
• For the errors that you can’t anticipate, the last line of defense
is still the presentation
tier, which logs the error and politely asks the visitor to come
back later.
TRY – CATCH – FINALLY CONSTRUCT
try
{
// code that might generate an exception
}
catch (Exception ex)
{
// code that is executed only in case of an exception
// (exception's details are accessible through the ex object)
//it is optional
}
finally
{
// code that executes at the end, no matter if
// an exception was generated or not
//it is optional
}
SENDING EMAILS
• To send emails, you need the SmtpClient and
MailMessage classes from the System.Net.Mail
namespace.
• MailMessage has four important properties that you
set before sending an email: From, To, Subject, and
Body.
• When working with SmtpClient, you can set its Host
property to the address of an external SMTP server;
otherwise, the mail is sent through the local SMTP
service in Windows.
START PRACTICE SHEET 2