HSQLDB.Net: .Net interface for HSQLDB Database


HSQLDB.Net is a library for HSQLDB that wrapper that DBMS engine’s Java classes and so provides an interface for C# Windows Forms and Console apps to existing HSQLDB databases. The library is provided on GitHub along with a sample console app that creates and exercises a database.

The GitHub Repository:

https://github.com/djaus2/HSQLDB.Net

About HSQLDB

“HSQLDB (HyperSQL DataBase) is the leading SQL relational database software written in Java. It offers a small, fast multithreaded and transactional database engine with in-memory and disk-based tables and supports embedded and server modes. It includes a powerful command line SQL tool and simple GUI query tools.”

The point is that it is Java based. There is not much support for using it with .NET. A search for “How can I access an HSQLDB from a .NET application?” only comes with ODBC as the solution. We need a suitable .NET wrapper class that can wrapper Java jar files. This repository goes a little way to address this issue.

Background

This is only a partial solution. The basis of this solution was provided by JuggerNET from CodeMesh To further develop the solution provided here you will need to install and licence that product.

Limitation

The solution is only for Windows forms x64/x86 or .NET Console apps. It is partially a prebuilt solution. No solution for Xamarin. No solution for .NET Core/.Net Standard.

Usage limitations: Please see the message from CodeMesh on the repository README

Getting started

Firstly you wil need a UI for creating HSQLDBs. I use RazorSQL: RazorSQL is a desktop application that provides users with the ability to query, edit, browse, and manage databases. To download RazorSQL, click the “Download” link next to the text below that best describes your computer. Please see the links below for installation and uninstallation procedures for the various operating systems supported by RazorSQL You can get a full trial version of it that lasts 30 days before you have to purchase it.

The App in the Project Solution

The provided app is a simple C# .NET Console app that opens an HSQLDB Database, writes some records to it and then queries for them.

The sample app
Output from the sample app

First time it runs you need to uncomment one line that creates the table, in Program.cs:

    //Update(conn, "CREATE TABLE sample_table ( id INTEGER IDENTITY, str_col VARCHAR(256), num_col INTEGER)");

Note that when you first connect to an HSQLDB, if the db doesn’t exist, it is created. You need to download and install HSQLDB The app needs a path to the HSQLDB.jar Note that if you are using an existing HSQDB with its own DBMS jar for it you add a reference to it. Nb: Just build and run the x64 solution.

Note that some of the prebuilt code will probably be excluded from the GitHub repository so I will link to this on my blog:

Download generated.zip from https://davidjones.sportronics.com.au/Other Download and unzip the folder. Copy its dotnet folder into the Solution-folder/generated folder.

Also you may need to tweak the jdbcr10-x64 project post build to find dumpbin on your system. You can cancel those check to simplify things.

Nb: Make sure you unblock the downloaded zip before extracting it’s contents

Similarly, also download and unzip packages.zip from the link as above. Place the packages folder in the Solution-Folder

You may need to tweak the console app project to find dumpbin on your system in the post build events. You might just comment these checks out if you wish.

One last thing. Need to have Java installed. Modify the line in Program.cs:

    loader.JvmPath = @"C:\Program Files\Java\jdk1.8.0_161\jre\bin\server\jvm.dll";

to point your jvm.dll (Make sure its x64) I could code this a bit better.

Using a Java Class

  1. First you link the Java loader to the class jar file eg 
         loader.JvmPath = @"C:\Program Files\Java\jdk1.8.0_161\jre\bin\server\jvm.dll"; 
     //loader.AppendBootClassPath = "hsqldb.jar";
     loader.AppendToClassPath("hsqldb.jar");
     loader.AppendToClassPath("myDB.jar");
     loader.AppendToClassPath("castor-0.9.3.21.jar");
       
     IJvm jvm = loader.Load();
     if (jvm != null)
     {
     ....
     }
  2. Make sure tha the class jar file/s is/are included in the built project
  3. Preload classes 
         Class.ForName("java.lang.String");

    Nb (From Java documentation): Class.ForName does not create an instance of the class. It can return a reference that can then be used to instantiate the class. It wil though run any static code in the class.

     Class.ForName("org.hsqldb.jdbcDriver");

    Preloads the JDBC driver

 TopicSubtopic
  Next: > Jekyll
<  Prev:    
   
 This Category Links 
Category:Database Index:Database