Working with Databases in ASP.NET 2.0 and Visual Studio 2005By Scott Mitchell
On November 7th, 2005, Microsoft officially released .NET 2.0 (including ASP.NET 2.0), Visual Studio 2005, and SQL Server 2005. All of these components were designed to work side-by-side with previous versions. That is, both ASP.NET version 1.x and version 2.0 can be installed on the same machine; you can have both Visual Studio .NET 2002/2003 and Visual Studio 2005, as well as both SQL Server 2000 and SQL Server 2005. Furthermore, Microsoft is releasing an "Express" SKU of Visual Studio 2005 and SQL Server 2005. The Express line does not have all the features of the professional editions, but you can't beat the price - free for SQL Server 2005 and a free for one year for the Express line of Visual Studio 2005! (More information on the Express SKU pricing is available here.)
Since the vast majority of interesting ASP.NET web applications access data in some manner, one of the first questions many developers have when exploring 2.0 is, "How do I work with databases in 2.0?" and, "What databases can I connect to through an ASP.NET 2.0 page?" The good news is that 2.0 is everything that version 1.x was plus additional features. Therefore, whatever databases you could work with in 1.x and whatever code you used will work in 2.0.
In addition to 2.0 supporting the 1.x style of data access, 2.0 also includes a number of new datasource controls, which make
accessing and modifying database data a breeze. With datasource controls, you simply add the control to the ASP.NET page
and configure the connection string and SQL query through the control's properties. This datasource control can then be
bound to a data Web control (such as the GridView)
by setting the data Web control's
DataSourceID property to the
ID of the datasource control.
The days of having to write code to create the connection, create the command, specify the SQL query, retrieve a DataReader
or DataSet, and bind it to the data Web control are now over. These steps can be replaced through using the declarative
datasource controls. (Of course, you can still access data programmatically using the familiar steps/classes from your 1.x code.)
In this article we'll look at how to connect and display data from a database in ASP.NET 2.0. Specifically, we'll see how to use both the programmatic and declarative approaches for accessing data, as well as the basics of displaying data using the GridView control. Read on to learn more!
Working with Databases Through Visual Studio 2005
When you install any of the Visual Studio 2005 editions you'll be asked if you also want to install SQL Server 2005 Express edition. If you choose Yes, this will install SQL Server 2005 Express edition on your development box, where you're installing Visual Studio 2005. (SQL Server 2005 Express edition can be installed side-by-side with other versions of SQL Server, including SQL Server 2000 and other editions of SQL Server 2005.)
If you use SQL Server 2000 in your current projects you are probably most comfortable working with the database through Enterprise Manager. While you can still use Enterprise Manager for SQL Server 2000, or SQL Server 2005's Management Studio for your SQL Server 2005 databases, you can also manage these databases through Visual Studio 2005's Data Connections. I mention this because for SQL Server 2005 Express edition there is not a GUI tool like SQL Server 2000's Enterprise Manager or SQL Server 2005's Management Studio; instead, you have to create and manage your SQL Server 2005 Express edition databases through Visual Studio 2005. (Microsoft is working on a SQL Server Management Studio Express Edition for SQL Server 2005 Express; currently the software is released as a community technical preview edition, although an "official" version should be released in due time - keep your eyes on the SQL Server 2005 Express homepage for the latest news.)
|Managing SQL Server 2005 Express Edition Databases|
|If you have any other SQL Server 2005 edition other than Express, you can install the client tools on your machine, which includes Management Studio, the GUI tool for managing SQL Server 2005 databases. If you have this tool installed you can use it to also manage SQL Server 2005 Express edition databases.|
To manage a database through Visual Studio 2005, go to the Server Explorer; there you'll find a Data Connections node (shown in
the screenshot to the right). You can add new database connections by right-clicking on Data Connections node and choosing
Add Connection. This will bring up a dialog box that prompts you for information such as the database server, authentication
information, what database to use, and so on. If you installed SQL Server 2005 Express edition on your machine, the database
was installed, by default, using the instance name SQLExpress. Therefore, the server name to connect to would be
.\SQLExpress. In addition to connecting to an existing database,
you can also create a new database by right-clicking on the Data Connections node and choosing Create New SQL Server Database.
Once a database has been added to the Data Connections tab, you can add, delete, or modify tables, stored procedures, views, and so on through the appropriate folders. To create a new table or stored procedure, right-click on the appropriate folder and choose Add New X menu option; to modify an existing table, stored procedure, or view, double-click the table, stored procedure, or view. This will bring it up in Visual Studio, where you can modify it as needed. You can also view and modify the data in individual tables by right-clicking on a table name and choosing Show Table Data.
Adding a Database to the
In addition to adding existing databases through the Data Connections tab, you can also add a site-specific database to the website's
App_Datais a new, reserved folder in ASP.NET 2.0 that is designed to hold data-related content, including SQL database files (
.mdffiles), Access database files (
.mdbfiles), XML files, and so on. From an ASP.NET website project, you can effortlessly create and add a new SQL Server 2005 Express database to your project by right-clicking on the
App_Datafolder in the Solution Explorer and choosing Add New Item. Then, from the Add New Item dialog box (shown below), choose to add a new SQL Database.
If you want to follow along with this article, create a SQL Server 2005 Express database in the
Customers.mdf. Add a single table to this database named
Customers with the following columns:
CustomerID (an auto-increment primary key field),
ZipCode. Then add some records to this table through VS 2005. (The database file and complete ASP.NET code
is available for download at the end of this article...)
You can also add existing Access database files and even SQL Server 2000 database files. (Note: In order to add an existing
SQL Server 2000
.mdf file you'll need to ensure that it has first been detached via the Enterprise Manager; to
accomplish this, right-click on the database name in Enterprise Manager, select All Tasks, and choose Detach Database. Once you've
detached the database and added it to the ASP.NET 2.0 project, you can reattach it through Enterprise Manager.)
Connecting to a Database Using the SqlDataSource Control
Now that we've seen how to create and work with databases through Visual Studio's interface, let's turn our attention to accessing and displaying data from a database in an ASP.NET 2.0 web page. ASP.NET 2.0 includes a number of new datasource controls, whose sole purpose is to provide declarative access to data. There are five built-in datasource controls, all of which can be found in the "Data" portion of the Toolbox in Visual Studio (see the screenshot to the right).
- SqlDataSource - used to retrieve and modify data from a relational database. The "Sql" in the name doesn't mean that this datasource only works with Microsoft SQL Server; rather, it can be used against any relational database: SQL Server, Access, Oracle, and so on. The control is intelligent enough to, internally, use the more efficient SqlClient classes if you are connecting to a SQL Server database.
- AccessDataSource - used to retrieve and modify data from an Access database file. You may be wondering why this control
exists if SqlDataSource can work with Access database files. The AccessDataSource control makes it easier to connect to
an Access database - you just specify the path to the Access database's
.mdbfile. With the SqlDataSource you need to use a fully qualified connection string that specifies the data provider.
- ObjectDataSource - used for retrieving and modifying data through a business object. Ideally your ASP.NET application includes a set of classes that constitute the middle tier (rather than having the ASP.NET pages work directly with the database). If you have such an architecture, the ObjectDataSource can be used to query the middle tier.
- XmlDataSource - used for retrieving data from an XML file.
- SiteMapDataSource - used to provide read-only access to the site structure as defined in the site map. This control is used when you want to display a site's structure in a TreeView or Menu control; for more information on ASP.NET 2.0's site navigation features refer to Creating a Site Navigation Hierarchy.
Start by adding a SqlDataSource control to your ASP.NET page. From the Design view, the SqlDataSource control contains a "smart tag" that lists its common functionality. The Configure Data Source smart tag link will launch a wizard that will step through the process of configuring the data source. There are three main steps to this wizard (and an optional "sub-step"):
- Choose Your Data Connection - in the first step we need to specify what database to connect to. This screen contains
a drop-down list of those databases in the
App_Datafolder and in the Data Connections tab. There's also a New Connection button that you can click if you want to connect to a database not in either of these locations. If you are following along choose the
Customers.mdfoption from the drop-down list.
- Sub-Step: If this is the first time you are adding this connection, you will be prompted if you want
the connection string saved in
Web.configand, if so, what the connection string name should be. If you are following along, I opted to save the connection string in
Web.configwith the name
- Sub-Step: If this is the first time you are adding this connection, you will be prompted if you want the connection string saved in
- Configure the Select Statement - the next step is to specify what records you want to return, what conditional
statements are applied, and the sort order of the returned data. You can accomplish this through the wizard by picking a
table or view and choosing which columns to return, or you can manually enter in a
SELECTstatement. Either way, have the SqlDataSource control return all columns from the
Customerstable - in other words, have it do
SELECT * FROM Customers.
- Test Query - you can run your
SELECTquery against the database to get a preview of the data returned. Feel free to click the Test Query button; you should see all columns and all records from the
A couple things to note: first, the
ConnectionString property uses the new syntax
<%$ ... %>.
This syntax retrieves from the
Web.config the value of the
value. In other words, it grabs that connection string for the
Customers database that we saved in that sub-step of
the SqlDataSource control's wizard; second, the
SELECT query we crafted is specified in the control's
The datasource controls, by themselves, merely work with data. They do not display it. If you visit this ASP.NET page through your browser, you'll find that there's no output generated. In order to view the data returned by the SqlDataSource control we just created, we need to add a data Web control. For this article, let's use the GridView control, which is also found in the "Data" section of the Toolbox. The GridView is the "big brother" of ASP.NET 1.x's DataGrid control, and offers a host of new features. In this article we're going to look at simply binding data to the GridView, saving an examination of its other features for future articles. If you are itching to learn more about the GridView, refer to my GridView Examples for ASP.NET 2.0.
Drag a GridView control onto your ASP.NET page. From the Design view, the GridView's smart tag includes a Choose Data Source
option with a drop-down list of all of the datasource controls on the page. Set this drop-down list to
ID of the SqlDataSource control we just added. Upon doing this, the GridView will automatically have BoundFields
added to it for each of the columns returned by the datasource. (The GridView's BoundField is equivalent to a BoundColumn in the
DataGrid.) Setting the Choose Data Source drop-down list through the GridView's smart tag sets the GridView's
property to the
ID of the datasource control selected.
That's all there is to it! We're done! No need to write a lick of code. A screenshot of the GridView, when viewed through a browser, can be seen below.
Using the GridView and SqlDataSource control we were able to go from a blank page to a page that displayed the contents of a database table in under 30 seconds. In fact, adding paging, bi-directional sorting, deleting, and editing support to the GridView could be added within another 30 seconds, but those will be topics saved for future articles! :-)
Connecting to a Database Programmatically
As we just saw, retrieving information using a SqlDataSource is a snap, but what if you want to programmatically access data? Or maybe you already have code that gets the precise data you want and manipulates it however you need, and its that finished, modified data you want to display in a GridView. No problem, you can access data in 2.0 just like you did in 1.x: by creating a connection to the database, creating a command, specifying the query, populating a DataReader or DataSet, and then binding the results to the data Web control using the control's
The following code show's the
Page_Load event handler for an ASP.NET page that programmatically binds the contents
Customers database table to the GridView control
A couple of things to note here: to get the connection string, we can reference the connection string setting in
Web.config using the syntax
The value for connStringName would be the value specified in the sub-step of the SqlDataSource control wizard
CustomersConnectionString). Also, note that Visual Basic 8 (the version of VB used with 2.0) now supports
Using keyword, a language feature unique to C# in the previous version. (For more on
VB, see An Overview
of Visual Baic 2005.)
The output of the programmatically-accessed data is identical to when using the SqlDataSource control.
In this article we looked at working with databases in ASP.NET 2.0 and Visual Studio 2005. We looked at how to manage databases through Visual Studio, as well as how to add website-specific databases through the
App_Datafolder. Finally, we looked at how to use both the SqlDataSource control and programmatic means to access data. When using a datasource control, bind the data to a GridView control by setting the GridView's
DataSourceIDproperty; when programmatically accessing the data, bind it to the GridView by assigning the data to the GridView's
DataSourceproperty, and by calling the GridView's
DataBind()method. (This latter approach follows the pattern used in ASP.NET 1.x to bind data to a DataGrid.) Regardless of what approach was used for accessing data, the end result, as seen in a user's browser, is the same.