C# Language Accessing Databases ADO.NET Connections

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Example

ADO.NET Connections are one of the simplest ways to connect to a database from a C# application. They rely on the use of a provider and a connection string that points to your database to perform queries against.

Common Data Provider Classes

Many of the following are classes that are commonly used to query databases and their related namespaces :

  • SqlConnection,SqlCommand,SqlDataReader from System.Data.SqlClient
  • OleDbConnection,OleDbCommand,OleDbDataReader from System.Data.OleDb
  • MySqlConnection, MySqlCommand, MySqlDbDataReader from MySql.Data

All of these are commonly used to access data through C# and will be commonly encountered throughout building data-centric applications. Many other classes that are not mentioned that implement the same FooConnection,FooCommand,FooDataReader classes can be expected to behave the same way.

Common Access Pattern for ADO.NET Connections

A common pattern that can be used when accessing your data through an ADO.NET connection might look as follows :

// This scopes the connection (your specific class may vary)
using(var connection = new SqlConnection("{your-connection-string}")
{
    // Build your query
    var query = "SELECT * FROM YourTable WHERE Property = @property");
    // Scope your command to execute
    using(var command = new SqlCommand(query, connection))
    {
         // Open your connection
         connection.Open();

         // Add your parameters here if necessary

         // Execute your query as a reader (again scoped with a using statement)
         using(var reader = command.ExecuteReader())
         {
               // Iterate through your results here
         }
    }
}

Or if you were just performing a simple update and didn't require a reader, the same basic concept would apply :

using(var connection = new SqlConnection("{your-connection-string}"))
{
     var query = "UPDATE YourTable SET Property = Value WHERE Foo = @foo";
     using(var command = new SqlCommand(query,connection))
     {
          connection.Open();
          
          // Add parameters here
          
          // Perform your update
          command.ExecuteNonQuery();
     }
}

You can even program against a set of common interfaces and not have to worry about the provider specific classes. The core interfaces provided by ADO.NET are:

  • IDbConnection - for managing database connections
  • IDbCommand - for running SQL commands
  • IDbTransaction - for managing transactions
  • IDataReader - for reading data returned by a command
  • IDataAdapter - for channeling data to and from datasets
var connectionString = "{your-connection-string}";
var providerName = "{System.Data.SqlClient}"; //for Oracle use "Oracle.ManagedDataAccess.Client"
//most likely you will get the above two from ConnectionStringSettings object

var factory = DbProviderFactories.GetFactory(providerName);

using(var connection = new factory.CreateConnection()) {
    connection.ConnectionString = connectionString;
    connection.Open();

    using(var command = new connection.CreateCommand()) {
        command.CommandText = "{sql-query}";    //this needs to be tailored for each database system

        using(var reader = command.ExecuteReader()) {
            while(reader.Read()) {
                ...
            }
        }
    }
}


Got any C# Language Question?