Build Your Own ASP.NET 3.5 Website Using C# & VB (63 page)

to get a feel for how this works.

The code of the stored procedure starts by creating a new department with the name

specified by the
@NewDepartmentName
parameter:

--Create a new department

INSERT INTO Departments (Department)

VALUES (@NewDepartmentName)

Licensed to [email protected]

Speaking SQL

345

Immediately after it creates the department, the stored procedure stores the value

generated for the IDENTITY primary key column (DepartmentID). This value is returned by the scope_identity function, which returns the most recently generated identity value. Keep in mind that it’s good practice to store this identity value right

after the INSERT query that generated it; if we don’t store this value immediately, a

second INSERT query may generate another identity value, and that second identity

value would then be returned by scope_identity. The value is saved into a new

variable named @NewDepartmentID.

Next, you can see how we use the DECLARE statement to declare a new variable in

an SQL stored procedure:

--Obtain the ID of the created department

DECLARE @NewDepartmentID INT

SET @NewDepartmentID = scope_identity()

The stored procedure continues by creating a new employee using the name and

username it received as parameters; it assigns this employee to the department that

was created earlier:

--Create a new employee

INSERT INTO Employees (DepartmentID, Name, Username)

VALUES (@NewDepartmentID, @NewEmployeeName, @NewEmployeeUsername)

Again, right after creating the new employee, we store its ID into a variable named

@NewEmployeeID. Earlier, we needed to store the generated DepartmentID so that

we could assign the new employee to it; this time, we’re storing the new employee

ID so we can delete the employee later:

--Obtain the ID of the created employee

DECLARE @NewEmployeeID INT

SET @NewEmployeeID = scope_identity()

Finally, with the new department and employee in place, the stored procedure selects

the list of departments together with their employees:

Licensed to [email protected]

346

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

--List the departments together with their employees

SELECT Departments.Department, Employees.Name

INNER JOIN Employees ON Departments.DepartmentID =

Employees.DepartmentID

For the purposes of this example, we’d prefer to keep the database tidy, which is

why we’re deleting the new records at the end of the stored procedure:

--Delete the new employee

WHERE EmployeeID=@NewEmployeeID

--Delete the new department

WHERE DepartmentID=@NewDepartmentID

In the code above, the department and employee IDs that we saved earlier come in

very handy: without them, we wouldn’t have any way to guarantee that we were

deleting the right records!

As you can see, a stored procedure is similar to a function in VB or C#: just like

functions in VB or C# code, stored procedures can accept parameters, perform calculations based on those parameters, and return values. SQL also allows for some of the other programming constructs we’ve seen in the preceding chapters, such as

If statements, While loops, and so on, but advanced stored procedure programming

is a little beyond the scope of this book.

Summary

Robust, reliable data access is crucial to the success of any application, and SQL

meets those needs. As you have seen, SQL not only returns simple results from individual tables, but can produce complex data queries complete with filtering, sorting, expressions, and even nested statements.

In the latter part of this chapter, we learned how to group T-SQL statements and

save them together as stored procedures. In
Chapter 9
, you’ll begin to use the knowledge you’ve gained about databases, and the language that connects those

databases together, to create a real, working application.

Licensed to [email protected]

ADO.NET

Through the preceding chapters, you’ve made major strides into the world of dynamic web development using ASP.NET. You’ve learned about interface development using web forms and web controls, you’ve learned about modeling and structuring your data within the framework of a database—you’ve even learned

about the SQL language that’s used to access the data stored within your database.

What you haven’t yet learned is how to access that data through your web applications.

The next step is to learn how to access a database using VB or C# code. This, of

course, is the goal we’ve been aiming for from the beginning of our adventures in

ASP.NET. The whole purpose of the data store is to support an application; in our

case, that application is the Dorknozzle Intranet web site, the purpose of which is

to offer users an easy-to-use interface to company data.

