Use Visual Web Developer to operate databases in ASP. NET (1)

VWD2005 contains SQL Server 2005 Express Edition, which is a free version of SQL Server 2005, its target users are non-professional or enthusiastic 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 learn about these technologies and make sure that your applications can run on a higher-level platform, such as SQL Server Enterprise Edition ). SQL Server Express is easy to download and install. It is smaller than 36 MB), and Visual Studio 2005 and Visual Web Developer 2005 also include it.

Visual Studio includes tools for Database management, such as Database Explorer) and Query Builder). You can use them to manage SQL Server Express databases. Visual Studio also supports a new file-based database used to create an SQL Server Express database and use it as part of a project. Visual Studio 2005 and SQL Server 2005 Express provide a complete solution for creating and deploying data-driven Web applications, including 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 how to create and use a file-based database in Visual Studio 2005 and the designer.

Visual Web Developer and Data

Visual Web Developer provides a large number of tools for Web applications to use databases. You can use the database browser to easily connect to the database and create or view the database relationship diagram or outline schema ). You can also use the query constructor dialog box and query result table to query the database and fill in data. Creating a new database using local files) is also provided as a project data item.

Once you connect to the database in Visual Web Developer, you can drag and drop the database table to the page to create a data-bound GridView control. Visual Web Developer automatically creates an associated data source control and configures its selection, update, insertion, and deletion operations based on the data table. This quick operation saves you a lot of effort to manually create a data source. You can also easily replace the GridView with a suitable data binding 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 specify the server name, database name, and credential to connect to the server-based database. 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 contents in this directory will never respond to user requests. This directory is also a recommended location for storing XML files and other data storage. The local SQL Server Express database has a. MDF extension such as "MyDatabase. MDF"), which is a standard file format supported by SQL Server. When you connect to the server, the database also has an associated log file such as "MyDatabase_log.LDF "). The database files and log files must be located together.

We can automatically attach the local file database to SQL Server Express by using the relative path connection string. The relative path ensures that the database connection is not interrupted when the application is migrated to any other place. The relative path connection string in the Web application is as follows:

 
 

  
  
"server=(local)\SQLExpress;AttachDbFileName=  
  
  
|DataDirectory|MyDatabase.mdf;Integrated   
  
  
Security=true;User Instance=true"  
 
 

The preceding connection string has two additional attributes. The AttachDbFileName attribute specifies the location of the database files dynamically appended to the server when the connection is opened. Although this attribute can accept the full path of the database, for example, using | DataDirectory | syntax), this path will be replaced by the App_Data directory of the application at runtime. This ensures that the connection will not be interrupted when the application is migrated to another location. The second attribute is User Instance = true, which specifies the method for attaching a database to SQL Server Express. In this case, SQL Server Express creates a new process to attach the database to a new instance and runs it under the user identity that opens the connection. In ASP. NET applications, this user is a local ASPNET account or the default Network Service, which depends on the operating system. To securely append database files provided by non-system administrator accounts, such as ASP. NET accounts, it is necessary to create an independent SQL Server user instance.

Note: by default, all ASP. NET applications run in the same process, so all applications will attach the local database to the same SQL Server Express instance. This means that no matter which database is initially appended to the application, all applications have the same access permissions 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 mainly used to facilitate development, rather than to replace the Server-based database in the shared host environment.

Another key 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, such as applications called for trial run in the designer ).

Create a local database

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

Create a local database:

1. Right-click solution browser and select "Add new project... "Option.

2. Select "SQL Database" and specify a file name, for example, "Database. mdf ".

3. Visual Studio prompts you to add this file to the App_Data directory. Click "yes ".

4. Add the file in Visual Studio and automatically connect to the database using the database browser.

Add a data table to the local database:

1. Right-click the Tables data table node in the Database Browser and select "Add new table.

2. Enter the name and type of columns in the database. You can set other column attributes in the Attribute Table. To create an example Contacts database, follow the steps below.

3. Set the name of the first column to "ContactID" and the data type to "int ". Deselect the selected status in the "allow blank" check box.

4. Right-click the gray box on the left of the ContactID column and select "set as primary key.

5. In the "column attributes" table below, expand the "identifier specification" node and set "whether to be an identifier" to "yes ".

6. Set the name of the second column to "ContactName" and the data type to "varchar (50 )". Leave the "allow blank" 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.

Fill the 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 the data of the Data Table row in the displayed table. 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.