Sams Teach Yourself .Net in 21 Days

By Jason Beres

• Table of Contents
  • Copyright
  • About the Author
  • About the Technical Editor
  • Acknowledgments
  • We Want to Hear from You
  • Introduction
  • Week 1: At a Glance
  • Day 1. Introduction to the Microsoft .NET Framework
  • Day 2. Introduction to Visual Studio .NET
  • Day 3. Writing Windows Forms Applications
  • Day 4. Deploying Windows Forms Applications
  • Day 5. Writing ASP.NET Applications
  • Day 6. Deploying ASP.NET Applications
  • Day 7. Exceptions, Debugging, and Tracing
  • Week 1. In Review
  • Week 2: At a Glance
  • Day 8. Core Language Concepts in Visual Basic .NET and C#
  • Day 9. Using Namespaces in .NET
  • Day 10. Accessing Data with ADO.NET
    • Looking Inside ADO.NET
    • Connecting to a Database
    • Using the Command Object and DataReaders
    • Using Windows Controls with DataReaders and Command Objects
    • Using DataAdapters and DataSets
    • Using Databinding with DataSets
    • Binding to a DataGrid Control
    • Summary
    • Q&A
    • Quiz
    • Exercises
  • Day 11. Understanding Visual Database Tools
  • Day 12. Accessing XML in .NET
  • Day 13. XML Web Services in .NET
  • Day 14. Components and .NET
  • Week 2. In Review
  • Week 3: At a Glance
  • Day 15. Writing International Applications
  • Day 16. Using Macros in Visual Studio .NET
  • Day 17. Automating Visual Studio .NET
  • Day 18. Using Crystal Reports
  • Day 19. Understanding Microsoft Application Center Test
  • Day 20. Using Visual SourceSafe
  • Day 21. Object Role Modeling with Visio
  • Week 3. In Review

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.

Table 10.2. Common SqlCommand and OleDbCommand Properties

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.

Table 10.3. Execute Methods of the SqlCommand and OleDbCommand Classes

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.

Example 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.

Table 10.4. Methods of the SqlDataReader Class

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.

Example 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.

Share This