static void TestPreparedStatements_2(COdbc odbc) { string sql_insert_parameter = "INSERT INTO test_rare1(myguid,myxml,myvariant,mydateimeoffset)VALUES(?,?,?,?)"; bool ok = odbc.Prepare(sql_insert_parameter, dr); CDBVariantArray vData = new CDBVariantArray(); //first set vData.Add(Guid.NewGuid()); vData.Add("<myxmlroot />"); vData.Add(23.456); vData.Add(DateTime.Now); //second set vData.Add(Guid.NewGuid()); vData.Add("<myxmlroot_2 />"); vData.Add("马拉阿歌俱乐部"); vData.Add(DateTime.Now.AddMinutes(1)); //third set vData.Add(Guid.NewGuid()); vData.Add("<myxmlroot_3 />"); vData.Add(1); vData.Add(DateTime.Now.AddMinutes(-1)); ok = odbc.Execute(vData, er); }
static CDBVariantArray TestStoredProcedure(COdbc odbc, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra) { CDBVariantArray vPData = new CDBVariantArray(); //first set vPData.Add(1); vPData.Add(2.35m); //input/output vPData.Add(null); //input/output //second set vPData.Add(2); vPData.Add(0.99m); //input/output vPData.Add(null); //input/output //Parameter info array can be ignored for some ODBC drivers like MySQL, MS SQL Server, etc but performance will be degraded for code simplicity bool ok = odbc.Prepare("{call sp_TestProc(?,?,?)}", dr); 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); }; ok = odbc.Execute(vPData, er, r, rh); return(vPData); }
static void TestPreparedStatements(COdbc odbc) { string sql_insert_parameter = "INSERT INTO company(ID,NAME,ADDRESS,Income)VALUES(?,?,?,?)"; bool ok = odbc.Prepare(sql_insert_parameter, dr); CDBVariantArray vData = new CDBVariantArray(); //first set vData.Add(1); vData.Add("Google Inc."); vData.Add("1600 Amphitheatre Parkway, Mountain View, CA 94043, USA"); vData.Add(66000000000.0); //second set vData.Add(2); vData.Add("Microsoft Inc."); vData.Add("700 Bellevue Way NE- 22nd Floor, Bellevue, WA 98804, USA"); vData.Add(93600000000.0); //third set vData.Add(3); vData.Add("Apple Inc."); vData.Add("1 Infinite Loop, Cupertino, CA 95014, USA"); vData.Add(234000000000.0); ok = odbc.Execute(vData, er); }
static void TestStoredProcedure_2(COdbc odbc, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra, CDBVariantArray vPData) { CParameterInfo[] vInfo = { new CParameterInfo(), new CParameterInfo(), new CParameterInfo(), new CParameterInfo(), new CParameterInfo() }; vInfo[0].DataType = tagVariantDataType.sdVT_I4; //return direction can be ignorable vInfo[1].DataType = tagVariantDataType.sdVT_I4; vInfo[2].DataType = tagVariantDataType.sdVT_XML; vInfo[2].Direction = tagParameterDirection.pdInputOutput; vInfo[3].DataType = tagVariantDataType.sdVT_CLSID; vInfo[3].Direction = tagParameterDirection.pdInputOutput; vInfo[4].DataType = tagVariantDataType.sdVT_VARIANT; vInfo[4].Direction = tagParameterDirection.pdOutput; bool ok = odbc.Prepare("{?=call sp_TestRare1(?, ?, ?, ?)}", dr, vInfo); 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); }; ok = odbc.Execute(vPData, er, r, rh); }
static void InsertBLOBByPreparedStatement(COdbc odbc) { 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 into employee(EmployeeId,CompanyId,name,JoinDate,myimage,DESCRIPTION,Salary)values(?,?,?,?,?,?,?)"; bool ok = odbc.Prepare(sqlInsert, dr); 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.24m); //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.15m); //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.08m); //send three sets of parameterized data in one shot for processing ok = odbc.Execute(vData, er); } }
static CDBVariantArray TestStoredProcedure_2(COdbc odbc, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra) { //vInfo is ignorable for MS SQL server ODBC drivers for code simplicity CParameterInfo[] vInfo = { new CParameterInfo(), new CParameterInfo(), new CParameterInfo(), new CParameterInfo(), new CParameterInfo() }; vInfo[0].DataType = tagVariantDataType.sdVT_I4; //return direction can be ignorable vInfo[1].DataType = tagVariantDataType.sdVT_I4; vInfo[2].DataType = tagVariantDataType.sdVT_XML; vInfo[2].Direction = tagParameterDirection.pdInputOutput; vInfo[3].DataType = tagVariantDataType.sdVT_CLSID; vInfo[3].Direction = tagParameterDirection.pdInputOutput; vInfo[4].DataType = tagVariantDataType.sdVT_VARIANT; vInfo[4].Direction = tagParameterDirection.pdOutput; bool ok = odbc.Prepare("{?=call sp_TestRare1(?,?,?,?)}", dr, vInfo); CDBVariantArray vPData = new CDBVariantArray(); //first set vPData.Add(-1); //return int. output parameter value not important. vPData.Add(1); //@testid vPData.Add("<test_sqlserver />"); //@myxml vPData.Add(Guid.NewGuid()); //@tuuid vPData.Add(-1); //@myvar. output parameter value not important. //second set vPData.Add(2); //return int. output parameter data type not important. vPData.Add(4); //@testid vPData.Add("<test_sqlserver_again />"); //@myxml vPData.Add(Guid.NewGuid()); //@tuuid vPData.Add(2); //@myvar. output parameter value not important. 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); }; ok = odbc.Execute(vPData, er, r, rh); return(vPData); }
static void TestPreparedStatements_2(COdbc odbc) { CParameterInfo[] vInfo = new CParameterInfo[4]; vInfo[0] = new CParameterInfo(); vInfo[0].DataType = tagVariantDataType.sdVT_CLSID; vInfo[1] = new CParameterInfo(); vInfo[1].DataType = tagVariantDataType.sdVT_BSTR; vInfo[2] = new CParameterInfo(); vInfo[2].DataType = tagVariantDataType.sdVT_VARIANT; vInfo[3] = new CParameterInfo(); vInfo[3].DataType = tagVariantDataType.sdVT_DATE; string sql_insert_parameter = "INSERT INTO test_rare1(myguid,myxml,myvariant,mydateimeoffset)VALUES(?,?,?,?)"; bool ok = odbc.Prepare(sql_insert_parameter, dr, vInfo); CDBVariantArray vData = new CDBVariantArray(); //first set vData.Add(Guid.NewGuid()); vData.Add("<myxmlroot />"); vData.Add(23.456); vData.Add(DateTime.Now); //second set vData.Add(Guid.NewGuid()); vData.Add("<myxmlroot_2 />"); vData.Add("马拉阿歌俱乐部"); vData.Add(DateTime.Now.AddMinutes(1)); //third set vData.Add(Guid.NewGuid()); vData.Add("<myxmlroot_3 />"); vData.Add(1); vData.Add(DateTime.Now.AddMinutes(-1)); ok = odbc.Execute(vData, er); }
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(); } }