July 12, 2011, 9:42 a.m.
posted by pumi
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.
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:
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.
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 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;
The following common problems also arise because of user instancing:
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.
<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 id="SqlDataSource1" 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. In Visual Web Developer, the Database Explorer is used, which by default is on the right of the screen, as shown in Figure. As you can see, their contents are the same, allowing access to the contents of the database from within the development tool.
1. The Visual Studio 2005 Server Explorer
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.