static void StreamSQLsWithManualTransaction(CSqlite sqlite)
{
bool ok = sqlite.BeginTrans(tagTransactionIsolation.tiReadCommited, (h, res, errMsg) =>
{
if (res != 0)
{
lock (m_csConsole) Console.WriteLine("BeginTrans: Error code={0}, message={1}", res, errMsg);
}
});
ok = sqlite.Execute("delete from EMPLOYEE;delete from COMPANY", (h, res, errMsg, affected, fail_ok, id) =>
{
if (res != 0)
{
lock (m_csConsole) Console.WriteLine("Execute_Delete: affected={0}, fails={1}, res={2}, errMsg={3}",
affected, (uint)(fail_ok >> 32), res, errMsg);
}
});
ok = sqlite.Prepare("INSERT INTO COMPANY(ID,NAME)VALUES(?,?)");
CDBVariantArray vData = new CDBVariantArray();
vData.Add(1); vData.Add("Google Inc.");
vData.Add(2); vData.Add("Microsoft Inc.");
//send two sets of parameterized data in one shot for processing
ok = sqlite.Execute(vData, (h, res, errMsg, affected, fail_ok, id) =>
{
if (res != 0)
{
lock (m_csConsole) Console.WriteLine("INSERT COMPANY: affected={0}, fails={1}, res={2}, errMsg={3}",
affected, (uint)(fail_ok >> 32), res, errMsg);
}
});
ok = sqlite.Prepare("INSERT INTO EMPLOYEE(EMPLOYEEID,CompanyId,name,JoinDate)VALUES(?,?,?,?)");
vData.Clear();
vData.Add(1); vData.Add(1); /*google company id*/ vData.Add("Ted Cruz"); vData.Add(DateTime.Now);
vData.Add(2); vData.Add(1); /*google company id*/ vData.Add("Donald Trump"); vData.Add(DateTime.Now);
vData.Add(3); vData.Add(2); /*Microsoft company id*/ vData.Add("Hillary Clinton"); vData.Add(DateTime.Now);
//send three sets of parameterized data in one shot for processing
ok = sqlite.Execute(vData, (h, res, errMsg, affected, fail_ok, id) =>
{
if (res != 0)
{
lock (m_csConsole) Console.WriteLine("INSET EMPLOYEE: affected={0}, fails={1}, res={2}, errMsg={3}",
affected, (uint)(fail_ok >> 32), res, errMsg);
}
});
sqlite.EndTrans(tagRollbackPlan.rpDefault, (h, res, errMsg) =>
{
if (res != 0)
{
lock (m_csConsole) Console.WriteLine("EndTrans: Error code={0}, message={1}", res, errMsg);
}
});
}
static void Main(string[] args)
{
Console.WriteLine("Remote host: ");
string host = Console.ReadLine();
CConnectionContext cc = new CConnectionContext(host, 20901, "usqlite_client", "pwd_for_usqlite");
using (CSocketPool <CSqlite> spSqlite = new CSocketPool <CSqlite>())
{
if (!spSqlite.StartSocketPool(cc, 1, 1))
{
Console.WriteLine("Failed in connecting to remote async sqlite server");
Console.WriteLine("Press any key to close the application ......");
Console.Read();
return;
}
CSqlite sqlite = spSqlite.Seek();
bool ok = sqlite.Open("", (handler, res, errMsg) =>
{
Console.WriteLine("res = {0}, errMsg: {1}", res, errMsg);
});
TestCreateTables(sqlite);
List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > lstRowset = new List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> >();
ok = sqlite.BeginTrans();
TestPreparedStatements(sqlite, lstRowset);
InsertBLOBByPreparedStatement(sqlite, lstRowset);
ok = sqlite.EndTrans();
sqlite.WaitAll();
int index = 0;
Console.WriteLine();
Console.WriteLine("+++++ Start rowsets +++");
foreach (KeyValuePair <CDBColumnInfoArray, CDBVariantArray> it in lstRowset)
{
Console.Write("Statement index = {0}", index);
if (it.Key.Count > 0)
{
Console.WriteLine(", rowset with columns = {0}, records = {1}.", it.Key.Count, it.Value.Count / it.Key.Count);
}
else
{
Console.WriteLine(", no rowset received.");
}
++index;
}
Console.WriteLine("+++++ End rowsets +++");
Console.WriteLine();
Console.WriteLine("Press any key to close the application ......");
Console.Read();
}
}
static void Main(string[] args)
{
Console.WriteLine("Remote host: ");
string host = Console.ReadLine();
Console.WriteLine("Table name: ");
string tableName = Console.ReadLine();
Console.WriteLine("sql filter: ");
string filter = Console.ReadLine();
CConnectionContext cc = new CConnectionContext(host, 20901, "usqlite_client", "pwd_for_sqlite");
Console.WriteLine("Asynchronous execution (0) or synchronous execution (1) ?");
bool sync = (Console.ReadKey().KeyChar != '0');
using (CSocketPool <CSqlite> spSqlite = new CSocketPool <CSqlite>())
{
if (!spSqlite.StartSocketPool(cc, 1))
{
Console.WriteLine("Failed in connecting to remote helloworld server");
Console.WriteLine("Press any key to close the application ......");
Console.Read();
return;
}
Console.WriteLine("");
Console.WriteLine("Computing ......");
CSqlite sqlite = spSqlite.Seek();
CAsyncDBHandler.DResult dr = (handler, res, errMsg) =>
{
if (res != 0)
{
Console.WriteLine("res = {0}, errMsg: {1}", res, errMsg);
}
};
uint obtained = 0;
bool ok = sqlite.Open("sakila.db", dr);
#if USE_DATATABLE
List <KeyValuePair <CDBColumnInfoArray, DataTable> > ra = new List <KeyValuePair <CDBColumnInfoArray, DataTable> >();
#else
List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra = new List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> >();
#endif
CAsyncDBHandler.DExecuteResult er = (handler, res, errMsg, affected, fail_ok, id) =>
{
if (res != 0)
{
Console.WriteLine("fails = {0}, oks = {1}, res = {2}, errMsg: {3}", (uint)(fail_ok >> 32), (uint)fail_ok, res, errMsg);
}
ra.Clear();
++obtained;
};
CAsyncDBHandler.DRows r = (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
#if USE_DATATABLE
KeyValuePair <CDBColumnInfoArray, DataTable> item = ra[last];
CAsyncDBHandler.AppendRowDataIntoDataTable(rowData, item.Value);
#else
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = ra[last];
item.Value.AddRange(rowData);
#endif
};
CAsyncDBHandler.DRowsetHeader rh = (handler) =>
{
//rowset header comes here
#if USE_DATATABLE
DataTable dt = CAsyncDBHandler.MakeDataTable(handler.ColumnInfo);
KeyValuePair <CDBColumnInfoArray, DataTable> item = new KeyValuePair <CDBColumnInfoArray, DataTable>(handler.ColumnInfo, dt);
#else
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(handler.ColumnInfo, new CDBVariantArray());
#endif
ra.Add(item);
};
ok = sqlite.WaitAll();
obtained = 0;
string sql = "select * from " + tableName;
if (filter.Length > 0)
{
sql += " where " + filter;
}
uint count = 10000;
DateTime start = DateTime.Now;
for (uint n = 0; n < count; ++n)
{
ok = sqlite.Execute(sql, er, r, rh);
if (sync && ok)
{
ok = sqlite.WaitAll();
}
if (!ok)
{
break;
}
}
if (!sync && ok)
{
ok = sqlite.WaitAll();
}
double diff = (DateTime.Now - start).TotalMilliseconds;
Console.WriteLine("Time required = {0} milliseconds for {1} query requests", diff, obtained);
//you need to compile and run the sample project test_sharp before running the below code
ok = sqlite.Open("", dr); //open a global database at remote server
ok = sqlite.Execute("delete from company where id > 3");
string sql_insert_parameter = "INSERT INTO company(ID,NAME,ADDRESS,Income)VALUES(?,?,?,?)";
ok = sqlite.Prepare(sql_insert_parameter, dr);
ok = sqlite.WaitAll();
int index = 0;
count = 50000;
Console.WriteLine();
Console.WriteLine("Going to insert {0} records into the table mysqldb.company", count);
start = DateTime.Now;
CDBVariantArray vData = new CDBVariantArray();
ok = sqlite.BeginTrans();
for (int n = 0; n < count; ++n)
{
vData.Add(n + 4);
int data = (n % 3);
switch (data)
{
case 0:
vData.Add("Google Inc.");
vData.Add("1600 Amphitheatre Parkway, Mountain View, CA 94043, USA");
vData.Add(66000000000.12);
break;
case 1:
vData.Add("Microsoft Inc.");
vData.Add("700 Bellevue Way NE- 22nd Floor, Bellevue, WA 98804, USA");
vData.Add(93600000001.24);
break;
default:
vData.Add("Apple Inc.");
vData.Add("1 Infinite Loop, Cupertino, CA 95014, USA");
vData.Add(234000000002.17);
break;
}
++index;
//send 2000 sets of parameter data onto server for processing in batch
if (2000 == index)
{
ok = sqlite.Execute(vData, er);
ok = sqlite.EndTrans();
vData.Clear();
Console.WriteLine("Commit {0} records into the table mysqldb.company", index);
ok = sqlite.BeginTrans();
index = 0;
}
}
if (vData.Count > 0)
{
ok = sqlite.Execute(vData, er);
Console.WriteLine("Commit {0} records into the table mysqldb.company", index);
}
ok = sqlite.EndTrans();
ok = sqlite.WaitAll();
diff = (DateTime.Now - start).TotalMilliseconds;
Console.WriteLine("Time required = {0} milliseconds for {1} insert requests", diff, count);
Console.WriteLine("Press any key to close the application ......");
Console.ReadLine();
}
}
static Task <bool> DoFuture(CSocketPool <CSqlite> sp)
{
TaskCompletionSource <bool> tcs = new TaskCompletionSource <bool>();
CSqlite sqlite = sp.Lock();
if (sqlite == null)
{
lock (m_csConsole) Console.WriteLine("All sockets are disconnected from server"); tcs.SetResult(false); return(tcs.Task);
}
bool ok = false;
do
{
if (!sqlite.BeginTrans(tagTransactionIsolation.tiReadCommited, (h, res, errMsg) =>
{
if (res != 0)
{
lock (m_csConsole) Console.WriteLine("BeginTrans: Error code={0}, message={1}", res, errMsg);
}
}))
{
break;
}
if (!sqlite.Execute("delete from EMPLOYEE;delete from COMPANY", (h, res, errMsg, affected, fail_ok, id) =>
{
if (res != 0)
{
lock (m_csConsole) Console.WriteLine("Execute_Delete: affected={0}, fails={1}, res={2}, errMsg={3}",
affected, (uint)(fail_ok >> 32), res, errMsg);
}
}))
{
break;
}
if (!sqlite.Prepare("INSERT INTO COMPANY(ID,NAME)VALUES(?,?)"))
{
break;
}
CDBVariantArray vData = new CDBVariantArray();
vData.Add(1); vData.Add("Google Inc.");
vData.Add(2); vData.Add("Microsoft Inc.");
//send two sets of parameterised data in one shot for processing
if (!sqlite.Execute(vData, (h, res, errMsg, affected, fail_ok, id) =>
{
if (res != 0)
{
lock (m_csConsole) Console.WriteLine("INSERT COMPANY: affected={0}, fails={1}, res={2}, errMsg={3}",
affected, (uint)(fail_ok >> 32), res, errMsg);
}
}))
{
break;
}
if (!sqlite.Prepare("INSERT INTO EMPLOYEE(EMPLOYEEID,CompanyId,name,JoinDate)VALUES(?,?,?,?)"))
{
break;
}
vData.Clear();
vData.Add(1); vData.Add(1); /*google company id*/ vData.Add("Ted Cruz"); vData.Add(DateTime.Now);
vData.Add(2); vData.Add(1); /*google company id*/ vData.Add("Donald Trump"); vData.Add(DateTime.Now);
vData.Add(3); vData.Add(2); /*Microsoft company id*/ vData.Add("Hillary Clinton"); vData.Add(DateTime.Now);
//send three sets of parameterised data in one shot for processing
if (!sqlite.Execute(vData, (h, res, errMsg, affected, fail_ok, id) =>
{
if (res != 0)
{
lock (m_csConsole) Console.WriteLine("INSET EMPLOYEE: affected={0}, fails={1}, res={2}, errMsg={3}",
affected, (uint)(fail_ok >> 32), res, errMsg);
}
}))
{
break;
}
if (!sqlite.EndTrans(tagRollbackPlan.rpDefault, (h, res, errMsg) =>
{
if (res != 0)
{
lock (m_csConsole) Console.WriteLine("EndTrans: Error code={0}, message={1}", res, errMsg);
}
tcs.SetResult(true);
}, (h, canceled) =>
{
lock (m_csConsole) Console.WriteLine("EndTrans: " + (canceled ? "Request canceled" : "Socket closed"));
tcs.SetResult(false);
}))
{
break;
}
ok = true;
sp.Unlock(sqlite); //put handler back into pool for reuse
} while (false);
if (!ok)
{
//Socket is closed at server side and the above locked handler is automatically unlocked
lock (m_csConsole) Console.WriteLine("DoFuture: Connection disconnected error code ={0}, message ={1}",
sqlite.AttachedClientSocket.ErrorCode, sqlite.AttachedClientSocket.ErrorMsg);
tcs.SetResult(false);
}
return(tcs.Task);
}
static void Main(string[] args)
{
Console.WriteLine("Remote host: ");
string host = Console.ReadLine();
CConnectionContext cc = new CConnectionContext(host, 20901, "usqlite_client", "password_for_usqlite");
using (CSocketPool <CSqlite> spSqlite = new CSocketPool <CSqlite>())
{
//start a socket pool with 1 thread hosting 1 non-blocking socket
if (!spSqlite.StartSocketPool(cc, 1, 1))
{
Console.WriteLine("Failed in connecting to remote async sqlite server");
Console.WriteLine("Press any key to close the application ......");
Console.Read();
return;
}
CSqlite sqlite = spSqlite.Seek();
//open a global database at server side because an empty string is given
bool ok = sqlite.Open("", (handler, res, errMsg) =>
{
Console.WriteLine("res = {0}, errMsg: {1}", res, errMsg);
});
//prepare two test tables, COMPANY and EMPLOYEE
TestCreateTables(sqlite);
//a container for receiving all tables data
List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > lstRowset = new List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> >();
ok = sqlite.BeginTrans(); //start manual transaction
//test both prepare and query statements
TestPreparedStatements(sqlite, lstRowset);
//test both prepare and query statements involved with reading and updating BLOB and large text
InsertBLOBByPreparedStatement(sqlite, lstRowset);
ok = sqlite.EndTrans(); //end manual transaction
sqlite.WaitAll();
//display received rowsets
int index = 0;
Console.WriteLine();
Console.WriteLine("+++++ Start rowsets +++");
foreach (KeyValuePair <CDBColumnInfoArray, CDBVariantArray> it in lstRowset)
{
Console.Write("Statement index = {0}", index);
if (it.Key.Count > 0)
{
Console.WriteLine(", rowset with columns = {0}, records = {1}.", it.Key.Count, it.Value.Count / it.Key.Count);
}
else
{
Console.WriteLine(", no rowset received.");
}
++index;
}
Console.WriteLine("+++++ End rowsets +++");
Console.WriteLine();
Console.WriteLine("Press any key to close the application ......");
Console.Read();
}
}