ADO.NET (
A
ctiveX
D
ata
O
bjects
.NET
) is a modern Microsoft technology that permits us to access a relational database from an application’s code. With ADO.NET, we’ll

be able to display lists of employees and departments, and allow users to add data

to the data store, directly from the Dorknozzle application.

Licensed to [email protected]

348

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

In this chapter, you’ll learn:

■ how to connect to your database using ADO.NET

■ how to execute SQL queries and retrieve their results using ADO.NET

■ how to display data that is read from a database

■ how to handle data access errors

Introducing ADO.NET

In previous chapters, we learned how to use Visual Web Developer and SQL Management Studio to connect to a database and execute SQL queries. Now, it’s time to apply this knowledge. Within our web application, we’ll use ADO.NET’s classes

to connect to the database; we’ll then use that connection to execute SQL queries.

In order to use ADO.NET, we must first decide which kind of database we’ll use,

and import those namespaces containing classes that work with the database. Since

we’re using SQL Server, you’ll need to import the System.Data.SqlClient

namespace. This contains all the required Sql classes, the most important of which

are:

SqlConnection

This class exposes properties and methods for connecting to an SQL Server

database.

SqlCommand

This class holds data about the SQL queries and stored procedures that you intend to run on your SQL Server database.
SqlDataReader

Data is returned from the database in an SqlDataReader class. This class comes

with properties and methods that let you iterate through the data it contains.

Traditional ASP developers can think of the SqlDataReader as being similar to

a forward-only RecordSet, in which data can only be read forward, one record

at a time, and we can’t move back to the beginning of the data stream.

The System.Data.SqlClient namespace exposes many more than the few classes

listed above. We’ll discuss some of the more advanced classes in the next few

chapters.

Licensed to [email protected]

ADO.NET

349

ADO.NET and Generic Data Access

ADO.NET is able to use different types of data connections, depending on the

kind of database to which the application is trying to connect. The ADO.NET

classes whose names start with Sql (such as the previously mentioned

SqlConnection, SqlCommand, and so on) are specifically built to connect to

SQL Server.

Similar classes are provided for other databases—for example, if you’re working

with Oracle, you can use classes such as OracleConnection, OracleCommand,

and so on. If, on the other hand, you’re working with database systems for which

such classes aren’t specifically designed, you can use generic low-level interfaces;

most databases can be accessed through the OLE DB interface (using classes such

as OleDbConnection and OleDbCommand), or the older ODBC interface (using

classes such as OdbcConnection and OdbcCommand).

In this book, we’ll use only the Sql classes, but it’s good to know that you have

options!

Once you’re ready to begin working with ADO.NET, the task of establishing a link

between the database and your application is a straightforward, six-step process:

1.

Import the necessary namespaces.

2.

Define a connection to your database with an SqlConnection object.

3.

When you’re ready to manipulate your database, set up the appropriate query

in an SqlCommand object.

4.

Open the connection and execute the SQL query to return the results into a

SqlDataReader object.

5.

Extract relevant database data from the SqlDataReader object and display it on

your web page.

6.

Close the database connection.

Let’s walk through this process, discussing each step.

Licensed to [email protected]

350

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

Importing the SqlClient Namespace

It’s been a while since we’ve written some VB or C# code! Let’s fire up our old

friend, Visual Web Developer, and load the LearningASP project. We’ll use this

application to create a few simple scripts; then we’ll move to Dorknozzle, adding

more functionality to the project site.

Open the LearningASP project and go to
File
>
New File…
to create a new file. Select the
Web Form
template, and name it
AccessingData.aspx
. Uncheck the
Place code in
separate file
and
Select master page
checkboxes, as shown in Figure 9.1
. Figure 9.1. Creating the
AccessingData.aspx
web form

Once the form is created, we can import the SqlClient namespace:

LearningASP\VB\AccessingData_01.aspx
(excerpt)

<%@ Page Language="VB" %>

<%@ Import Namespace = "System.Data.SqlClient" %>

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

⋮

Licensed to [email protected]