Database Access in C#

Comparing Database Drivers
Database drivers such as JDBC or ODBC can be used to access data in Java and C#. The Java Database Connectivity (JDBC) driver is used from a program written in Java. Open Database Connectivity (ODBC) is Microsoft's database programming interface for accessing a variety of relational databases on a number of platforms. There is also a JDBC-ODBC bridge standard on both the Solaris and Windows versions of the Java platform so you can also use ODBC from a Java program.

In C#, using the .NET Framework, you do not have to load ODBC or JDBC drivers in order to access the database. Simply set the connection string for the database connection object, as follows:

static string connectionString = "Initial Catalog=northwind;Data Source=(local);Integrated Security=SSPI;";
static SqlConnection cn = new SqlConnection(connectionString);

C# Read Database Example

In C#, using the .NET Framework, accessing data is further simplified through the set of classes provided by ADO.NET, which supports database access using ODBC drivers as well as through OLE DB providers. C# applications can interact with SQL databases for reading, writing, and searching data using.NET Framework's ADO.NET classes, and through a Microsoft Data Access Component (MDAC). The .NET Framework's System.Data.SqlClient namespace and classes make accessing SQL server databases easier.

In C#, to perform a database read operation, you can use a connection, a command, and a data table. For example, to connect to a SQL Server database using the System.Data.SqlClient namespace, you can use the following:

• A SqlConnection class.

• A query such as a SqlCommand class.

• A result set such as a DataTable class.

The .NET Framework provides the DataAdapter, which brings these three objects together, as follows:

• The SqlConnection object is set using the DataAdapter object's connection property.

• The query to execute is specified using the DataAdapter's SelectCommand property.

• The DataTable object is created using the Fill method of the DataAdapter object. The DataTable object contains the result set data returned by the query. You can iterate through the DataTable object to access the data rows using rows collection.

To compile and run the code, you need the following; otherwise, the line databaseConnection.Open(); fails and throws an exception.

• Microsoft Data Access Components (MDAC) version 2.7 or later.

  If you are using Microsoft Windows XP or Windows Server 2003, you already have MDAC 2.7. However, if you are using Microsoft Windows 2000, you may need to upgrade the MDAC already installed on your computer. For more information, see MDAC Installation.

• Access to the SQL Server Northwind database and integrated security privileges for the current user name running the code on a local SQL Server with the Northwind sample database installed.

// Sample C# code accessing a sample database

// You need:
//   A database connection
//   A command to execute
//   A data adapter that understands SQL databases
//   A table to hold the result set

namespace DataAccess
{
   using System.Data;
   using System.Data.SqlClient;

   class DataAccess
   {
       //This is your database connection:
       static string connectionString = "Initial Catalog=northwind;Data Source=(local);Integrated Security=SSPI;";
       static SqlConnection cn = new SqlConnection(connectionString);

       // This is your command to execute:
       static string sCommand = "SELECT TOP 10 Lastname FROM Employees ORDER BY EmployeeID";

       // This is your data adapter that understands SQL databases:
       static SqlDataAdapter da = new SqlDataAdapter(sCommand, cn);

       // This is your table to hold the result set:
       static DataTable dataTable = new DataTable();

       static void Main()
       {
           try
           {
               cn.Open();

               // Fill the data table with select statement's query results:
               int recordsAffected = da.Fill(dataTable);

               if (recordsAffected > 0)
               {
                   foreach (DataRow dr in dataTable.Rows)
                   {
                       System.Console.WriteLine(dr[0]);
                   }
               }
           }
           catch (SqlException e)
           {
               string msg = "";
               for (int i=0; i < e.Errors.Count; i++)
               {
                   msg += "Error #" + i + " Message: " + e.Errors[i].Message + "\n";
               }
               System.Console.WriteLine(msg);
           }
           finally
           {
               if (cn.State != ConnectionState.Closed)
               {
                   cn.Close();
               }
           }
       }
   }
}