C#. ADO .NET. Class DbCommand. Examples

Class DbCommand. Examples

Before studying this topic, it is recommended that you familiarize yourself with the following topic:

Search other resources:


1. Purpose and tasks of the DbCommand class

The DbCommand class is used to create an object that encapsulates a SQL query. The SQL query formed in the object can be applied to an existing connection with the underlying data in the connected mode.

To create an object of type DbCommand or a type inherited from it (SqlCommand, OledbCommand, OdbcCommand), you need to call the SqlCommand() constructor, which receives 2 parameters:

  • object of type DbConnection – directly implements the connection with the database;
  • string in SQL language, which will be set in the DbConnection object.


2. Properties and methods of the DbCommand class
2.1. Properties of the DbCommand class (using the SqlCommand class as an example)


Property Data type Explanation
CommandText string Contains the query text in SQL
CommandTimeOut Int32 Time in seconds. This is the amount of time the adapter waits for the request to complete (30 seconds by default)
CommandType CommandType

Query type (by default Text). There is also TableDirect and StoredProcedure


Connection SqlConnection Connecting to a data warehouse
Parameters SqlParameterCollection A set of request parameters
Transaction SqlTransaction Specifies the transaction that is used for the request


2.2. Methods of DbCommand class


Method Explanation




Starts asynchronous query execution
Cancel Cancels the execution of the request
Clone Returns a copy of the DbCommand (SqlCommand) object
CreateParameter Creates a new query parameter




Ends asynchronous execution of a request
ExecuteNonQuery Executes a SQL query that returns no records
ExecuteReader Executes a SQL query that returns records. The result is inserted into a DbDataReader (SqlDataReader) object
Prepare Stores the prepared version of the query in the data store
ResetCommandTimeout Sets the default CommandTimeout value (30 seconds)


2.3. Example of sending a request

The example shows a code fragment that displays all the fields of the Group table, which consists of two fields:

  • ID_Group – unique group identifier, primary key;
  • NameGroup – group name.

In the code snippet, the connection is made to the Microsoft SQL Server provider.

string connString =
  @"DataSource = .SQLExpress;" +
  "InitialCatalog = MyDatabase;" +
  "Integrated Security = True";
string strSQL = "SELECT * FROM [Group]";

SqlConnection conn = new SqlConnection(connString);

// Open database connection
SqlCommand command = new SqlCommand(strSQL, conn);
SqlDataReader reader = command.ExecuteReader();
int RowsRetrieved = 0; // Number of read rows

// To cancel a request, call command.Cancel();
while (reader.Read())
  listBox1.Items.Add(reader[0].toString() +
    " - " +

label2.Text = "Number rows retrieved: " + RowsRetrieved.toString();

// Close the reader object

In the above fragment, using the SqlCommand() constructor, an object is created that encapsulates the SQL command. This is done with the line

SqlCommand command = new SqlCommand(strSQL, conn);


  • strSQL is the query text in SQL (T-SQL);
  • conn – an object of type SqlConnection that provides a connection to the database.

The reader object of type SqlDataReader is used to get the result set of rows based on the command query. The reader object is a collection of table columns that is generated based on a given SQL query. To get the reader object with the filled data in the form of a table, the ExecuteReader() method of the command object is called.

SqlDataReader reader = command.ExecuteReader();

Based on the request formed in the command object, the reader object is formed. This object is a collection of columns from the source table. This table is created on the basis of the SQL query generated in the command object. Therefore, the table fields in the reader object can be accessed using index operation:

  • reader[0] – table column with index 0
  • reader[1] – table column with index 1

For each column (reader[0], reader[1], …) we can cast to the type of the column and thus get the correct representation of the data in our program.

The reader object implements a Read() method that reads lines in one direction (from beginning to end). If the string can be read, then the Read() method returns true. Therefore, when reading lines, it is advisable to place this method in a while() loop

while (reader.Read())

By introducing an additional variable, you can count the number of lines in the reader object that are returned as a result of a command request.


3. An example of reading data from a database and adding data to a ListBox component. Application type Windows Forms

The example provides an event handler in a Windows Forms application that reads data from a database and writes it to display in a ListBox.

In order to get a working code for your case of a database table, just specify (change):

  • the text of the SQL query;
  • the Connection String of the database connection.

You can also change as you wish:

  • the ListBox component to any other component;
  • the format of the information presentation.
private void GetDataFromDataBase()
  // Reads data from the database
  // 1. Query string
  string queryStr = "SELECT * FROM [dbo].[Table1]";

  // 2. The connection string
  string connStr = @"Data Source=(localdb)\ProjectModels;" +
    "Initial Catalog=Database4;Integrated Security=True;";

  // 3. Get data
  using (SqlConnection connection =
    new SqlConnection(connStr))
      // create command in SQL language
      DbCommand command = connection.CreateCommand();
      command.CommandText = queryStr;
      command.CommandType = CommandType.Text;

      // Open the connection

      // Get data - class DDataReader
      DbDataReader dataReader = command.ExecuteReader();

      // Output data to listBox1
      while (dataReader.Read())

listBox1.Items.Add(dataReader[0].ToString() + " - " +
    catch (Exception e)
      // Close the connection


Related topics