Read all rows from a table in Esent

Question

I am new to esent, I have gone through some sample code and found to read rows based of key. What is way to read all rows from any particular table. Like we do in sql

"Select * from Table_Name"

Answer 1

There is no option with EDB to use queries as you do in SQL. Instead you can use the functions provided by the esent API to access the database. Ultimately it will look like this:

CreateInstance
Init
BeginSession
AttachDatabase
OpenDatabase
OpenTable
RetrieveColumns (this is where you actually read the data)
...

Of course there are many functions and features to speed up your database transactions. But you do have to deal with one of the interfaces mentioned below:

You can either try using the API provided by Microsoft. It's rather well documented and freely available here: Extensible Storage Engine

Or you can use the Managed Esent Interface, which you can easily use with Visual Studio: ESENT Managed Interop

Answer 2

There is no query processor in the Extensible Storage Engine (ESE). That means that there is no component that can convert a text query into code.

In the terminology, the ESE is an Indexed Sequential Access Method (ISAM).

What this means is that if you wanted to query for a customer:

SELECT FirstName, LastName FROM Customers
WHERE AccountNumber = 6191128

You have to:

• tell it you want to use the Customers table
• tell it you want to use the IX_Customers_AccountNumber index
• tell it you want to perform an equality search against 6191128
• iterate the results; storing them in memory somewhere
• now you're going to use the IX_Customers_FirstNameLastName index
• tell it to use the clustered index
• loop through all results from earlier
• perform an equality search against the cluster key value in your object
• iterate the results; storing them in memory somewhere

In pseudo-code:

//Use the InvoiceDate index on invoices
db.SetCurrentIndex("IX_Invoices_InvoiceDate");
db.ClearSearchPredicate();
db.AddSearchPredicate(SEEK_GreaterOrEqual, "20170801");
db.AddSearchPredicate(SEEK_LessThen, "20180901");

//read matching primary keys into list
List<Guid> invoiceIDs = new List<Guid>();
IDataReader rdr = db.GetResults();
while (rdr.Read()) do
{
   invoiceIDs.Add(rdr.GetGUID("InvoiceGUID"));
}

//Now use the primary clustered key to read the invoice numbers, and customer IDs
db.SetCurrentIndex("PK_Invoices");
for (Guid invoiceID in invoiceIDs) do
{
    db.ClearSearchPredicate();
    db.AddSearchPrediate(SEEK_Equal, invoiceID);
    rdr = db.GetResults();
    if rdr.Read() then
    {
        //todo: store these in another list
        customerID = rdr.GetInt32("CustomerID");
        invoiceNumber = rdr.GetInt32("InvoiceNumber");
    }
}

//Now seek for customers by customerID
db.ClearSearchPredicate()
db.AddSearchPredicate(SEEK_Equal, customerID);
rdr = db.GetResults();
if rdr.Read() then
{
   String name = rdr.GetString("Name");
   String isActive = rdr.GetString("IsActive");
}