Working with Commands in ADO.NET

By: Cary Jensen

Abstract: This article takes a look at classes that implement the IDbCommand interface, and how you use them to execute SQL statements against your connected database.

In the preceding article in this series you learned how to use classes that implement the IDbConnection interface to connect to an underlying database. Once a connection is established, you select records, manipulate data, and manage your database using SQL (the structured query language). Within ADO.NET,  these SQL statements are always executed using an instance of a class that implements the IDbCommand interface. 

In order to simplify the following discussions, classes that implement the IDbCommand interface will be referred to generically as commands

For example, consider the following C# code segment (which appeared in the preceding article in this series):

connection = new SqlConnection("Persist Security Info=False;" + 
  "Integrated Security=SSPI;database=northwind;" + 
  "server=localhost;Connect Timeout=30"); 
connection.Open(); 
dataadapter = new SqlDataAdapter("SELECT * FROM customers", connection);

In Delphi 8 for for the Microsoft .NET framework, this same operations looks like the following:

Connection1 := SqlConnection.Create('Persist Security Info=False;' +
  'Integrated Security=SSPI;database=northwind;' +
  'server=localhost;Connect Timeout=30');
Connection1.Open();
DataAdapter1 := SqlDataAdapter.Create('SELECT * FROM customers', Connection1);

As you can see in the last line of each of these code segments, a SQL statement is passed as the first parameter to the SqlDataAdapter constructor. What is not obvious here is that this constructor creates an appropriate command (an instance of SqlCommand in this case), and assigns this instance to the SqlDataAdapter's SelectCommand property. If you subsequently invoke the SqlDataAdapter's Fill method, this command object is used to request the associated records from the underlying MS SQL Server database.

Overview of Commands

Classes that implement the IDbCommand interface are responsible for submitting SQL commands through an IDbConnection instance. Regardless of the publisher, all ADO.NET-related namespaces provide an IDbCommand implementation. For example, the System.Data.SqlClient namespace includes SqlCommand, System.Data.Odbc includes OdbcCommand, and System.Data.OleDb includes OleDbCommand. With the Borland Data Providers (BDP.NET), this class is named BdpCommand.

For the purpose of executing SQL statements, Commands have four essential properties: CommandText, CommandType, Connection, and Parameters. 

The contents of CommandText depend on the value of CommandType, which is set to one of the values of the System.Data.CommandType enumeration. If CommandType is System.Data.CommandType.Text (the default), CommandText holds a SQL statement. 

If CommandType is set to System.Data.CommandType.TableDirect, CommandText holds the name of a table or view. Internally, the command object will construct a SELECT * FROM statement, selecting all fields and all records from the specified table or view. If CommandType is set to System.Data.CommandType.StoredProcedure, you assign the name of the stored procedure that you want to execute to CommandText. Internally the command object generates an appropriate query, such as EXECUTE PROCEDURE procname, where procname is replaced with the name of the stored procedure assigned to CommandText (the actual SQL statement depends on the dialect of SQL supported by the associated connection).

Every implementation of IDbCommand that I have worked with supports a CommandType of Text. However, not all data providers support all three CommandType values. For example, the System.Data.SqlClient.SqlCommand class does not support TableDirect.

The Connection property of a command object must be set to an appropriate connection object. For example, the Connection property of a SqlCommand must be set to a SqlConnection instance, while the Connection property of a BdpCommand must be set to a BdpConnection. This connection must be opened (active) before the command object can execute a SQL statement.

The final essential property is Parameters. The Parameters property is a collection of zero or more appropriate IDbParameter instances. The use of the Parameters property is only necessary if the CommandText property holds a parameterized query. The use of Parameters is discussed in the next article in this series.

Exactly how these essential properties of a command are set depends on how you are using the ADO.NET components. In the two preceding code segments, a SQL statement was passed to the constructor of a data adapter. The constructor, in turn, created an instance of the SqlCommand class, set its CommandText property to the string passed in the first parameter, set its Connection property to the SqlConnection that the data adapter received in its second parameter, and then assign the command object to the data adapter's SelectCommand property. The command's CommandType property was left at its default setting of Text, and no parameters were required since the passed SQL statement is not a parameterized query.

