Becoming Familiar with ADO.NET Operations | .NET Web Services Solutions

Across the Web, many database applications make use of Active Data Objects (ADO) to simplify a program’s interactions with a database. Using ADO, programs can easily connect to and interact with remote databases. As you will learn, the Microsoft .NET environment further enhances database access using the ADO.NET model.

For years, programmers used ADO objects to manipulate databases using a RecordSet object. In general a record set is simply an object that provides a program with access to the data stored within a database. Using the RecordSet object, a program can query a database. Behind the scenes, the ADO software will assign the query’s result to the RecordSet. Then, the program can use the RecordSet object to manipulate the query’s result.

Within the ADO.NET environment, programmers can use a DataSet object to interact with a database. The advantage of the DataSet object over the RecordSet is that programs that use the DataSet object do not have to maintain a constant connection to the database. Instead, the programs can use the DataSet object to query the database and then disconnect from the database. Later, after the program manipulates the data that the DataSet object contains, the program can reconnect to the database and update the database contents as required. The ability of a program to disconnect from the database, process data, and reconnect to the database makes the DataSet object very well suited for use on the Web.

As you have learned throughout this book, behind the scenes, web services make extensive use of XML-based content. In a similar way, the ADO.NET environment uses XML to package the data a program exchanges with a remote database.

The following sections will get you up and running with ADO.NET. To start, you will examine the ADO.NET data provider—special software that your program uses to interact with a database. Next, you will examine the steps you must perform within your program to connect to a database using ADO.NET. Then, you will learn how to perform simple queries against a database. You can use the steps these sections provide to perform most of the database operations you will require to support web services that need database support. The programs this chapter presents use the Duwamish7vb database provided with Visual Studio .NET.

Understanding the Data Provider

Within ADO.NET, programmers refer to the software that interacts with the remote database as the data provider. The data provider consists of four key objects:

Connection object Connects a program to a database

Command object Lets a program query the database

DataReader object Provides a program with stream-based read-only access to the database

DataAdapter object Provides a program with the ability to query and later update the database contents

ADO.NET supports two data providers:

SQL Server .NET Data Provider
OLE DB.NET Data Provider

In general, you can think of the data provider as the source of the database data that your programs manipulate.

Understanding the Steps to Access a Database

To access a database, client programs will perform the following steps:

Connect to the database using a Connection object.
Query the database using either a DataReader (for read-only access) or a DataSet object.
Optionally update the database using a DataAdapter object.
End the connection using the Connection object.

The Visual Basic .NET program in Listing 7.1, ConnectDemo.vb, connects to the Duwamish7vb database provided with Visual Studio .NET. When you run the program, your screen will display a form that contains a Connect button. After you click the Connect button, the program will use the SqlConnection method to connect to the database. The program will then display specifics about the database—such as its name, version number, and packet size—within the form, as shown in Figure 7.1.

Figure 7.1: Using ADO.NET to connect to a database

To create the ConnectDemo.vb program, perform these steps:

Within Visual Studio .NET, select the File menu New Project option. Visual Studio .NET will display the New Project dialog box.
Within the New Project dialog box Project Types list, click Visual Basic Projects. Then, within the Templates field, click Windows Application. Finally, within the Location field, specify the folder within which you want to store the program and within the Name field type the program name ConnectDemo. Select OK. Visual Studio .NET will display a page onto which you can drag and drop the program’s controls.
Using the Toolbox, drag and drop the button and text box previously shown in Figure 7.1 onto the page. Set the text box multiline attribute to true.
Select the View menu Code option. Visual Studio .NET will display the program’s source code. Within the source code add the program statements in Listing 7.1.

Listing 7.1 ConnectDemo.vb

