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> 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 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 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); })); }
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 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 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 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(); } }