Using SQL Server Express with ASP.NET
Microsoft SQL Server 2005 Express Edition provides a simple database solution for building applications. SQL Server Express Edition supports the complete SQL Server 2005 programming model including Transact-SQL, stored procedures, views, triggers, SQL Server CLR Integration (SQLCLR), and the XML data type. When you develop an application using SQL Server Express Edition as the data source, the application will be compatible with production servers running SQL Server 2005.
Connecting to a SQL Server Express Edition Database
You can connect to a SQL Server Express Edition database just like you would connect to any SQL Server database by specifying the database server as the local SQL Server Express Edition data source. For example, the following connection string connects to a database named Customers.
Data Source=.\SQLEXPRESS;Initial Catalog=Customers;Integrated Security=True;
You can also specify a database file to attach to by using the AttachDBFilename connection-string attribute in place of the InitialCatalog or Database connection-string attributes. Connecting to the database by using a file name simplifies deploying your database with your application (provided the target server is running SQL Server Express Edition). For example, the following connection string connects to a database that is stored in the Customers.mdf file.
Data Source=.\SQLEXPRESS;AttachDbFileName=e:\data\Customers.mdf;Integrated Security=True;User Instance=True
ASP.NET provides a convenient option for storing data in the App_Data directory of a Web application. Contents of the App_Data directory are not served in response to Web requests, which improves the security of the data for your application. As an added convenience, you can supply the |DataDirectory| connection string variable in place of the file path to the App_Data directory for your application. ASP.NET features — such as the SqlDataSource control or the providers for membership, roles, user profiles, Web Parts personalization, and so on — will automatically substitute the file path to the App_Data directory for the |DataDirectory| connection-string variable when opening a connection to the database. This ensures that the path to your database remains current if your Web application is moved to a different directory. The following code example shows a connection string that includes the |DataDirectory| connection-string variable.
Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|Customers.mdf;Integrated Security=True;User Instance=True
Note
SQL Server Express Edition allows only a single connection to an .mdf file when you connect with a connection string that has User Instance set to true.
You can close the connection held by Visual Web Developer by right-clicking the database in Solution Explorer and selecting the Detach option, or by right-clicking the database in Server Explorer and selecting Close Connection.
Additionally, if you need to release any open connections to a SQL Server Express Edition database, you can unload your Web application by using Internet Information Services Manager (IIS Manager). You can also unload a Web application by adding an HTML file named App_offline.htm to the root directory of your Web application. To allow your Web application to start responding to Web requests again, simply remove the App_offline.htm file. You will need to release open connections to a SQL Server Express Edition database when you want to copy or move the database to a new location.
Setting up a SQL Server Express Edition Database
Visual Web Developer provides tools to help you create a SQL Server Express Edition database; manage database elements, such as tables, stored procedures, and so on; and manage connections to the database. You can access these capabilities through the Server Explorer window. For instructions on creating a SQL Server Express Edition database, see How to: Create SQL Server Express Edition Databases.
You can also create a SQL Server Express Edition database by connecting to a computer running SQL Server Express Edition and issuing a CREATE DATABASE command, or by using the SQL Server management tools provided for SQL Server Express Edition.
The default provider for ASP.NET features that store data in a SQL Server database — such as membership, roles, the user profiles, Web Parts personalization, and so on — is configured to connect to the Aspnetdb.mdf SQL Server Express Edition database in the App_Data directory for your application. If you enable any of these data-storage features using the default provider and the Aspnetdb.mdf SQL Server Express Edition database does not exist in the App_Data directory for your application, the database will be automatically created. The App_Data directory for your application will also be created if it does not exist.
User Instances
SQL Server Express Edition supports user instances, which means that a new process will be started for each user that connects to a SQL Server Express Edition database. The identity of the process will be the user that opened the connection. For information on determining the identity of an ASP.NET application, see ASP.NET Impersonation.
When you connect to a local database project, Visual Web Developer connects to the SQL Server Express Edition database with user instances enabled, by default. For example, the following code example shows a typical connection string used by Visual Web Developer to connect to a SQL Server Express Edition database.
Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True
Although enabling user instances is suitable for desktop development, starting worker processes is not appropriate on Web servers hosting sites for multiple customers where applications must be separated and secured. ASP.NET applications that run with the same process identity can connect to the same user instance. Because all ASP.NET applications run with the same process identity on Windows 2000 and Windows XP Professional (by default, the local ASPNET account) and ASP.NET applications in the same application pool run with the same process identity on Windows Server 2003 (by default, the NETWORK SERVICE account), shared hosting servers that contain applications that do no trust each other should explicitly disable user instances. This functionality can be turned off by connecting to the SQL Server Express Edition instance (for example, by issuing the following command at a command prompt: osql –E –S .\SQLEXPRESS) and issuing the following Transact-SQL command.
EXEC sp_configure 'show advanced option', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'user instances enabled', 0
GO
RECONFIGURE WITH OVERRIDE
GO
Deploying a SQL Server Express Edition Database
A SQL Express Edition database is made up of two files: the .mdf file, which contains the database schema and data; and the .ldf file, which contains log information for the database. When you deploy a Web site by using the Copy Web Site tool, the SQL Server Express Edition database files are copied as well. Your application will continue to run as long as SQL Server Express Edition is installed on the target server. For more information, see How to: Copy Web Site Files with the Copy Web Site Tool.
Here are some other options for deploying a SQL Server Express Edition database.
If you are making a file-based connection to your SQL Server Express Edition database, these files can be copied with your application to a target server (that has SQL Server Express Edition installed) using XCopy, FTP, or another means.
Because SQL Server Express Edition uses the same file format as other versions of SQL Server 2005, you can copy the .mdf and .ldf files to a server running SQL Server and then attach the files as a database.
If you want to copy an empty SQL Server Express Edition database that contains database schema, but no data, the SQL Server management tools enable you to generate scripts that can be run in your target database to duplicate the schema from your development database.
Note
If you are deploying your SQL Server Express Edition database to a Web server that hosts multiple sites that do not trust each other, then you cannot use file-based connections or user instances to help ensure that your data is not exposed to other applications on the server. In this case, it is recommended that you migrate the contents your SQL Server Express Edition database to another version of SQL Server 2005 that your deployed ASP.NET application can access.
If your SQL Server Express Edition database contains encrypted information, such as encrypted passwords stored in a membership database, make sure that your encryption keys are copied to the target server as well.
If you want to move the entire SQL Server Express Edition database, you need to ensure that there are no open connections to the database that would cause it to be locked.
Unlocking a Locked Database
If there is an open connection to a database, the database is locked and cannot be moved or deleted. Open connections can be held by an ASP.NET application, Visual Studio, or some other program or database client. To unlock a database, all open connections to the database must be closed. You can close the open connections in the following ways:
You can close the connection held by Visual Web Developer by right-clicking the database in Solution Explorer and selecting the Detach option, or by right-clicking the database in Server Explorer and selecting Close Connection.
You can close any connections held by an ASP.NET application by ending the application. This can be done by using IIS Manager, or by placing a file named App_offline.htm in the root directory of the ASP.NET application (you must remove this file to restart the application).
You can close any connections held by other sources, such as a Windows Forms application, by exiting the program.
See Also
Concepts
ASP.NET Profile Properties Overview
Other Resources
Managing Users by Using Membership
Managing Authorization Using Roles
Change History
Date |
History |
Reason |
|---|---|---|
January 2011 |
Removed note about automatically closing connections. |
Content bug fix. |