However, the exact same effect could have been achieved using a variety of different approaches. For example, the following two examples produce precisely the same result as the preceding two, albeit using more lines of code. This first example is in C#:

connection = new SqlConnection("Persist Security Info=False;" +
  "Integrated Security=SSPI;database=northwind;" +
  "server=localhost;Connect Timeout=30");
connection.Open();
command = new SqlCommand();
command.Connection = connection;
command.CommandText = "SELECT * FROM customers";
dataadapter = new SqlDataAdapter();
dataadapter.SelectCommand = command;

And the following is how this same example looks using Delphi 8 for .NET:

Connection1 := SqlConnection.Create('Persist Security Info=False;' +
  'Integrated Security=SSPI;database=northwind;' +
  'server=localhost;Connect Timeout=30');
Connection1.Open();
Command1 := SqlCommand.Create;
Command1.Connection := Connection1;
Command1.CommandText := 'SELECT * FROM customers';
DataAdapter1 := SqlDataAdapter.Create;
DataAdapter1.SelectCommand := Command1;

Executing IDbCommand Queries

ADO.NET provides four mechanisms through which the queries represented by commands are executed. Three of these involve the invocation of an appropriate method of the IDbCommand interface. These methods are ExecuteNonQuery, ExecuteScalar, and ExecuteReader. The fourth method requires the use of a data adapter. Each of these techniques is described in the following sections.

Executing Queries That Return No Data

Queries that do not return a result set are executed using the ExecuteNonQuery method of the IDbCommand interface. Queries such as these include data definition language (DDL) statements, such as CREATE TABLE, DROP INDEX, and so forth. Similarly, queries that manipulate data, but that do not return a result set, such as INSERT, UPDATE, and DELETE, and also executed using ExecuteNonQuery.

The following is a C# example that uses a command to create a table named NewCust:

connection = new SqlConnection("Persist Security Info=False;" +
 "Integrated Security=SSPI;database=northwind;" +
 "server=localhost;Connect Timeout=30");
connection.Open();
command = new SqlCommand();
command.Connection = connection;
command.CommandText = "CREATE TABLE NewCust (ID Int, Name VarChar(30))";
command.ExecuteNonQuery();

In Delphi 8 this same code segment looks like the following:

Connection1 := SqlConnection.Create('Persist Security Info=False;' +
  'Integrated Security=SSPI;database=northwind;' +
  'server=localhost;Connect Timeout=30');
Connection1.Open();
Command1 := SqlCommand.Create;
Command1.Connection := Connection1;
Command1.CommandText := 'CREATE TABLE NewCust (ID Int, Name VarChar(30))';
Command1.ExecuteNonQuery();

Executing Queries That Return One Value

When you want to get back data from an underlying database, you use a SELECT query. SELECT statements return one or more columns and zero or more rows of data, depending on the nature or the SELECT command.

While ADO.NET provides for three mechanisms for executing a SELECT command, one of these is unique. Specifically, regardless of the SELECT statement you use, the ExecuteScalar method of the IDbCommand interface returns a single value, representing the contents of the first row and first column of the result set.

ExecuteScalar is normally used with queries that only return one row and one column. For example, if you need to know how many records there are in a given database table, ExecuteScalar is ideal. It is simple, and requires fewer lines of code that other mechanisms that return result sets from a SELECT query.

The following C# example uses IDbCommand.ExecuteScalar method in conjunction with the System.Windows.Forms.MessageBox class to display the number of records in the customers table.

connection = new SqlConnection("Persist Security Info=False;" +
  "Integrated Security=SSPI;database=northwind;" +
  "server=localhost;Connect Timeout=30");
connection.Open();
command = new SqlCommand();
command.Connection = connection;
command.CommandText = "SELECT Count(*) FROM customers";
Int32 numRecs = (int) command.ExecuteScalar();
MessageBox.Show(this, numRecs.ToString(), "Number of in customers table");

