DataSets, DataTables, Data & Table Adapters

With DataReaders you need to be careful about how long you keep your connection open while iterating. Sometimes it is better to pull back your entire result set into memory, release your connection to the database, and then process the result set afterwards. This is where DataSets can be helpful. If you have a batch processing scenario that requires the entire result set in memory or you need to provide end user editing in a grid control, then DataSets are a good place to hold that data.
One of the primary benefits of a DataSet is that it not only caches the data in an efficient memory structure, but it can provide change tracking (what records were added? Deleted? Changed? What were the original values of each column, etc). DataSets work collaboratively with a SqlDataAdapter object, which is simply a composite of 4 SqlCommands (InsertCommand, SelectCommand,
UpdateCommand, and DeleteCommand) mapping to the appropriate CRUD behavior (create, read, update, and delete).
DataSets can contain multiple result sets, each mapped into its own DataTable.
Tables can be related to each other with Relations which mimic foreign keys.
DataSets take care of master-detail linkages and keep everything “hooked up” when inserting records into parent and child tables with identity columns. Each table will require its own SqlDataAdapter. If you are doing a mix of insert,updates, and deletes and your database has referential constraints, you’ll need to orchestrate the changes, making sure parent records are inserted before children and that deletions happen in the reverse order.
DataSets/DataTables come in two flavors: typed and untyped. Untyped DataSets simply infer the table and column definitions after executing the SqlDataAdapter.
Fill method. Accessing the columns must be done similarly to DataReaders using column names in quotes or by positional reference. Typed Datasets on the other hand use wizards and a design surface to generate a strongly typed object. The typed dataset is defined by an XSD document. The XSD is then turned into a strongly typed/named class that essentially sits on top of an untyped dataset. A table mappings collection on the SqlDataAdapter class allows you to use different names for your strongly typed object than that of your database table and column names.
Many people in the .NET 1.0-2.0 era used typed datasets as business entities and wrapped some additional business logic around them. Although you could map column names, you were typically tied tightly to your table design in the database. DataSets can also be converted to and from XML, including a diffgram format that retains pending change information. In many circles, DataSets have and continue to be serialized between distributed layers of an application.
This makes for a pretty productive development environment, but has drawn architectural criticisms compared to properly modeled services with messages and contracts.
In .NET 1.0, DataAdapters and DataSets were different objects that really didn’t know about each other until you executed the DataAdapter Fill or Update method and passed the DataSet/DataTable to the respective method. It was possible for the table mappings in the SqlDataAdapter to get out of sync with the strongly typed dataset. To improve this situation, Microsoft introduced
TableAdapters in .NET 2.0, which are really just DataAdapters that are strongly typed and embedded into the definition of the typed dataset.

Browsing and Modifying Databases in Visual Studio

As an ASP.NET developer, you may have the responsibility of creating the database required for a web application. Alternatively, it may already exist, or it may be the responsibility of a dedicated database administrator. If you’re using a full version of SQL Server, you’ll probably use a graphical tool such as SQL Server Management Studio to create and manage your databases.

If you don’t have a suitable tool for managing your database, or you don’t want to leave the comfort of Visual Studio, you can perform many of the same tasks using Visual Studio’s
Server Explorer window.
Here’s how you can get started. First, choose View äServer Explorer from the Visual Studio menu to show the Server Explorer window. Then, using the Data Connections node in the
Server Explorer, you can connect to existing databases or create new ones. Assuming you’ve installed the pubs database (see the readme.txt file for instructions), you can create a connection to it by following these steps:
1. Right-click the Data Connections node, and choose Add Connection. If the Choose
Data Source window appears, select Microsoft SQL Server and then click Continue.
2. If you’re using a full version of SQL Server, enter localhost as your server name. This indicates the database server is the default instance on the local computer. (Replace this with the name of a remote computer if needed.) If you’re using SQL Server Express, you’ll need to use the server name localhost\SQLEXPRESS instead, as shown in
Figure 15-2. The SQLEXPRESS part indicates that you’re connecting to a named instance of SQL Server. By default, this is the way that SQL Server Express configures itself when you first install it.
3. Click Test Connection to verify that this is the location of your database. If you haven’t installed a database product yet, this step will fail. Otherwise, you’ll know that your database server is installed and running.
4. In the Select or Enter a Database Name list, choose the pubs database. (In order for this to work, the pubs database must already be installed. You can install it using the database script that’s included with the sample code, as explained in the following section.)
If you want to see more than one database in Visual Studio, you’ll need to add more than one data connection.