Imports System.Data.SqlClient Public Class Form1     Inherits System.Windows.Forms.Form #Region " Windows Form Designer generated code "   ' Code not shown #End Region     Private Sub Button1_Click(ByVal sender As System.Object, _ Ä   ByVal e As System.EventArgs) Handles Button1.Click         Dim Connection As _ Ä       New SqlConnection("Initial catalog=Duwamish7vb;" & _             "Data Source=(local); User ID=sa; password=;")         Connection.Open()         TextBox1.Text = "Datebase name: " & Connection.Database         TextBox1.Text = TextBox1.Text & vbCrLf         TextBox1.Text = TextBox1.Text + "Data source: " & _ Ä        Connection.DataSource         TextBox1.Text = TextBox1.Text & vbCrLf         TextBox1.Text = TextBox1.Text + "Server version: " & _ Ä        Connection.ServerVersion         TextBox1.Text = TextBox1.Text & vbCrLf         TextBox1.Text = TextBox1.Text + "Packet size: " & _ Ä        Connection.PacketSize         TextBox1.Text = TextBox1.Text & vbCrLf         TextBox1.Text = TextBox1.Text + "Workstation: " & _ Ä        Connection.WorkstationId         TextBox1.Text = TextBox1.Text & vbCrLf     End Sub End Class

The program begins by using the SqlConnection object to specify information about the database the program will use, such as its name, location, and authentication data (username and password). Next, the program uses the Open method to establish a connection to the database. After the connection exists, the program uses the connection object to display specifics about the database within a text box.

Note

Note that the program’s first statement imports the System.Data.SQLClient namespace. Many of the programs this chapter presents must import the namespace before the programs can use the classes and methods the namespace defines. As you enter this chapter’s source code, pay attention to the namespaces the programs import.

The Duwamish7vb database contains information about books, authors, and publishers. The C# program in Listing 7.2, DataReaderDemo.cs, uses a DataReader object to perform a read-only query of the database. When you run the program, it will display a form that contains a List Authors button. If you click the button, the program will retrieve the names of

the authors that appear in the database, displaying each author’s name within the text box, as shown in Figure 7.2.

Figure 7.2: Using a DataReader object to perform read-only queries

To create the DataReaderDemo.cs program, perform these steps:

Within Visual Studio .NET, select the File menu New Project option. Visual Studio .NET will display the New Project dialog box.
Within the New Project dialog box Project Types list, click Visual C# Projects. Then, within the Templates field, click Windows Application. Finally, within the Location field, specify the folder within which you want to store the program and the program name DataReaderDemo. Select OK. Visual Studio .NET will display a page onto which you can drag and drop the program’s controls.
Using the Toolbox, drag and drop the button and text box previously shown in Figure 7.2 onto the page, setting the text box multiline property to true.
Select the View menu Code option. Visual Studio .NET will display the program’s source code. Within the source code add the program statements in Listing 7.2.

Listing 7.2 DataReaderDemo.cs

