static Task <CAsyncDBHandler.SQLExeInfo>[] TestCreateTables(CSqlite sqlite)
{
var v = new Task <CAsyncDBHandler.SQLExeInfo> [2];
v[0] = sqlite.execute("CREATE TABLE IF NOT EXISTS COMPANY(ID INT8 PRIMARY KEY NOT NULL,name CHAR(64)NOT NULL,ADDRESS varCHAR(256)not null,Income float not null)");
v[1] = sqlite.execute("CREATE TABLE IF NOT EXISTS EMPLOYEE(EMPLOYEEID INT8 PRIMARY KEY NOT NULL unique,CompanyId INT8 not null,name NCHAR(64)NOT NULL,JoinDate DATETIME not null default(datetime('now')),IMAGE BLOB,DESCRIPTION NTEXT,Salary real,FOREIGN KEY(CompanyId)REFERENCES COMPANY(id))");
return(v);
}
private void btnConnect_Click(object sender, EventArgs e)
{
CConnectionContext cc = new CConnectionContext(txtHost.Text, 20901, txtUser.Text, txtPassword.Text);
m_spSqlite = new CSocketPool <CSqlite>(false);
//set event for MySQL/Mariadb database shutdown
m_spSqlite.SocketPoolEvent += new CSocketPool <CSqlite> .DOnSocketPoolEvent(m_spSqlite_SocketPoolEvent);
if (!m_spSqlite.StartSocketPool(cc, 1, 1))
{
txtMessage.Text = "No connection to " + txtHost.Text;
return;
}
CSqlite sqlite = m_spSqlite.AsyncHandlers[0];
//set event for tracking all database table update events, delete, update and insert
m_spSqlite.Sockets[0].Push.OnPublish += new DOnPublish(Push_OnPublish);
//create a DB session with default to sample database sakil
bool ok = sqlite.Open("sakila.db", null, DB_CONSTS.ENABLE_TABLE_UPDATE_MESSAGES);
m_ds = new DataSet("real-time cache");
DataTable dt = null;
string errMsg = "";
//query all cached tables into client side for intial cache data
ok = sqlite.Execute("", (h, ret, err_msg, affected, fail_ok, id) =>
{
//this callback is fired from worker thread from socket pool thread
ok = (ret == 0);
errMsg = err_msg;
}, (h, data) =>
{
//this callback is fired from worker thread from socket pool thread
CSqlite.AppendRowDataIntoDataTable(data, dt);
}, (h) =>
{
//this callback is fired from worker thread from socket pool thread
dt = CSqlite.MakeDataTable(h.ColumnInfo);
string name = h.ColumnInfo[0].DBPath + "." + h.ColumnInfo[0].TablePath;
dt.TableName = name;
m_ds.Tables.Add(dt);
});
ok = sqlite.WaitAll();
txtMessage.Text = errMsg;
lstTables.Items.Clear();
foreach (DataTable table in m_ds.Tables)
{
lstTables.Items.Add(table.TableName);
}
if (m_ds.Tables.Count > 0)
{
lstTables.SelectedIndex = 0;
}
btnDisconnect.Enabled = ok;
btnConnect.Enabled = !ok;
}
static Task <CAsyncDBHandler.SQLExeInfo> TestBLOBByPreparedStatement(CSqlite sqlite, List <KeyValue> ra)
{
//a complex SQL statement combined with two insert and query prepare statements
sqlite.Prepare("insert or replace into employee(EMPLOYEEID,CompanyId,name,JoinDate,image,DESCRIPTION,Salary)values(?,?,?,?,?,?,?);select * from employee where employeeid=?");
CDBVariantArray vData = new CDBVariantArray();
using (CScopeUQueue sbBlob = new CScopeUQueue())
{
//first set of data
vData.Add(1);
vData.Add(1); //google company id
vData.Add("Ted Cruz");
vData.Add(DateTime.Now);
sbBlob.Save(m_wstr);
vData.Add(sbBlob.UQueue.GetBuffer());
vData.Add(m_wstr);
vData.Add(254000.0);
vData.Add(1);
//second set of data
vData.Add(2);
vData.Add(1); //google company id
vData.Add("Donald Trump");
vData.Add(DateTime.Now);
sbBlob.UQueue.SetSize(0);
sbBlob.Save(m_str);
vData.Add(sbBlob.UQueue.GetBuffer());
vData.Add(m_str);
vData.Add(20254000.0);
vData.Add(2);
//third set of data
vData.Add(3);
vData.Add(2); //Microsoft company id
vData.Add("Hillary Clinton");
vData.Add(DateTime.Now);
sbBlob.Save(m_wstr);
vData.Add(sbBlob.UQueue.GetBuffer());
vData.Add(m_wstr);
vData.Add(6254000.0);
vData.Add(3);
}
//send three sets of parameterized data in one shot for processing
return(sqlite.execute(vData, (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValue item = ra[last];
item.Value.AddRange(rowData);
}, (handler) =>
{
//rowset header meta info comes here
KeyValue item = new KeyValue(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
}));
}
static void TestBatch(CSqlite sqlite, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra)
{
CDBVariantArray vParam = new CDBVariantArray();
vParam.Add(1); //ID
vParam.Add(2); //EMPLOYEEID
//there is no manual transaction if isolation is tiUnspecified
bool ok = sqlite.ExecuteBatch(tagTransactionIsolation.tiUnspecified,
"Select datetime('now');select * from COMPANY where ID=?;select * from EMPLOYEE where EMPLOYEEID=?",
vParam, (handler, res, errMsg, affected, fail_ok, id) =>
{
Console.WriteLine("affected = {0}, fails = {1}, oks = {2}, res = {3}, errMsg: {4}, last insert id = {5}",
affected, (uint)(fail_ok >> 32), (uint)fail_ok, res, errMsg, id);
}, (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = ra[last];
item.Value.AddRange(rowData);
}, (handler) =>
{
//rowset header meta info comes here
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
});
vParam.Clear();
vParam.Add(1); //ID
vParam.Add(2); //EMPLOYEEID
vParam.Add(2); //ID
vParam.Add(3); //EMPLOYEEID
//Same as sqlite.BeginTrans();
//Select datetime('now');select * from COMPANY where ID=1;select * from COMPANY where ID=2;Select datetime('now');
//select * from EMPLOYEE where EMPLOYEEID=2;select * from EMPLOYEE where EMPLOYEEID=3
//ok = sqlite.EndTrans();
ok = sqlite.ExecuteBatch(tagTransactionIsolation.tiReadCommited,
"Select datetime('now');select * from COMPANY where ID=?;Select datetime('now');select * from EMPLOYEE where EMPLOYEEID=?",
vParam, (handler, res, errMsg, affected, fail_ok, id) =>
{
Console.WriteLine("affected = {0}, fails = {1}, oks = {2}, res = {3}, errMsg: {4}, last insert id = {5}",
affected, (uint)(fail_ok >> 32), (uint)fail_ok, res, errMsg, id);
}, (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = ra[last];
item.Value.AddRange(rowData);
}, (handler) =>
{
//rowset header meta info comes here
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
});
}
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 Task <CAsyncDBHandler.SQLExeInfo>[] TestBatch(CSqlite sqlite, List <KeyValue> ra)
{
var v = new Task <CAsyncDBHandler.SQLExeInfo> [2];
CDBVariantArray vParam = new CDBVariantArray();
vParam.Add(1); //ID
vParam.Add(2); //EMPLOYEEID
//there is no manual transaction if isolation is tiUnspecified
v[0] = sqlite.executeBatch(tagTransactionIsolation.tiUnspecified,
"Select datetime('now');select * from COMPANY where ID=?;select * from EMPLOYEE where EMPLOYEEID=?",
vParam, (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValue item = ra[last];
item.Value.AddRange(rowData);
}, (handler) =>
{
//rowset header meta info comes here
KeyValue item = new KeyValue(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
});
vParam.Clear();
vParam.Add(1); //ID
vParam.Add(2); //EMPLOYEEID
vParam.Add(2); //ID
vParam.Add(3); //EMPLOYEEID
//Same as sqlite.BeginTrans();
//Select datetime('now');
//select * from COMPANY where ID=1;
//select * from COMPANY where ID=2;
//Select datetime('now');
//select * from EMPLOYEE where EMPLOYEEID=2;
//select * from EMPLOYEE where EMPLOYEEID=3
//ok = sqlite.EndTrans(tagRollbackPlan.rpDefault);
v[1] = sqlite.executeBatch(tagTransactionIsolation.tiReadCommited,
"Select datetime('now');select * from COMPANY where ID=?;Select datetime('now');select * from EMPLOYEE where EMPLOYEEID=?",
vParam, (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValue item = ra[last];
item.Value.AddRange(rowData);
}, (handler) =>
{
//rowset header meta info comes here
KeyValue item = new KeyValue(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
});
return(v);
}
static void TestCreateTables(CSqlite sqlite)
{
string create_table = "CREATE TABLE COMPANY(ID INT8 PRIMARY KEY NOT NULL, name CHAR(64) NOT NULL, ADDRESS varCHAR(256) not null, Income float not null)";
bool ok = sqlite.Execute(create_table, (handler, res, errMsg, affected, fail_ok, id) =>
{
Console.WriteLine("affected = {0}, fails = {1}, oks = {2}, res = {3}, errMsg: {4}, last insert id = {5}", affected, (uint)(fail_ok >> 32), (uint)fail_ok, res, errMsg, id);
});
create_table = "CREATE TABLE EMPLOYEE(EMPLOYEEID INT8 PRIMARY KEY NOT NULL unique, CompanyId INT8 not null, name NCHAR(64) NOT NULL, JoinDate DATETIME not null default(datetime('now')), IMAGE BLOB, DESCRIPTION NTEXT, Salary real, FOREIGN KEY(CompanyId) REFERENCES COMPANY(id))";
ok = sqlite.Execute(create_table, (handler, res, errMsg, affected, fail_ok, id) =>
{
Console.WriteLine("affected = {0}, fails = {1}, oks = {2}, res = {3}, errMsg: {4}, last insert id = {5}", affected, (uint)(fail_ok >> 32), (uint)fail_ok, res, errMsg, id);
});
}
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 Demo_Multiple_SendRequest_MultiThreaded_Wrong(object sp)
{
uint cycle = m_cycle; CSocketPool <CSqlite> spSqlite = (CSocketPool <CSqlite>)sp;
while (cycle > 0)
{
//Seek an async handler on the min number of requests queued in memory and its associated socket connection
CSqlite sqlite = spSqlite.Seek();
//lock(sqlite) //uncomment this call to remove potential batch request overlap
StreamSQLsWithManualTransaction(sqlite);
--cycle;
}
foreach (CSqlite s in spSqlite.AsyncHandlers)
{
s.WaitAll();
}
}
private void btnDoSQL_Click(object sender, EventArgs e)
{
CSqlite sqlite = null;
if (m_spSqlite != null)
{
sqlite = m_spSqlite.Seek();
}
if (sqlite == null)
{
txtMessage.Text = "No connection to a SocketPro SQLite server";
return;
}
sqlite.Execute(txtSQL.Text, (h, res, errMsg, affected, fail_ok, lastId) => {
BeginInvoke(m_sql_message, res, errMsg);
});
}
private async void btnDoSQL_Click(object sender, EventArgs e)
{
CSqlite sqlite = null;
if (m_spSqlite != null)
{
sqlite = m_spSqlite.Seek();
}
if (sqlite == null)
{
txtMessage.Text = "No connection to a SocketPro SQLite server";
return;
}
var res = await sqlite.execute(txtSQL.Text);
txtMessage.Text = res.em;
}
static void Demo_Multiple_SendRequest_MultiThreaded_Correct_Lock_Unlock(object sp)
{
uint cycle = m_cycle; CSocketPool <CSqlite> spSqlite = (CSocketPool <CSqlite>)sp;
while (cycle > 0)
{
//Take an async handler infinitely from socket pool for sending multiple requests from current thread
CSqlite sqlite = spSqlite.Lock();
StreamSQLsWithManualTransaction(sqlite);
//Put back a previously locked async handler to pool for reuse
spSqlite.Unlock(sqlite);
--cycle;
}
foreach (CSqlite s in spSqlite.AsyncHandlers)
{
s.WaitAll();
}
}
static void Demo_Cross_Request_Dead_Lock(CSqlite sqlite)
{
uint count = 1000000;
//uncomment the following call to remove potential cross SendRequest dead lock
//sqlite.AttachedClientSocket.ClientQueue.StartQueue("cross_locking_0", 3600);
do
{
bool ok = sqlite.Open(sample_database, (handler, res, errMsg) =>
{
if (res != 0)
{
Console.WriteLine("Open: res = {0}, errMsg: {1}", res, errMsg);
}
});
--count;
} while (count > 0);
}
static void TestPreparedStatements(CSqlite sqlite, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra)
{
//a complex SQL statement combined with query and insert prepare statements
string sql_insert_parameter = "Select datetime('now');INSERT OR REPLACE INTO COMPANY(ID,NAME,ADDRESS,Income)VALUES(?,?,?,?)";
bool ok = sqlite.Prepare(sql_insert_parameter, (handler, res, errMsg) =>
{
Console.WriteLine("res = {0}, errMsg: {1}", res, errMsg);
});
CDBVariantArray vData = new CDBVariantArray();
vData.Add(1);
vData.Add("Google Inc.");
vData.Add("1600 Amphitheatre Parkway, Mountain View, CA 94043, USA");
vData.Add(66000000000.0);
vData.Add(2);
vData.Add("Microsoft Inc.");
vData.Add("700 Bellevue Way NE- 22nd Floor, Bellevue, WA 98804, USA");
vData.Add(93600000000.0);
vData.Add(3);
vData.Add("Apple Inc.");
vData.Add("1 Infinite Loop, Cupertino, CA 95014, USA");
vData.Add(234000000000.0);
//send three sets of parameterized data in one shot for processing
ok = sqlite.Execute(vData, (handler, res, errMsg, affected, fail_ok, id) =>
{
Console.WriteLine("affected = {0}, fails = {1}, oks = {2}, res = {3}, errMsg: {4}, last insert id = {5}", affected, (uint)(fail_ok >> 32), (uint)fail_ok, res, errMsg, id);
}, (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = ra[last];
item.Value.AddRange(rowData);
}, (handler) =>
{
//rowset header meta info comes here
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
});
}
static void TestCreateTables(CSqlite sqlite)
{
string sql = "CREATE TABLE COMPANY(ID INT8 PRIMARY KEY NOT NULL,NAME CHAR(64)NOT NULL)";
bool ok = sqlite.Execute(sql, (handler, res, errMsg, affected, fail_ok, id) =>
{
if (res != 0)
{
Console.WriteLine("affected = {0}, fails = {1}, oks = {2}, res = {3}, errMsg: {4}",
affected, (uint)(fail_ok >> 32), (uint)fail_ok, res, errMsg);
}
});
sql = @"CREATE TABLE EMPLOYEE(EMPLOYEEID INT8 PRIMARY KEY NOT NULL,CompanyId INT8 not null,name NCHAR(64)NOT
NULL,JoinDate DATETIME not null default(datetime('now')),FOREIGN KEY(CompanyId)REFERENCES COMPANY(id))";
ok = sqlite.Execute(sql, (handler, res, errMsg, affected, fail_ok, id) =>
{
if (res != 0)
{
Console.WriteLine("affected = {0}, fails = {1}, oks = {2}, res = {3}, errMsg: {4}",
affected, (uint)(fail_ok >> 32), (uint)fail_ok, res, errMsg);
}
});
}
static Task <CAsyncDBHandler.SQLExeInfo> TestPreparedStatements(CSqlite sqlite, List <KeyValue> ra)
{
//a complex SQL statement combined with query and insert prepare statements
sqlite.Prepare("Select datetime('now');INSERT OR REPLACE INTO COMPANY(ID,NAME,ADDRESS,Income)VALUES(?,?,?,?)");
CDBVariantArray vData = new CDBVariantArray();
vData.Add(1);
vData.Add("Google Inc.");
vData.Add("1600 Amphitheatre Parkway, Mountain View, CA 94043, USA");
vData.Add(66000000000.0);
vData.Add(2);
vData.Add("Microsoft Inc.");
vData.Add("700 Bellevue Way NE- 22nd Floor, Bellevue, WA 98804, USA");
vData.Add(93600000000.0);
vData.Add(3);
vData.Add("Apple Inc.");
vData.Add("1 Infinite Loop, Cupertino, CA 95014, USA");
vData.Add(234000000000.0);
//send three sets of parameterized data in one shot for processing
return(sqlite.execute(vData, (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValue item = ra[last];
item.Value.AddRange(rowData);
}, (handler) =>
{
//rowset header meta info comes here
KeyValue item = new KeyValue(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
}));
}
private void m_spSqlite_SocketPoolEvent(CSocketPool <CSqlite> sender, tagSocketPoolEvent spe, CSqlite AsyncServiceHandler)
{
switch (spe)
{
case tagSocketPoolEvent.speSocketClosed:
//this event is fired from worker thread from socket pool thread
BeginInvoke(m_closed, "Database server or network shut down");
break;
default:
break;
}
}
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();
}
}
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>())
{
//spSqlite.QueueName = "qsqlite";
if (!spSqlite.StartSocketPool(cc, 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();
//a container for receiving all tables data
List <KeyValue> lstRowset = new List <KeyValue>();
try
{
//stream all DB requests with in-line batching for the best network efficiency
//open a global database at server side because an empty string is given
var topen = sqlite.open("");
//prepare two test tables, COMPANY and EMPLOYEE
Task <CAsyncDBHandler.SQLExeInfo>[] vT = TestCreateTables(sqlite);
var tbt = sqlite.beginTrans(); //start manual transaction
//test both prepare and query statements
var tp0 = TestPreparedStatements(sqlite, lstRowset);
//test both prepare and query statements involved with reading and updating BLOB and large text
var tp1 = TestBLOBByPreparedStatement(sqlite, lstRowset);
var tet = sqlite.endTrans(); //end manual transaction
var vB = TestBatch(sqlite, lstRowset);
Console.WriteLine("All SQL requests streamed, and waiting for their results");
Console.WriteLine(topen.Result);
foreach (var e in vT)
{
Console.WriteLine(e.Result);
}
Console.WriteLine(tbt.Result);
Console.WriteLine(tp0.Result);
Console.WriteLine(tp1.Result);
Console.WriteLine(tet.Result);
foreach (var e in vB)
{
Console.WriteLine(e.Result);
}
}
catch (AggregateException ex)
{
foreach (Exception e in ex.InnerExceptions)
{
//An exception from server (CServerError), Socket closed after sending a request (CSocketError) or request canceled (CSocketError),
Console.WriteLine(e);
}
}
catch (CSocketError ex)
{
//Socket is already closed before sending a request
Console.WriteLine(ex);
}
catch (Exception ex)
{
//bad operations such as invalid arguments, bad operations and de-serialization errors, and so on
Console.WriteLine(ex);
}
//display received rowsets
int index = 0;
Console.WriteLine();
Console.WriteLine("+++++ Start rowsets +++");
foreach (KeyValue 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 void InsertBLOBByPreparedStatement(CSqlite sqlite, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra)
{
string wstr = "";
while (wstr.Length < 128 * 1024)
{
wstr += "广告做得不那么夸张的就不说了，看看这三家，都是正儿八经的公立三甲，附属医院，不是武警，也不是部队，更不是莆田，都在卫生部门直接监管下，照样明目张胆地骗人。";
}
string str = "";
while (str.Length < 256 * 1024)
{
str += "The epic takedown of his opponent on an all-important voting day was extraordinary even by the standards of the 2016 campaign -- and quickly drew a scathing response from Trump.";
}
string sqlInsert = "insert or replace into employee(EMPLOYEEID, CompanyId, name, JoinDate, image, DESCRIPTION, Salary) values(?, ?, ?, ?, ?, ?, ?);select * from employee where employeeid = ?";
bool ok = sqlite.Prepare(sqlInsert, (handler, res, errMsg) =>
{
Console.WriteLine("res = {0}, errMsg: {1}", res, errMsg);
});
CDBVariantArray vData = new CDBVariantArray();
using (CScopeUQueue sbBlob = new CScopeUQueue())
{
//first set of data
vData.Add(1);
vData.Add(1); //google company id
vData.Add("Ted Cruz");
vData.Add(DateTime.Now);
sbBlob.Save(wstr);
vData.Add(sbBlob.UQueue.GetBuffer());
vData.Add(wstr);
vData.Add(254000.0);
vData.Add(1);
//second set of data
vData.Add(2);
vData.Add(1); //google company id
vData.Add("Donald Trump");
vData.Add(DateTime.Now);
sbBlob.UQueue.SetSize(0);
sbBlob.Save(str);
vData.Add(sbBlob.UQueue.GetBuffer());
vData.Add(str);
vData.Add(20254000.0);
vData.Add(2);
//third set of data
vData.Add(3);
vData.Add(2); //Microsoft company id
vData.Add("Hillary Clinton");
vData.Add(DateTime.Now);
sbBlob.Save(wstr);
vData.Add(sbBlob.UQueue.GetBuffer());
vData.Add(wstr);
vData.Add(6254000.0);
vData.Add(3);
}
//send three sets of parameterized data in one shot for processing
ok = sqlite.Execute(vData, (handler, res, errMsg, affected, fail_ok, id) =>
{
Console.WriteLine("affected = {0}, fails = {1}, oks = {2}, res = {3}, errMsg: {4}, last insert id = {5}", affected, (uint)(fail_ok >> 32), (uint)fail_ok, res, errMsg, id);
}, (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = ra[last];
item.Value.AddRange(rowData);
}, (handler) =>
{
//rowset header comes here
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
});
}
static void Main(string[] args)
{
const int sessions_per_host = 2; const int cycles = 10000; string[] vHost = { "localhost", "192.168.2.172" };
using (CSocketPool <CSqlite> sp = new CSocketPool <CSqlite>()) {
sp.QueueName = "ar_sharp"; //set a local message queue to backup requests for auto fault recovery
CConnectionContext[,] ppCc = new CConnectionContext[1, vHost.Length *sessions_per_host]; //one thread enough
for (int n = 0; n < vHost.Length; ++n)
{
for (int j = 0; j < sessions_per_host; ++j)
{
ppCc[0, n *sessions_per_host + j] = new CConnectionContext(vHost[n], 20901, "AClientUserId", "Mypassword");
}
}
bool ok = sp.StartSocketPool(ppCc);
if (!ok)
{
Console.WriteLine("There is no connection and press any key to close the application ......");
Console.Read(); return;
}
string sql = "SELECT max(amount), min(amount), avg(amount) FROM payment";
Console.WriteLine("Input a filter for payment_id"); string filter = Console.ReadLine();
if (filter.Length > 0)
{
sql += (" WHERE " + filter);
}
var v = sp.AsyncHandlers;
foreach (var h in v)
{
ok = h.Open("sakila.db", (hsqlite, res, errMsg) => {
if (res != 0)
{
Console.WriteLine("Error code: {0}, error message: {1}", res, errMsg);
}
});
}
int returned = 0; double dmax = 0.0, dmin = 0.0, davg = 0.0;
SocketProAdapter.UDB.CDBVariantArray row = new SocketProAdapter.UDB.CDBVariantArray();
CAsyncDBHandler.DExecuteResult er = (h, res, errMsg, affected, fail_ok, lastId) => {
if (res != 0)
{
Console.WriteLine("Error code: {0}, error message: {1}", res, errMsg);
}
else
{
dmax += double.Parse(row[0].ToString());
dmin += double.Parse(row[1].ToString());
davg += double.Parse(row[2].ToString());
}
++returned;
};
CAsyncDBHandler.DRows r = (h, vData) => {
row.Clear(); row.AddRange(vData);
};
CSqlite sqlite = sp.SeekByQueue(); //get one handler for querying one record
ok = sqlite.Execute(sql, er, r); ok = sqlite.WaitAll();
Console.WriteLine("Result: max = {0}, min = {1}, avg = {2}", dmax, dmin, davg);
returned = 0; dmax = 0.0; dmin = 0.0; davg = 0.0;
Console.WriteLine("Going to get {0} queries for max, min and avg", cycles);
for (int n = 0; n < cycles; ++n)
{
sqlite = sp.SeekByQueue(); ok = sqlite.Execute(sql, er, r);
}
foreach (var h in v)
{
ok = h.WaitAll();
}
Console.WriteLine("Returned = {0}, max = {1}, min = {2}, avg = {3}", returned, dmax, dmin, davg);
Console.WriteLine("Press any key to close the application ......"); Console.Read();
}
}
static void TestCreateTables(CSqlite sqlite)
{
string create_table = "CREATE TABLE COMPANY(ID INT8 PRIMARY KEY NOT NULL, name CHAR(64) NOT NULL, ADDRESS varCHAR(256) not null, Income float not null)";
bool ok = sqlite.Execute(create_table, (handler, res, errMsg, affected, fail_ok, id) =>
{
Console.WriteLine("affected = {0}, fails = {1}, oks = {2}, res = {3}, errMsg: {4}, last insert id = {5}", affected, (uint)(fail_ok >> 32), (uint)fail_ok, res, errMsg, id);
});
create_table = "CREATE TABLE EMPLOYEE(EMPLOYEEID INT8 PRIMARY KEY NOT NULL unique, CompanyId INT8 not null, name NCHAR(64) NOT NULL, JoinDate DATETIME not null default(datetime('now')), IMAGE BLOB, DESCRIPTION NTEXT, Salary real, FOREIGN KEY(CompanyId) REFERENCES COMPANY(id))";
ok = sqlite.Execute(create_table, (handler, res, errMsg, affected, fail_ok, id) =>
{
Console.WriteLine("affected = {0}, fails = {1}, oks = {2}, res = {3}, errMsg: {4}, last insert id = {5}", affected, (uint)(fail_ok >> 32), (uint)fail_ok, res, errMsg, id);
});
}
static void InsertBLOBByPreparedStatement(CSqlite sqlite, List<KeyValuePair<CDBColumnInfoArray, CDBVariantArray>> ra)
{
string wstr = "";
while (wstr.Length < 128 * 1024)
{
wstr += "广告做得不那么夸张的就不说了，看看这三家，都是正儿八经的公立三甲，附属医院，不是武警，也不是部队，更不是莆田，都在卫生部门直接监管下，照样明目张胆地骗人。";
}
string str = "";
while (str.Length < 256 * 1024)
{
str += "The epic takedown of his opponent on an all-important voting day was extraordinary even by the standards of the 2016 campaign -- and quickly drew a scathing response from Trump.";
}
string sqlInsert = "insert or replace into employee(EMPLOYEEID, CompanyId, name, JoinDate, image, DESCRIPTION, Salary) values(?, ?, ?, ?, ?, ?, ?);select * from employee where employeeid = ?";
bool ok = sqlite.Prepare(sqlInsert, (handler, res, errMsg) =>
{
Console.WriteLine("res = {0}, errMsg: {1}", res, errMsg);
});
CDBVariantArray vData = new CDBVariantArray();
using (CScopeUQueue sbBlob = new CScopeUQueue())
{
//first set of data
vData.Add(1);
vData.Add(1); //google company id
vData.Add("Ted Cruz");
vData.Add(DateTime.Now);
sbBlob.Save(wstr);
vData.Add(sbBlob.UQueue.GetBuffer());
vData.Add(wstr);
vData.Add(254000.0);
vData.Add(1);
//second set of data
vData.Add(2);
vData.Add(1); //google company id
vData.Add("Donald Trump");
vData.Add(DateTime.Now);
sbBlob.UQueue.SetSize(0);
sbBlob.Save(str);
vData.Add(sbBlob.UQueue.GetBuffer());
vData.Add(str);
vData.Add(20254000.0);
vData.Add(2);
//third set of data
vData.Add(3);
vData.Add(2); //Microsoft company id
vData.Add("Hillary Clinton");
vData.Add(DateTime.Now);
sbBlob.Save(wstr);
vData.Add(sbBlob.UQueue.GetBuffer());
vData.Add(wstr);
vData.Add(6254000.0);
vData.Add(3);
}
//send three sets of parameterized data in one shot for processing
ok = sqlite.Execute(vData, (handler, res, errMsg, affected, fail_ok, id) =>
{
Console.WriteLine("affected = {0}, fails = {1}, oks = {2}, res = {3}, errMsg: {4}, last insert id = {5}", affected, (uint)(fail_ok >> 32), (uint)fail_ok, res, errMsg, id);
}, (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValuePair<CDBColumnInfoArray, CDBVariantArray> item = ra[last];
item.Value.AddRange(rowData);
}, (handler) =>
{
//rowset header comes here
KeyValuePair<CDBColumnInfoArray, CDBVariantArray> item = new KeyValuePair<CDBColumnInfoArray, CDBVariantArray>(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
});
}
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 TestPreparedStatements(CSqlite sqlite, List<KeyValuePair<CDBColumnInfoArray, CDBVariantArray>> ra)
{
string sql_insert_parameter = "Select datetime('now');INSERT OR REPLACE INTO COMPANY(ID, NAME, ADDRESS, Income) VALUES (?, ?, ?, ?)";
bool ok = sqlite.Prepare(sql_insert_parameter, (handler, res, errMsg) =>
{
Console.WriteLine("res = {0}, errMsg: {1}", res, errMsg);
});
CDBVariantArray vData = new CDBVariantArray();
vData.Add(1);
vData.Add("Google Inc.");
vData.Add("1600 Amphitheatre Parkway, Mountain View, CA 94043, USA");
vData.Add(66000000000.0);
vData.Add(2);
vData.Add("Microsoft Inc.");
vData.Add("700 Bellevue Way NE- 22nd Floor, Bellevue, WA 98804, USA");
vData.Add(93600000000.0);
vData.Add(3);
vData.Add("Apple Inc.");
vData.Add("1 Infinite Loop, Cupertino, CA 95014, USA");
vData.Add(234000000000.0);
//send three sets of parameterized data in one shot for processing
ok = sqlite.Execute(vData, (handler, res, errMsg, affected, fail_ok, id) =>
{
Console.WriteLine("affected = {0}, fails = {1}, oks = {2}, res = {3}, errMsg: {4}, last insert id = {5}", affected, (uint)(fail_ok >> 32), (uint)fail_ok, res, errMsg, id);
}, (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValuePair<CDBColumnInfoArray, CDBVariantArray> item = ra[last];
item.Value.AddRange(rowData);
}, (handler) =>
{
//rowset header comes here
KeyValuePair<CDBColumnInfoArray, CDBVariantArray> item = new KeyValuePair<CDBColumnInfoArray, CDBVariantArray>(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
});
}
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>())
{
//start socket pool having 1 worker thread which hosts 2 non-blocking sockets
if (!spSqlite.StartSocketPool(cc, 2, 1))
{
Console.WriteLine("No connection to sqlite server and press any key to close the demo ......");
Console.Read(); return;
}
CSqlite sqlite = spSqlite.AsyncHandlers[0];
//Use the above bad implementation to replace original SocketProAdapter.ClientSide.CAsyncDBHandler.Open method
//at file socketpro/src/SproAdapter/asyncdbhandler.cs for cross SendRequest dead lock demonstration
Console.WriteLine("Doing Demo_Cross_Request_Dead_Lock ......");
Demo_Cross_Request_Dead_Lock(sqlite);
//create two tables, COMPANY and EMPLOYEE
TestCreateTables(sqlite);
bool ok = sqlite.WaitAll();
Console.WriteLine("{0} created, opened and shared by multiple sessions", sample_database); Console.WriteLine();
//make sure all other handlers/sockets to open the same database mysample.db
CSqlite[] vSqlite = spSqlite.AsyncHandlers;
for (int n = 1; n < vSqlite.Length; ++n)
{
vSqlite[n].Open(sample_database, (handler, res, errMsg) =>
{
if (res != 0)
{
Console.WriteLine("Open: res = {0}, errMsg: {1}", res, errMsg);
}
}); ok = vSqlite[n].WaitAll();
}
//execute manual transactions concurrently with transaction overlapping on the same session
var tasks = new[] {
Task.Factory.StartNew(Demo_Multiple_SendRequest_MultiThreaded_Wrong, spSqlite),
Task.Factory.StartNew(Demo_Multiple_SendRequest_MultiThreaded_Wrong, spSqlite),
Task.Factory.StartNew(Demo_Multiple_SendRequest_MultiThreaded_Wrong, spSqlite)
}; Demo_Multiple_SendRequest_MultiThreaded_Wrong(spSqlite); Task.WaitAll(tasks);
Console.WriteLine("Demo_Multiple_SendRequest_MultiThreaded_Wrong completed"); Console.WriteLine();
//execute manual transactions concurrently without transaction overlapping on the same session by lock/unlock
tasks = new[] {
Task.Factory.StartNew(Demo_Multiple_SendRequest_MultiThreaded_Correct_Lock_Unlock, spSqlite),
Task.Factory.StartNew(Demo_Multiple_SendRequest_MultiThreaded_Correct_Lock_Unlock, spSqlite),
Task.Factory.StartNew(Demo_Multiple_SendRequest_MultiThreaded_Correct_Lock_Unlock, spSqlite)
}; Demo_Multiple_SendRequest_MultiThreaded_Correct_Lock_Unlock(spSqlite); Task.WaitAll();
Console.WriteLine("Demo_Multiple_SendRequest_MultiThreaded_Correct_Lock_Unlock completed"); Console.WriteLine();
Console.WriteLine("Demonstration of DoFuture .....");
if (!DoFuture(spSqlite).Wait(5000))
{
Console.WriteLine("The requests within the function DoFuture are not completed in 5 seconds");
}
else
{
Console.WriteLine("All requests within the function DoFuture are completed");
}
Console.WriteLine();
Console.WriteLine("Press any key to close the application ......"); Console.Read();
}
}
