ADO.NET, Getting Started Sample

From SwinBrain

In this article we will look at setting up a basic ADO.NET connection to a database, and then using this connection to read and display data from the database. The ADO.NET page outlines the overall architecture of ADO.NET, if you are unfamiliar with ADO.NET please read the ADO.NET page first.

Download the Managed Provider

Before you can complete this sample you will need to download the Firebird database engine, as described in Create a Firebird Database, as well as the Firebird Managed Provider. Once the provider is installed note down the location of the provider's DLL, for example C:\Program Files\FirebirdClient\FirebirdSql.Data.FirebirdClient.dll. You will need to use this as a reference when compiling the sample.

Step 1: Setting the Provider

The first step with programming with ADO.NET is to get a instance of a DbProviderFactory. This is not strictly required, but can be used to abstract the exact DBMS that you are interacting with. In this example we will hard code the provider by asking for the FirebirdClientFactory.Instance, the DbProviderFactory used to connect to Firebird SQL DBMS.

using System;
using System.Data;
using System.Data.Common;
using FirebirdSql.Data.FirebirdClient;
 
///<summary>This sample illustrates the reading
/// of data from a Firebird database using the 
/// embedded .NET client.
///</summary>
public class Sample
{
  public static void Main()
  {
    //Step 1: Get Provider
 
    DbProviderFactory f = FirebirdClientFactory.Instance;
  }
}

Step 2: Creating a Connection

At this point we need a database to connect to. We will be using the database that is created in the Create a Firebird Database article.

With the provider's factory we can now create a variety of ADO.NET objects starting with a DbConnection. The DbConnection will allow us to communicate with a DBMS and Database.

The following code uses the Factory to create a connection to the database. The ConnectionString property informs the DBMS of the database we want to connect to. This example use Firebird, and the connection string indicates that the database is Sample.fdb in the current directory. The ServerType is set to 1 for an embedded DBMS, there are a number of other connection string parameters that can be configured.

public class Sample
{
  public static void Main()
  {
    //Step 1: Get Provider
    ...
    //Step 2: Create a connection
 
    DbConnection con = f.CreateConnection();
    con.ConnectionString = "Database=Sample.fdb;User=sysdb;password=sysdb;ServerType=1;";
    con.Open();
 
    using(con)
    {
    }
  }
}

Step 3: Creating a Command

Now that we have a connection that is open and ready to use we can create a Command object and execute it across the connection. The factory is used to create the DbCommand object using the CreateCommand factory method. The command returned from this method is not configured and must have its Connection and CommandText properties set. The CommandText should be set to the SQL statement to be executed. With this configured you call the ExecuteReader method to execute the command and fetch the DbDataReader used to read the results.

public class Sample
{
  public static void Main()
  {
    //Step 1: Get Provider
    ...
    //Step 2: Create a connection
    ...
 
    using(con)
    {
      //Step 3: Create a Command
      DbCommand stmt = f.CreateCommand();
      stmt.Connection = con;
      stmt.CommandText = "SELECT ProductCode, Name, Cost FROM Product ORDER BY Cost";
 
      DbDataReader reader = stmt.ExecuteReader();
    }
  }
}

Step 4: Read the results

Now we have the DbDataReader we can use it to read the result set from the command.

public class Sample
{
  public static void Main()
  {
    //Step 1: Get Provider
    ...
    //Step 2: Create a connection
    ...
 
    using(con)
    {
      //Step 3: Create a Command
      ...
      using(reader)
      {
        //Step 4: Reading the results
        string id, name;
        decimal cost;
 
	while(reader.Read())
        {
          id = reader["ProductCode"] as String;
          name = reader["Name"] as String;
          cost = Convert.ToDecimal(reader["Cost"]);
 
          Console.WriteLine("{0}: {1} {2:C}", id, name, cost);	
        }
      }
    }
  }
}

Step 5: Closing Everything

With ADO.NET you need to close the DbConnection and the DbDataReader. Fortunately the using statement will take care of closing both the connection and the data reader. If you want you can manually close these anyway using the Close method.

public class Sample
{
  public static void Main()
  {
    //Step 1: Get Provider
    ...
    //Step 2: Create a connection
    ...
 
    using(con)
    {
      //Step 3: Create a Command
      ...
      using(reader)
      {
        //Step 4: Reading the results
        ...
 
        //Step 5: Close everything
        reader.Close();
      }
      con.Close
    }
  }
}

Step 6: Compile and Run

Now you can compile and run the code to check that it is working correctly. The following code will create a Sample.exe application. You may need to adjust the path of the provider's DLL depending on where you installed it.

csc /nologo /r:"C:Program FilesFirebirdClientFirebirdSql.Data.FirebirdClient.dll" Sample.cs

Links

Links from SwinBrain.

• Download the source code associated with this sample
• Read about the architecture of ADO.NET
• See how JDBC achieves the same result in the JDBC, Getting Started Sample how to

External links

• API links from the MSDN, DbProviderFactory, DbCommand, DbConnection, DbDataReader