Working with Databases in Visual Web Developer 2005 Express Edition
Visual Web Developer 2005 Express Edition includes SQL Server 2005 Express Edition, a free edition of SQL Server 2005 targetted at non-professional
or hobbyist developers who want a simple database solution for building applications. Because SQL Server Express supports the
complete SQL Server programming model, such as SQLCLR, T-SQL, stored procedures, views, triggers, and the XML data type, you can
get acquainted with the this technology using SQL Server Express, while ensuring that your application
continues to run against higher-level SKUs such as SQL Server Enterprise Edition.
SQL Server Express is easy to download and set up (it is less than 36MB), and is also included with
Visual Studio 2005 and Visual Web Developer 2005.
Visual Studio includes tools for database management such as the Database Explorer and Query Builder,
which can be used with SQL Server Express databases. Visual Studio also supports a new file-based approach
to databases for creating a SQL Server Express database as part of your project. Together, Visual Studio
2005 and SQL Server 2005 Express enable a complete solution for building and deploying data-driven
Web applications, including the following features:
This section describes the file-based approach to databases in Visual Studio 2005 and how to create
and consume these databases from the designer.
- Database project item
- Local database connections
- Database Explorer management
- Relative path connection strings
- XCopy deployment support
- Integrated debugging support
Visual Web Developer and Data
Visual Web Developer provides a variety of tools for working with the databases used by your Web applications.
You can easily connect to databases and create or view database diagrams or schemas using the Database Explorer.
You can also query databases and populate data using the Query Builder dialog and Query Results grid.
Creating a new database (using a local file) is also exposed as a project item (described below).
Once you have a connection to a Database in Visual Web Developer, you can drag and drop database
tables directly to pages to create a data-bound GridView control. Visual Web Developer automatically
creates an associated data source control configured for Select, Update, Insert, and Delete operations
against this table. This shortcut can spare you the effort of having to create a data source manually,
and you can easily replace the GridView with an appropriate data-bound control for your scenario.
Local and Server Databases
You are probably already familiar with server-based databases and connection strings. In this configuration,
a database server such as SQL Server 2005 associates a database name to a database file that attached and
maintained by the server. You can connect to a server-based database with a connection string that specifies
the server name, database name, and authentication scheme, for example:
However, Visual Studio 2005 also supports the notion of a local database, that is added as a file to the current
Web application's App_Data directory. The App_Data directory is a secure place to store data files, because
the contents of this directory are never served to client requests. It is also the recommended location for
XML files and other data stores. A local SQL Server Express database has a .MDF extension, for example "MyDatabase.MDF",
which is the standard file format supported by SQL Server. When connected to a server, the database also has an associated
log file, such as "MyDatabase_log.LDF". The database file and log file should always be moved together.
A local file database is attached to SQL Server Express dynamically through the use of a relative path connection
string. The relative path ensures that the application can be moved to another location without breaking the
connection to the database. A relative-path connection string in a Web application is specified as follows:
"server=(local)\SQLExpress;AttachDbFileName=|DataDirectory|MyDatabase.mdf;Integrated Security=true;User Instance=true"
There are two additional properties of the above connection string. The AttachDbFileName property specifies
the location of the database file to attach to the server dynamically when the connection is opened. Although this
property can accept a fully-qualified path to the database, this example uses the |DataDirectory| syntax,
which will be substituted with the path to the Web application's App_Data directory at runtime. This is what
allows the application to be moved to a different location without breaking the connection. Secondly, the User Instance=true
property dictates the way in which SQL Server Express will attach to the database. In this case, SQL Server Express spawns a
new process running as the user that opened the connection in order to attach the database to this new instance. In an
ASP.NET application, this user will be the local ASPNET account or Network Service by default, depending on your operating
system. A separate user instance of SQL Server is required to securely attach database files from a non-administrative account,
such as the ASP.NET account.
Important: Because all ASP.NET applications run under the same process account by default, all applications will attach
local databases to the same instance of SQL Server Express. This means that all applications will have equal access to all
attached databases in this instance, regardless of the individual application that attached a database initially. For
isolation between applications using SQL Server Express, you must run each application is a separate worker process or application
pool (under IIS 6). For this reason, local SQL Server databases are considered more of a development-time convenience, and not
an replacement for server-based databases in shared hosting scenarios. For more information on how to move the contents of a local
database to a server database, refer to the deployment topic at the end of this section.
Another important point is that no two users may be connected to a single local database at the same time. When designing an application
in Visual Studio, the designer takes care of automatically releasing connections to enable Visual Studio and ASP.NET
to share access to the file (when running the application to debug from within the designer, for example).
Creating a Local Database
You can easily create a local database in a Web application project in Visual Studio. Provided you installed SQL Server Express with
Visual Studio, you can follow these steps to create a local database, add tables, and populate data.
To create a local database:
To add a table to the local database:
- Right-click the Solution Explorer and choose the "Add New Item..." option.
- Select the "SQL Database" item and give the file a name, for example "Database.mdf".
- Visual Studio prompts to add this file to the "App_Data" directory. Click "Yes".
- Visual Studio adds the file and automatically connects to the database using Database Explorer.
- Right-click the Tables node in Database Explorer and choose the "Add New Table" option.
- Enter names and types for the columns in the database, optionally setting other column properties in the property grid.
To create a sample Contacts database, continue following the steps below.
- Set the first Column Name to "ContactID" and set the Data Type to "int". Uncheck the "Allow Nulls" checkbox for this column.
- Right-click the grey box with the arrow next to the ContactID column and choose the "Set Primary Key" option.
- In the Column Properties grid below the Columns grid, expand the "Identity Specification" node and set "Is Identity" to "Yes".
- Set the second Column Name to "ContactName" and set the Data Type to "varchar(50)". Leave the "Allow Nulls" checkbox checked.
- Type Ctrl-S to save the table and give it the name "Contacts". Click OK to save the table.
- Close the table definition window.
To populate a table with data:
- Right-click the table node (for example, "Contacts") in Database Explorer and choose the "Show Table Data" option.
- Enter data for the rows of your table in the results grid. If following the "Contacts" example from above, you can enter values for the ContactName column and the database will automatically generate corresponding ContactID values.
- Close the results grid window.
Binding to a Local Database
To bind to a local database you can configure an ASP.NET data source control to connect to the file using a relative-path connection.
To easily bind a SQL Server Express database to a SqlDataSource and GridView control, follow the steps below:
The example below shows a GridView and SqlDataSource control connected to a local database:
- Double-click a page in Solution Explorer, for example "Default.aspx". Visual Studio opens the file.
- Select the Design View tab below the page window to switch to Design View.
- Double-click the database file in Solution Explorer, for example "Database.mdf". Visual Studio opens the Database Explorer for this connection.
- Expand the Tables nodes to reveal the tables in your database.
- Drag and drop a table from Database Explorer to the open page window in Design View. Visual Studio create a GridView bound to a SqlDataSource control.
- Expand the Smart Task Panel on the GridView control and enable paging, sorting, and editing for the grid.
- Type Ctrl-F5 to run the page (without debugging).
VB Binding to a Local Database
Deploying a Local Database
One benefit of a local database file is that it can be easily moved as part of the application to another location or machine (as
long as that machine is also running SQL Server Express). To move the database, the file must be unlocked. While a designer or
application is connected to the database, the file will remain locked. To unlock the database, all active connections to the database
must be closed. You can close the connections to the database using the following techniques:
Visual Studio provides a Copy Web feature to move application files from the working directory to a
target server, using xcopy, FTP, or FrontPage Server Extensions. The target machine may be local or remote.
To invoke the Copy Web feature, choose "Website > Copy Web Site..." from the Visual Studio menu bar.
- If the Visual Studio designer has the open connection, you can close Visual Studio or right-click the
database and select the "Detach" option.
- If ASP.NET has open connections, you can shut down the application
domain by adding a file named "app_offline.htm" to the Web application root directory. This has the effect
of shutting down the application domain (not the process) and redirecting all requests to the application to
this file (returning a 404 response code). To allow the application to restart, remove this file. Note that
simply closing connections in your page code will not release the file lock, because by default the ADO.NET
connection pool retains the active connections.
While you can move a database using Copy Web or a simple xcopy or FTP operation, the target machine must also be running SQL Server Express (under
the same instance name) in order for the application to continue working. As mentioned above, because all applications running as
ASP.NET connect to the same SQL instance, all applications on the target machine should be trusted to each other. On machines
where applications should not be able to see each other's database, the server-based approach (using SQL authentication or other
isolation technique) is recommended instead of a local database. If you use SQL Server Express as a development tool, you will need
to copy your SQL Server Express database contents to the provisioned server database as part of production deployment.