Lesson 1: Accessing Data with ADO.NET

Lesson 1: Accessing Data with ADO.NET

Visual Studio .NET provides access to databases through the set of tools and namespaces collectively referred to as Microsoft ADO.NET. Data access in ADO.NET is standardized to be mostly independent of the source of the data once you ve established a connection to a database, you use a consistent set of objects, properties, and methods, regardless of the type of database you are using.

In this lesson, you ll learn how to use ADO.NET to connect to a database and how to read, modify, and delete records from that database. The lessons in this chapter use the Contacts database (Contacts.mdf) included on the companion CD for all the examples. Be sure to install that database before trying to work with the examples.

After this lesson, you will be able to

• Understand the structure and steps involved in accessing data through ADO.NET

• Connect to a database and create a data set at design time

• Explain how ADO.NET refers to data items using type information

• Add, delete, and modify records in a data set

• Update a database from a data set

• Connect to a database and create a data set at run time

Estimated lesson time: 35 minutes

Understanding ADO.NET

There are four layers to data access in ADO.NET:

• The physical data store

  This can be a SQL, an OLE, or an Oracle database or an Extensible Markup Language (XML) file.

• The data provider

  This consists of the Connection object and command objects that create the in-memory representation of the data.

• The data set

  This is the in-memory representation of the tables and relationships that you work with in your application.

• The data view

  This determines the presentation of a table in the data set. You typically use data views to filter or sort data to present to the user. Each data set has a default data view matching the row order used to create the data set.

The data provider layer provides abstraction between the physical data store and the data set you work with in code. After you ve created the data set, it doesn t matter where the data set comes from or where it is stored. This architecture is referred to as disconnected because the data set is independent of the data store.

Figure 5-1 shows the ADO.NET object model in action.

Figure 5-1. The Connection and DataAdapter objects

There are currently three types of database connection in ADO.NET:

• Use an OleDbConnection object to connect to a Microsoft Access or third-party database, such as MySQL.

  OLE database connections use the OleDbDataAdapter object to perform commands and return data.

• Use a SqlConnection object to connect to a Microsoft SQL Server database.

  SQL database connections use the SqlDbDataAdapter object to perform commands and return data.

• Use an OracleConnection object to connect to Oracle databases.

  Oracle database connections use the OracleDataAdapter object to perform commands and return data. This connection object was introduced in Microsoft .NET Framework version 1.1.

In addition to these database connections, you can access XML files directly from data sets using the DataSet object s ReadXML and WriteXML methods. XML files are static representations of data sets. ADO.NET uses XML for all data transfer across the Internet.

ADO.NET provides its objects, properties, and methods through the three namespaces described in Table 5-1. The System.Data.SqlClient, System.Data.Oracle Client, and System.Data.OleDb namespaces listed in the table provide equivalent features for Microsoft SQL Server and other databases, respectively.

Table 5-1. ADO.NET Namespaces

Namespace	Provides
System.Data	Classes, types, and services for creating and accessing data sets and their subordinate objects
System.Data.SqlClient	Classes and types for accessing Microsoft SQL Server databases
System.Data.OracleClient	Classes and types for accessing Oracle databases (Microsoft .NET Framework version 1.1 and later)
System.Data.OleDb	Classes and types for accessing other databases

When working with databases in code, you need to add the following Imports or using declarations at the top of your code module:

Visual Basic .NET

Imports System.Data ' For Microsoft SQL Server database connections. Imports System.Data.SqlClient ' For Oracle database connections. Imports System.Data.OracleClient ' For other database connections. Imports System.Data.OleDb

Visual C#

using System.Data; // For Microsoft SQL Server database connections. using System.Data.SqlClient; // For Oracle database connections. using System.Data.OracleClient; // For other database connections. using System.Data.OleDb;

To access a database through ADO.NET, follow these steps:

1. Create a connection to the database using a connection object.

2. Invoke a command to create a DataSet object using an adapter object.

3. Use the DataSet object in code to display data or to change items in the database.

