.NET (C#)
1. Two interfaces to access the same data

LeanXcale is a SQL database with NoSQL characteristics. As a relational key-value database, LeanXcale provides access to the same data through two independent APIs:
-
A 2003 ANSI SQL interface that is powerful and easy to use, but can be slow, so is most suitable for complex queries (e.g., multijoin queries).
-
A proprietary key-value interface, called KiVi, is fast and provides all functionality except for join operations, so it is most suitable for simpler queries where the overhead of SQL is too costly (e.g., updates and inserts).
Both interfaces interact with the same data and can be part of the same transaction.
2. SQL

2.1. Prerequisites
The SQL interface in LeanXcale is exposed to C# through our ODBC connector, so you’ll need to install it first. Please, refer to the documentation to install the ODBC connector for Windows or Linux and then come back here to continue configuring the connection.
2.2. Quick Start
2.2.1. Connecting to LeanXcale
The first thing to do is connect to the database using the same DSN
name we put in odbc.ini/.odbc.ini
file in previous steps:
string connetionString = "DSN=LeanXcale";
OdbcConnection connection = new OdbcConnection(connetionString);
connection.Open();
Keep in mind that if you are connecting to a LeanXcale database working with security, you will need some changes in your ODBC configuration. Check them here.
Remember that C# will automatically close the connection when your script ends, but you can close it by hand if you need so:
connection.Close();
2.2.2. Creating a table
To create a new table in LeanXcale, you can use the following code:
command = new OdbcCommand("CREATE TABLE persons (socialsecurity VARCHAR(15),"
+ "firstname VARCHAR(40), lastname VARCHAR(40), city VARCHAR(50),"
+ "PRIMARY KEY(socialsecurity))", connection);
command.ExecuteNonQuery();
2.2.3. Inserting, updating and deleting
Inserting one Record:
Inserting one row is a very easy task:
command = new OdbcCommand("INSERT INTO persons (socialsecurity, firstname, lastname, city)"
+ "VALUES ('728496923-J', 'John', 'Smith', 'San Francisco')", connection);
command.ExecuteNonQuery();
Or with prepared statements:
command = new OdbcCommand("INSERT INTO persons (socialsecurity, firstname, lastname, city)"
+ "VALUES (?, ?, ?, ?)", connection);
command.Prepare();
command.Parameters.Add("@ss", OdbcType.VarChar).Value = "7691241241-Z";
command.Parameters.Add("@fn", OdbcType.VarChar).Value = "Carl";
command.Parameters.Add("@ln", OdbcType.VarChar).Value = "Parson";
command.Parameters.Add("@ct", OdbcType.VarChar).Value = "New York";
command.ExecuteNonQuery();
command.Parameters.Clear();
2.2.4. Reading and scanning Data
command = new OdbcCommand("SELECT * FROM persons"
+ " WHERE city = 'Madrid'", connection);
OdbcDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("Social security number: {0}", reader[0]);
Console.WriteLine("First name: {0}", reader[1]);
Console.WriteLine("Last name: {0}", reader[2]);
Console.WriteLine("City: {0}", reader[3]);
}
reader.Close();
3. KiVi

3.1. Install
You can install the NuGet package using the Visual Studio NuGet Package Manager. You can open it right-clicking over your project, then clicking "Manage NuGet Packages…":

Once the NuGet Package Manager opens, you can click on the gear icon to add our repository to the project:

On the screen that appears, you can see your current repositories. To add the LeanXcale .NET repository, click on the green (+) icon at the top right:

That will create a new row, and you have to put the LeanXcale Repository
and
https://nexus.leanxcale.com/repository/nuget-hosted/
as values and click
the Update
and OK
buttons:

Once you’ve added the LeanXcale Repository to Visual Studio, choose it in the package manager at the top-right and then install the version that matches your LeanXcale version by clicking on the arrow at the side of the number as in this image:

