ToC path:

eXtremeSQL C# Example

This article is only applicable to .NET Framework 4.x. eXtremeDB for .NET 5 and later is documented in the eXtremeSQL C# API for .NET Core articles.

The following code snippet demonstrates a basic embedded eXtremeSQL C# application implementation:
    public static void Main(String[] args)
    {
        Database db;
        Database.Device[] devs;
        Database.Parameters parameters = new Database.Parameters();
        bool disk = false;
        bool shared = false;
        bool use_dptr = false;
        Database.Mode mode = 0;
        String binPath;

        // get config parameters from the command line
        foreach (String s in args)
        {
            switch (s)
            {
                case "disk":
                    disk = true;
                    mode |= Database.Mode.DiskSupport;
                    break;
                case "shm":
                    shared = true;
                    mode |= Database.Mode.SharedMemorySupport;
                    break;
                case "mvcc":
                    mode |= Database.Mode.MVCCTransactionManager;
                    break;
                case "dptr":
                    use_dptr = true;
                    break;
                case "debug":
                    mode |= Database.Mode.DebugSupport;
                    break;
                default:
                    binPath = s;
                    break;
            }
        }

        binPath = FindLibrary(use_dptr);
        int hint = use_dptr ? 0x20000000 : 0;

        parameters.MemPageSize = PAGE_SIZE; // memory page size
        parameters.Classes = new Type[] { typeof(MyTable) };
        parameters.MaxConnections = 10;

        if (disk) // on-disk configuration
        {

            if (System.IO.File.Exists("opendb.dbs"))
                System.IO.File.Delete("opendb.dbs");
            if (System.IO.File.Exists("opendb.log"))
                System.IO.File.Delete("opendb.log");

            parameters.DiskPageSize = DISK_PAGE_SIZE; // disk page size
            parameters.DiskClassesByDefault = true;   // mark @Persistent classes as

            // on-disk classes by default
            // declare memory devices
            devs = new Database.Device[4];

            if (shared)
            {
                devs[0] = new Database.SharedMemoryDevice(Database.Device.Kind.Data,
                                    "opendb-db", new IntPtr(hint),DATABASE_SIZE);
                devs[1] = new Database.SharedMemoryDevice(Database.Device.Kind.DiskCache,
                                    "opendb-cache", new IntPtr(hint),DISK_CACHE_SIZE);
            }
            else
            {
                devs[0] = new Database.PrivateMemoryDevice(Database.Device.Kind.Data,
                                    DATABASE_SIZE);
                devs[1] = new atabase.PrivateMemoryDevice(Database.Device.Kind.DiskCache,
                                    DISK_CACHE_SIZE);
            }

            devs[2] = new Database.FileDevice(Database.Device.Kind.Data, "opendb.dbs");
            devs[3] = new Database.FileDevice(Database.Device.Kind.TransactionLog, "opendb.log");

        }
        else // in-memory configuration
        {

            devs = new Database.Device[1];

            if (shared)
             {
                devs[0] = new Database.SharedMemoryDevice(Database.Device.Kind.Data,
                        "opendb-db", new IntPtr(hint), DATABASE_SIZE);
            }
            else
            {
                devs[0] = new Database.PrivateMemoryDevice(Database.Device.Kind.Data,
                                DATABASE_SIZE);
            }
        }

        // create Database object
        db = new Database(mode, binPath);
        db.Open("sqldb", parameters, devs); // open database.
     
Once the database has been opened, a connection must be created to access it. Applications can create connections via the Database class, for example:
    Database db = new Database(Database.Mode.DebugSupport,“../../target/bin.so");
    int maxAttempts = 10;
    SqlLocalConnection conLocal = db.ConnectSql();
    SqlRemoteConnection conRemote = db.ConnectRemoteSql(host, port, maxAttempts);
     
Or connections can be instantiated by calling the SqlLocalConnection or SqlRemoteConnection constructor. Note that one limitation for remote connections is that it is currently not possible to execute more than one query simultaneously in a single connection. So, for example, something like the following will not work:
    SqlResultSet rs1 = conRemote.executeQuery("select * from Class1");
    SqlResultSet rs2 = conRemote.executeQuery("select * from Class2");
    Iterator<Class1>  i1 = rs1.iterator();
    Iterator<Class2>  i2 = rs2.iterator();
    while (i1.hasNext() && i2.hasNext())
    {
        ...
    }
     
SQL queries are then performed using the ExecuteQuery() method which executes an SQL select and returns an SqlResultSet or the ExecuteStatement() method which executes an SQL insert, update or delete statement and returns the number of rows affected. For example:
        SqlLocalConnection con = db.ConnectSql();
            con.StartTransaction(Database.TransactionType.ReadWrite);
            con.ExecuteStatement("insert into MyTable (pk,value) values (?,?)", 2012,
                        "Good bye");

            con.ExecuteStatement("insert into MyTable (pk,value) values (?,?)", 2013,
                        "Hello");

            con.CommitTransaction();

            con.StartTransaction(Database.TransactionType.ReadOnly);

            using (SqlResultSet result = con.ExecuteQuery("select pk,value from MyTable”
                                “ where pk>=?", 2013))
            {
                foreach (string column in result.ColumnNames)
                {
                    Console.Write(column + ", ");
                }
                Console.WriteLine();
                foreach (SqlTuple tuple in result)
                {
                    Console.WriteLine(tuple[0] + ", " + tuple["value"]);
                }
            }
            con.CommitTransaction();

            con.Disconnect();
            db.Close();
            Console.ReadLine();
        }
    }
     
Note that a database transaction must be explicitly started with con.StartTransaction() and closed with con.CommitTransaction()or con.RollbackTransaction(). And the connection must be closed with con.Disconnect() and the database closed with db.Close(). Also note that both methods ExecuteQuery() and ExecuteStatement() allow substitution of parameters into the statement for placeholders marked by ? or %x, where x specifies a parameter type which can be one of the following:
    %b = bool
    %i = signed<4>
    %u = unsigned<4>
    %l = signed<8>
    %t = datetime
    %s = string
    %w = nstring
     
In most cases it is possible to use the traditional ODBC/JDBC placeholder ?. The specific parameter type specifiers are needed if you want to pass a string as unicode (wide character) string (%w ) rather than a multibyte-character string (%s). For example:
    con.ExecuteQuery("select * from Employee where department=? and salary > ?", "HR", 50000);
 
ExecuteQuery() returns an SqlResultSet object which implements the IEnumerable<SqlTuple> interface. It is recommended to use this SqlResultSet with a using statement, for example:
    using ( SqlResultSet result = con.ExecuteQuery(
    "select pk,value from MyTable where pk>=?", 2013) )
    {
        foreach (string column in result.ColumnNames)
        {
            Console.Write(column + ", ");
        }
        Console.WriteLine();
        foreach (SqlTuple tuple in result)
        {
            Console.WriteLine(tuple[0] + ", " + tuple["value"]);
        }
    }
     
The using clause causes invocation of the Dispose() method on exit from its scope which releases the query resources. It is also possible to get the number and list of the result column names. An SqlCursor instance is returned by SqlResultSet.GetEnumerator() which implements the IEnumerator<SqlTuple> interface and provides a one-way iteration through the selected tuples. (Note that it is not possible to reset the iterator to restart traversal.) The SqlTuple class represents a collection of column values. It provides methods for getting the number of columns (though it is better to get this number from SqlResultSet rather than from the tuple) and for extracting the value of a particular column.

Columns can be accessed either by index (0-based), or by name as in the example above. The type of the returned value is:

bool for boolean types,

long for integer types,

double for floating point types,

decimal for floating numeric types,

String for character types

byte[] for blob types, and

DateTime for the datetime type.

Accessing a tuple's value by index is recommended since using the column name requires a hash lookup. The column name can be used once by the SqlResultSet.GetColumnNo(string columnName) method to get the column’s index; then use the returned index to fetch the tuple's value.