The
SqlDataSource control enables you to quickly and easily represent a SQL
database in a web page. In many cases, you can take advantage of the
SqlDataSource control to write a database-driven web page without
writing a single line of code. You use the SqlDataSource control to
represent a connection and set of commands that can be executed against a
SQL database. You can use the SqlDataSource control when working with
Microsoft SQL Server, Microsoft SQL Server Express, Microsoft Access,
Oracle, DB2, MySQL, or just about any other SQL relational database ever
created by man.
Although
you can use the SqlDataSource control when working with Microsoft
Access, the ASP.NET Framework does include the AccessDataSource control,
which was designed specifically for Microsoft Access.
The
SqlDataSource control is built on top of ADO.NET. Under the covers, the
SqlDataSource uses ADO.NET objects such as the DataSet, DataReader, and
Command objects. Because the SqlDataSource control is a control, it
enables you to use these ADO.NET objects declaratively rather than
programmatically.
Creating Database Connections
You can use the SqlDataSource control to connect to just about any SQL relational database
server.
In this section, you learn how to connect to Microsoft SQL Server and
other databases such as Oracle. You also learn how you can store the
database connection string used by the SqlDataSource securely in your
web configuration files.
Connecting to Microsoft SQL Server
By
default, the SqlDataSource control is configured to connect to
Microsoft SQL Server version 7.0 or higher. The default provider used by
the SqlDataSource control is the ADO.NET provider for Microsoft SQL
Server.
The
.NET Framework includes a utility class, named the SqlConnectionBuilder
class, that you can use when working with SQL connection strings. This
class automatically converts any connection string into a canonical
representation. It also exposes properties for extracting and modifying
individual connection string parameters such as the Password parameters.
For
security reasons, you should never include a connection string that
contains security credentials in an ASP.NET page. Theoretically, no one
should able to see the source of an ASP.NET page. However, Microsoft
does not have a perfect track record. Later in this section, you learn
how to store connection strings in the web configuration file (and
encrypt them).
Connecting to Other Databases
If
you need to connect to any database server other than Microsoft SQL
Server, then you need to modify the SqlDataSource control’s ProviderName
property.
The .NET Framework includes the following providers:
- System.Data.OracleClient—Use the ADO.NET provider for Oracle when connecting to an Oracle database.
- System.Data.OleDb—Use the OLE DB provider when connecting to a data source that supports an OLE DB provider.
- System.Data.Odbc—Use the ODBC provider when connecting to a data source with an ODBC driver.
Storing Connection Strings in the Web Configuration File
Storing
connection strings in your pages is a bad idea for three reasons.
First, it is not a good practice from the perspective of security. In
theory, no one should ever be able to view the source code of your
ASP.NET pages. In practice, however, hackers have discovered security
flaws in the ASP.NET framework. To sleep better at night, you should
store your connection strings in a separate file.
Also,
adding a connection string to every page makes it difficult to manage a
website. If you ever need to change your password, then you need to
change every page that contains it. If, on the other hand, you store the
connection string in one file, you can update the password by modifying
the single file.
Finally,
storing a connection string in a page can, potentially, hurt the
performance of your application. The ADO.NET provider for SQL Server
automatically uses connection pooling to improve your application’s data
access performance. Instead of being destroyed when they are closed,
the connections are kept alive so that they can be put back into service
quickly when the need arises. However, only connections that are
created with the same connection strings are pooled together (an exact
character-by-character match is made). Adding the same connection string
to multiple pages is a recipe for defeating the benefits of connection
pooling.
For
these reasons, you should always place your connection strings in the
web configuration file. The Web.Config file in picture includes a
connectionStrings section.
The
expression <%$ ConnectionStrings:Movies %> is used to represent
the connection string. This expression is not case sensitive. Rather
than add a connection string to your project’s web configuration file,
you can add the connection string to a web configuration file higher in
the folder hierarchy. For example, you can add the connection string to
the root Web.Config file and make it available to all applications
running on your server. The root Web.Config file is located at the
following path:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG
Encrypting Connection Strings
You can encrypt the <connectionStrings> section of a web configuration file. For example, figure contains an encrypted version of the Web.Config file that was created for the previous figure.
Notice
that the contents of the <connectionStrings> section are no
longer visible. However, an ASP.NET page can continue to read the value
of the Movie database connection string by using the <%$
ConnectionStrings:Movie %> expression.
The
easiest way to encrypt the <connectionStrings> section is to use
the aspnet_regiis command-line tool. This tool is located in the
following folder:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 \
Executing
the following command encrypts the <connectionStrings> section of
a Web.Config file located in a folder with the path
c:\Websites\MyWebsite:
aspnet_regiis -pef connectionStrings “c:\Websites\MyWebsite”
The -pef option (Protect Encrypt Filepath) encrypts a particular configuration section located at a particular path.
You can decrypt a section with the -pdf option like this:
aspnet_regiis -pdf connectionStrings “c:\Websites\MyWebsite”
Executing Database Commands
In
this section, you learn how to represent and execute SQL commands with
the SqlDataSource control. In particular, you learn how to execute both
inline SQL statements and external stored procedures. You also learn how
to capture and gracefully handle errors that result from executing SQL
commands.
Executing Inline SQL Statements
The
SqlDataSource control can be used to represent four different types of
SQL commands. The control supports the following four properties:
- SelectCommand
- InsertCommand
- UpdateCommand
- DeleteCommand
Using ASP.NET Parameters with the SqlDataSource Control
You can use any of the following ASP.NET Parameter objects with the SqlDataSource control:
- Parameter—Represents an arbitrary static value.
- ControlParameter—Represents the value of a control or page property.
- CookieParameter—Represents the value of a browser cookie.
- FormParameter—Represents the value of an HTML form field.
- ProfileParameter—Represents the value of a Profile property.
- QueryStringParameter—Represents the value of a query string field.
- SessionParameter—Represents the value of an item stored in Session state.
The SqlDataSource control includes five collections of ASP.NET parameters:
SelectParameters, InsertParameters, DeleteParameters, UpdateParameters, and
FilterParameters.
You can use these parameter collections to associate a particular
ASP.NET parameter with a particular SqlDataSource command or filter.
Using the ASP.NET Parameter Object
The ASP.NET parameter object has the following properties:
- ConvertEmptyStringToNull—When true, if a parameter represents an empty string then the empty string is converted to the value Nothing (null) before the associated command is executed.
- DefaultValue—When a parameter has the value Nothing (null), the DefaultValue is used for the value of the parameter.
- Direction—Indicates the direction of the parameter. Possible values are Input, InputOutput, Output, and ReturnValue.
- Name—Indicates the name of the parameter. Do not use the @ character when indicating the name of an ASP.NET parameter.
- Size—Indicates the data size of the parameter.
- Type—Indicates the .NET Framework type of the parameter. You can assign any value from the TypeCode enumeration to this property.
You
can use the ASP.NET parameter object to indicate several parameter
properties explicitly, such as a parameter’s type, size, and default
value.
