More ASP'NET Database - PowerPoint PPT Presentation

1 / 40

About This Presentation

Title:

More ASP'NET Database

Description:

More ASP.NET Database. In a properly organized application, your data access code ... The DataSet isn't required in ASP.NET pages. ... – PowerPoint PPT presentation

Number of Views:62

Avg rating:3.0/5.0

Slides: 41

Provided by: kuro9

Category:

more less

Transcript and Presenter's Notes

Title: More ASP'NET Database

1
More ASP.NET Database

In a properly organized application, your data
access code is never embedded directly in the
code-behind for a page. Instead, its separated
into a dedicated data component.
Create a simple data access class, adding a
separate method for each data task you need to
perform.
Disconnected datathe ADO.NET features that
revolve around the DataSet and allow you to
interact with data long after youve closed the
connection to the datasource.
The DataSet isnt required in ASP.NET pages.
However, it gives you more flexibility for
navigating, filtering, and sorting your data.

2
Building a Data Access Component

In professional applications, database code is
not embedded directly in the client but
encapsulated in a dedicated class.
To perform a database operation, the client
creates an instance of this class and calls the
appropriate method.

3
Open and close connections quickly

Open the database connection in every method
call, and close it before the method ends.
Connections should never be held open between
client requests, and the client should have no
control over how connections are acquired or when
they are released.
If the client does have this ability, it
introduces the possibility that a connection
might not be closed as quickly as possible or
might be inadvertently left open, which hampers
scalability.

4
Implement error handling

Use error handling to make sure the connection is
closed even if the SQL command generates an
exception.
Remember, connections are a finite resource, and
using them for even a few extra seconds can have
a major overall effect on performance.

5
Follow stateless design practices

Accept all the information needed for a method in
its parameters, and return all the retrieved data
through the return value.
If you create a class that maintains state, it
cannot be easily implemented as a web service or
used in a load-balancing scenario.
Also, if the database component is hosted out of
the process, each method call has a measurable
overhead, and using multiple calls to set
properties will take much longer than invoking a
single method with all the information as
parameters.

6
Dont let the client specify connection string
information

This poses security risks, raises the possibility
that an out-of-date client will fail, and
compromises the ability of connection pooling,
which requires matching connection strings.

7
Dont connect with the clients user ID

Introducing any variability into the connection
string will thwart connection pooling.
Instead, rely on rolebased security or a
ticket-based system whereby you authenticate
users and prevent them from attempting to perform
a restricted operation.
This model is also faster than trying to perform
a database query under an invalid security
account and waiting for an error.

8
Dont let the client use wide-open queries

Every query should judiciously select only the
columns it needs.
Also, you should restrict the results with a
WHERE clause whenever possible.
A good, straightforward design for a database
component uses a separate class for every
database table (or logically related group of
tables).
The common database access methods such as
inserting, deleting, and modifying a record are
all wrapped in separate stateless methods.
Finally, every database call uses a dedicated
stored procedure.

9
(No Transcript)
10
The following example demonstrates a simple
database component.

Rather than placing the database code in the web
page, it follows a much better design practice of
separating the code into a distinct class that
can be used in multiple pages.
This class can then be compiled as part of a
separate component if needed.
Additionally, the connection string is retrieved
from the ltconnectionStringsgt section of the
web.config file, rather than being hard-coded.
The data component actually consists of two
classesa data package class that wraps a single
record of information and a database utility
class that performs the actual database
operations with ADO.NET code.

11
The Data Package

To make it easier to shuffle information to the
Northwind database and back, it makes sense to
create an EmployeeDetails class that provides all
the fields as public properties. Heres the full
code for this class
public class EmployeeDetails
private int employeeID
public int EmployeeID
get return employeeID
set employeeID value
private string firstName

12
The Data Package

public string FirstName
get return firstName
set firstName value
private string lastName
public string LastName
get return lastName
set lastName value

13
The Data Package

private string titleOfCourtesy
public string TitleOfCourtesy
get return titleOfCourtesy
set titleOfCourtesy value
public EmployeeDetails(int employeeID, string
firstName, string lastName,
string titleOfCourtesy)
this.employeeID employeeID
this.firstName firstName
this.lastName lastName
this.titleOfCourtesy titleOfCourtesy

14
The Stored Procedures

Before you can start coding the data access
logic, you need to make sure you have the set of
stored procedures you need in order to retrieve,
insert, and update information.
The following code shows the five stored
procedures that are needed

15
The Stored Procedures

