Using JDBC from .NET

Introduction

If you've ever had to switch database drivers you know what a hassle that can be:

• different performance issues.
• different bugs.
• different application behavior.

Many applications standardize on one or a few drivers for just that reason. If your environment is predominantly Java-based, your (JDBC) drivers won't help you much with .NET applications, unless you're willing to entrust your data health to the JDBC-ODBC bridge.

Exposing your Java data architecture to .NET can prevent driver-related problems or it can even allow you to add one of many free embedded Java database engines to your .NET application.

This is not the most compelling use case for most users, but we have a couple of customers who had such big problems with their database layers that they chose this integration route.

Architecture

Your .NET application uses generated in-process bindings to use the JDBC API. The following example code is from the bundled hsqldb example. hsqldb is a lightweight, embedded Java database, that you can now use within your .NET application.

using System;
using Codemesh.JuggerNET;
using Java.Lang;
using Java.Sql;
using Java.Util;


public class Application
{
	public static void	Main( string[] args )
	{
        //the database filename
        string	db_file_name_prefix = args.Length > 0 && !"-info".Equals( args[ 0 ].ToLower() ) ? args[ 0 ] : "test_db";

        //for now just terminate if we're invoked with the -info option
        if( args.Length > 0 && "-info".Equals( args[ 0 ].ToLower() ) ) 
            return;

        //the database connection
        Connection		conn = null;

        try
        {
            IJvmLoader	loader = JvmLoader.GetJvmLoader( true, true, TraceFacility.TraceJvm, TraceLevel.TraceErrors );

            // put the hsqldb classes (driver) on the bootclasspath to work around a 
            // limitation in the DriverManager when invoked from .NET
            loader.AppendBootClassPath = "../lib/hsqldb.jar;../../lib/hsqldb.jar";

            // make the JDBC driver available by preloading it
            Class.ForName("org.hsqldb.jdbcDriver");

            // connect to the database.   This will load the db files and start the
            // database if it is not alread running.
            // db_file_name_prefix is used to open or create files that hold the state
            // of the db.
            // It can contain directory names relative to the
            // current working directory
            conn = DriverManager.GetConnection( "jdbc:hsqldb:" + db_file_name_prefix,
                                                "sa",                     // username
                                                "");                      // password

            // create a table and insert some entries
            // this will only worl the first time and throw an exception every following time
            // because the table already exists 
            try
            {
                Update( conn, "CREATE TABLE sample_table ( id INTEGER IDENTITY, str_col VARCHAR(256), num_col INTEGER)");

                // add some rows - will create duplicates if run more then once
                // the id column is automatically generated
                Update( conn, "INSERT INTO sample_table(str_col,num_col) VALUES('Ford', 100)");
                Update( conn, "INSERT INTO sample_table(str_col,num_col) VALUES('Toyota', 200)");
                Update( conn, "INSERT INTO sample_table(str_col,num_col) VALUES('Honda', 300)");
                Update( conn, "INSERT INTO sample_table(str_col,num_col) VALUES('GM', 400)");
                Update( conn, "INSERT INTO sample_table(str_col,num_col) VALUES('BMW', 80)");
                Update( conn, "INSERT INTO sample_table(str_col,num_col) VALUES('Mercedes-Benz', 60)");
                Update( conn, "INSERT INTO sample_table(str_col,num_col) VALUES('VW', 800)");
            }
            catch( SQLException sqle )
            {
                Console.WriteLine( sqle.Message );
            }
            catch( Throwable it )
            {
                Console.WriteLine( it.StackTrace );
            }
            catch( System.Exception ie )
            {
                Console.WriteLine( ie.ToString() );
            }

			try
			{
				Console.WriteLine( "---------------" );

				// do a query
				Query( conn, "SELECT * FROM sample_table WHERE num_col < 250");

				Console.WriteLine( "---------------" );

				// do another query
				Query( conn, "SELECT str_col FROM sample_table WHERE num_col >= 100");

				Console.WriteLine( "---------------" );

				conn.Close();
			}
			catch( Throwable t )
			{
				Console.WriteLine( "Caught throwable of type {0}", t.GetClass().GetName() );
				Console.WriteLine( t.ToString() );
				return;
			}
			catch( JuggerNETFrameworkException jnfe )
			{
				Console.WriteLine( "Caught framework exception" );
				Console.WriteLine( jnfe.Message );
				Console.WriteLine( jnfe.StackTrace );
				return;
			}

			return;
		}
		catch( System.Exception se )
		{
			Console.WriteLine( se.ToString() );
		}
	}

	public static void	Dump( ResultSet rs )
	{
		// the order of the rows in a cursor
		// are implementation dependent unless you use the SQL ORDER statement
		ResultSetMetaData meta   = rs.GetMetaData();
		int               colmax = meta.GetColumnCount();
		int               i;
		object            o = null;

		// the result set is a cursor into the data.  You can only
		// point to one row at a time
		// assume we are pointing to BEFORE the first row
		// rs.next() points to next row and returns true
		// or false if there is no next row, which breaks the loop
		for (; rs.Next(); ) 
		{
			for (i = 0; i < colmax; ++i) 
			{
				o = rs.GetObject(i + 1);    // Is SQL the first column is indexed

				// with 1 not 0
				Console.Write( "{0} ", o.ToString() );
			}

			Console.WriteLine( "" );
		}
	}


	public static void Update( Connection conn, String expression)
	{
		Statement st = null;

		st = conn.CreateStatement();    // statements

		int i = st.ExecuteUpdate(expression);    // run the query

		if (i == -1) 
		{
			Console.WriteLine( "db error : {0}", expression );
		}

		st.Close();
	}


	public static void		Query( Connection conn, String expression )
	{
		Statement st = null;
		ResultSet rs = null;

		st = conn.CreateStatement();         // statement objects can be reused with

		// repeated calls to execute but we
		// choose to make a new one each time
		rs = st.ExecuteQuery(expression);    // run the query

		// do something with the result set.
		Dump(rs);
		st.Close();    
		// NOTE!! if you close a statement the associated ResultSet is closed 
		// too so you should copy the contents to some other object.
		// the result set is invalidated also  if you recycle an Statement
		// and try to execute some other query before the result set has been
		// completely examined.
	}
}