Welcome to weblogs.com.pk Sign in | Join | Help

Embedded Databases :: SharpHSQL :: Enhancing SharpHSQLReader to Support Data Binding

Data plays the centre role in today’s software. We are exposed to abstract data structures like arrays, stack, queues, linked lists, trees and maps etc, and then on the other hand we have relational, hierarchical and object database management systems. Usually such data management systems are “out of process” and most of our time is spend bringing data in and out of these systems, which of course has its own implications. We as developers try to reduce these database “hits” for the performance sake.

 

There can be number of scenarios where we can not avoid such hits. For instance comparing two records (may be in a single database or across different databases), sequential processing of large streams/records etc. As hardware costs are decreasing, client/front-end hardware is becoming powerful, thus letting us use the “embedded database management system”. These systems do not run as a separate process, instead it’s linked into the client/front-end application. These are smaller in size, better performing (for small set of record) and require less maintenance.

 

HSQLDB (derived from Hypersonic SQL) is one such popular embedded database system with support of ANSI-92 SQL and standard JDBC drivers. SharpHSQL (Sharp Hypersonic SQL) is a .NET port of Hypersonic SQL. (It’s free and comes with source) The latest release of SharpHSQL now supports .NET data provider, which means anyone exposed to ADO.NET can easily start using SharpHSQL. With this latest release we can also use .NET assemblies “inside the engine” (smells like CLR in Yukon and Java stored procedures in Oracle)

 

Data binding with SharpHSQLReader

 

I myself started using SharpHSQL wherever it fits (well). Soon I discovered that SharpHSQL Data Reader doesn’t support data binding, I was unable to bind controls like Data Grid to its Data Reader. I had Mono’s code lying on hard-disk, I opened its folder to find how they have done it and what pieces are missing in SharpHSQL code.

 

Here are my modifications in System.Data.HSQL.SharpHSQLReader class

 

1-     Inherit the class from System.Collections.IEnumerable

2-     Implement IEnumerable (See listing 1)

3-     Update SharpHSQLReader.GetDataTypeName() implementation (See listing 2)

4-     Test your modification, by compiling SharpHSQL project and using it in some test project. (See listing 3)

 

Future Work

 

I have just enhanced the data reader provider to support web controls. My little Windows forms control testing reveals that we need to support other interfaces, as I got exception saying “Additional information: Complex DataBinding accepts as a data source either an IList or an IListSource”

 

Listing 1

 

#region IEnumerable Members

bool enumerating = false;

public IEnumerator GetEnumerator() {

       this.enumerating = true;

       return new System.Data.Common.DbEnumerator(this);

}

#endregion

Listing 2

 

public string GetDataTypeName(int i) {

       if (this.enumerating) {

              if( _first ) {

                     return this._rs.rRoot.data[i].GetType().Name;

              }

              else {

                     return _current.data[i].GetType().Name;

              }

       }

       else {

              return _current.data[i].GetType().Name;

       }

}

 

Listing 3

 

private void Page_Load(object sender, System.EventArgs e)

{

       string connectionString = string.Format("Initial Catalog={0};User Id=sa;Pwd=;", this.Server.MapPath("mytest"));

       SharpHSQLConnection conn = new SharpHSQLConnection(connectionString);

       conn.Open();

       SharpHSQLTransaction tran = conn.BeginTransaction();

       SharpHSQLCommand cmd = new SharpHSQLCommand("", conn);

       int res;

       cmd.CommandText = "DROP TABLE IF EXIST \"clientes\";CREATE CACHED TABLE \"clientes\" (\"id\" int NOT NULL IDENTITY PRIMARY KEY, \"DoubleValue\" double, \"nombre\" char);";

       res = cmd.ExecuteNonQuery();

       for(int i=0;i<10;i++)

       {

              cmd.CommandText = "INSERT INTO \"clientes\" (\"DoubleValue\", \"nombre\") VALUES (1.1, 'NOMBRE" + i.ToString() + "');";

              res = cmd.ExecuteNonQuery();

       }

       cmd.CommandText = "SELECT \"clientes\".\"id\", \"clientes\".\"DoubleValue\", \"clientes\".\"nombre\" FROM \"clientes\"";

       IDataReader reader = cmd.ExecuteReader();

       this.DataGrid1.DataSource = reader;

       this.DataGrid1.DataBind();

       reader.Close();

       tran.Commit();

       conn.Close();

       conn = null;

}

 

Published Sunday, January 16, 2005 12:42 PM by khurram
Filed under: ,

Comments

# re: Embedded Databases :: SharpHSQL :: Enhancing SharpHSQLReader to Support Data Binding

Sunday, January 16, 2005 12:47 PM by Khurram AzizT
The test code above is taken from SharpHSQL test cases; which comes with its source distribution.
New Comments to this post are disabled