using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace DataReaderDemo {    // Code generated by Visual Studio .NET not shown    private void button1_Click(object sender, System.EventArgs e)    {       string CrLf = "\r\n";       SqlConnection Connection = new Ä     SqlConnection("Initial catalog=Duwamish7vb;" +           "Data Source=(local); User ID=sa; password=;");       Connection.Open();       SqlCommand Query = new SqlCommand("SELECT * From Authors", Ä     Connection);       SqlDataReader DataReaderObj = Ä      Query.ExecuteReader(CommandBehavior.CloseConnection);       while (DataReaderObj.Read())         textBox1.Text = textBox1.Text + DataReaderObj.GetSqlValue(1) + Ä         CrLf;    } }

Before a program can query a database, the program must establish a connection to the database. In this case, the program establishes the connection using a SqlConnection object. Then, the program creates a SqlCommand object named Query that contains the query the program will perform (the SELECT statement). The program then uses the object’s Execute method to perform the query, assigning the result to a DataReader object. As discussed, a DataReader object provides read-only access to the results of a database query. The program then uses the DataReader object’s Read method within a While loop to read the query’s data one row at a time.

As briefly discussed, ADO.NET improves on ADO by providing a DataSet object programs can use to interact with a remote database, without the need to maintain a constant connection to the database. The C# program in Listing 7.3, DataSetDemo.cs, illustrates the use of a DataSet object to query a database. When you run the program, it will display a form that contains a List Publishers button. When you click the button, the program will use a DataSet object to query the database, displaying a list of publishers within a text box, as shown in Figure 7.3.

Figure 7.3: Using an ADO.NET DataSet object to query a database

To create the DataSetDemo.cs program, perform these steps:

Within Visual Studio .NET, select the File menu New Project option. Visual Studio .NET will display the New Project dialog box.
Within the New Project dialog box Project Types list, click Visual C# Projects. Then, within the Templates field, click Windows Application. Finally, within the Location field, specify the folder within which you want to store the program and the program name DataSetDemo. Select OK. Visual Studio .NET will display a page onto which you can drag and drop the program’s controls.
Using the Toolbox, drag and drop the button and text box previously shown in Figure 7.3 onto the page, setting the text box multiline property to true.
Select the View menu Code option. Visual Studio .NET will display the program’s source code. Within the source code add the program statements in Listing 7.3.

Listing 7.3 DataSetDemo.cs

using System.Data.SqlClient; private void button1_Click(object sender, System.EventArgs e) {    string CrLf = "\r\n";    SqlConnection Connection = new Ä     SqlConnection("Initial catalog=Duwamish7vb;" +           "Data Source=(local); User ID=sa; password=;");       string Query = "SELECT * From Publishers";       DataSet DataSetObj = new DataSet();       SqlDataAdapter Adapter = new SqlDataAdapter(Query, Connection);       Adapter.Fill(DataSetObj);       for (int i = 0; i < DataSetObj.Tables[0].Rows.Count; ++i)         textBox1.Text = textBox1.Text + Ä       DataSetObj.Tables[0].Rows[i].ItemArray[1] + CrLf;       Connection.Close(); }

Using a DataSet object, a program can read or update database data. The DataSet object can perform these operations without maintaining a constant connection to the database. To begin, the program must establish a connection to the database. Then, the program creates a string that contains the database query and a DataSet object that will hold the query’s result. Next, the program creates an Adapter object that the program will use to interact with the database. Using the Adapter object, the code then fills the DataSet object with the query’s result. The DataSet object stores the query’s result as arrays that hold one or more tables, rows within each table, and columns within each row. In this case, the program then uses a For loop to display the query’s result one row at the time. With each iteration of the loop, the code displays only the column that contains the publisher name.

Using a DataSet Object to Update a Database

As discussed, using a DataSet object, a program can read or update the contents of a database. The program in Listing 7.4, UpdateDatabase.vb, lets you change the names of the publishers within the database by converting the characters to uppercase or lowercase. When you run the program, your screen will display a form that contains two buttons. If you click the Uppercase button, the program will convert the publisher names to uppercase characters. Likewise, if you click the Lowercase button, the program will convert the names to lowercase. To verify that the names have been changed, click the List Publishers button to query the database. The program, in turn, will display the names, as shown in Figure 7.4.

Figure 7.4: Using a DataAdapter object to update data within a database

To create the UpdateDatabase.vb program, perform these steps:

Within Visual Studio .NET, select the File menu New Project option. Visual Studio .NET will display the New Project dialog box.
Within the New Project dialog box Project Types list, click Visual Basic Projects. Then, within the Templates field, click Windows Application. Finally, within the Location field, specify the folder within which you want to store the program and the program name UpdateDatabase. Select OK. Visual Studio .NET will display a page onto which you can drag and drop the program’s controls.
Using the Toolbox, drag and drop the buttons and text box previously shown in Figure 7.4 onto the page, setting the text box multiline property to true.
Select the View menu Code option. Visual Studio .NET will display the program’s source code. Within the source code add the program statements in Listing 7.4.

Listing 7.4 UpdateDatabase.vb

Imports System.Data.SqlClient Public Class Form1     Inherits System.Windows.Forms.Form #Region " Windows Form Designer generated code "    ' Generated code not shown #End Region Private Sub Button1_Click(ByVal sender As System.Object, _ Ä ByVal e As System.EventArgs) Handles Button1.Click  Dim Connection As New SqlConnection("Initial Catalog=Duwamish7vb;" & _ Ä "Data Source=(local);User ID=sa;password=;")  Connection.Open()  Dim Query As String = "SELECT * From Publishers"  Dim DataSetObj As New DataSet()  Dim Adapter As SqlDataAdapter = New SqlDataAdapter(Query, Connection)  Dim CmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(Adapter)  Adapter.Fill(DataSetObj)  Dim I As Integer  For I = 0 To DataSetObj.Tables(0).Rows.Count - 1    DataSetObj.Tables(0).Rows(I).Item(1) = _ Ä    UCase(DataSetObj.Tables(0).Rows(I).Item(1))  Next Adapter.UpdateCommand = CmdBuilder.GetUpdateCommand()  Adapter.Update(DataSetObj.Tables(0))   MessageBox.Show("Update complete") End Sub  Private Sub Button2_Click(ByVal sender As System.Object, _ Ä ByVal e As System.EventArgs) Handles Button2.Click     Dim Connection As New SqlConnection("Initial " & _ Ä  "Catalog=Duwamish7vb;Data Source=(local);User ID=sa;password=;")     Connection.Open()     Dim Query As String = "SELECT * From Publishers"     Dim DataSetObj As New DataSet()     Dim Adapter As SqlDataAdapter = New SqlDataAdapter(Query, _ Ä   Connection)     Dim CmdBuilder As SqlCommandBuilder = _ Ä   New SqlCommandBuilder(Adapter)     Adapter.Fill(DataSetObj)     Dim I As Integer     For I = 0 To DataSetObj.Tables(0).Rows.Count - 1       DataSetObj.Tables(0).Rows(I).Item(1) = _ Ä     LCase(DataSetObj.Tables(0).Rows(I).Item(1))     Next     Adapter.UpdateCommand = CmdBuilder.GetUpdateCommand()     Adapter.Update(DataSetObj.Tables(0)) Ä MessageBox.Show("Update complete")    End Sub    Private Sub Button3_Click(ByVal sender As System.Object, _ Ä  ByVal e As System.EventArgs) Handles Button3.Click      Dim Connection As New SqlConnection("Initial Catalog=" & _ Ä    "Duwamish7vb;Data Source=(local);User ID=sa;password=;")      Connection.Open()      Dim Query As String = "SELECT * From Publishers"      Dim DataSetObj As New DataSet()      Dim Adapter As SqlDataAdapter = New _ Ä    SqlDataAdapter(Query, Connection) Ä  Dim CmdBuilder As SqlCommandBuilder = New _ Ä    SqlCommandBuilder(Adapter)      Adapter.Fill(DataSetObj)      Dim I As Integer      TextBox1.Text = ""      For I = 0 To DataSetObj.Tables(0).Rows.Count - 1         TextBox1.Text = TextBox1.Text & _ Ä       DataSetObj.Tables(0).Rows(I).ItemArray(1) + vbCrLf      Next      Connection.Close()    End Sub End Class

As you can see, the program displays a form that contains three buttons: one that converts the publisher names to uppercase, one that converts the publisher names to lowercase, and one that displays the publisher names within a textbox. The code for each button’s operation is quite similar.

To begin, the code establishes a connection to the database. Then, the program creates a string that contains the database query and a DataSet object that will hold the query’s result. Using an Adapter object, the program fills the DataSet with the query’s result—in this case, with the columns that make up the Publisher table.

Next, depending on the button the user selects, the code will use a For loop to convert the publisher names to uppercase or lowercase, or to display the publisher names. If the code is changing the database contents, the program will use a SqlCommandBuilder object that tracks the queries the program performed to retrieve the current data to update the Adapter (the program uses the Adapter object to interact with the database). Then, the code uses the Adapter object’s Update method to update the database’s contents with the uppercase or lowercase publisher names.

In other words, to update a database, the program must use a SqlCommandBuilder object to track the queries the program performed to create the table it will return to the database. The database will use the information the SqlCommandBuilder object contains to determine which data it should update. The program assigns the specifics about the queries to the Adapter object and then uses the Update method to direct the database to apply the changes.