Breaking News       1200+ New Mid-size Indian Companies Adopt Oracle Database Over Two Years           Independent Survey Reveals Oracle HCM Offerings Are A Top ChoiceAmong Asia Pacific Organisations          Molex Releases PowerCat 6A Shielded Solution          Molex Introduces New ModLink Plug & Play Copper Solution          Research Backbone of IDS Softwares         




Blooming Distributed Database Access With Java and C#

Posted On January 4, 2008 by Rose Mary filed under

This article introduces the database connectivity with Java and C# . The main purpose of this article is to introduce the reader about the blooming options available in the market for distributed database access. In this article more concentration is given on the scope .NET platform and C# for distributed database access . 

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. 

Database Access Layer :

The Database Access Layer provides a uniform mechanism for efficient database accesses and encapsulation of database system aspects.
Use a layered architecture consisting of two layers. The Logical Access Layer provides the stable application kernel’s interface, while the Physical Access Layer accesses the database system. The latter may adapt to changing performance needs. Use a Query Broker to decouple both layers.(Figure-1)



Driving forces in Database access:
· Separation of concerns versus cost of programming. 
· Ease-of-use versus power of an interface. 
· Performance of the resulting solution. 
· Flexibility versus complexity. 
· Possible integration of legacy systems versus optimal design for non legacy data. 

The New Generation of JDBC 