This is what this code looks like in Delphi 8 for .NET:

var
  NumRecs: TObject;
begin
  Connection1 := SqlConnection.Create('Persist Security Info=False;' +
    'Integrated Security=SSPI;database=northwind;' +
    'server=localhost;Connect Timeout=30');
  Connection1.Open();
  Command1 := SqlCommand.Create;
  Command1.Connection := Connection1;
  Command1.CommandText := 'SELECT Count(*) FROM customers';
  NumRecs := Command1.ExecuteScalar;
  MessageBox.Show(Self, NumRecs.ToString, 'Number of records in customers table');

Getting Readonly Result Sets

When you are interested in more than one value from a SELECT statement, commands offer two solutions. The first, and most efficient, is to use a an instance of a class that implements the IDataReader interface. 

A data reader permits you to read the rows of a result set ,one at a time. This reading is forward only, meaning that once you have read a row, you cannot return to a previous row.

Unlike many of the other objects you use in ADO.NET, you cannot create a data reader by invoking a constructor. Instead, you call the ExecuteReader method of a command object in order to create the corresponding data reader.  

The following C# example uses a data reader to populate a listbox with the customer ID's from the Customer table.

connection = new SqlConnection("Persist Security Info=False;" +
  "Integrated Security=SSPI;database=northwind;" +
  "server=localhost;Connect Timeout=30");
connection.Open();
command = new SqlCommand();
command.Connection = connection;
command.CommandText = "SELECT CustomerID FROM customers";
SqlDataReader datareader = command.ExecuteReader();
while (datareader.Read())
  listBox1.Items.Add(datareader.GetString(0));
datareader.Close();

In Delphi 8, the code looks like this:

var
  DataReader1: SqlDataReader;
begin
  Connection1 := SqlConnection.Create('Persist Security Info=False;' +
    'Integrated Security=SSPI;database=northwind;' +
    'server=localhost;Connect Timeout=30');
  Connection1.Open();
  Command1 := SqlCommand.Create;
  Command1.Connection := Connection1;
  Command1.CommandText := 'SELECT "CustomerID" FROM customers';
  DataReader1 := Command1.ExecuteReader;
  while DataReader1.Read do
  begin
    listBox1.Items.Add(DataReader1.GetString(0));
  end;
  DataReader1.Close;

Data readers are discussed in greater detail in a future article in this series.

Selecting Record Sets into Memory

While data readers are fast and efficient, there is another alternative that provides additional features. The records of a result set can be placed into a System.Data.DataTable, which is an in-memory structure. These records can then be traversed forward and backwards, and the data can be changed and those changes can be resolved back to the underlying database.

Unlike the previous three techniques shown in this article, which involved methods of the IDbCommand interface, adding a result set to a data table does not use a command method directly. Instead, it is performed by the Fill method of a data adapter (which uses a data reader internally to perform the operation). The fill method populates a DataTable based on the query associated with the command object assigned to the data adapter's SelectCommand property.

The following C# example demonstrates how to populate a data table with the results of a query. The contents of this data table is then displayed in a data grid.

connection = new SqlConnection("Persist Security Info=False;" +
  "Integrated Security=SSPI;database=northwind;" +
  "server=localhost;Connect Timeout=30");
connection.Open();
dataadapter = new SqlDataAdapter("SELECT * FROM customers", connection);
dataTable = new DataTable();
dataadapter.Fill(dataTable);
dataGrid1.DataSource = dataTable;

Here is a Delphi 8 version of this same code:

Connection1 := SqlConnection.Create('Persist Security Info=False;' +
  'Integrated Security=SSPI;database=northwind;' +
  'server=localhost;Connect Timeout=30');
Connection1.Open();
DataAdapter1 := SqlDataAdapter.Create('SELECT * FROM customers', Connection1);
DataTable1 := DataTable.Create;
DataAdapter1.Fill(DataTable1);
dataGrid1.DataSource := DataTable1;

