When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
•Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Feedback
 
Print this Page!
Published: Wednesday, November 9, 2005

Working with Databases in ASP.NET 2.0 and Visual Studio 2005

By Scott Mitchell

Introduction


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!

- continued -

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 YourMachineName\SQLExpress or .\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 App_Data Folder


In addition to adding existing databases through the Data Connections tab, you can also add a site-specific database to the website's App_Data folder. App_Data is a new, reserved folder in ASP.NET 2.0 that is designed to hold data-related content, including SQL database files (.mdf files), Access database files (.mdb files), 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_Data folder 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 App_Data folder called Customers.mdf. Add a single table to this database named Customers with the following columns: CustomerID (an auto-increment primary key field), Name, City, State, and 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 .mdb file. 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.
In this article we'll be examining just the SqlDataSource control, and just its basic functionality.

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"):

  1. 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_Data folder 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.mdf option 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.config and, if so, what the connection string name should be. If you are following along, I opted to save the connection string in Web.config with the name CustomersConnectionString.

  2. 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 SELECT statement. Either way, have the SqlDataSource control return all columns from the Customers table - in other words, have it do SELECT * FROM Customers.
  3. Test Query - you can run your SELECT query 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 Customers table.
Once you have configured the SqlDataSource, take a moment to go to the Source view and inspect the declarative markup used by the SqlDataSource control: 

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
     ConnectionString="<%$ ConnectionStrings:CustomersConnectionString %>"
     SelectCommand="SELECT * FROM [Customers]">
</asp:SqlDataSource>

A couple things to note: first, the ConnectionString property uses the new syntax <%$ ... %>. This syntax retrieves from the Web.config the value of the ConnectionStrings section's CustomersConnectionString 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 SelectCommand property.

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 SqlDataSource1, the 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 DataSourceID 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 DataSource property and DataBind() method.

The following code show's the Page_Load event handler for an ASP.NET page that programmatically binds the contents of the Customers database table to the GridView control gvCustomers: 

Protected Sub Page_Load(ByVal sender As Object, _
                        ByVal e As System.EventArgs) Handles Me.Load
 If Not Page.IsPostBack Then
   'Start by determining the connection string value
   Dim connString As String = _
       ConfigurationManager.ConnectionStrings(connStringName).ConnectionString

   'Create a SqlConnection instance
   Using myConnection As New SqlConnection(connString)
     'Specify the SQL query
     Const sql As String = "SELECT * FROM Customers"

     'Create a SqlCommand instance
     Dim myCommand As New SqlCommand(sql, myConnection)

     'Get back a DataSet
     Dim myDataSet As New DataSet

     'Create a SqlDataAdapter instance
     Dim myAdapter As New SqlDataAdapter(myCommand)
     myAdapter.Fill(myDataSet)

     'Bind the DataSet to the GridView
     gvCustomers.DataSource = myDataSet
     gvCustomers.DataBind()

     'Close the connection
     myConnection.Close()
   End Using
 End If
End Sub

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 ConfigurationManager.ConnectionStrings(connStringName).ConnectionString. 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 the Using keyword, a language feature unique to C# in the previous version. (For more on Using in VB, see An Overview of Visual Baic 2005.)

The output of the programmatically-accessed data is identical to when using the SqlDataSource control.

Conclusion


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_Data folder. 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 DataSourceID property; when programmatically accessing the data, bind it to the GridView by assigning the data to the GridView's DataSource property, 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.

Happy Programming!

  • By Scott Mitchell

    Attachments


  • Download the demo database and code files (in ZIP format)


    • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article