4. Invoke a command to update the database from the DataSet object using an adapter object.

5. Close the database connection if you explicitly opened it in step 2 using the Open method. Invoking commands without first invoking the Open method implicitly opens and closes the connection with each request.

The following sections discuss each of these steps in more detail.

Connecting to a Database

Use the Server Explorer to connect to a database in Visual Studio.

To connect to a database in the Visual Studio design environment, follow these steps:

1. From the View menu, choose Server Explorer. Visual Studio displays the Server Explorer window.

2. In the Server Explorer, click Connect To Database on the toolbar. Visual Studio .NET displays the DataLink Properties dialog box shown in Figure 5-2.

   

   Figure 5-2. The DataLink Properties dialog box

3. Click the Provider tab to select the type of database for the connection. By default, the database provider is Microsoft OLE DB Provider For SQL Server. That is the correct selection if your database is running under Microsoft SQL Server. To access a different type of database, select the appropriate provider. For instance, to access a Microsoft Access database directly, select Microsoft Jet 4.0 OLE DB Provider.

4. Click the Connection tab to specify the database to connect to. Changing the database provider in step 3 changes the connection settings, as shown in Figure 5-3.

   

   Figure 5-3. The Connection tab

5. Enter the connection settings and click Test Connection to make sure your settings are correct. Click OK when the connection succeeds. Visual Studio adds the data connection to the Server Explorer, as shown in Figure 5-4.

   

   Figure 5-4. The Server Explorer

6. Clicking the plus signs in the Server Explorer expands items. To view the tables in a data connection, expand the items under the data connection, and then expand the items under Tables. Visual Studio displays the tables, as shown in Figure 5-5.

   

   Figure 5-5. Expanded items in the Server Explorer

7. To add a data item to your application, drag the item from the Server Explorer onto your Web form. For instance, create a new Web Forms project, and drag the Contacts table shown in Figure 5-5 onto the Web form. When you drop the table onto the Web form, Visual Studio creates connection and adapter objects with the appropriate settings, as shown in Figure 5-6.

   

   Figure 5-6. Connection and adapter objects

IMPORTANT
Web applications run using the ASPNET user account. The SQL database administrator will have to set up this account and grant it permissions before your Web application will have access to a SQL database. For file-based databases, such as Microsoft Access, you must grant permissions on the database file to the ASPNET user account using Windows file security settings.

Creating a Data Set

Use the data connection and adapter objects created in step 7 in the preceding section to create a data set.

To create a data set in Design mode, follow these steps:

1. Right-click the data adapter object, and select Generate Dataset from the shortcut menu. Visual Studio displays the Generate Dataset dialog box, as shown in Figure 5-7.

   

   Figure 5-7. The Generate Dataset dialog box

2. Select the Contacts table to add to the data set, and click OK. Visual Studio creates a new DataSet object and adds it to the Web form.

To view the data in the data set in Design mode, right-click the DataSet object, and then select View Schema from the shortcut menu. Visual Studio displays the data set in the XML Designer window, as shown in Figure 5-8.

Figure 5-8. Data set schema in Design mode

Displaying a Data Set

To display the data set on the Web form at run time, follow these steps:

1. Add a control to the Web form to display the data. For instance, add a DataGrid control to the Web form.

2. Set the data set as the data source for the control. For instance, for the DataGrid control, click the Property Builder link in the Properties window, set the DataSource property to the DataSet object, and set the DataMember property to a table in the data set, as shown in Figure 5-9.

   

   Figure 5-9. Data binding on a DataGrid control

3. Set the columns to display in the control. For the DataGrid control, click the Columns item in the Properties dialog box, clear the Create Columns Automatically At Run Time check box, and then add the columns to display from the Available Columns list, as shown in Figure 5-10. Click OK when you have finished.

   

   Figure 5-10. Adding columns to display in the DataGrid control