CREATE PROCEDURE InsertEmployee
_at_EmployeeID int OUTPUT,
_at_FirstName varchar(10),
_at_LastName varchar(20),
_at_TitleOfCourtesy varchar(25)
AS
INSERT INTO Employees
(TitleOfCourtesy, LastName, FirstName, HireDate)
VALUES (_at_TitleOfCourtesy, _at_LastName, _at_FirstName,
GETDATE())
SET _at_EmployeeID _at__at_IDENTITY
GO

16
The Stored Procedures

CREATE PROCEDURE DeleteEmployee
_at_EmployeeID int
AS
DELETE FROM Employees WHERE EmployeeID
_at_EmployeeID
GO

17
The Stored Procedures

CREATE PROCEDURE UpdateEmployee
_at_EmployeeID int,
_at_TitleOfCourtesy varchar(25),
_at_LastName varchar(20),
_at_FirstName varchar(10)
AS
UPDATE Employees
SET TitleOfCourtesy _at_TitleOfCourtesy,
LastName _at_LastName,
FirstName _at_FirstName
WHERE EmployeeID _at_EmployeeID
GO

18
The Stored Procedures

CREATE PROCEDURE GetAllEmployees
AS
SELECT EmployeeID, FirstName, LastName,
TitleOfCourtesy FROM Employees
GO

19
The Stored Procedures

CREATE PROCEDURE CountEmployees
AS
SELECT COUNT(EmployeeID) FROM Employees
GO

20
The Stored Procedures

CREATE PROCEDURE GetEmployee
_at_EmployeeID int
AS
SELECT FirstName, LastName, TitleOfCourtesy FROM
Employees
WHERE EmployeeID _at_EmployeeID
GO

21
The Data Utility Class

Finally, you need the utility class that performs
the actual database operations.
This class uses the stored procedures that were
shown previously.
In this example, the data utility class is named
EmployeeDB.
It encapsulates all the data access code and
database-specific details.

22
The Data Utility Class

public class EmployeeDB
private string connectionString
public EmployeeDB()
// Get default connection string.
connectionString WebConfigurationManager.Connect
ionStrings"Northwind".ConnectionString
public EmployeeDB(string connectionStringName)
// Get the specified connection string.
connectionString WebConfigurationManager.Connect
ionStrings
"connectionStringName".ConnectionString

23
The Data Utility Class

public int InsertEmployee(EmployeeDetails emp)
...
public void DeleteEmployee(int employeeID)
...
public void UpdateEmployee(EmployeeDetails emp)
...
public EmployeeDetails GetEmployee()
...
public EmployeeDetails GetEmployees()
...
public int CountEmployees()
...

24
The Data Utility Class

Heres the code for inserting a record
public int InsertEmployee(EmployeeDetails emp)
SqlConnection con new SqlConnection(connectionSt
ring)
SqlCommand cmd new SqlCommand("InsertEmployee",
con)
cmd.CommandType CommandType.StoredProcedure
cmd.Parameters.Add(new SqlParameter("_at_FirstName",
SqlDbType.NVarChar, 10))
cmd.Parameters"_at_FirstName".Value
emp.FirstName
cmd.Parameters.Add(new SqlParameter("_at_LastName",
SqlDbType.NVarChar, 20))
cmd.Parameters"_at_LastName".Value emp.LastName
cmd.Parameters.Add(new SqlParameter("_at_TitleOfCourt
esy",
SqlDbType.NVarChar, 25))
cmd.Parameters"_at_TitleOfCourtesy".Value
emp.TitleOfCourtesy

25
The Data Utility Class

cmd.Parameters.Add(new SqlParameter("_at_EmployeeID",
SqlDbType.Int, 4))
cmd.Parameters"_at_EmployeeID".Direction
ParameterDirection.Output
try
con.Open()
cmd.ExecuteNonQuery()
return (int)cmd.Parameters"_at_EmployeeID".Value
catch (SqlException err)
// Replace the error with something less
specific.
// You could also log the error now.
throw new ApplicationException("Data error.")
finally
con.Close()

26
The Data Utility Class

The method accepts data using the EmployeeDetails
package.
Any errors are caught, and the sensitive internal
details are not returned to the web-page code.
This prevents the web page from providing
information that could lead to possible exploits.
This would also be an ideal place to call another
method in a logging component to report the full
information in an event log or another database.

27
The Data Utility Class

The GetEmployee() and GetEmployees() methods
return the data using the EmployeeDetails
package
public EmployeeDetails GetEmployee(int
employeeID)
SqlConnection con new SqlConnection(connectionSt
ring)
SqlCommand cmd new SqlCommand("GetEmployee",
con)
cmd.CommandType CommandType.StoredProcedure
cmd.Parameters.Add(new SqlParameter("_at_EmployeeID",
SqlDbType.Int, 4))
cmd.Parameters"_at_EmployeeID".Value employeeID
try
con.Open()
SqlDataReader reader cmd.ExecuteReader(CommandBe
havior.SingleRow)

