Slitherine

// trackerdbAccess
//
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
// <http://www.gnu.org/licenses/>.
//------------------------------------------------------------------------

namespace trackerdbAccess
{
    /// .NET System lib is used to display text from trackerdb in a console window.
    using System;

    // IKVM.NET enables running compiled Java code (byte code) directly on Microsoft
    // .NET or Mono. The byte code is converted on the fly to CIL and executed.
    // You can use the Nuget package manager to add IKVM to your .NET project.
    // IKVM.NET is free software, distributed under a permissive free software license.
    using ikvm;

    // Tracker's database is a HyperSQL database.
    // You must use IKVM and the console app to create the HyperSQL lib.
    using org.hsqldb;

    // To connect with SQL databases, the Java Database Connectivity API (JDBC) requires
    // a driver. The JDBC driver provides the connection to the database and implements
    // the protocol for transferring the query and result between client and database.
    // A JDBC driver is used to access Tracker's HyperSQL database.
    // Java libs are automatically available to your project once you add IKVM via Nuget.
    using java;

    class Program
    {
        // Even though I used using declarations above, I use fully qualified domains below for clarity. 
        static void Main(string[] args)
        {
            // Instantiate the database interface as null before the try-catch-final block.
            // As of at least Java 7 Datasource is now the preferred interface, but its not yet available via IKVM. 
            java.sql.Connection conn = null;

            // Instantiate a Result Set object as null to hold the table of data generated by your query.
            // ResultSet also provides the methods used to view your query results.
            java.sql.ResultSet rs = null;
            
            // For pedagogical reasons, I use three variables to provide required information to the database interface.
            string url = @"jdbc:hsqldb:file:c:\Database\trackerdb";    // the interface, driver, HyperSQL database type, database location
            string username = "sa";  // HyperSQL's default admin account.
            string password = "";

            try
            {
                // Register the HyperSQL driver with the DriverManager, a basic service that will manage the driver. 
                // Note that Java apps no longer need to explicitly load JDBC drivers using Class.forName().
                System.Console.WriteLine("Registering HyperSQL driver . . .");
                java.sql.DriverManager.registerDriver(new org.hsqldb.jdbcDriver());

                // Establish a connection to the Tracker database using the driver you registered above with DriverManager.
                System.Console.WriteLine("Establishing connection to database . . .");
                conn = java.sql.DriverManager.getConnection(url, username, password);

                // Create a Statement object that will be used to execute and return the results of your sql query.
                System.Console.WriteLine("Creating SQL query . . .");
                java.sql.Statement st = conn.createStatement();

                // Declare a string variable and assign it your sql query.
                string query = "SELECT NAME FROM Aircraft WHERE ID = 160;";

                // Execute the query and assign the results to your ResultSet object.
                rs = st.executeQuery(query);

                // Advance the cursor within the ResultSet and display the value your query obtained that is in ResultSet row 1.
                // I'm a little confused why I had to advance the cursor and will be reading a lot more about ResultSets.
                // Critical Tip: HyperSQL comes with a great Java utility you can use to view the structure of the Tracker database.
                // Using the utility I know that the aircraft assigned ID 1 is "Anson I".
                rs.next();
                System.Console.WriteLine(rs.getString(1)); 

                // Keep the console window open in debug mode until you can read the result.
                System.Console.WriteLine("Press any key to exit.");
                System.Console.ReadKey();

            }

            // Just a lazy bare minimum exception implementation for step debugging.
            catch (System.Exception ex)
            {
                throw ex;
            }

            // You want to make sure to release resources and close the database if you made a connection.
            finally
            {
                // Release resources allocated to your ResultSet.
                if (rs != null)
                {
                    rs.close();
                    rs = null;
                }

                if (conn != null)
                {
                    //  Release the Connection object's database and JDBC resources
                    //  immediately instead of waiting for them to be automatically released.
                    conn.close();
                    conn = null;
                }
            }
        }
    }
}