Java Database Connectivity (JDBC), which has existed from the first public version of the core Java language, has evolved significantly over the last 10 years. In its current version, 4.0, which is packaged with Java Standard Edition 6.0 (Java SE is Sun's new name for J2SE), it shows significant improvements in design and provides a richer API, with focus on ease of development and improvement in productivity.

The new API defines a set of Query and DataSet interfaces. The Query interface defines a set of methods decorated with the JDBC annotations. These decorated methods describe the SQL select and update statements, and specify how the result set should be bound to a DataSet. The DataSet interface is a parameterized type, as defined by generics. The DataSet interface provides a type-safe definition for the result set data. 
All Query interfaces inherit from the BaseQuery interface. A concrete implementation of the interface can be instantiated using either the Connection.createQueryObject() or DataSource.createQueryObject() methods and passing a Query interface type as its parameter. 

A DataSet interface inherits from java.util.List. A data class describing the columns of the result set data, returned by an annotated method of the Query interface, is its parameter type. A DataSet can be manipulated and operated upon both in a connected and disconnected mode. Thus, the DataSet is implemented either as a ResultSet or a CachedRowSet, depending on its operating mode: connected or disconnected. DataSet, being a sub-interface of the java.util.List, allows access of its data rows with the Iterator pattern, using the java.util.Iterator interface.

The data class or the user-defined class, which is a parameter type of the DataSet interface, can be specified in two ways: as a structure or as a JavaBeans object. Either method achieves the goal of binding result set data columns to user-defined class definitions, but the JavaBeans component model is more elegant and facilitates object definition reuse within other frameworks that support the JavaBeans model. 

Following code segment[Code-1] is the simple example which illustrates how the new API is used to create and run SQL queries, define result set data using a user-defined class, and bind the returned result set to the user-defined specifications.

..................................................................................................................................................
Code-1:

pubic class Employee {
private int employeeId;
private String firstName;
private String lastName;

public int getEmployeeId() {
return employeeId;
}

public setEmployeeId(int employeeId) {
this.employeeId = employeeId;
}

public String getFirstName() {
return firstName;
}

public setFirstName(String firstName) {
this.firstName = firstName;
}

public String getLastName() {
return lastName;
}

public setLastName(String lastName) {
this.lastName = lastName;
}
}

interface EmployeeQueries extends BaseQuery {
@Select (sql="SELECT employeeId, firstName, lastName FROM employee")
DataSet<Employee> getAllEmployees ();

@Update (sql="delete from employee")
int deleteAllEmployees ();
}


Connection con = ...........

EmployeeQueries empQueries = con.createQueryObject (EmployeeQueries.class);

DataSet<Employee> empData = empQueries.getAllEmployees ();

Data Access in .NET Environment

There are three Oracle methods for .NET data access that can be invoked by any .NET compliant programming language. These data access drivers provide high performance and compliance with the latest .NET specifications. ODBC.NET and OLE DB.NET use the Oracle database ODBC and OLEDB drivers. These drivers have been thoroughly tested with .NET to ensure stability and high performance. Oracle also provides the Oracle Data Provider for .NET which is a native implementation. Oracle recommends this as the preferred .NET data access method.

Developers could also invoke some of Oracle’s COM based data access methods through the .NET programming framework. Oracle COM Automation feature which allows callouts to COM clients from the database could also invoke a .NET application/Web Service. Developers could also use Java stored procedures to call Web services from the database.



Sample C# code accessing a sample database:

......................................................................................................................................................................
Code-2

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

Data Access from SQL Server in C# :

To access SQL Server database and execute Stored Procedures. If you need Dataset, you can use GetDataSet method or if you need DataReader use GetDataReader method and so on.

......................................................................................................................................................................

Code-3

using System;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient; 
public class DataAccess
{ 
//************************************** 
//* Purpose: Accessing SQL database 
//*Methods: 
//*GetDataSet
//*RunProc
//*GetDataReader
//*GetDataView
//* ************************************* 
public DataSet GetDataSet 
(string strConnect,string[] ProcName , string[] DataTable)
{ 
//******************************** 
//* Purpose: Returns Dataset for one or multi datatables 
//* Input parameters: 
//*strConnect----Connection string 
//*ProcName() ---StoredProcedures name in array 
//*DataTable()---DataTable name in array 
//* Returns : 
//*DataSet Object contains data 
//************************************************** 
DataSet dstEorder ; 
SqlConnection conn; 
SqlDataAdapter dadEorder; 
try 
{ 
int intCnt = ProcName.GetUpperBound(0); 
dstEorder = new DataSet(); 
conn = new SqlConnection(strConnect); 
// if one datatable and SP 
if(intCnt == 0) 
{ 
dadEorder = new SqlDataAdapter(ProcName[0], conn); 
dadEorder.Fill(dstEorder, DataTable[0]); 
} 
// more than one datatable and one SP 
else 
{ 
conn.Open(); 
//add first data table and first SP 
dadEorder = new SqlDataAdapter(ProcName[0], conn); 
dadEorder.Fill(dstEorder, DataTable[0]); 
// add second datatable and second SP onwards 
for(int i=1 ;i< (intCnt +1) ;i++) 
{ 
dadEorder.SelectCommand = new SqlCommand(ProcName[i], conn); 
dadEorder.Fill(dstEorder, DataTable[i]); 
} 
conn.Close(); 
} 
return dstEorder; 
} 
catch ( Exception objError) 
{ 
//write error to the windows event log 
WriteToEventLog(objError); 
throw; 
} 
} 
public void RunProc(string strConnect,string ProcName) 
{ 
//**************************************** 
//* Purpose: Executing Stored Procedures where UPDATE, INSERT 
//*and DELETE statements are expected but does not 
//*work for select statement is expected. 
//* Input parameters: 
//*strConnect----Connection string 
//*ProcName ---StoredProcedures name 
//* Returns : 
//*nothing 
//* ***************************************
string strCommandText= ProcName; 
//create a new Connection object using the connection string 
SqlConnection objConnect =new SqlConnection(strConnect); 
//create a new Command using the CommandText and Connection object 
SqlCommand objCommand =new SqlCommand(strCommandText, objConnect); 
try 
{ 
objConnect.Open(); 
objCommand.ExecuteNonQuery(); 
} 
catch(Exception objError) 
{ 
//write error to the windows event log 
WriteToEventLog(objError); 
throw; 
} 
finally 
{ 
objConnect.Close(); 
} 
} 
public SqlDataReader GetDataReader(string strConnect, string ProcName) 
{ 
//************************************** 
//* Purpose: Getting DataReader for the given Procedure 
//* Input parameters: 
//*strConnect----Connection string 
//*ProcName ---StoredProcedures name 
//* Returns : 
//*DataReader contains data 
//* ************************************
string strCommandText= ProcName; 
SqlDataReader objDataReader; 
//create a new Connection object using the connection string 
SqlConnection objConnect =new SqlConnection(strConnect); 
//create a new Command using the CommandText and Connection object 
SqlCommand objCommand = new SqlCommand(strCommandText, objConnect); 
try 
{ 
//open the connection and execute the command 
objConnect.Open(); 
//objDataAdapter.SelectCommand = objCommand 
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection); 
} 
catch( Exception objError) 
{ 
//write error to the windows event log 
WriteToEventLog(objError); 
throw; 
} 
return objDataReader; 
} 
public DataView GetDataView ( string strConnect,string ProcName,string DataSetTable) 
{ 
//***************************************** 
//* Purpose: Getting DataReader for the given Procedure 
//* Input parameters: 
//* strConnect----Connection string 
//* ProcName ---StoredProcedures name 
//* DataSetTable--DataSetTable name sting 
//* Returns : 
//* DataView contains data 
//* **************************************** 
string strCommandText= ProcName; 
//create a new Connection object using the connection string 
SqlConnection objConnect = new SqlConnection(strConnect); 
//create a new Command using the CommandText and Connection object 
SqlCommand objCommand= new SqlCommand(strCommandText, objConnect); 
//declare a variable to hold a DataAdaptor object 
SqlDataAdapter objDataAdapter = new SqlDataAdapter();
try 
{ 
//open the connection and execute the command 
objConnect.Open(); 
objDataAdapter.SelectCommand = objCommand; 
//objDataReader = objCommand.ExecuteReader() 
} 
catch(Exception objError) 
{ 
//write error to the windows event log 
WriteToEventLog(objError); 
throw; 
} 
DataSet objDataSet; 
objDataSet = new DataSet(DataSetTable); 
objDataAdapter.Fill(objDataSet); 
DataView objDataView ; 
objDataView = new DataView(objDataSet.Tables[0]); 
objConnect.Close(); 
return objDataView; 
} 
private void WriteToEventLog( Exception objError) 
{ 
//************************************* 
//* Purpose:Writing error to the windows event log 
//* Input parameters: 
//*objError----Exception object 
//* Returns : 
//*nothing 
//* ***************************************************
System.Diagnostics.EventLog objEventLog = new System.Diagnostics.EventLog(); 
objEventLog.Source = "Your Application Name"; 
objEventLog.WriteEntry(objError.Message.ToString()); 
}
} 

Conclusion

This article covers most of the phases of the distributed database access .It depends upon the developers to choose and use according to the requirement and ease of programming. 

References

[R1]:Database Programming with C# By Carsten Thomsen , Apress Pub. 
[R2]:http://java.sun.com/products/jdbc/
[R3]:http://www.javaworld.com/channel_content/jw-jdbc-index.shtml 
[R4]: http://www.microsoft.com




Added on January 4, 2008 Comment

Comments

Post a comment