Using the SqlDataSource Control

Using the SqlDataSource Control

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.



No comments:

Post a Comment