private void btnConnect_Click(object sender, EventArgs e) { CConnectionContext cc = new CConnectionContext(txtHost.Text, 20903, txtUser.Text, txtPassword.Text); m_spSql = new CSocketPool <COdbc>(false); //set event for MySQL/Mariadb database shutdown m_spSql.SocketPoolEvent += new CSocketPool <COdbc> .DOnSocketPoolEvent(m_spSql_SocketPoolEvent); if (!m_spSql.StartSocketPool(cc, 1, 1)) { txtMessage.Text = "No connection to " + txtHost.Text; return; } COdbc sql = m_spSql.AsyncHandlers[0]; //set event for tracking all database table update events, delete, update and insert m_spSql.Sockets[0].Push.OnPublish += new DOnPublish(Push_OnPublish); //create a DB session with default to sample database sakila bool ok = sql.Open("sakila", 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 = sql.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 COdbc.AppendRowDataIntoDataTable(data, dt); }, (h) => { //this callback is fired from worker thread from socket pool thread dt = COdbc.MakeDataTable(h.ColumnInfo); string name = h.ColumnInfo[0].DBPath + "." + h.ColumnInfo[0].TablePath; dt.TableName = name; m_ds.Tables.Add(dt); }); ok = sql.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 void Main(string[] args) { Console.WriteLine("Remote host: "); string host = Console.ReadLine(); CConnectionContext cc = new CConnectionContext(host, 20903, "sa", "Smash123"); //20901 for plugindev using (CSocketPool <COdbc> spOdbc = new CSocketPool <COdbc>(true, 600000)) { if (!spOdbc.StartSocketPool(cc, 1)) { Console.WriteLine("Failed in connecting to remote async odbc server"); Console.WriteLine("Press any key to close the application ......"); Console.Read(); return; } COdbc odbc = spOdbc.Seek(); bool ok = odbc.Open("", dr); //use default database List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra = new List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> >(); COdbc.DRows r = (handler, rowData) => { //rowset data come here int last = ra.Count - 1; KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = ra[last]; item.Value.AddRange(rowData); }; COdbc.DRowsetHeader rh = (handler) => { //rowset header comes here KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(handler.ColumnInfo, new CDBVariantArray()); ra.Add(item); }; TestCreateTables(odbc); ok = odbc.Execute("delete from employee;delete from company;delete from test_rare1;delete from SpatialTable;INSERT INTO SpatialTable(mygeometry, mygeography)VALUES(geometry::STGeomFromText('LINESTRING(100 100,20 180,180 180)',0),geography::Point(47.6475,-122.1393,4326))", er); ok = odbc.Execute("INSERT INTO test_rare1(mybool,mymoney,myxml,myvariant,mydateimeoffset)values(1,23.45,'<sometest />', N'美国总统川普下个星期四','2017-05-02 00:00:00.0000000 -04:00');INSERT INTO test_rare1(mybool,mymoney,myvariant)values(0,1223.45,'This is a test for ASCII string inside sql_variant');INSERT INTO test_rare1(myvariant)values(283.45)", er); TestPreparedStatements(odbc); TestPreparedStatements_2(odbc); InsertBLOBByPreparedStatement(odbc); ok = odbc.Execute("SELECT * from company;select * from employee;select CONVERT(datetime,SYSDATETIME());select * from test_rare1;select * from SpatialTable", er, r, rh); ok = odbc.Tables("sqltestdb", "%", "%", "TABLE", er, r, rh); CDBVariantArray vPData = TestStoredProcedure(odbc, ra); ok = odbc.WaitAll(); Console.WriteLine(); Console.WriteLine("There are {0} output data returned", odbc.Outputs * 2); CDBVariantArray vPData2 = TestStoredProcedure_2(odbc, ra); ok = odbc.WaitAll(); Console.WriteLine(); Console.WriteLine("There are {0} output data returned", odbc.Outputs * 2); CDBVariantArray vPData3 = TestBatch(odbc, ra); ok = odbc.WaitAll(); Console.WriteLine(); Console.WriteLine("There are {0} output data returned", odbc.Outputs * 2); ok = odbc.Tables("AdventureWorks", "%", "%", "TABLE", er, r, rh); ok = odbc.WaitAll(); ok = odbc.Execute("use AdventureWorks", er); KeyValuePair <CDBColumnInfoArray, CDBVariantArray> tables = ra[ra.Count - 1]; int columns = tables.Key.Count; int num_tables = tables.Value.Count / columns; for (int n = 0; n < num_tables; ++n) { string sql = "select * from " + tables.Value[columns * n + 1].ToString() + "." + tables.Value[columns * n + 2].ToString(); ok = odbc.Execute(sql, er, r, rh); } ok = odbc.WaitAll(); ok = odbc.Tables("AdventureWorksDW", "%", "%", "TABLE", er, r, rh); ok = odbc.WaitAll(); ok = odbc.Execute("use AdventureWorksDW", er); tables = ra[ra.Count - 1]; columns = tables.Key.Count; num_tables = tables.Value.Count / columns; for (int n = 0; n < num_tables; ++n) { string sql = "select * from " + tables.Value[columns * n + 1].ToString() + "." + tables.Value[columns * n + 2].ToString(); ok = odbc.Execute(sql, er, r, rh); } ok = odbc.WaitAll(); int index = 0; Console.WriteLine(); Console.WriteLine("+++++ Start rowsets +++"); foreach (KeyValuePair <CDBColumnInfoArray, CDBVariantArray> it in ra) { 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("SocketPro performance test against a remote MS SQL backend DB"); Console.WriteLine("Remote host: "); string host = Console.ReadLine(); Console.WriteLine("Wide Arae Netork ? (1 or 0)"); string str = Console.ReadLine(); int wan = 0; try { wan = int.Parse(str); } finally { } using (CSocketPool <COdbc> spSql = new CSocketPool <COdbc>()) { CConnectionContext cc = new CConnectionContext(host, 20903, "sa", "Smash123"); if (!spSql.StartSocketPool(cc, 1)) { Console.WriteLine("Failed in connecting to remote helloworld server. Press any key to close the application ......"); Console.Read(); return; } Console.WriteLine("Database name: "); string dbName = Console.ReadLine(); Console.WriteLine("Table name: "); string tableName = Console.ReadLine(); Console.WriteLine("sql filter: "); string filter = Console.ReadLine(); Console.WriteLine("Asynchronous execution (0) or synchronous execution (1) ?"); bool sync = (Console.ReadKey().KeyChar != '0'); Console.WriteLine(""); Console.WriteLine("Computing ......"); COdbc mysql = spSql.Seek(); CAsyncDBHandler.DResult dr = (handler, res, errMsg) => { if (res != 0) { Console.WriteLine("res = {0}, errMsg: {1}", res, errMsg); } }; uint obtained = 0; bool ok = mysql.Open(dbName, 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); }; obtained = 0; string sql = "select * from " + tableName; if (filter.Length > 0) { sql += " where " + filter; } int count = (wan == 0) ? 50000 : 5000; DateTime start = DateTime.Now; for (int n = 0; n < count; ++n) { ok = mysql.Execute(sql, er, r, rh); if (sync && ok) { ok = mysql.WaitAll(); } if (!ok) { break; } } if (!sync && ok) { ok = mysql.WaitAll(); } double diff = (DateTime.Now - start).TotalMilliseconds; Console.WriteLine("Time required = {0} milliseconds for {1} query requests", diff, obtained); //run the sample socketpro/stream_sql/uodbc/test_sharp first to create the test table before the following inserting test ok = mysql.Execute("USE sqltestdb;delete from company where id > 3"); string sql_insert_parameter = "INSERT INTO company(ID,NAME,ADDRESS,Income)VALUES(?,?,?,?)"; ok = mysql.Prepare(sql_insert_parameter, dr); ok = mysql.WaitAll(); int index = 0; count = (wan == 0) ? 250000 : 25000; Console.WriteLine(); Console.WriteLine("Going to insert {0} records into the table sqltestdb.company", count); start = DateTime.Now; CDBVariantArray vData = new CDBVariantArray(); ok = mysql.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 = mysql.Execute(vData, er); ok = mysql.EndTrans(); vData.Clear(); if (sync) { ok = mysql.WaitAll(); } Console.WriteLine("Commit {0} records into the table sqltestdb.company", index); ok = mysql.BeginTrans(); index = 0; } } if (vData.Count > 0) { ok = mysql.Execute(vData, er); Console.WriteLine("Commit {0} records into the table sqltestdb.company", index); } ok = mysql.EndTrans(); ok = mysql.WaitAll(); diff = (DateTime.Now - start).TotalMilliseconds; Console.WriteLine("Time required = {0} milliseconds for {1} insert requests", diff, count); Console.WriteLine(); Console.WriteLine("Press any key to close the application ......"); Console.ReadLine(); } }
static void Main(string[] args) { Console.WriteLine("Remote host: "); string host = Console.ReadLine(); Console.WriteLine(""); Console.WriteLine("Table name: "); string tableName = Console.ReadLine(); Console.WriteLine("sql filter: "); string filter = Console.ReadLine(); CConnectionContext cc = new CConnectionContext(host, 20903, "sa", "Smash123"); Console.WriteLine("Asynchronous execution (0) or synchronous execution (1) ?"); bool sync = (Console.ReadKey().KeyChar != '0'); using (CSocketPool <COdbc> spOdbc = new CSocketPool <COdbc>()) { if (!spOdbc.StartSocketPool(cc, 1, 1)) { Console.WriteLine("Failed in connecting to remote async ODBC server"); Console.WriteLine("Press any key to close the application ......"); Console.Read(); return; } Console.WriteLine(""); Console.WriteLine("Computing ......"); COdbc odbc = spOdbc.Seek(); CAsyncDBHandler.DResult dr = (handler, res, errMsg) => { if (res != 0) { Console.WriteLine("res = {0}, errMsg: {1}", res, errMsg); } }; uint obtained = 0; bool ok = odbc.Open("sakila", 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 = odbc.WaitAll(); obtained = 0; string sql = "select * from " + tableName; if (filter.Length > 0) { sql += " where " + filter; } uint count = 50000; DateTime start = DateTime.Now; for (uint n = 0; n < count; ++n) { ok = odbc.Execute(sql, er, r, rh); if (sync && ok) { ok = odbc.WaitAll(); } if (!ok) { break; } } if (!sync && ok) { ok = odbc.WaitAll(); } double diff = (DateTime.Now - start).TotalMilliseconds; Console.WriteLine("Time required = {0} millseconds for {1} requests", diff, obtained); Console.WriteLine("Press any key to close the application ......"); Console.ReadLine(); } }