While both of these examples used a data table directly, in most cases you will use a DataSet, which is a special container for zero or more data tables. For example, the following C# example performs essentially the same operations as the preceding C# example. However, the data adapter's Fill method is used to add the result set to a data set, which in turn creates a data table into which the data is stored.

connection = new SqlConnection("Persist Security Info=False;" +
  "Integrated Security=SSPI;database=northwind;" +
  "server=localhost;Connect Timeout=30");
connection.Open();
dataadapter = new SqlDataAdapter("SELECT * FROM customers", connection);
dataSet = new DataSet();
dataadapter.Fill(dataSet);
dataGrid1.DataSource = dataSet;
dataGrid1.DataMember = dataSet.Tables[0].TableName;

Both data tables and data sets are discussed in greater detail later in this series.

Commands and Borland IDEs

While the code examples shown in this article demonstrate how you create and configure connection and command objects using code, both C#Builder and Delphi 8 for .NET do some of work for you when you place and configure data access components into your project from the IDE. 

Use the following steps to see how this works.

  1. Create a new Windows Form application.
  2. From the Tool Palette, place the following four components onto the displayed WinForm: SqlConnection, SqlDataAdapter, DataSet, and DataGrid.
  3. Set the ConnectionString property of the SqlConnection to a valid connection string. If you are connecting to the Northwind SQL Server database as demonstrated earlier in this article, the ConnectionString will look like the following.
    Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=localhost;Connect Timeout=30 
    (remove any carriage returns from the preceding string)
  4. Select the SqlDataAdapter and expand its SelectCommand property.
  5. Double-click the Connection property of the SelectCommand subcomponent to set it to the SqlConnection.
  6. Set the CommandText property of the SelectCommand subcomponent to the following SQL statement:
    SELECT * FROM customers
  7. Set the DataSource property of the DataGrid to the DataSet.
  8. Add a Load event handler to the WinForm. In C# this event handler should look like the following:
try
{
  sqlConnection1.Open();
  sqlDataAdapter1.Fill(dataSet1);
  dataGrid1.DataMember = dataSet1.Tables[0].TableName;
}
catch (Exception ex)
{
  MessageBox.Show(ex.Message);
}

In Delphi 8 for .NET this code should look like the following:

try
  SqlConnection1.Open;
  SqlDataAdapter1.Fill(DataSet1);
  DataGrid1.DataMember := DataSet1.Tables[0].TableName;
except
  on e: Exception do
  begin
    MessageBox.Show(e.Message);
  end;
end;

If you now run this application, your WinForm will look something like the following:

 If you inspect the designer-generated code, specifically the InitializeComponent method, you will see that the designer created SqlCommand objects and associated them with the command-related SqlDataAdapter properties. This method also includes the assignment of the various property values that you set using the Object Inspector.

About the Author

Cary Jensen is President of Jensen Data Systems, Inc., a Texas-based training and consulting company that won the 2002 and 2003 Delphi Informant Magazine Readers Choice Awards for Best Training. He is the author and presenter for Delphi Developer Days 2004 (www.DelphiDeveloperDays.com) and Advantage Developer Days 2004 (www.AdvantageDeveloperDays.com), information-packed seminars that tour North America and Europe. Cary is also an award-winning, best-selling co-author of nineteen books, including Advantage Database Server: The Official Guide (2003, McGraw-Hill/Osborne), Building Kylix Applications (2001, Osborne/McGraw-Hill), Oracle JDeveloper (1999, Oracle Press), JBuilder Essentials (1998, Osborne/McGraw-Hill), and Delphi In Depth (1996, Osborne/McGraw-Hill). For information about onsite training and consulting you can contact Cary at cjensen@jensendatasystems.com, or visit his Web site at www.JensenDataSystems.com.

Copyright ) 2004 Cary Jensen, Jensen Data Systems, Inc.
ALL RIGHTS RESERVED. NO PART OF THIS DOCUMENT CAN BE COPIED IN ANY FORM WITHOUT THE EXPRESS, WRITTEN CONSENT OF THE AUTHOR.


Server Response from: ETNASC04