Introduction to using VWD operation database in asp.net

asp.net| Data | database

VWD2005 contains SQL Server, the Edition Express, which is a free version of SQL Server 2005, and is intended for unprofessional or fanatical developers who want to build applications with simple database solutions. Because SQL Server Express supports a 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 understand these technologies, And make sure your applications can run on a higher level platform (for example, SQL Server Enterprise Edition). SQL Server Express is easy to download and install (it is less than 36MB) and is included in 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 the SQL Server Express Database (Builder). Visual Studio also supports a new file-based database for building a SQL Server Express database and as part of a project. At the same time, Visual Studio 2005 and SQL Server, the full Express offer a complete solution for building and deploying data-driven Web applications, including some of the following features:

Database project Data Items

Local Database connection

Database Browser Management

Relative path Connection string

xcopy Deployment support

Integrated debugging support

This article describes the File-based database approach in Visual Studio 2005 and how designers build and use these databases.

Visual Web Developer and data

The Visual Web Developer provides a number of tools for Web applications to use databases. You can use a database browser to easily connect to a database and create or view a database diagram or outline (schema). You can also use the Query Builder dialog box and the query results table to query the database and populate the data. Creating a new database (using local files) is also provided as a project data item.

Once you have connected the database in the Visual Web developer, you can drag and drop the database table onto the page to establish the data-bound GridView control. The Visual Web developer automatically establishes an associated data source control and configures its selection, update, insert, and delete operations based on the data table. This quick action saves you a lot of work to manually build a data source, and you can easily replace the GridView with the appropriate data-bound controls as needed.

local and server databases

You may already be familiar with server-based databases and connection strings. In this case, the database server (for example, SQL Server 2005) associates the database name with the database files 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 a local database, 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 content in this directory will never respond to user requests. This directory is also the recommended location for storing XML files and other data stores. The local SQL Server Express database has a. MDF extension (for example, "Mydatabase.mdf"), which is the standard file format supported by SQL Server. When connecting to the server, the database also has an associated log file (for example, "Mydatabase_log."). LDF "). The location of the database files and log files must be together.

We can automatically attach the local file database to SQL Server Express by using the relative path connection string. A relative path ensures that the database connection is not interrupted when the application migrates to any other place. 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 connection string above also has two 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 the full path of the database (for example, use | datadirectory| syntax), but at run time this path is replaced by the application's App_Data directory. This also ensures that the connection is not interrupted when the application migrates to another location. The second property is the user instance=true, which specifies how SQL Server Express attaches the database. In this case, SQL Server Express creates a new process for attaching the database to a new instance, running under the identity of the user who opened the connection. In a asp.net application, this user is the local ASPNET account or the default network Service, which relies on the operating system. In order to securely attach a database file provided by a non-system administrator account (such as a asp.net account), it is necessary to establish a separate SQL Server user instance.

Note that because all ASP.net applications run under the same process by default, all applications attach the local database to the same SQL Server Express instance. This means that regardless of which database the application initially attaches, all applications have the same access rights to all databases attached to the instance. To isolate different applications, you must have each application run in a different worker process or application pool (in IIS 6). For this reason, the local SQL Server database is intended primarily for easy development rather than a server-based database in lieu of a shared host 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 frees the connection to ensure that Visual Studio and asp.net can share database files, such as debugging running applications in the designer.

setting up a local database

You can easily create a local database in a Visual Studio Web application project. Because Visual Studio has SQL Server Express installed, you can use the following steps to create a local database, add a datasheet, and populate the data.

To establish a local database:

1. Right-click the solution Browser and select "Add New Item ..." option.

2. Select the "SQL Database" item and specify a filename, 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 a database browser.

To add a data table to the local database:

1. Right-click the tables (datasheet) node in the database browser and select the "Add New table" option.

2. Enter the name and type of the column in the database, optionally set some other column properties in the Property table. To establish the sample contacts database, follow the steps below.

3. Set the name of the first column to "ContactID" and the data type is set to "int". Uncheck the Allow Nulls check box.

4. Right-click the gray square on the left side of the ContactID column and select the "Set as primary Health" option.

5. In the Column Properties table below, expand the Identifiers Specification node and set the identifier to Yes.

6. Set the name of the second column to "ContactName" and the data type is set to "varchar (50)". Keep the Allow Nulls check box selected.

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 in the database browser (for example, "Contacts") and select the "Show Table data" option.

2. Enter data for data table rows in the displayed table. If you use the contacts example above, you can enter a value in the ContactName column, and the database automatically generates the corresponding ContactID value.

3. Close the table window.

Bind to local Database

To bind to a local database, you need to use a relative path connection to configure the ASP.net data source control to connect to the file. To simply bind a SQL Server Express database to the SqlDataSource and GridView controls, you can use the following steps:

1. Double-click a page in the Solution Explorer (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 Explorer. Visual Studio opens the 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 into the open page in Design view. Visual Studio establishes a GridView bound to the SqlDataSource Control.

6. Expand the "Smart transactions panel" of the GridView control, and select paging, sorting, and editing.

7. Press CTRL-F5 to run the page (no debugging).

The following example shows a GridView and SqlDataSource control that is connected to a local database. To run this example, ASP. NET process account must have read/write access to the MDF and LDF files in the ~/app_data directory. This permission setting process is as follows:

1. Select the ~/app_data/database.mdf file in Windows browser and choose 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," IIS 5. The "ASPNET" is in X.

5. Select "Read" and "write" in the "Allow" column and click "OK".

6. If LDF files exist in the App_Data directory, you need to repeat the steps above to set the properties of the LDF file.

If you use Visual Studio to create an application on your local computer, by default you have the permissions of the App_Data directory. If a database file needs to be changed after it is attached, you must shut down the application domain (application domain) first before the new permissions take effect.

Deploying a local database

One of the advantages of a local database file is that it can accompany the application, migrate to other locations as part of the application, or other computers (of course, the computer must also run SQL Server Express). The file must be unlocked (unlocked) when the database is moved. When a designer or application connects to a database, the file is locked. In order to unlock, all active connections to the database must be closed. You can use the following techniques to close a database connection:

If ASP.net has already opened the connection, you can close the application domain by adding a "app_offline.htm" file to the Web application root directory. Its role is to shut down the application domain (not the process) and request all applications to redirect (redirect) to this file (return 404 response Code). If you want to restart the application, you only need to delete the file. Note that simply closing the connection in the page code does not release the file lock because, by default, ADO. NET connection pool retains the active connection.

Visual Studio provides the copy Web feature that enables you to copy application files from the working directory to the target server using Xcopy, FTP, or FrontPage Server Extensions. The target computer can be local or remote. You can invoke the Copy Web feature by selecting "Web site > Copy Web site ..." from the Visual Studio menu bar.

Although you can use the copy Web or a simple xcopy or FTP operation to move the database, the target computer must run SQL Server Express (under the same instance name) in order for the application to continue to work. As we mentioned earlier, because all applications running asp.net are connected to the same SQL instance, all applications on the target computer must trust each other. If an application cannot see a database of other applications, 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, you need to replicate the SQL Server Express database content as part of your product deployment to your customers.