Teach Yourself Visual Studio® .NET 2003 in 21 Days [Electronic resources]

Using the Command Object and DataReaders

The Command object is used to execute SQL statements against a database. The SQL statements can be ad hoc text or the name of a stored procedure in SQL Server. The SqlCommand class is responsible for SQL Server access, and the OleDbCommand class is responsible for OLE DB data sources.

You create a Command object in one of two ways:

• By calling the CreateCommand method of a Connection object

• By creating an instance of the SqlCommand or OleDbCommand class, and passing a valid Connection object to the Command instance

After you create a Command object, you set properties that indicate what the SQL statement is, the timeout, the connection information, and parameters if there are any in your SQL statement. Table 10.2 lists the common properties of the SqlCommand class and the OleDbCommand class.

After you set up the Command object and specify a SQL statement or stored procedure name in the CommandText property, you call one of the methods listed in Table 10.3 to execute your SQL statement against the database.

Each of the methods of the Command object gives you a different type of action based on the requirements.

To read the data after you executed a Command with ExecuteReader, you create a DataReader object that holds the data returned from the database. The DataReader comes in two flavors, SqlDataReader and OleDbDataReader, and you choose one or the other depending on the type of database you want to access.

To see how this all works, Listing 10.1 demonstrates how to connect to a database, how to use the Command object, and how to read data from a DataReader object.

Listing 10.1 Using Connection, Command, and DataReader Objects to Retrieve Data

Dim cn As New SqlConnection( _
"Server=(local)\NetSDK;DataBase=pubs;" _
& "Integrated Security=SSPI")
' Create a SqlDataReader object
Dim dr As SqlDataReader
' Create a new SqlCommand object
Dim cmd As New SqlCommand()
' Set the Select statement in the CommandText property and
' set the Connection property to the "cn" SqlConnection object
' you just created
With cmd
.CommandText = "Select au_lname, au_fname from Authors"
.Connection = cn
End With
' Open the Connection
cn.Open()
' Call the ExecuteReader method of the Command object
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
' Use this for concatenating the data from the database
Dim strName As String
' Call the Read method of the DataReader to loop thru the records
While dr.Read
' Add the items to the ListBox1 control
strName = dr("au_lname") & ", " & dr("au_fname")
MessageBox.Show(strName)
End While
' Close the connection
cn.Close()

SqlConnection cn = new SqlConnection
(@"Server=(local)\NetSDK;DataBase=pubs;Integrated Security=SSPI");
// Create a SqlDataReader object
SqlDataReader dr;
// Create a new SqlCommand object
SqlCommand cmd = new SqlCommand();
// Set the Select statement in the CommandText property and
// set the Connection property to the "cn" SqlConnection object
// you just created
cmd.CommandText = "Select au_lname, au_fname from Authors";
cmd.Connection = cn;
// Open the Connection
cn.Open();
// Call the ExecuteReader method of the Command object
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// Use this for concatenating the data from the database
string strName;
// Call the Read method of the DataReader to loop thru the records
while (dr.Read())
{
// Add the items to the ListBox1 control
strName = dr.GetString(0) + ", " + dr.GetString(1);
MessageBox.Show(strName);
listBox1.Items.Add(strName);
}
// Close the Connection object
cn.Close();

After you have called the ExecuteReader method, you use the Read method of the DataReader class to loop through the records. Each time a record is read, the position in the Reader is advanced to the next record. This makes it easy to use the While statement to read each row. The Read method returns a False value when there are no more records to read, and your code execution continues after the While loop. Remember, a DataReader is a forward-only set of records, so you can't move backward in the DataReader.

You can also implement other looping techniques for reading data from a DataReader, as the following Visual Basic .NET code demonstrates:

Do Until dr.Read = False
' do something with the data
Loop

If you compare the Visual Basic .NET and C# code for Listing 10.1, you'll notice a difference in the way the actual data is retrieved from the current record on the Read method of the DataReader. The Visual Basic .NET code uses the actual field name, and the C# code uses the GetString method with the ordinal position of the field you're attempting to access. When reading data with a SQLDataReader, you have different methods of accessing the specific data type in the field of the current row. Table 10.4 is a very compact list of some of the methods in the SqlDataReader class that get values based on data type.

There are many other methods to retrieve all the data types for SQL Server and OLE DB data sources. It isn't important to list them all here. When you're writing the code in the Visual Studio .NET integrated development environment (IDE), you get the list of possible members for the DataReader class. You'll know if you're using the wrong method when an exception occurs. A safe bet is to use either the numeric ordinal position of the data in the row with the GetString method, or the field name and put the field data into a string. You can then use the System.Convert class to manipulate the data. There's some overhead in doing that, but if you aren't sure of the data type or how to handle it, you can still get the data you need.

Using ExecuteNonQuery with a Command Object

The ExecuteNonQuery method is used when you aren't returning any data, as in the case of an Insert, Update, or Delete. Listing 10.2 demonstrates the use of ExecuteNonQuery using a Command object and a DataReader.

Listing 10.2 Using ExecuteNonQuery with a Command Object

Sub DoNonQuery()
Dim cn As New SqlConnection( _
"Server=(local)\NetSDK;DataBase=pubs;" _
& "Integrated Security=SSPI")
Dim cmd As New SqlCommand()
With cmd
.CommandText = "Delete from Authors where au_lname = 'Smith'"
.Connection = cn
.CommandType = CommandType.Text
End With
Try
cn.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
End Sub

private void DoNonQuery()
{
SqlConnection cn = new SqlConnection
(@"Server=(local)\NetSDK;DataBase=pubs;Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Delete from Authors where au_lname = 'Smith'";
cmd.Connection = cn;
cmd.CommandType = CommandType.Text ;
try
{
cn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
}
}

The code in Listing 10.2 is almost identical to the code in Listing 10.1. You created a Connection object, a Command object, and then executed the Command. The difference is in the type of SQL statement. When you perform an Insert, Update, or Delete on a database, you don't expect a result set back. By calling the ExecuteNonQuery method, you're saving resources on the server. Because no data is coming back, there's no need to call the ExecuteReader method to hold the returning data stream.

You also check the State property of the Connection object. This is a safe way to close the Connection if it's open. By wrapping this code in a try/catch block, you catch any error that might occur when you open the connection and execute the command. The finally block makes sure that the connection is closed when you're done with it.