Databases and Connections

Most Web sites will have some form of database behind them, whether to store a product catalog or just to manage user logins. This often requires a full installation of SQL Server on your development machine, but with ASP.NET 2.0 there is another optionSQL Server 2005 Express Edition (SSE). SSE is a cut-down version of SQL Server 2005, but has some interesting characteristics that make it especially suitable for the development environment. Two of these characteristics are automatic database attachment and user instancing.

Auto-Attached Databases

One problem with developing applications that use databases is the need to have SQL Server installed, where you need to attach a database, create a login to SQL Server, and add the login to the database. This would need to be done on each development machine unless the user is logged on as an Administrator, which is often the case but is not recommended.

SQL Server 2005 (both full and Express editions) supports automatic attachment of databases by adding the following into the connection string:

AttachDbFilename=db.mdf

With this in the connection string, SQL Server 2005 will automatically attach to the database when the application starts. You can also avoid explicit hard-coded paths to the database by using the new App_Data directory and a special feature to point to it. For example, consider the following connection string:

Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|db.mdf

ASP.NET will replace the special string |DataDirectory| with the path to the App_Data directory, thus avoiding the need for a hard-coded path.

Even though databases can be dynamically attached, the problem of permissions still exists. How can a database be automatically attached if the user doesn't have permissions to attach databases? User Instancing is the solution to this problem, and it eliminates users having to be an Administrator.

User Instancing

To understand user instancing, you need to know how SQL Server instances work. By default, SQL Express will install with an instance name of SQLEXPRESS, which is why .\SQLEXPRESS is used in connections strings, because you are explicitly connecting to an instance. The default service account that SQL Express runs under is "NT AUTHORITY\NETWORK SERVICE"this is the account that the instance will run under when the process starts. As it stands, this would still require explicit login and user creation.

User instancing solves this problem by creating an instance on demand, and instead of using the default service account, a user instance uses the user account as the service account. Thus, the process runs under the credentials of the user, so no explicit logins are required; because the process is running as the user, auto-attachment requires no additional permissionsthe owner of the process automatically has full administrative rights to the database, even if the user is not an Administrator on the machine. All of this happens on demand, when the application starts, so there is no configuration required. The SQL Server process running the user instance stays active for 60 minutes after the last connection, although this can be configured with the sp_configure option "user instance timeout."

You can achieve user instancing by including the following in your connection string:

User Instance=True

User instancing only works with integrated security, so the full connection string will look like this:

Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|db.mdf; User Instance=True; Integrated Security=True;

User instancing is designed for the development scenario, to save configuration. As such there are limitations.

• Only local connections are allowed.

• Replication does not work with other user instances.

• Distributed queries do not work to remote databases.

• User instancing only works in SQL Server Express Edition.

The following common problems also arise because of user instancing:

• When using the Visual Studio 2005 or Visual Web Developer IDEs to connect to SQL Express, the SQL Express instance will run under the "NT AUTHORITY\NETWORK SERVICE" account, so this account requires write permissions on the MDF and LDF database files.

• ASP.NET pages run under the ASPNET user account, which means that an ASP.NET page that connects to a database with user instancing will result in the SQL Express process being owned by the ASPNET user. This means that the ASPNET user also requires write permissions on the database files.

• User instancing opens databases with exclusive access, to reduce the potential for data corruption due to multiple user instances with the same database name. Therefore, it is not possible to open the database in the IDE while it is still being used by the application. This is alleviated to a degree by the IDE, because running an application (via F5) will automatically close and detach the instance from the tool so that ASP.NET can open it. If you are running the application outside of the IDE, such as from a virtual root in IIS, then this will not be the case, and you may have to close the connection manually (use the Close Connection on the context menu).

• When connecting to a user instance database, you are connecting as an administrator. When deploying applications, you should connect as a lower-privileged account, so you will need to configure permissions on the database.

Connection String Placement

Connection strings are typically stored in web.config, and in version 1.x that usually meant the appSettings section. In ASP.NET 2.0, there is a new connectionStrings section, which provides a similar key/value pairing.

For example:

<connectionStrings>   <add name="AW"     connectionString="Data Source=.\SQLEXPRESS; . . ."     providerName="System.Data.SqlClient" /> </connectionStrings>

Although the providerName attribute isn't compulsory, connection strings won't appear in the Configure Data Source dialogs without a provider name being set.

Within applications, you can access these connection strings in two ways. In code, you use the ConnectionStrings property of the ConfigurationManager object. For example:

SqlConnection conn = new SqlConnection(); conn.ConnectionString =      ConfigurationManager.ConnectionStrings["AW"].ConnectionString;

The ConnectionStrings property contains a collection of the connection strings from the section in web.config, so you use the name property as the index to the collection. The ConnectionString property then returns the actual connection string.

Within the markup of ASP.NET pages, you use an expression builder, which is a new feature of ASP.NET 2.0. Expression builders allow you to declaratively access features such as connection strings, application settings, and resources. For example, consider the following code:

<asp:SqlDataSource  runat="server"   ConnectionString="<%$ ConnectionStrings:AW %> "

The expression builder uses a server side <% %> block, but when the first character within that block is a $ signthis indicates an expression builder is to be used. Each expression builder has a known prefix, and for connection strings this is ConnectionStrings. In a similar method to code, you use the name attribute from the web.config section to identify the required connection string, using a : to separate the builder prefix from the name.

The beauty of these two methods is that from both code and markup you can use the centrally stored connection strings.

Visual Web Developer and Visual Studio 2005 Database Explorer

Both Visual Studio 2005 and Visual Web Developer have database management features, and although both are reached through different places, they act the same. In Visual Studio 2005, you use the Server Explorer, which by default is on the left of the screen, as shown in Figure 3.1. In Visual Web Developer, the Database Explorer is used, which by default is on the right of the screen, as shown in Figure 3.2. As you can see, their contents are the same, allowing access to the contents of the database from within the development tool.

Figure 3.1. The Visual Studio 2005 Server Explorer

Figure 3.2. The Visual Web Developer Database Explorer

Whichever tool you use, working with databases is simple. You can create and modify tables, stored procedures, views, and functions, and you can run ad-hoc queries. Throughout the book we'll refer to the Database Explorer because it's more explicit; if you are using Visual Studio 2005, then use the Server Explorer to access the same functionality.