4. The Data Provider Model
ADO.NET relies on the
functionality in a small set of core classes. You can divide these
classes into two groups: those that are used to contain and manage data
(such as DataSet, DataTable, DataRow, and DataRelation) and those that
are used to connect to a specific data source (such as Connection,
Command, and DataReader).
The data container classes
are completely generic. No matter what data source you use, once you
extract the data, it's stored using the same data container: the
specialized DataSet class. Think of the DataSet as playing the same role
as a collection or an array—it's a package for data. The difference is
that the DataSet is customized for relational data, which means it
understands concepts such as rows, columns, and table relationships
natively.
The second group of classes exists in several different flavors. Each set of data interaction classes is called an ADO.NET data provider.
Data providers are customized so that each one uses the best-performing
way of interacting with its data source. For example, the SQL Server
data provider is designed to work with SQL Server. Internally, it uses
SQL Server's TDS (tabular data stream) protocol for communicating, thus
guaranteeing the best possible performance. If you're using Oracle, you
can use an Oracle data provider instead.
Each provider designates its
own prefix for naming classes. Thus, the SQL Server provider includes
SqlConnection and SqlCommand classes, and the Oracle provider includes
OracleConnection and OracleCommand classes. Internally, these classes
work quite differently, because they need to connect to different
databases using different low-level protocols. Externally, however,
these classes look quite similar and provide an identical set of basic
methods because they implement the same common interfaces. This means
your application is shielded from the complexity of different standards
and can use the SQL Server provider in the same way the Oracle provider
uses it. In fact, you can often translate a block of code for
interacting with a SQL Server database into a block of Oracle-specific
code just by editing the class names in your code.
The classes you'll use fall into
three key namespaces, as outlined in Table 2.
Table 2. ADO.NET Namespaces for SQL Server Data Access
| Namespace | Purpose |
|---|
| System.Data.SqlClient | Contains
the classes you use to connect to a Microsoft SQL Server database and
execute commands (like SqlConnection and SqlCommand). |
| System.Data.SqlTypes | Contains
structures for SQL Server–specific data types such as SqlMoney and
SqlDateTime. You can use these types to work with SQL Server data types
without needing to convert them into the standard .NET equivalents (such
as System.Decimal and System.DateTime). These types aren't required,
but they do allow you to avoid any potential rounding or conversion
problems that could adversely affect data. |
| System.Data | Contains
fundamental classes with the core ADO.NET functionality. This includes
DataSet and DataRelation, which allow you to manipulate structured
relational data. These classes are totally independent of any specific
type of database or the way you connect to it. |
First, you'll consider the most
straightforward approach—direct data access. Then, you'll consider
disconnected data access, which allows you to retrieve data in the
DataSet and cache it for longer periods of time. Both approaches
complement each other, and in many web applications you'll use a
combination of the two.
5. Direct Data Access
The
most straightforward way to interact with a database is to use direct
data access. When you use direct data access, you're in charge of
building a SQL command and executing it. You use commands to query, insert, update,
and delete information.
When you query data with
direct data access, you don't keep a copy of the information in memory.
Instead, you work with it for a brief period of time while the database
connection is open, and then close the connection as soon as possible.
This is different than disconnected data access, where you keep a copy
of the data in the DataSet object so you can work with it after the
database connection has been closed.
The direct data model is well
suited to ASP.NET web pages, which don't need to keep a copy of their
data in memory for long periods of time. Remember, an ASP.NET web page
is loaded when the page is requested and shut down as soon as the
response is returned to the user. That means a page typically has a
lifetime of only a few seconds (if that).
NOTE
Although ASP.NET web pages
don't need to store data in memory for ordinary data management tasks,
they just might use this technique to optimize performance. For example,
you could get the product catalog from a database once, and keep that
data in memory on the web server so you can reuse it when someone else
requests the same page.
To query information with simple data access, follow these steps:
Create Connection, Command, and DataReader objects.
Use the DataReader to retrieve information from the database, and display it in a control on a web form.
Send
the page to the user. At this point, the information your user sees and
the information in the database no longer have any connection, and all
the ADO.NET objects have been destroyed.
To add or update information, follow these steps:
Create new Connection and Command objects.
Execute the Command (with the appropriate SQL statement).
Figure 8 shows a high-level look at how the ADO.NET objects interact to make direct data access work.
Before continuing, make sure
you import the ADO.NET namespaces. We assume you're
using the SQL Server provider, in which case you need these two
namespace imports:
Imports System.Data
Imports System.Data.SqlClient
5.1. Creating a Connection
Before you can retrieve or
update data, you need to make a connection to the data source.
Generally, connections are limited to some fixed number, and if you
exceed that number (either because you run out of licenses or because
your database server can't accommodate the user load), attempts to
create new connections will fail. For that reason, you should try to
hold a connection open for as short a time as possible. You should also
write your database code inside a Try/Catch error handling structure so
you can respond if an error does occur, and make sure you close the
connection even if you can't perform all your work.
When creating a Connection
object, you need to specify a value for its ConnectionString property.
This ConnectionString defines all the information the computer needs to
find the data source, log in, and choose an initial database. The ConnectionString is
the one value you might have to tweak before it works for the database
you want to use. Luckily, it's quite straightforward. Here's an example
that uses a connection string to connect to SQL Server:
Dim myConnection As New SqlConnection()
myConnection.ConnectionString = "Data Source=localhost;" & _
"Initial Catalog=Pubs;Integrated Security=SSPI"
If you're using SQL Server Express, your connection string will include an instance name, as shown here:
Dim myConnection As New SqlConnection()
myConnection.ConnectionString = "Data Source=localhost\SQLEXPRESS;" & _
"Initial Catalog=Pubs;Integrated Security=SSPI"
5.1.1. The Connection String
The connection string is
actually a series of distinct pieces of information separated by
semicolons (;). Each separate piece of information is known as a
connection string property.
The following list
describes some of the most commonly used connection string properties,
including the three properties used in the preceding example:
Data source:
This indicates the
name of the server where the data source is located. If the server is on
the same computer that hosts the ASP.NET site, localhost is sufficient.
The only exception is if you're using a named instance of SQL Server.
For example, if you've installed SQL Server Express, you'll need to use
the data source localhost\SQLEXPRESS, because the instance name is
SQLEXPRESS. You'll also see this written with a period, as .\SQLEXPRESS,
which is equivalent.
Initial catalog:
This is the name of the
database that this connection will be accessing. It's only the "initial"
database because you can change it later by using the
Connection.ChangeDatabase() method.
Integrated security:
This indicates you want
to connect to SQL Server using the Windows user account that's running
the web page code, provided you supply a value of SSPI (which stands for
Security Support Provider Interface). Alternatively, you can supply a
user ID and password that's defined in the database for SQL Server
authentication, although this method is less secure and generally
discouraged.
ConnectionTimeout:
This determines how long
your code will wait, in seconds, before generating an error if it
cannot establish a database connection. Our example connection string
doesn't set the ConnectionTimeout, so the default of 15 seconds is used.
You can use 0 to specify no limit, but this is a bad idea. This means
that, theoretically, the code could be held up indefinitely while it
attempts to contact the server.
You can set some other,
lesser-used options for a connection string. For more information, refer
to the Visual Studio Help. Look under the appropriate Connection class
(such as SqlConnection or OleDbConnection) because there are subtle
differences in connection string properties for each type of Connection
class.
5.1.2. Windows Authentication
The previous example uses integrated Windows authentication, which is the default security standard for new SQL Server installations. You can also use SQL Server authentication.
In this case, you will explicitly place the user ID and password
information in the connection string. However, SQL Server authentication
is disabled by default in SQL Server 2000 and later versions, because
it's not considered to be as secure.
Here's the lowdown on both types of authentication:
With SQL Server
authentication, SQL Server maintains its own user account information in
the database. It uses this information to determine whether you are
allowed to access specific parts of a database.
With
integrated Windows authentication, SQL Server automatically uses the
Windows account information for the currently logged-in process. In the
database, it stores information about what database privileges each user
should have.
|
You can set what type of
authentication your SQL Server uses using a tool such as SQL Server
Management Studio. Just right-click your server in the tree, and select
Properties. Choose the Security tab to change the type of
authentication. You can choose either Windows Only (for the tightest
security) or SQL Server and Windows, which allows both Windows
authentication and SQL Server authentication. This option is also known
as mixed-mode authentication.
|
|
For Windows
authentication to work, the currently logged-on Windows user must have
the required authorization to access the SQL database. This isn't a
problem while you test your websites, because Visual Studio launches
your web applications using your user account. However, when you deploy
your application to a web server running IIS, you might run into
trouble. In this situation, all ASP.NET code is run by a more limited
user account that might not have the rights to access the database.
Although the exact user depends on your version of IIS , the best approach is usually to grant access to the IIS_IUSRS group.
5.1.3. User Instance Connections
Every database server
stores a master list of all the databases that you've installed on it.
This list includes the name of each database and the location of the
files that hold the data. When you create a database (for example, by
running a script or using a management tool), the information about that
database is added to the master list. When you connect to the database,
you specify the database name using the Initial Catalog value in the
connection string.
NOTE
If you haven't made any
changes to your database configuration, SQL Server will quietly tuck
the files for newly created databases into a directory like c:\Program
Files\Microsoft SQL Server\MSSQL.10.MSSQLSERVER\MSSQL\Data (although the
exact path depends on the version of SQL Server you're using). Each
database has at least two files—an .mdf file with the actual data and an
.ldf file that stores the database log. Of course, database
professionals have a variety of techniques and tricks for managing
database storage, and can easily store databases in different locations,
create multiple data files, and so on. The important detail to realize
is that ordinarily your database files are stored by your database
server, and they aren't a part of your web application directory.
Interestingly, SQL Server Express
has a feature that lets you bypass the master list and connect directly
to any database file, even if it's not in the master list of databases.
This feature is called user instances. Oddly enough, this feature isn't available in the full edition of SQL Server.
To use this feature, you need
to set the User Instances value to True (in the connection string) and
supply the file name of the database you want to connect to with the
AttachDBFilename value. You don't supply an Initial Catalog value.
Here's an example connection string that uses this approach:
myConnection.ConnectionString = "Data Source=localhost\SQLEXPRESS;" & _
"User Instance=True;AttachDBFilename=|DataDirectory|\Northwind.mdf;" & _
"Integrated Security=True"
There's another
trick here. The file name starts with |DataDirectory|. This
automatically points to the App_Data folder inside your web application
directory. This way, you don't need to supply a full file path, which
might not remain valid when you move the web application to a web
server. Instead, ADO.NET will always look in the App_Data directory for a
file named Northwind.mdf.
User instances is a handy
feature if you have a web server that hosts many different web
applications that use databases and these databases are frequently being
added and removed. However, because the database isn't in the master
list, you won't see it in any administrative tools (although most
administrative tools will still let you connect to it manually, by
pointing out the right file location). But remember, this quirky but
interesting feature is available in SQL Server Express only—you won't
find it in the full version of SQL Server.
5.1.4. Storing the Connection String
Typically, all the database
code in your application will use the same connection string. For that
reason, it usually makes the most sense to store a connection string in a
class member variable or, even better, a configuration file.
You can also create a Connection object and supply the connection string in one step by using a dedicated constructor:
Dim myConnection As New SqlConnection(connectionString)
' myConnection.ConnectionString is now set to connectionString.
You don't need to hard-code a
connection string. The <connectionStrings> section of the
web.config file is a handy place to store your connection strings.
Here's an example:
<configuration>
<connectionStrings>
<add name="Pubs" connectionString=
"Data Source=localhost;Initial Catalog=Pubs;Integrated Security=SSPI"/>
</connectionStrings>
...
</configuration>
You can then retrieve your
connection string by name. First, import the System.Web.Configuration
namespace. Then, you can use code like this:
Dim connectionString As String = _
WebConfigurationManager.ConnectionStrings("Pubs").ConnectionString
This approach helps to ensure
all your web pages are using the same connection string. It also makes
it easy for you to change the connection string for an application,
without needing to edit the code in multiple pages.
5.1.5. Making the Connection
Once you've created your connection (as described in the previous section), you're ready to use it.
Before you can perform any database operations, you need to explicitly open your connection:
myConnection.Open()
To verify that you have
successfully connected to the database, you can try displaying some
basic connection information. The following example writes some basic
information to a Label control named lblInfo (see Figure 9).
Here's the code with basic error handling:
' Define the ADO.NET Connection object.
Dim connectionString As String = _
WebConfigurationManager.ConnectionStrings("Pubs").ConnectionString
Dim myConnection As New SqlConnection(connectionString)
Try
' Try to open the connection.
myConnection.Open()
lblInfo.Text = "<b>Server Version:</b> " & myConnection.ServerVersion
lblInfo.Text &= "<br /><b>Connection Is:</b> " & _
myConnection.State.ToString()
Catch err As Exception
' Handle an error by displaying the information.
lblInfo.Text = "Error reading the database."
lblInfo.Text &= err.Message
Finally
' Either way, make sure the connection is properly closed.
' (Even if the connection wasn't opened successfully,
' calling Close() won't cause an error.)
myConnection.Close()
lblInfo.Text &= "<br /><b>Now Connection Is:</b> "
lblInfo.Text &= myConnection.State.ToString()
End Try
Once you use the Open()
method, you have a live connection to your database. One of the most
fundamental principles of data access code is that you should reduce the
amount of time you hold a connection open as much as possible. Imagine
that as soon as you open the connection, you have a live, ticking time
bomb. You need to get in, retrieve your data, and throw the connection
away as quickly as possible in order to ensure your site runs
efficiently.
Closing a connection is just as easy, as shown here:
myConnection.Close()
Another approach is to use the
Using statement. The Using statement declares that you are using a
disposable object for a short period of time. As soon as you finish
using that object and the Using block ends, the common language runtime
will release it immediately by calling the Dispose() method. Here's the
basic structure of the Using block:
Using object
...
End Using
It just so happens that calling the Dispose() method of a connection object is equivalent
to calling Close()
and then discarding the connection object from memory. That means you
can shorten your database code with the help of a Using block. The best
part is that you don't need to write a Finally block—the Using statement
releases the object you're using even if you exit the block as the
result of an unhandled exception.
Here's how you could rewrite the earlier example with a Using block:
' Define the ADO.NET Connection object.
Dim connectionString As String = _
WebConfigurationManager.ConnectionStrings("Pubs").ConnectionString
Dim myConnection As New SqlConnection(connectionString)
Try
Using myConnection
' Try to open the connection.
myConnection.Open()
lblInfo.Text = "<b>Server Version:</b> " & myConnection.ServerVersion
lblInfo.Text &= "<br /><b>Connection Is:</b> " & _
myConnection.State.ToString()
End Using
Catch err As Exception
' Handle an error by displaying the information.
lblInfo.Text = "Error reading the database."
lblInfo.Text &= err.Message
End Try
lblInfo.Text &= "<br /><b>Now Connection Is:</b> "
lblInfo.Text &= myConnection.State.ToString()
There's one difference in the
way this code is implemented as compared to the previous example. The
error handling code wraps the Using block. As a result, if an error
occurs the database connection is closed first, and then
the exception handling code is triggered. In the first example, the
error handling code responded first, and then the Finally finally block
closed the connection afterward. Obviously, this rewrite is a bit
better, as it's always good to close database connections as soon as
possible.
5.2. The Select Command
The Connection object
provides a few basic properties that supply information about the
connection, but that's about all. To actually retrieve data, you need a
few more ingredients:
A SQL statement that selects the information you want
A Command object that executes the SQL statement
A DataReader or DataSet object to access the retrieved records
Command objects represent SQL
statements. To use a Command, you define it, specify the SQL statement
you want to use, specify an available connection, and execute the
command. To ensure good database performance, you should open your
connection just before you execute your command and close it as soon as
the command is finished.
You can use one of the earlier SQL statements, as shown here:
Dim myCommand As New SqlCommand()
myCommand.Connection = myConnection
myCommand.CommandText = "SELECT * FROM Authors ORDER BY au_lname"
Or you can use the constructor as a shortcut:
Dim myCommand As New SqlCommand(_
"SELECT * FROM Authors ORDER BY au_lname ", myConnection)
NOTE
It's
also a good idea to dispose of the Command object when you're finished,
although it isn't as critical as closing the Connection object.
