VWD2005 includes SQL Server 2005 Express Edition, a free version of SQL Server 2005 targeted at non-professional or enthusiast developers who want to build applications with a simple database solution. Because SQL Server Express supports the complete SQL Server programming model, such as SQLCLR, T-SQL, stored procedures, views, triggers, and XML data types, you can use SQL Server Express to learn about these technologies and ensure that your applications can run On higher level platforms (such as SQL Server Enterprise Edition). SQL Server Express is easy to download and install (it's less than 36MB), and is included with Visual Studio 2005 and Visual Web Developer 2005.
Visual Studio includes tools for database management, such as Database Explorer and Query Builder, which you can use to manage SQL Server Express databases. Visual Studio also supports a new file-based database for building SQL Server Express databases as part of a project. At the same time, Visual Studio 2005 and SQL Server 2005 Express provide a complete solution for building and deploying data-driven Web applications, including the following features:
· Database project data items
· Local database connection
· Database browser management
· Relative path connection Strings
· XCopy deployment support
· Integrated debugging support
This article describes the file-based database approach in Visual Studio 2005 and how the designer creates and uses these databases.
Visual Web Developer and DataVisual
Web Developer provides a number of tools for using databases with Web applications. You can use Database Browser to easily connect to a database and create or view a database diagram or schema. You can also use the query builder dialog box and query results table to query the database and populate data. Creating new databases (using local files) is also provided as project data items.
Once you have connected to the database in Visual Web Developer, you can drag and drop database tables directly onto the page to create a data-bound GridView control. Visual Web Developer automatically creates an associated data source control and configures its select, update, insert, and delete operations based on the data table. This quick operation saves you a lot of work required to manually create a data source, and you can easily replace the GridView with a suitable data-bound control as needed.
Local and Server Databases
You may already be familiar with server-based databases and connection strings. In this case, the database server (such as SQL Server 2005) associates the database name with the database file maintained by the server. You can connect to a server-based database by specifying the server name, database name, and credentials, for example:
"server=(local)SQLExpress;database=Pubs;Integrated Security=true"
However, Visual Studio 2005 also supports the concept of local databases , which is a file added to the App_Data directory of the current web application. It is safe to store data files in the App_Data directory because the contents of this directory will never respond to user requests. This directory is also the recommended location for storing XML files and other data storage. Native SQL Server Express databases have a .MDF extension (such as "MyDatabase.MDF"), which is a standard file format supported by SQL Server. When connected to the server, the database also has an associated log file (such as "MyDatabase_log.LDF"). The location of the database file and log file must be together.
We can automatically attach a local file database to SQL Server Express by using a relative path connection string. Relative paths ensure that the database connection will not be interrupted when the application is moved to any other location. The relative path connection string in the web application looks like this:
"server=(local)SQLExpress;AttachDbFileName=|DataDirectory|MyDatabase.mdf;Integrated Security=true;User Instance=true"
The above connection string has two more additional attributes. The AttachDbFileName property specifies the location of the database file that is dynamically attached to the server when the connection is opened. Although this property can accept a full path to the database (e.g. using the |DataDirectory| syntax), at runtime this path will be replaced by the application's App_Data directory. This also ensures that connections are not interrupted when the application is moved to another location. The second property is User Instance=true, which specifies how SQL Server Express attaches the database. In this case, SQL Server Express creates a new process in order to attach the database to the new instance, running as the user who opened the connection. In an ASP.NET application, this user is the local ASPNET account or the default Network Service, depending on the operating system. In order to securely attach database files provided by non-administrator accounts (such as ASP.NET accounts), it is necessary to create a separate SQL Server user instance.
Please note: Because by default, all ASP.NET applications run in the same process, all applications will attach the local database to the same SQL Server Express instance. This means that all applications have the same access to all databases attached to this instance, regardless of which database the application originally attached to. In order to isolate different applications, you must have each application run in a different worker process or application pool (in IIS 6). For this reason, a local SQL Server database is primarily intended to facilitate development and is not intended to replace a server-based database in a shared hosting environment.
Another important point is that two users are not allowed to connect to a local database at the same time. When designing an application in Visual Studio, the designer automatically releases the connection to ensure that Visual Studio and ASP.NET can share database files (for example, when debugging a running application in the designer).
Creating a local database
You can easily create a local database in a Visual Studio Web application project. Since Visual Studio comes with SQL Server Express installed, you can use the following steps to create a local database, add tables, and populate the data.
To create a local database:
1. Right-click in the Solution Browser and select the "Add New Item..." option.
2. Select the "SQL Database" item and specify a file name, such as "Database.mdf".
3. Visual Studio prompts to add this file to the App_Data directory. Click "Yes".
4. Visual Studio adds this file and automatically connects to the database using Database Browser.
To add a data table to the local database:
1. Right-click the Tables node in the database browser and select the "Add New Table" option.
2. Enter the name and type of the column in the database, and optionally set other column properties in the property table. In order to set up the sample Contacts database, follow the steps below.
3. Set the name of the first column to "ContactID" and the data type to "int". Uncheck the "Allow nulls" checkbox.
4. Right-click the gray square to the left of the ContactID column and select the "Set as Primary Key" option.
5. In the "Column Properties" table below, expand the "Identifier Specification" node and set "Is it an identifier" to "Yes".
6. Set the name of the second column to "ContactName" and the data type to "varchar(50)". Leave the "Allow Nulls" checkbox checked.
7. Press Ctrl-S to save the table and set the table name to "Contacts". Click OK to save the table.
8. Close the table definition window.
To populate a table with data:
1. Right-click the data table node (for example, "Contacts") in the Database Browser and select the "Show Table Data" option.
2. Enter the data for the data table rows in the displayed form. If you use the Contacts example above, you can enter a value in the ContactName column and the database will automatically generate the corresponding ContactID value.
3. Close the table window.
Binding to a local database
In order to bind to a local database, you need to configure the ASP.NET data source control to connect to the file using a relative path connection. To simply bind a SQL Server Express database to the SqlDataSource and GridView controls, use the following steps:
1. Double-click a page in the Solution Browser (for example, "Default.aspx"). Visual Studio will open this page.
2. Select the "Design View" tab at the bottom of the page window to switch to design view.
3. Double-click the database file (for example "Database.mdf") in the Solution Browser. Visual Studio opens a database browser for the connection.
4. Expand the Tables node to display the tables in the database.
5. Drag and drop the table in the Database Browser to the open page in Design view. Visual Studio creates a GridView bound to the SqlDataSource control.
6. Expand the "Intelligent Transaction Panel" of the GridView control and select paging, sorting and editing.
7. Press Ctrl-F5 to run the page (without debugging).
The following example shows a GridView and SqlDataSource control connected to a local database. In order to run this example, the ASP.NET process account must have read/write permissions to the MDF and LDF files in the ~/App_Data directory. The permission setting process is as follows:
1. Select the ~/App_Data/Database.MDF file in Windows browser and select "Properties".
2. Select the "Security" tab and click "Add".
3. Click "Location...", select your computer name (at the top of the list), and click "OK".
4. In the Object Name text area, enter the name of the ASP.NET process account. By default, the name in IIS 6.0 is "Network Service" and in IIS 5.x it is "ASPNET".
5. Select "Read" and "Write" in the "Allow" column and click "OK".
6. If there is an LDF file in the App_Data directory, you need to repeat the above steps to set the properties of the LDF file.
If you use Visual Studio to build an application on your local computer, then by default, you have the above permissions on the App_Data directory. If a database file needs to change permissions after it has been attached, you must first close the application domain before the new permissions can take effect.
<asp:GridView AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="ContactID" DataSourceID="SqlDataSource1" ID="GridView1" runat="server">
＜Columns＞
＜asp:BoundField DataField="ContactID" HeaderText="ID" ReadOnly="True" SortExpression="ID" /＞
＜asp:BoundField DataField="ContactName" HeaderText="Name" SortExpression="Name" /＞
＜/Columns＞
＜/asp:GridView＞
＜asp:SqlDataSource ConnectionString="＜%$ ConnectionStrings:ContactsDatabase %＞" ID="SqlDataSource1" runat="server" SelectCommand="SELECT [ContactID], [ContactName] FROM [Contacts]" OnSelected="SqlDataSource1_Selected"＞＜/ asp:SqlDataSource>
Deploying a local database
One of the advantages of a local database file is that it can accompany the application, be moved as part of the application to other locations or to other computers (of course the computer must also be running SQL Server Express). When moving the database, the file must be in an unlocked state. The file is locked while the designer or application is connected to the database. In order to unlock, all active connections to the database must be closed. You can use the following techniques to close the database connection:
· If ASP.NET has opened the connection, you can close the application domain by adding an "app_offline.htm" file to the web application root directory. Its function is to close the application domain (not the process) and redirect all application requests to this file (returning a 404 response code). If you want to restart the application, just delete this file. Please note that simply closing the connection in the page code will not release the file lock, because by default, the ADO.NET connection pool keeps active connections.
Visual Studio provides the Copy Web feature, which can copy application files from the working directory to the target server using xcopy, FTP, or the FrontPage server extension. The target computer can be local or remote. You can call the Copy Web feature by selecting "Web Site > Copy Web Site..." from the Visual Studio menu bar.
Although you can use Copy Web or a simple xcopy or FTP operation to move the database, in order for the application to continue to work, the target computer must be running SQL Server Express (under the same instance name). We mentioned earlier that since all applications running ASP.NET are connected to the same SQL instance, all applications on the target computer must trust each other. If one application cannot see another application's database, then we recommend using a server-based approach (using SQL authentication or other separation techniques) instead of the local database. If you use SQL Server Express as a development tool, then you need to copy the SQL Server Express database content to customers as part of the production deployment.