Using Access Data on an ASP.NET Web Page

You can now use the database in a Web page. This part of the walkthrough uses an AccessDataSource control and a DataList control.

To add AccessDataSource and DataList controls to the page

1. Open the Default.aspx page (or another page that you want to use) and switch to Design view.
2. From the Data group in the toolbox, drag an AccessDataSource control onto the page.

   Note  If the Access Data Source Tasks menu does not appear, right-click the control and then click Show Smart Tag.

3. On the Access Data Source Tasks shortcut menu, click Configure Data Source. The Configure Data Source wizard is displayed.
4. On the Choose a database page, in the Microsoft Access Data file box, type ~/App_Data/Northwind.mdb or use the Browse button to select the .mdb file.
5. Click Next to open the Configure Select Statement page, and then click Specify columns from a table or view.
6. In the Name list, click Categories.
7. Select the CategoryName and Description check boxes and then click Next.
8. Optionally, click Test Query to test your query.
9. Click Finish.
10. From the Data group in the toolbox, drag a DataList control onto the page.
11. On the DataList Tasks menu, in the Choose Data Source box, click AccessDataSource1.
12. Click Ctrl+F5 to run the page with the default layout.
13. Close the browser.

Databinding with Dynamic Languages for ASP.NET

Prerequisites

In order to complete this walkthrough, you will need the following:

• Microsoft Visual Studio 2008 or Visual Web Developer 2008 Express Edition.
• A copy of the website included in the ASP.NET Dynamic Language Support download. There is currently no project template, so it is necessary to copy the website in order to start with a blank ASP.NET Dynamic Language website.
• The Northwind.mdb file that contains the Access version of the sample Northwind database. This file is included in the ASP.NET Quickstarts. If you installed the ASP.NET Quickstarts with Visual Studio, the file is located in the QuickStart\aspnet\samples\data\App_Data folder. Alternatively, you can use another database and adjust the steps in the walkthrough to match the database and tables that you are using.

Creating the Web Site and Page

If you have already created a Web site in Visual Studio (for example, by working with the companion walkthrough Using Dynamic Languages with ASP.NET), you can use that Web site and go to the next part of the walkthrough, where you add an Access database to the project. Otherwise, create a new Web site and page by following these steps.

To create a Web site with a default ASP.NET Web page

1. Copy the files from the ASP.NET Dynamic Language Support project into an empty directory.
2. In Visual Studio (or Visual Web Developer), in the File menu, click Open Web Site. The Open Web Site dialog box is displayed.
3. Select the directory in which you copied the files in step 1. Make sure that FileSystem is selected in the left panel of the dialog.

   Note: You can use statically compiled languages in the same Web application by creating pages and components in different programming languages.

4. Click Open. Visual Studio opens the folder as a website and displays the files in the Solution Explorer.

Next you will add the Northwind.mdb Access database or your own database to the Web application project.

To add an Access database to the project

1. If the Web site does not already contain an App_Data folder, in Solution Explorer, right-click the name of your Web site, click Add ASP.NET Folder, and then click App_Data.
2. In Visual Studio, in Solution Explorer, right-click the App_Data folder, and then click Add Existing Item.
3. In the Add Existing Item dialog box, browse to the Northwind.mdb file, and then click Add.