Class DbCommand. Examples

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

Class DbConnection. Examples

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
BeginExecuteNonQuery, BeginExecuteReader, BeginExecuteXmlReader	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
EndExecuteNonQuery, EndExecuteReader, EndExecuteXmlReader	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
conn.Open();
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() +
    " - " +
    reader[1].toString());
  RowsRetrieved++;
}

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

// Close the reader object
reader.Close();
conn.Close();

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);

where

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))
  {
    try
    {
      // create command in SQL language
      DbCommand command = connection.CreateCommand();
      command.CommandText = queryStr;
      command.CommandType = CommandType.Text;

      // Open the connection
      connection.Open();

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

      // Output data to listBox1
      listBox1.Items.Clear();
      while (dataReader.Read())
      {

listBox1.Items.Add(dataReader[0].ToString() + " - " +
        dataReader[1].ToString());
      }
    }
    catch (Exception e)
    {
      MessageBox.Show(e.Message);
    }
    finally
    {
      // Close the connection
      connection.Close();
    }
  }
}

BestProg

C#. ADO .NET. Class DbCommand. Examples