If Visual Studio warns you that it’s about to make changes to your solution, just click the "OK" button:

If you prefer installing the driver from the Package Manager command line or the .NET CLI, you can do it too:
// Package Manager
PM> Install-Package LeanXcale.KiVi.NET.Driver -Version 1.9.9
// .NET CLI
> dotnet add package LeanXcale.KiVi.NET.Driver --version 1.9.9
If you prefer downloading the driver by hand to install it by other means, you can do it in our Drivers page, looking for .NET (C#) KiVi Direct API (nupkg).
After you’ve installed the NuGet package, you must install the platform specific driver. We have drivers for Windows and Linux.
3.1.1. Windows Installation
Download the .NET (C#) KiVi Direct API for Windows from our Drivers page and execute the file:
It will install the Visual C++ Redistributable libraries if you don’t have them installed:

Just click on "I agree" and then on "Install".
If you get a screen like this, don’t worry and click "Close", as it means that you already have the libraries installed:

After that, the Windows driver for LeanXcale starts to install. Just click "Next" on the following screens accepting the default values:




To finish the installation process, click "Close".
You will need to add the installation path (C:\Program Files\Leanxcale\lx-odbc-driver if you didn’t change it) to the PATH system environment variable. To do so, open the Control Panel and go to the Environment Variables dialog:


Double-click over "Path" in "System variables":

And add the new value:

3.1.2. Linux Installation
-
Download Leanxcale Development Libraries from the Drivers page and unpack it.
-
Copy the libraries
libphpcpp.so
,libckvapi.so
andlibcppkvapi.so
to your system path or add them toLD_LIBRARY_PATH
. In Ubuntu you could do:sudo cp libphpcpp* /lib/x86_64-linux-gnu sudo cp libckvapi* /lib/x86_64-linux-gnu sudo cp libcppkvapi* /lib/x86_64-linux-gnu
3.2. Quick Start
With this C# driver you’ll be using the LeanXcale KiVi proprietary key-value interface, so you get the full speed while reading, inserting and updating information without the SQL overhead. You’ll be using the KiVi .NET Direct API. You can read the API here.
The first thing you’ll need to do to start using the API in your application is start "using" it:
using lxapi;
3.2.1. Connecting and starting a session
In order to connect to the KiVi API for LeanXcale you’ll need some basic information:
-
If you are using LeanXcale as a Cloud Service, you can find the IP or hostname for your instance in the LeanXcale Cloud Console.
-
If you’re using LeanXcale on-premise, you can use the information from your installation.
-
In case you have enabled cryptography, you have to ensure that the application process have access to the user’s certificate.
In our examples we will use these parameters:
-
IP / hostname:
123.45.67.89
-
Lxis port:
9876
We will connect using this piece of code:
Connection conn = Connection.connect("lx://" + ip + ":" + port + "/db@APP;kvnat=auto;user=" + uid);
In case you are connecting to a LeanXcale database with Security, you must provide the connection with a valid path to an application certificate.
Connection conn = Connection.connect("lx://" + ip + ":" + port + "/db@APP;kvnat=auto;user=" + uid + ";cert=/path/to/certificate/file.kcf");
Learn how to generate your certificate here.
Note that it is only possible to have one active network connection with the KiVi API. However many Connection instances may share the same network connection.
3.2.2. Creating a table and its indexes
To create a new table in LeanXcale and add indexes to it, you can use the following code:
using (Session session = conn.session())
{
string[] fnames = { "personId", "dni", "name", "lastName", "address", "phone",
"email", "comment", "birthday", "weight", "height", "photo" };
session.createTable(tablename, "l[k]sssssssyffm", fnames);
// Non unique
ushort[] phoneposition = { 5 };
session.createIndex(tablename, "phoneidx", 1, phoneposition);
//Unique
ushort[] dniposition = { 1 };
session.createIndex(tablename, "dniidx", 1, dniposition, (int)IndexCreationFlags.IDXUNIQ);
}
The variable ufmt
defines the structure of the table. To see how you can define
the structure of your tables, give a look at the section
KiVi Tuple Format
below.
As you can see in the API documentation, you’re specifying in the parameters:
-
the name of the table
-
the name of the index to create
-
the number of columns to index
-
the position of columns to index
-
an optional parameter with the index type. If left unspecified, it will be a standard index. The other index types you can specify are constants defined in
IndexCreationFlags
.
3.2.3. Inserting, updating and deleting
Inserting one Record:
TupleBuilder builder;
using (Session session = conn.session())
{
using (Table table = session.table(tablename))
{
int jimbirthday = (int)(new DateTimeOffset(new DateTime(2010, 12, 12)).ToUnixTimeSeconds() / 86400);
object[] jimrow = { 0, null, "Jim", "Doe", "No comments",
"Mulholland Drive", null, null, jimbirthday, 50, 3.26, null };
table.insert(jimrow);
// With Builder
builder = table.tupleBuilder();
int johnbirthday = (int)(new DateTimeOffset(new DateTime(1975, 1, 1)).ToUnixTimeSeconds() / 86400);
object[] johnrow = { 1, "123456789A", "John", "Doe", "No comments",
"Mulholland Drive", "555333695", "johndoe@nowhere.no", johnbirthday, 70, 1.79, null };
builder.add(johnrow);
table.insert(builder);
Insert using a Sequence:
session.createSequence(seqname, 100, 2, 10);// start at 2, increment by 10
ulong next_value = session.seqNextValue(seqname);
Console.WriteLine("Next value: {0}", next_value);
// reuse builder
builder.add(0, next_value).add(1, "912345678B").add(2, "Jane");
int janebirthday = (int)(new DateTimeOffset(new DateTime(1972, 10, 7)).ToUnixTimeSeconds() / 86400);
builder.add(6, "0034698765432").add(7, "janedoe@lx.com").add(8, janebirthday)
.add(9, 75).add(10, 1.79);
table.upsert(builder);
Updating a Record:
builder.add(tableTuple);
builder.add(9, 62.7); // change weight
table.update(builder);
3.2.4. Reading and scanning Data
Getting a Data Row directly by Key:
using (Table table = session.table(tablename))
{
keyBuilder = table.keyBuilder();
lxapi.Tuple tableTuple = table.get(keyBuilder.add(0, 2));
for (int inx = 0; inx < table.info.numOfFields() - 1; inx++)
{
if (!tableTuple.isNull(inx) && !tableTuple.isIgnore(inx))
{
Console.WriteLine("Field #{0} -> {1}", inx, tableTuple.getString(inx));
}
}
Simple SCAN with basic options:
Table table = session.table(tablename);
using (Scan scanmaxrows = new ScanProperties(table).maxrows(2).buildScan())
{
scanmaxrows.begin();
lxapi.Tuple tuple = scanmaxrows.next();
int count = 0;
while (tuple != null)
{
count++;
tuple = scanmaxrows.next();
}
if (count != 2)
{
throw new Exception("Not the expected num of rows but " + count);
}
}
Scan a Table using a Secondary Index:
using (lxapi.Index index = table.index("dniidx"))
{
lxapi.Tuple min = index.tupleBuilder().add(0, "111111111Q").build(index);
using (Scan scan = new ScanProperties(index).skRange(min, null, 0).buildScan()) { }
}
Advanced Finding and Filtering
The following example show how to define a pipeline with a filter.
Expression expr = Expression.fieldFloat(9).op(Expression.Op.KVLE, new Expression((double)70));
using (Scan scan = new ScanProperties(table).addPredicate(expr).maxrows(10).buildScan()) { }
Project:
The project may also include operations over the fields to be retrieved.
ushort[] projection = { 1, 2, 3 };// dni, name, lastname
using (Scan scan = new ScanProperties(table).project(projection).buildScan()) { }
Aggregations:
First, you need to define an array of fields(or expressions) to be used as the group by key (if any), and then the list of aggregation expressions.
Expression[] groupbyexprs = new Expression[3];
groupbyexprs[0] = Expression.fieldStr(3);
groupbyexprs[1] = Expression.fieldFloat(10).op(Expression.Op.KVMUL, new Expression((double)100));
groupbyexprs[2] = Expression.aggr(Expression.Op.KVMAX, Expression.fieldFloat(9));
using (Scan scan = new ScanProperties(table).groupBy(groupbyexprs).buildScan()) { }
3.3. Appendix A: KiVi Tuple format
While using KiVi, each row that you insert is called a Tuple. When you define the structure of the table, you’re also defining the format for the tuples that will be inserted on the table.
You can specify tuple formats using format strings. A format string
uses one character per field, showing its type, followed optionally by
options enclosed in []
.
For example, we could have this tuple format variable:
$ufmt = "l[k0]sssyffm";
Here we have the definition of a table with 8 fields whose first field is a TLong that’s also the first (0) primary key for the table, followed by three Tstr fields, one Tdate, two Tfloat and one Tmem field.
3.3.1. KiVi Field Types
Type | Char to use | Spec |
---|---|---|
Tbool |
b |
int8_t (boolean) |
Tbyte |
c |
int8_t |
Tshort |
h |
int16_t |
Tenum |
e |
int16_t (static name enumeration) |
Tint |
i |
int32_t |
Ttime |
t |
int32_t (total nb of seconds) |
Tdate |
y |
int32_t (days since epoch) |
Tfloat |
f |
float |
Tstr |
s |
string field |
Tdec |
r |
decimals (and big integers) |
Tjson |
j |
JSON text |
Tmem |
m |
raw bytes |
Tlong |
l |
int64_t |
Tts |
w |
int64_t (time stamp in microseconds since epoch) |
Tdouble |
d |
double |
3.3.2. KiVi Field Options
Field options can be written together (within '[]') or separated by commas or a space:
Option | Description |
---|---|
! |
adds the following text up to the end of the options as a user spec for the field. Its meaning is up to the user. Must be the last option. |
B |
use the field for bloom filtering in the table. |
b |
ask for a blob field. Valid only for string, memory, and JSON fields. Data may be placed in external storage, or not. See also x. |
c |
flags the field as desired for column formats. |
D |
flags the field as gone; implementation might actually remove the data for the field, or not. Tuples still carry the field, perhaps as a null value, for consistency. |
d |
flags the field as a delta field for adding values. Delta decimals require a number.number option too. |
dm |
flags the field as a delta field for min value. |
dM |
flags the field as a delta field for max value. |
e |
flags the (string) field as enumerable at run time (dictionary) |
i |
flags the (string) field as case insensitive |
k |
flags the field as a key field. The position in the key (counting from 0) should follow right after the option (no spaces). If no position is given, the first unused one is used. |
ks |
flags the field as a key field that is a split-point field. The position in the key (counting from 0) should follow right after the option (no spaces). Split fields must be at the end of the key. |
h |
flags the field as hashed one to compute the value of a hash field. It must be a key field. |
H |
flags the field as a hash field computed from hashed fields. It must be a key field. |
l |
shows that a string field uses the locale with the name that follows from the flag up to the end of the options or the start of the user spec option. Thus, it must be the last option but for the user spec. |
n |
flags the field as non-null |
u |
flags the (string) field as always upper case. But see the note below. |
x |
ask for a blob field with external storage. Valid only for string, memory, and JSON fields. Like b, but data will always be placed in external storage. |
number |
Sets the maximum size desired for a string or memory field, or the number of decimal positions for decimals. The first character not a digit terminates this option. |
number.number |
A number with a decimal sets the maximum number of digits for a decimal and the desired number of decimal positions. This is to be used for delta decimal fields, in most other cases decimals may shrink and grow in size without much trouble. |