28
The Data Utility Class

// Get the first row.
reader.Read()
EmployeeDetails emp new EmployeeDetails(
(int)reader"EmployeeID", (string)reader"FirstNa
me",
(string)reader"LastName", (string)reader"TitleO
fCourtesy")
reader.Close()
return emp
catch (SqlException err)
throw new ApplicationException("Data error.")
finally
con.Close()

29
The Data Utility Class

public ListltEmployeeDetailsgt GetEmployees()
SqlConnection con new SqlConnection(connectionSt
ring)
SqlCommand cmd new SqlCommand("GetAllEmployees",
con)
cmd.CommandType CommandType.StoredProcedure
// Create a collection for all the employee
records.
ListltEmployeeDetailsgt employees new
ListltEmployeeDetailsgt()
try
con.Open()
SqlDataReader reader cmd.ExecuteReader()

30
The Data Utility Class

while (reader.Read())
EmployeeDetails emp new EmployeeDetails(
(int)reader"EmployeeID", (string)reader"FirstNa
me",
(string)reader"LastName", (string)reader"TitleO
fCourtesy")
employees.Add(emp)
reader.Close()
return employees
catch (SqlException err)
throw new ApplicationException("Data error.")
finally
con.Close()

31
Concurrency Strategies

In any multiuser application, including web
applications, theres the potential that more
than oneuser will perform overlapping queries and
updates.
This can lead to a potentially confusing
situation where two users, who are both in
possession of the current state for a row,
attempt to commit divergent updates.
The first users update will always succeed. The
success or failure of the second update is
determined by your concurrency strategy.

32
Concurrency Strategies

There are several broad approaches to concurrency
management.
The most important thing to understand is that
you determine your concurrency strategy by the
way you write your UPDATE commands (particularly
the way you shape the WHERE clause).
Here are the most common examples
Last-in-wins updating This is a less restrictive
form of concurrency control that always commits
the update (unless the original row has been
deleted).
Every time an update is committed, all the values
are applied.
Last-in-wins makes sense if data collisions are
rare.
For example, you can safely use this approach if
there is only one person responsible for updating
a given group of records.
Usually, you implement a last-in-wins by writing
a WHERE clause that matches the record to update
based on its primary key.
UPDATE Employees SET ... WHERE EmployeeID_at_ID

33
Concurrency Strategies

Match-all updating To implement this strategy,
you add a WHERE clause that tries to match the
current values of every field in the record to
your UPDATE statement.
That way, if even a single field has been
modified, the record wont be matched and the
change will not succeed.
One problem with this approach is that compatible
changes are not allowed.
For example, if two users are attempting to
modify different parts of the same record, the
second users change will be rejected, even
though it doesnt conflict.
Another, more significant, problem with the
match-all updating strategy is that it leads to
large, inefficient SQL statements.
You can implement the same strategy more
effectively with timestamps.
UPDATE Employees SET ... WHERE EmployeeID_at_ID AND
FirstName_at_FirstName AND LastName_at_LastName ...

34
Concurrency Strategies

Timestamp-based updating Most database systems
support a timestamp column, which the data source
updates automatically every time a change is
performed.
You do not need to modify the timestamp column
manually.
However, you can examine it for changes and
thereby determine if another user has recently
applied an update.
If you write an UPDATE statement with a WHERE
clause that incorporates the primary key and the
current timestamp, youre guaranteed to update
the record only if it hasnt been modified, just
like with match-all updating.
UPDATE Employees SET ... WHERE EmployeeID_at_ID AND
TimeStamp_at_TimeStamp

35
Concurrency Strategies

Changed-value updating This approach attempts to
apply just the changed values in an UPDATE
command, thereby allowing two users to make
changes at the same time if these changes are to
different fields.
The problem with this approach is it can be
complex, because you need to keep track of what
values have changed (in which case they should be
incorporated in the WHERE clause) and what values
havent.

36
Testing the Component

Now that you've created the data component, you
just need a simple test page to try it out.
As with any other component, you must begin by
adding a reference to the component assembly.
Then you can import the namespace it uses to make
it easier to implement the EmployeeDetails and
EmployeeDB classes.
The only step that remains is to write the code
that interacts with the classes.
In this example, the code takes place in the
Page.Load event handler.
First, the code retrieves and writes the number
and the list of employees by using a private
WriteEmployeesList() method that translates the
details to HTML.
Next, the code adds a record and lists the table
content again.
Finally, the code deletes the added record and
shows the content of the Employees table one more
time.