Database Access (C# vs Java)

    C# and Java use similar means for accessing database data. Both C# and Java require a database driver to perform the actual database operations. In addition, both require a database connection, a SQL query to execute against the database connection, and a result set from the execution of the query.  

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 Java, the connection string information is supplied to the driver for a connection handle, as follows:

final static private String url = "jdbc:oracle:server,user,pass, …)";

   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); 

 Java Read Database Example
   In Java, to perform a database read operation, you can use a ResultSet object created by the executeQuery method of the Statement object. The ResultSet object contains the data returned by the query. You can then iterate through the ResultSet object to access the data.
   The following example provides the Java code to read from a database.

Connection c;
try
{
    Class.forName (_driver);
    c = DriverManager.getConnection(url, user, pass);
} 
catch (Exception e) 
{
    // Handle exceptions for DriverManager
    // and Connection creation:
}
try
{
    Statement stmt = c.createStatement();
    ResultSet results = stmt.executeQuery(
                "SELECT TEXT FROM dba ");
    while(results.next())
    {
        String s = results.getString("ColumnName");
        // Display each ColumnName value in the ResultSet:
    }
    stmt.close();
} 
catch(java.sql.SQLException e)
{
    // Handle exceptions for executeQuery and getString: 
}

   Similarly, to perform a database write operation, a Statement object is created from the Connection object. The Statement object has methods for executing SQL queries and updates against a database. Updates and queries are in the form of a string containing the SQL command of a write operation used in the executeUpdate method of the Statement object to return a ResultSet object.

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