Connected mode. Class DbDataReader

Search other resources:

Contents

• 1. Purpose of DbDataReader class
• 2. Methods and properties of class DbDataReader
• 3. An example of getting data
• 4. Method GetName(). Field FieldCount. Get the name of a column (field) in a table
• 5. Method GetFieldType(). Get the data type name of a field (column) based on its serial number (0, 1, 2, …) as a .NET type
• 6. Method GetDataTypeName(). Get the name of the data type in the database based on the serial number (0, 1, 2, …)
• Related topics

1. Purpose of DbDataReader class

The DbDataReader class provides a data reader for connected mode. An object of the DbDataReader class is designed to read records (rows) from a database table. Reading is carried out at the moment of opening a connection to the database. The class implements a one-way sequential reading. Compared to the DataSet object, this class provides better performance when accessing data by reducing overhead.

The DataReader object is not suitable for handling data between different layers of the application because the connection remains open when the DataReader object is connected.

  ⇑

2. Methods and properties of class DbDataReader

When reading data, the DataReader class uses a number of options and methods.

The properties of the DataReader class are listed below.

The methods of the DbDataReader class are as follows.

string strStudentName;
SqlDataReader rdr;

...

strStudentName = rdr.GetString(intStudentNameIndex);

strStudentName = (string)rdr.GetValue(intStudentNameIndex);

SqlDataReader rdr = command.ExecuteReader();
object[] aData = new object[rdr.FileCount];
while (rdr.Read())
{
  rdr.GetValues(aData);
  Console.WriteLine(aData[0].ToString());
}

An object of type DataReader can receive data from various sources, including an SQL query contained in an object of type Command (DbCommand, SqlCommand, OledbCommand, OdbcCommand). After receiving the data, it is possible to cancel the reading of the data using the Cancel() method.

  ⇑

3. An example of getting data

The example demonstrates reading data from a database named Database4. A table is created in the database named Table1, which has a data structure consisting of 3 columns.

The columns of the table have the following purpose

• Id – unique identifier, autoincrement field;
• Name – last name and first name of the student;
• Address – the student’s address.

The table rows are filled with values as shown in the table.

To display all the rows and columns of table in the ListBox component for Windows Forms application, you can use, for example, the following code.

private void GetDataFromDataBase()
{
  // Read data from database
  // 1. Query string
  string queryStr = "SELECT * FROM [dbo].[Table1]";

  // 2. 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 SQL-command
      DbCommand command = connection.CreateCommand();
      command.CommandText = queryStr;
      command.CommandType = CommandType.Text;

      // Open the connection
      connection.Open();

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

      // Display 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();
    }
  }
}

  ⇑

4. Method GetName(). Field FieldCount. Get the name of a column (field) in a table

To get the field name from a database table, use the GetName() method. Accordingly, the number of table fields is determined using the FieldCount property, which is read-only.

For example, for the following table

calling the method from a DataReader object named dataReader

dataReader.GetName(0);

will return “Id” string.

And the call

dataReader.GetName(2);

will return the “Address” string.

The following example is a code snippet that displays the names of all fields in a table named Table1 in Database4. The fields are displayed in the ListBox component. The example is implemented for Windows Forms application.

private void button7_Click(object sender, EventArgs e)
{
  // Get a list of field names based on the result of an SQL query and add them to the ListBox
  // 1. Connection string
  string connStr = @"Data Source=(localdb)\ProjectModels;" +
    "Initial Catalog=Database4;Integrated Security=True;";

  // 2. The text of SQL-query
  string strSQL = "SELECT * FROM [dbo].[Table1]";

  // 3. Connect to the database and get the data
  using (SqlConnection connection =
    new SqlConnection(connStr))
  {
    try
    {
      // Create the SQL-command
      DbCommand command = connection.CreateCommand();
      command.CommandText = strSQL;
      command.CommandType = CommandType.Text;

      // Open the connection
      connection.Open();

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

      // Display the names of the table columns in listBox1
      listBox1.Items.Clear();

      // FieldCount - the number of columns in the table from which data is being read
      for (int i = 0; i < dataReader.FieldCount; i++) 
      {
        // Get the field name
        string s = dataReader.GetName(i);

        // Display the field name in the ListBox
        listBox1.Items.Add(s);
      }

      // Close the dataReader object
      dataReader.Close();
    }
    catch (Exception ex)
    {
      MessageBox.Show(ex.Message);
    }
    finally
    {
      // Close the connection
      connection.Close();
    }
  }
}

  ⇑

5. Method GetFieldType(). Get the data type name of a field (column) based on its serial number (0, 1, 2, …) as a .NET type

The GetFieldType() method returns an instance of type Type with data about the type of the field filled in. The general form of a method declaration is as follows

Type DbDataReader.GetFieldType(int ordinal);

here

• ordinal – ordinal number of the column (field) in the table containing the rows represented by the DataReader object.

The following example shows how to read the data types of all fields in a given table.

private void button8_Click(object sender, EventArgs e)
{
  // Get the name of the field's data type
  // 1. Connection string
  string connStr = @"Data Source=(localdb)\ProjectModels;" +
    "Initial Catalog=Database4;Integrated Security=True;";

  // 2. The SQL-query
  string strSQL = "SELECT * FROM [dbo].[Table1]";

  // 3. Connect to the database and get the data
  using (SqlConnection connection =
    new SqlConnection(connStr))
  {
    try
    {
      // Create the SQL-command
      DbCommand command = connection.CreateCommand();
      command.CommandText = strSQL;
      command.CommandType = CommandType.Text;

      // Open the connection
      connection.Open();

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

      // Display data type names of table columns in listBox1
      listBox1.Items.Clear();

      // FieldCount - the number of columns in the table from which data is being read
      for (int i = 0; i < dataReader.FieldCount; i++) // iterate over all fields
      {
        // Get the field name
        string s = dataReader.GetFieldType(i).Name;

        // Display the field name in the ListBox
        listBox1.Items.Add(s);
      }

      // Close the dataReader object
      dataReader.Close();
    }
    catch (Exception ex)
    {
      MessageBox.Show(ex.Message);
    }
    finally
    {
      // Close the connection
      connection.Close();
    }
  }
}

For the table presented in p.p. 3-4 the result of adding fields to the ListBox are rows

Int32
String
String

  ⇑

6. Method GetDataTypeName(). Get the name of the data type in the database based on the serial number (0, 1, 2, …)

The general form of the declaration of the GetDataTypeName() method is:

string GetDataTypeName(int ordinal);

A fragment that reads a list of data types from the dataReader object of type DbDataReader and writes them to the ListBox component (application of type Windows Forms).

...

// Display the datatype names of the table columns in listBox1
listBox1.Items.Clear();

// FieldCount - the number of columns in the table from which data is being read
for (int i = 0; i < dataReader.FieldCount; i++) // iterate over all fields
{
  // Get the field name
  string s = dataReader.GetDataTypeName(i);
  listBox1.Items.Add(s);
}

...

For the data table from points 3, 4, the result of the program will be

int
nchar
nchar

  ⇑

Related topics

• Class DbConnection. Examples
• Class DbCommand. Examples
• Class DbParameter. Examples
• Class DbTransaction. Examples

  ⇑