Connected mode. Class DbConnection

Before studying this topic, it is recommended to familiarize yourself with the topic:

ADO .NET namespaces. Review. ADO .NET Interfaces

Search other resources:

1. Class DbConnection. General information

The DBConnection class is used to connect to a database from an application in connected mode. The concept of “connected mode” means that all operations with the database (reading, inserting, updating, deleting) are performed at the moment when the database is connected (open). The Open() method of the class is used to open a connection to the database. The Close() method is used to close the connection.

The DbConnection class implements the IDbConnection interface. You can read more about the implementation features of the IDbConnection interface here.

To use IDbConnection and its derived classes, you need to include the System.Data.Common namespace

using System.Data.Common;

The DBConnection class is the base class for all other connection classes SqlConnection, OdbcConnection, OledbConnection.

Using an object of type DbConnection, you can set:

data source type;
location of the data source;
various attributes of the data source.

The object of type DbConnection is used to connect to and disconnect from a database.

⇑

2. Analysis of methods and properties of the DbConnection class

In addition to the above two Close() and Open() methods, the DbConnection class has a number of other properties and methods.

The following table displays the properties of the SqlConnection class.

Property	Data type	Description
ConnectionString	String	Defines how the SqlConnection object is connected to the source
ConnectionTimeOut	Int32	Specifies the amount of time, in seconds, that the SqlConnection object attempts to establish a connection to the data source (read-only)
Database	String	Specifies the name of the database to which the connection was made
DataSource	String	Returns information about the location of the database to which the connection is or has been made (read-only)
PacketSize	Int32	The batch size used to communicate with the SQL Server database (read-only)
ServerVersion	String	Data source version (read-only)
State	ConnectionState	Indicates the current state of the SqlConnection object – active or destroyed (read-only)
WorkstationId	String	Returns the name of the database client. By default, it is equal to the computer name

The methods of the SqlConnection object are presented in the following table.

Method	Description
BeginTransaction	Starts a transaction for a connection
ChangeDatabase	Switches to the specified database when the connection is open
ClearAllPools	Clearing free connections in all SqlConnection pools. This is a static method
ClearPool	Clears free connections in the connection pool with the corresponding object of type SqlConnection. This is a static method
Close	Close the connection
CreateCommand	Creates the object of type SqlCommand for the current connection
GetSchema	Gets schema information for the connection
Open	Opens the connection
ResetStatistics	Resets statistics for the current connection
RetrieveStatistics	Returns statistics for the current connection

⇑

3. Create, open and close a connection. Fragment

In the most general case, the procedure for using a database connection consists of the following steps.

1. Create a connection

SqlConnection connection = new SqlConnection(connStr);

where connStr is the database connection string (Connection String).

2. Opening the connection

connection.Open();

After opening a connection to a database, you can make changes to that database.

3. Close connection

connection.Close();

After closing, the database connection remains in the so-called “connection pool” until the next call.

⇑

4. Examples of connecting to the data source

The examples below show different ways to modify data and read it in the connected mode. The data source is Sql Server. We consider code fragments for a Windows Forms application, in particular, click event handlers on the Button button.

4.1. Set the connection. Methods Open() and Close()

You can establish a connection and get information about the features of the data source in approximately the following way.

// Method that creates a SQL connection to a database
private void CreateSQLConnection()
{
  // Create SQL Connection
  // 1. The string of connection to database
  string connStr = @"Data Source=(localdb)\ProjectModels;" +
    "Initial Catalog=Database4;Integrated Security=True;" +
    "Connect Timeout=30;Encrypt=False;" +
    "TrustServerCertificate=False;ApplicationIntent=ReadWrite;" +
    "MultiSubnetFailover=False";

  // 2. Create a connection based on the connection string.
  using (SqlConnection connection =
    new SqlConnection(connStr))
  {
    try
    {
      // Open the connection
      connection.Open();
      label1.Text = "Connection is open. ";
      label2.Text = "Database: " + connection.Database;
      label3.Text = "DataSource: " + connection.DataSource;
    }
    catch(SqlException e)
    {
      label1.Text = e.Message;
    }
    finally
    {
      // Close the connection
      connection.Close();
      label1.Text = label1.Text + "Connection is closed...";
    }
  }
}

The principle of operation is as follows:

open a connection to the database;
perform operations on the database;
close the connection.

⇑

4.2. Read data from database

The following snippet demonstrates reading data from table Table1 in a database named Database4 based on:

query strings in SQL language (T-SQL);
database connection string.

private void GetDataFromDataBase()
{
  // Reads rows from the database
  // 1. SQL-query
  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();

      // Display data in 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.3. Get information about connection

You can use code like the following to get connection information. Otherwise, you can only change the connStr database connection string.

// Get information about connection
private void GetInformationAboutConnection()
{
  // 1. Form the connection string
  string connStr = @"Data Source=(localdb)\ProjectModels;" +
      "Initial Catalog=Database4;Integrated Security=True;";

  // 2. Get information
  using (SqlConnection connection =
    new SqlConnection(connStr))
  {
    try
    {
      // Open the connection
      connection.Open();

      listBox1.Items.Add("Connection is open...");
      listBox1.Items.Add("Database: " + connection.Database);
      listBox1.Items.Add("DataSource: " + connection.DataSource);
      listBox1.Items.Add("State: " + connection.State.ToString());
      listBox1.Items.Add("Server Version: " + connection.ServerVersion);
      listBox1.Items.Add("Access Token: " + connection.AccessToken);
    }
    catch (SqlException e)
    {
      MessageBox.Show(e.Message);
    }
    finally
    {
      // Close connection
      connection.Close();
      label1.Text = "Connection is closed...";
    }
  }
}

⇑

5. An example of defining a provider and accessing to data source

An example event handler for a Windows Forms application that selects a connection and establishes the appropriate connection in connected mode.

// An example of defining a provider to access the data source.
private void button5_Click(object sender, EventArgs e)
{
  IDbConnection conn = null;

  // Get selected item
  int index = comboBox1.SelectedIndex;

  // Get the provider name
  string provider = comboBox1.Text;

  // Create a connection
  if (provider == "SqlServer")
  {
    conn = new SqlConnection();
  }
  else
  if (provider == "OleDb")
  {
    conn = new OleDbConnection();
  }
  else
  if (provider == "Odbc")
  {
    conn = new OdbcConnection();
  }

  if (provider == "None")
  {
    return;
  }

  string info = conn.GetType().Name;
  listBox1.Items.Clear();
  listBox1.Items.Add(info);
}

BestProg

C#. ADO .NET. Connected mode. Class DbConnection