Combined with ASP. NET and SQL Server express Edition

Transfer http://msdn2.microsoft.com/zh-cn/library/ms228037.aspx

Microsoft SQL Server 2005 express edition generates applicationsProgramProvides a simple database solution. SQL Server express edition supports the complete programming model of SQL Server 2005, including Transact-SQL, stored procedures, views, triggers, SQL Server CLR integration (sqlclr) and XML data types. When you develop an application that uses SQL Server express as the data source, you can ensure that the application is compatible with the production server running SQL Server 2005.

Connect to the SQL Server express Database

By specifying the database server as a local SQL Server express data source, you can connect to the SQL Server express database, just as you connect to any SQL Server database. For example, the following connection string is connected toMERs.

 
Data Source =. \ sqlexpress; initial catalog = MERs MERS; Integrated Security = true;
 

You can use the attachdbfilename connection string attribute to replace the initialcatalog or database connection string attribute. You can also specify the database file to be appended. You can use the file name to connect to the database to simplify the deployment of the database and applications (if the target server runs SQL Server express ). For example, the following connection string is stored inCustomers. MDFFile.

Data Source =. \ sqlexpress; attachdbfilename = E: \ data \ customers. MDF; Integrated Security = true; user instance = true
 

ASP. NET provides convenient options for storing data in the app_data directory of Web applications. The content in the app_data directory is not provided in response to Web requests, which improves the data security of applications. As an additional convenience measure| Datadirectory |The connection string variable replaces the file path in the app_data directory of the application. ASP. net function (such as the sqldatasource control or the provider used for membership, role, user configuration file, Web Part personalized settings, etc.) automatically replaces the file path of the app_data directory| Datadirectory |Concatenate string variables. If you move the web application to another directory, this ensures that the database path is up to date. The followingCodeThe example shows| Datadirectory |The connection string that connects the string variable.

 
Data Source =. \ sqlexpress; attachdbfilename = | datadirectory | customers. MDF; Integrated Security = true; user instance = true
 

Note:
When you andUser instanceSetTrueSQL Server express only allows a single connection to The. MDF file.

To release any open connection to the SQL Server express edition database, you can use the Internet Information Service Manager (IIS manager) to uninstall the web application. You can also add an HTML file named app_offline.htm to the root directory of the Web application to uninstall the web application. To allow the web application to start responding to the Web request again, you only need to remove the app_offline.htm file. To copy or move a database to a new location, you must release the SQL Server express database connection.

Set up an SQL Server express Database

You can create an SQL Server express edition database by connecting to a computer running SQL Server express edition and issuing the create database Command, or by using the SQL server management tool provided for SQL Server express edition. In addition, development tools (such as Visual Studio) Provide data management tools that allow you to easily create and manage SQL Server express edition databases.

ASP. the default provider of net functions (such as membership, roles, user configuration files, and Web Part personalization settings) is configured as aspnetdb in the app_data directory of the application. mdf SQL Server express edition database. If you enable any of these data storage functions that use the default provider and the aspnetdb. mdf SQL Server express edition database does not exist in the app_data directory of the application, the database is automatically created. If the app_data directory of the application does not exist, the directory is created.

User instance

SQL Server express supports user instances, which means that the system starts a new process for each user connected to the SQL Server express database. The process ID is the user that opens the connection. For information about how to identify ASP. NET applications, see ASP. NET simulation.

Although enabling a user instance is suitable for desktop development, starting a secondary process is not suitable for Web servers that host multiple customer sites. In this case, you must separate applications and perform security protection. ASP. NET applications running with the same process ID can connect to the same user instance. Since Windows 2000 and Windows XP Professional, all ASP.. NET applications run with the same Process Identity (local ASPNET account by default). On Windows Server 2003, Asp.. NET applications also run with the same Process Identity (default: Network Service account). Therefore, hosted servers that contain untrusted applications should explicitly disable user instances. Connect to an instance of SQL Server Express (for example, by issuing the following command at a command prompt:Osql-e-s. \ sqlexpress) And issue the following Transact-SQL command to disable this function.

Exec sp_configure 'show advanced option', '1'

Go

Reconfigure with override

Go

Exec sp_configure 'user instances enabled', 0

Go

Reconfigure with override

Go

Deploy an SQL Server express Database

The SQL Server express edition database consists of two files: The. MDF file, which contains the database architecture and data, and the. LDF file, which contains the database log information. If you are using a file-based connection to the SQL Server express edition database, you can use xcopy, FTP, or other methods to copy these files along with the application to the target server. As long as SQL Server express edition is installed on the target server, the application continues to run.

Because SQL Server express uses the same file format as other versions of SQL Server 2005, you can. MDF and. copy the LDF file to the server running SQL Server, and then attach the file as the database.

Note:

If you are deploying an SQL Server express database to a Web server that hosts multiple untrusted sites, you cannot use file-based connections or user instances, to ensure that data is not exposed to other applications on the server. In this case, we recommend that you migrate the content of the SQL Server express edition database to another version of SQL Server 2005 that can be accessed by the deployed ASP. NET application.

If you want to copy an empty SQL Server express edition database that contains the database architecture but does not contain data, the SQL Server Management tool allows you to generate scripts that can run in the target database to copy the architecture of the development database.

If the SQL Server express database contains encrypted information, such as the encrypted password stored in the membership database, make sure that the encryption key is also copied to the target server.

If you want to move the entire SQL Server express database, make sure that there is no open database connection. This connection will cause the database to be locked.

Cancel database lock

If an opened database connection exists, the database will be locked and cannot be moved or deleted. The opened connection may be retained by an ASP. NET application, a designer like Visual Studio, or another program or database client. To cancel database locking, you must disable all opened database connections. You can close the opened connection by using the following methods:

• you can exit the program to close the connection retained by the designer. (IN Visual Studio, right-click the database in Solution Explorer and select the "detach" option; alternatively, you can right-click the database in "server resource manager" and select the "close connection" option .)

• You can end the application to close any connections retained by the ASP. NET application. This can be done using IIS manager, or put the file named app_offline.htm in the root directory of the ASP. NET application (you must remove this file to restart the application ).

• by exiting the program, you can close any connection occupied by other resources (such as Windows Forms applications.