4. Add code to the Web form s Page_Load event procedure to fill the data set from the data adapter and to bind the data from the DataSet object to the control. For example, the following code displays the data in the DataGrid control created in the preceding steps:

   Visual Basic .NET

   Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Fill the data set. SqlDataAdapter1.Fill(DataSet1) ' Update the DataGrid. DataGrid1.DataBind() End Sub

   Visual C#

   private void Page_Load(object sender, System.EventArgs e) { // Fill the data set. sqlDataAdapter1.Fill(DataSet1); // Update the DataGrid. dataGrid1.DataBind(); }

5. Run the application. When the page loads, the data set is displayed, as shown in Figure 5-11.

   

   Figure 5-11. The DataGrid control at run time

TIP
Because you are filling the data set and binding the data to the DataGrid control in the Page_Load event procedure, you don t need to maintain state information for the DataGrid control. Turning off state information improves performance because the data in the DataGrid control does not have to be saved to the page s ViewState between requests. Set the DataGrid control s EnableViewState property to False to turn off state maintenance for the control.

Creating a Custom Data View

The preceding section displays the default view of the data set in a DataGrid control, which is the quickest way to display data. However, you ll often want to sort and filter data within the DataGrid control. To do that, you ll need to bind the DataGrid to the DataSet object s DataView property.

To create and display a data view in Design mode:

1. Drag a DataView control from the Data Controls tab of the Toolbox to the Web form s Design window.

2. Set the Table property of the DataView control to one of the tables in a DataSet object.

3. Add a DataGrid control to the form and set its DataSource property to the DataView control created in step 1.

After you ve created a data view in Design mode, you can use the data view to change the sort order or apply filters to data displayed in a DataGrid control. For example, the following procedures change the sort order and apply a filter to a data view displayed in a DataGrid control:.

Visual Basic .NET

Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Fill data set. SqlDataAdapter1.Fill(DataSet11) ' Bind to data grid. DataGrid1.DataBind() End Sub Private Sub DataGrid1_SortCommand(ByVal source As Object, _ ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) _ Handles DataGrid1.SortCommand ' Change the sorting in the data view. DataView1.Sort = e.SortExpression ' Bind to display the new view. DataGrid1.DataBind() End Sub Private Sub butFilter_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles butFilter.Click ' Apply a filter. DataView1.RowFilter = "FirstName = 'New'" End Sub

Visual C#

// From Web Form Designer generated region. private void InitializeComponent() { // generated statements omitted here... this.Load += new System.EventHandler(this.Page_Load); this.butFilter.Click += new System.EventHandler(this.butFilter_Click); this.dataGrid1.SortCommand += new DataGridSortCommandEventHandler(dataGrid1_SortCommand); } private void Page_Load(object sender, System.EventArgs e) { // Fill data set. SqlDataAdapter1.Fill(DataSet11); // Bind to data grid. DataGrid1.DataBind(); } private void dataGrid1_SortCommand(object source, DataGridSortCommandEventArgs e) { // Change the sorting in the data view. dataView1.Sort = e.SortExpression; } private void butFilter_Click(object sender, System.EventArgs e) { // Apply a filter. dataView1.RowFilter = "FirstName = 'New'"; }

Changing Records in a Database

The DataSet object is the central object in ADO.NET. Any additions, deletions, or changes to records in a database are generally done through a DataSet object.

To change records through a DataSet object, follow these steps:

1. Get a DataSet object as described in the preceding sections.

2. Modify the DataSet object.

3. Update the database from the DataSet object by calling the data adapter s Update method.

You use the Tables, Rows, and Columns collections to get to data items in a DataSet object, as shown in Figure 5-12.

Figure 5-12. DataSet collections

How ADO.NET Refers to Objects

When you create connection, adapter, and data set objects in Design mode, you enable data typing for those objects. This means that you can use the specific names from the database schema to identify tables, rows, and fields. This is a big change from Microsoft ActiveX Data Objects (ADO), which provided only untyped references to data objects.

The following equivalent lines of code show a typed reference vs. an untyped reference to an object from a database:

Visual Basic .NET

' Typed reference to the Contacts table's HomePhone column. DataSet1.Contacts.HomePhoneColumn.Caption = "@Home" ' Untyped reference to the Contacts table's HomePhone column. DataSet1.Tables("Contacts").Columns("HomePhone").Caption = "@Home"

Visual C#

// Typed reference to the Contacts table's HomePhone column. dataSet1.Contacts.HomePhoneColumn.Caption = "@Home"; // Untyped reference to the Contacts table's HomePhone column. dataSet1.Tables["Contacts"].Columns["HomePhone"].Caption = "@Home";

The first line in the preceding code is not only easier to type and to read, but also much less error prone, because Visual Studio checks data types as you work, flagging any typos as unrecognized references.

In general, you should use typed references when working with data objects. The exception to this rule occurs when you don t know the specific object you are working with. Usually, this situation arises when the data source is supplied at run time rather than at design time.

Type information for data objects comes from the XML Schema that Visual Studio generates when you create a data set in Design mode.

To view type information for a data set, right-click the data set in the Design window and select View Schema from the shortcut menu. Visual Studio displays the data set in the XML Designer, as shown in Figure 5-13.

Figure 5-13. Viewing type information

You can use the XML Designer to add elements, specify unique keys, and view or change the data types of elements in a data set.

Adding, Changing, and Deleting Rows

Use the Rows collection to add, change, or delete rows in the DataSet object s Table object. To add a record to a data set, create a new Row object and add it to the DataSet object s Rows collection, as shown in the following code:

Visual Basic .NET

' Uses connection, adapter, and data set created in Design mode. Private Sub butAddRow_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles butAddRow.Click ' Create a new row object for the Contacts table. Dim rowNew As DataSet1.ContactsRow = DataSet1.Contacts.NewRow ' Add data to the columns in the row. rowNew.ContactID = m_NextID rowNew.FirstName = "New" rowNew.LastName = "User " + m_NextID.ToString() rowNew.WorkPhone = "[phone]" ' Add the row to the data set. DataSet1.Contacts.Rows.Add(rowNew) End Sub

Visual C#

// Uses connection, adapter, and data set created in Design mode. private void butAdd_Click(object sender, System.EventArgs e) { // Create a new row object for the Contacts table. DataSet1.ContactsRow rowNew = dataSet1.Contacts.NewContactsRow(); // Add data to the columns in the row. rowNew.ContactID = m_NextID; rowNew.FirstName = "New"; rowNew.LastName = "User " + m_NextID.ToString(); rowNew.WorkPhone = "[phone]"; // Add the row to the data set. dataSet1.Contacts.AddContactsRow(rowNew); }

To change a row in a data set, get a Row object from the table using the FindBy method, and then make changes to the fields in the row, as shown in the following code:

Visual Basic .NET

' Uses connection, adapter, and data set created in Design mode. Private Sub butChangeRow_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles butChangeRow.Click ' Declare a row object. Dim rowChange As DataSet1.ContactsRow ' Get the last row using the primary key. rowChange = DataSet1.Contacts.FindByContactID(m_NextID - 1) ' Change a field in the row. rowChange.WorkPhone = "[phone]" End Sub

Visual C#

// Uses connection, adapter, and data set created in Design mode. private void butChange_Click(object sender, System.EventArgs e) { // Declare a row object. DataSet1.ContactsRow rowChange; // Get the row to change using the primary key. rowChange = dataSet1.Contacts.FindByContactID(m_NextID - 1); // Change a field in the row. rowChange.WorkPhone = "[phone]"; }

To delete a row in a data set, get a Row object from the table using the FindBy method, and then delete the row using the Row object s Delete method, as shown here:

Visual Basic .NET

' Uses connection, adapter, and data set created in Design mode. Private Sub butDeleteRow_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles butDeleteRow.Click ' Declare a row object. Dim rowDelete As DataSet1.ContactsRow ' Get the last row rowDelete = DataSet1.Contacts.FindByContactID(m_NextID - 1) ' Delete the row. rowDelete.Delete() End Sub

Visual C#

// Uses connection, adapter, and data set created in Design mode. private void butDelete_Click(object sender, System.EventArgs e) { // Declare a row object. MCSDWebAppsCS.Chapter05.DataSet1.ContactsRow rowDelete; // Get the row to delete rowDelete = dataSet1.Contacts.FindByContactID(m_NextID - 1); // Delete the row. rowDelete.Delete(); }

Updating the Database from the DataSet

Use the data adapter s Update method to update the database with changes from a DataSet object. You will usually want to do this after all of the control events on a page have been processed; therefore, the Update method is usually called from the Page_PreRender event procedure, as shown here:

Visual Basic .NET

' Uses connection, adapter, and data set created in Design mode. Private Sub Page_PreRender(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles MyBase.PreRender ' Update the database with changes from the data set. SqlDataAdapter1.Update(DataSet1) ' Rebind to reflect the data set changes in the DataGrid DataGrid1.Bind() End Sub

Visual C#

// From Web Designer generated code region. private void InitializeComponent() { // generated code omitted... this.PreRender += new EventHandler(Page_PreRender); } // Uses connection, adapter, and data set created in Design mode. private void Page_PreRender(object sender, EventArgs e) { // Update the database with changes from the data set. sqlDataAdapter1.Update(dataSet1); // Refresh data grid just before the page is displayed. DataGrid1.DataBind(); }

When you update a database from a data set, ADO.NET follows these steps:

1. Determines the changes to the data set by checking each DataRow object s RowState property. Possible values are Added, Deleted, Modified, Unchanged, or Detached.

2. Invokes the adapter object s InsertCommand, DeleteCommand, or UpdateCommand properties to make the required changes in the database. These operations automatically open the database connection and close it when finished.

3. Resets the updated DataRow objects RowState properties to Unchanged.

The adapter object s InsertCommand, DeleteCommand, and UpdateCommand properties are generated automatically from the adapter object s SelectCommand when you create a data set from a data adapter in Design mode. Each of these properties represents an OleCommand or SqlCommand object. Command objects have the following properties that determine how the command is executed:

• The CommandText property contains the SQL statement or the stored procedure name to perform the command.

• The CommandType property determines how the command is performed by the following possible settings:

  • The StoredProcedure setting executes the command as a procedure stored in the database.

  • The Text setting executes the command as a SQL statement. (This is the default.)

  • The TableDirect setting returns the entire table. This setting applies only to OLE DB .NET data providers.

You can change the contents of the data set and how the data set is updated by changing the SQL statements used by these command objects.

To view or modify these commands, follow these steps:

1. In the Properties window, double-click the command property you want to change. This expands the list of properties that apply to the command object, as shown in Figure 5-14.

   

   Figure 5-14. Viewing and modifying SQL commands

2. Select the CommandText property for the command, and then click the ellipsis button to the right of the property setting. Visual Studio displays the Query Builder dialog box, as shown in Figure 5-15.

   

   Figure 5-15. The Query Builder dialog box

3. Select the columns to include in the command by selecting the check boxes next to the data field names in the table pane of the Query Builder dialog box, or type the SQL statement directly in the command pane of the Query Builder. When you have finished, click OK to close the dialog box.

Creating a Database Connection at Run Time

Creating data objects in Design mode is a great way to learn about data access in ADO.NET because Visual Studio generates the connection and adapter object property settings for you. Some of the property settings, such as ConnectionString, can be difficult and confusing to construct without the help of Design mode. After you ve created a connection in Design mode, you can cut and paste the property settings to create similar connections in code.

Using data-access objects in code follows the same sequence of events as it does in Design mode:

1. Create the data connection object.

2. Create a data adapter object.

3. Create a data set object.

4. Invoke methods on the adapter object to fill or update the data set.

5. Use data binding or another technique to display the data from the data set.

For example, the following code creates data objects and displays data from a Microsoft SQL data provider:

Visual Basic .NET

Dim m_adptContactMgmt As SqlDataAdapter Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' (1) Create the data connection. Dim ContactMgmt As New SqlConnection _ ("server=(local);database=Contacts;Trusted_Connection=yes") ' (2) Create a data adapter. m_adptContactMgmt = New _ SqlDataAdapter("select * from Contacts", ContactMgmt) ' (3) Create a data set. Dim dsContacts As New DataSet ' (4) Fill the data set. m_adptContactMgmt.Fill(dsContacts, "Contacts") ' (5) Display the table in a data grid using data binding. DataGrid1.DataSource = dsContacts.Tables("Contacts").DefaultView DataGrid1.DataBind() End Sub

Visual C#

SqlDataAdapter m_adptContactMgmt; private void Page_Load(object sender, System.EventArgs e) { // (1) Create the data connection. SqlConnection ContactMgmt = new SqlConnection("server=(local);" +  "database=Contacts;Trusted_Connection=yes"); // (2) Create a data adapter. m_adptContactMgmt = new SqlDataAdapter("select * from Contacts", ContactMgmt); // (3) Create a data set. DataSet dsContacts = new DataSet(); // (4) Fill the data set. m_adptContactMgmt.Fill(dsContacts, "Contacts"); // (5) Display the table in a data grid using data binding. DataGrid1.DataSource = dsContacts.Tables["Contacts"].DefaultView; DataGrid1.DataBind(); }

Updating a Run-Time Database Connection

As mentioned earlier in this section, ADO.NET uses the adapter object s InsertCommand, DeleteCommand, and UpdateCommand properties to update the database from the data set. When you create a data adapter at run time, you need to generate these properties before you can call the adapter object s Update method.

To generate InsertCommand, DeleteCommand, and UpdateCommand properties at run time, follow these steps:

1. Set the adapter object s SelectCommand. The SelectCommand is set by the data adapter s constructor, or it can be set using the SelectCommand property. ADO.NET uses the SelectCommand s CommandText property to generate the settings for the InsertCommand, DeleteCommand, and UpdateCommand properties.

2. Create a command builder object for the adapter object. The command builder object is dependent on the data provider, so it has three forms: SqlCommandBuilder, OracleCommandBuilder, and OleDbCommandBuilder.

The following code creates a database connection, data adapter, and data set in code and then displays that data set in a DataGrid (1 through 5). Next the code adds a row to the data set (6), creates a command builder for the adapter object (7), and then uses the dynamically created commands to update the database in the Page_PreRender event procedure (8):

Visual Basic .NET

Dim m_NextID As Integer Dim m_dtContacts As DataTable Dim m_adptContactMgmt As SqlDataAdapter Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' (1) Create the data connection. Dim ContactMgmt As New SqlConnection _ ("server=(local);database=Contacts;Trusted_Connection=yes") ' (2) Create a data adapter. m_adptContactMgmt = New _ SqlDataAdapter("select * from Contacts", ContactMgmt) ' (3) Create a data set. Dim dsContacts As New DataSet ' (4) Fill the data set. m_adptContactMgmt.Fill(dsContacts, "Contacts") ' Get the data table m_dtContacts = dsContacts.Tables("Contacts") ' Create a primary key on data table. m_dtContacts.PrimaryKey = New _ DataColumn() {m_dtContacts.Columns("ContactID")} ' Get a new, unique row ID. m_NextID = GetNewID(m_dtContacts) ' (5) Display the table in a data grid using data binding. DataGrid1.DataSource = m_dtContacts.DefaultView End Sub Private Sub butAdd_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles butAdd.Click ' (6) Create and add a new row. ' Create a new row object. Dim rowInsert As DataRow = m_dtContacts.NewRow ' Add data to fields in the row. rowInsert("ContactID") = m_NextID rowInsert("FirstName") = "New" rowInsert("LastName") = "User " + m_NextID.ToString() rowInsert("WorkPhone") = "[phone]" ' Add the row to the data set. m_dtContacts.Rows.Add(rowInsert) End Sub Private Sub Page_PreRender(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles MyBase.PreRender ' (7) Create insert, delete, and update commands automatically. Dim cmdContactMgmt As SqlCommandBuilder = New _ SqlCommandBuilder(m_adptContactMgmt) ' (8) Update the database. m_adptContactMgmt.Update(m_dtContacts) ' Bind data to DataGrid to update the display. DataGrid1.DataBind() End Sub ' Helper function to get a new, valid row ID. Function GetNewID(ByVal dt As DataTable) As Integer ' Get a new row number. Dim NextID As Integer = dt.Rows.Count + 1 ' If it isn't found in the table, return it. If IsNothing(dt.Rows.Find(NextID)) Then Return NextID ' Otherwis, check for free IDs between 1 and the row count. For NextID = 1 To dt.Rows.Count ' Check if this ID already exists. If IsNothing(dt.Rows.Find(NextID)) Then Return NextID End If Next ' Failed, return zero. Return 0 End Function

Visual C#

int m_NextID; DataTable m_dtContacts; SqlDataAdapter m_adptContactMgmt; private void Page_Load(object sender, System.EventArgs e) { // (1) Create the data connection. SqlConnection ContactMgmt = new SqlConnection("server=(local);" +  "database=Contacts;Trusted_Connection=yes"); // (2) Create a data adapter. m_adptContactMgmt = new SqlDataAdapter("select * from Contacts", ContactMgmt); // (3) Create a data set. DataSet dsContacts = new DataSet(); // (4) Fill the data set. m_adptContactMgmt.Fill(dsContacts, "Contacts"); // Get the data table m_dtContacts = dsContacts.Tables["Contacts"]; // Create a primary key on data table. m_dtContacts.PrimaryKey = new DataColumn[] {m_dtContacts.Columns["ContactID"]}; // Get a new, unique row ID. m_NextID = GetNewID(m_dtContacts); // (5) Display the table in a data grid using data binding. DataGrid1.DataSource = m_dtContacts.DefaultView; } private void butAdd_Click(object sender, System.EventArgs e) { // (6) Create a new row object for the Contacts table. DataRow rowNew = m_dtContacts.NewRow(); // Add data to the columns in the row. rowNew["ContactID"] = m_NextID; rowNew["FirstName"] = "New"; rowNew["LastName"] = "User " + m_NextID.ToString(); rowNew["WorkPhone"] = "[phone]"; // Add the row to the data set. m_dtContacts.Rows.Add(rowNew); } private void Page_PreRender(object sender, EventArgs e) { // (7) Create insert, delete, and update commands automatically. SqlCommandBuilder cmdContactMgmt = new SqlCommandBuilder(m_adptContactMgmt); // (8) Update the database. m_adptContactMgmt.Update(m_dtContacts); // Bind data to DataGrid to update the display. DataGrid1.DataBind(); } // Helper function to get a new, valid row ID. int GetNewID(DataTable dt) { // Get a new row number. int NextID = dt.Rows.Count + 1; // If it isn't found in the table, return it. if (dt.Rows.Find(NextID) == null) return NextID; // Otherwis, check for free IDs between 1 and the row count. for(NextID = 1; NextID <= dt.Rows.Count; NextID++) { // Check if this ID already exists. if (dt.Rows.Find(NextID) == null) return NextID; } // Failed, return zero. return 0; }

The Update method in the preceding code requires that you create the SqlCommandBuilder object first. Creating that object provides the settings for the InsertCommand property that the adapter object uses to add the new row. The Update method automatically opens the database connection before making changes and closes it when finished.

Storing Multiple Tables and Caching Data Sets

So far, we ve used data sets to store a single data table. This is convenient because Visual Studio can automatically generate the appropriate Select, Update, Delete, and Insert command objects for data adapter objects that create a data set containing one data table. However, data sets can just as easily contain multiple tables, relations between tables, and constraints.

Use the Add method to add tables to a data set. Remember that ASP.NET throws away Web form variables after the form is displayed, so you ll usually want to save data sets as Application, Session, or Cache variables so that you don t have to re-create them each time the form is displayed.

The Cache object is uniquely useful when you re working with data sets because it allows you to specify an expiration for the data it contains. Data sets can be large, and you usually don t want to leave them in memory indefinitely. As with Application state, all code within the application has access to data in the Cache object.

For example, the following code creates a data set containing two data tables, establishes a relationship between the tables, and stores the data set in the application cache:

Visual Basic .NET

' Data adapter, combines SQL command and connection string. Dim adptDB As New SqlDataAdapter("SELECT * FROM Contacts", _  "server=(local);database=Contacts;Trusted_Connection=yes") ' Data set to contain two data tables Public dsBoth As New DataSet Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Create Cache if this is the first time the page is displayed ' or if the Cache object doesn't yet exist. If Not (IsPostBack) Or IsNothing(Cache.Get("dsBoth")) Then ' Create the Contacts table. Dim Contacts As New DataTable("Contacts") adptDB.Fill(Contacts) ' Change the adapter's SELECT command. adptDB.SelectCommand.CommandText = "SELECT * FROM Calls" ' Create the Calls table. Dim Calls As New DataTable("Calls") adptDB.Fill(Calls) ' Add both tables to a single dataset. dsBoth.Tables.Add(Calls) dsBoth.Tables.Add(Contacts) ' Cache data set for 20 minutes. Cache.Add("dsBoth", dsBoth, Nothing, DateTime.MaxValue, _ System.TimeSpan.FromMinutes(20), _ Caching.CacheItemPriority.Default, Nothing) ' Bind the drop-down list to display data. drpContacts.DataBind() Else ' If this is post-back get the Cached data set. dsBoth = Cache("dsBoth") End If End Sub Private Sub drpContacts_SelectedIndexChanged(ByVal sender As _ System.Object, ByVal e As System.EventArgs) _ Handles drpContacts.SelectedIndexChanged ' Set a filter on the view of the Calls table. dsBoth.Tables("Calls").DefaultView.RowFilter = "Contactcodeblock_unnumbered">
Visual C#
 
// Data adapter, combines SQL command and connection string. SqlDataAdapter adptDB =new SqlDataAdapter("SELECT * FROM Contacts",  "server=(local);database=Contacts;Trusted_Connection=yes"); // Data set to contain two data tables public DataSet dsBoth = new DataSet("Both"); private void Page_Load(object sender, System.EventArgs e) { // Create Cache if this is the first time the page is displayed // or if the Cache object doesn't yet exist. if ((!IsPostBack) (Cache.Get("dsBoth") == null)) { // Create the Contacts table. DataTable Contacts = new DataTable("Contacts"); adptDB.Fill(Contacts); // Change the adapter's SELECT command. adptDB.SelectCommand.CommandText = "SELECT * FROM Calls"; // Create the Calls table. DataTable Calls = new DataTable("Calls"); adptDB.Fill(Calls); // Add both tables to a single dataset. dsBoth.Tables.Add(Calls); dsBoth.Tables.Add(Contacts); // Cache data set for 20 minutes. Cache.Add("dsBoth", dsBoth, null, DateTime.MaxValue, System.TimeSpan.FromMinutes(20), System.Web.Caching.CacheItemPriority.Default, null); // Bind the drop-down list to display data. drpContacts.DataBind(); } else // If this is post-back get the Cached data set. dsBoth = (DataSet) Cache["dsBoth"]; } private void drpContacts_SelectedIndexChanged(object sender, System.EventArgs e) { // Set a filter on the view of the Calls table. dsBoth.Tables["Calls"].DefaultView.RowFilter = "Contactnormal">The preceding code creates the data set once, the first time anyone visits the page. The data set is then maintained in memory for 20 minutes after it was last accessed at which time, it is discarded. This produces very quick response time for most requests but doesn t tie up the server memory with unused data.
 
Also, by combining both data tables in a single data set, you ensure that both items exist in the Cache object. If you store the data tables separately, it s possible that one item might be discarded before the other.