static void TestStoredProcedure_2(CSqlServer sql, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra, CDBVariantArray vPData)
{
CParameterInfoArray vPInfo = new CParameterInfoArray();
CParameterInfo info = new CParameterInfo();
info.ParameterName = "RetVal";
info.DataType = tagVariantDataType.sdVT_INT;
info.Direction = tagParameterDirection.pdReturnValue;
vPInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@testid";
info.DataType = tagVariantDataType.sdVT_INT;
vPInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@dot";
info.DataType = tagVariantDataType.sdVT_DATETIMEOFFSET;
vPInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@myxml";
info.DataType = tagVariantDataType.sdVT_XML;
info.ColumnSize = uint.MaxValue;
info.Direction = tagParameterDirection.pdInputOutput;
vPInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@tuuid";
info.DataType = tagVariantDataType.sdVT_CLSID;
info.Direction = tagParameterDirection.pdInputOutput;
vPInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@myvar";
info.DataType = tagVariantDataType.sdVT_VARIANT;
info.Direction = tagParameterDirection.pdOutput;
vPInfo.Add(info);
bool ok = sql.Prepare("sp_TestRare1", dr, vPInfo.ToArray());
//process multiple sets of parameters in one shot
ok = sql.Execute(vPData, er, (h, v) =>
{
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> p = ra[ra.Count - 1];
p.Value.AddRange(v);
}, (h) =>
{
CDBColumnInfoArray v = h.ColumnInfo;
ra.Add(new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(v, new CDBVariantArray()));
Console.WriteLine("dbPath={0}, tablePath={1}", v[0].DBPath, v[0].TablePath);
});
}
static void TestBatch2(CSqlServer sql, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra, CDBVariantArray vParam)
{
CParameterInfoArray vPInfo = new CParameterInfoArray();
CParameterInfo info = new CParameterInfo();
info.ParameterName = "@ID";
info.DataType = tagVariantDataType.sdVT_INT;
vPInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "RetVal";
info.DataType = tagVariantDataType.sdVT_INT;
info.Direction = tagParameterDirection.pdReturnValue;
vPInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@p_company_id";
info.DataType = tagVariantDataType.sdVT_INT;
vPInfo.Add(info);
//return direction can be ignorable
info = new CParameterInfo();
info.ParameterName = "@p_sum_salary";
info.DataType = tagVariantDataType.sdVT_R8;
info.Direction = tagParameterDirection.pdInputOutput;
vPInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@p_last_dt";
info.DataType = tagVariantDataType.sdVT_DATE;
info.Direction = tagParameterDirection.pdOutput;
vPInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@EMPLOYEEID";
info.DataType = tagVariantDataType.sdVT_INT;
vPInfo.Add(info);
//@sqltestdb.dbo.sp_TestProc@@@ -- one return (@) plus three parameters (@@@)
//there is no manual transaction if isolation is tiUnspecified
sql.ExecuteBatch(tagTransactionIsolation.tiUnspecified,
"select getdate();select * from company where id=@ID;@sqltestdb.dbo.sp_TestProc@@@;select * from employee where EMPLOYEEID=@EMPLOYEEID",
vParam, er, (h, v) => {
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> p = ra[ra.Count - 1];
p.Value.AddRange(v);
}, (h) => {
CDBColumnInfoArray v = h.ColumnInfo;
ra.Add(new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(v, new CDBVariantArray()));
Console.WriteLine("dbPath={0}, tablePath={1}", v[0].DBPath, v[0].TablePath);
}, (h) => {
}, vPInfo.ToArray());
}
static void TestBatch(CSqlServer sql, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra)
{
CDBVariantArray vParam = new CDBVariantArray();
vParam.Add(1); //ID
vParam.Add(1); //EMPLOYEEID
CParameterInfoArray vPInfo = new CParameterInfoArray();
CParameterInfo info = new CParameterInfo();
info.ParameterName = "@ID";
info.DataType = tagVariantDataType.sdVT_INT;
vPInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@EMPLOYEEID";
info.DataType = tagVariantDataType.sdVT_INT;
vPInfo.Add(info);
//there is no manual transaction if isolation is tiUnspecified
sql.ExecuteBatch(tagTransactionIsolation.tiUnspecified, "select getdate();select * from company where id=@ID;select * from employee where EMPLOYEEID=@EMPLOYEEID", vParam, er, (h, v) => {
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> p = ra[ra.Count - 1];
p.Value.AddRange(v);
}, (h) => {
CDBColumnInfoArray v = h.ColumnInfo;
ra.Add(new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(v, new CDBVariantArray()));
Console.WriteLine("dbPath={0}, tablePath={1}", v[0].DBPath, v[0].TablePath);
}, (h) => {
}, vPInfo.ToArray());
vParam = new CDBVariantArray();
vParam.Add(1); //ID
vParam.Add(2); //EMPLOYEEID
vParam.Add(2); //ID
vParam.Add(3); //EMPLOYEEID
//Same as sqlite.BeginTrans();
//Select getdate();select * from COMPANY where ID=1;select * from COMPANY where ID=2;getdate();
//select * from EMPLOYEE where EMPLOYEEID=2;select * from EMPLOYEE where EMPLOYEEID=3
//ok = sqlite.EndTrans();
sql.ExecuteBatch(tagTransactionIsolation.tiUnspecified, "select getdate();select * from company where id=@ID;select getdate();select * from employee where EMPLOYEEID=@EMPLOYEEID", vParam, er, (h, v) => {
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> p = ra[ra.Count - 1];
p.Value.AddRange(v);
}, (h) => {
CDBColumnInfoArray v = h.ColumnInfo;
ra.Add(new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(v, new CDBVariantArray()));
Console.WriteLine("dbPath={0}, tablePath={1}", v[0].DBPath, v[0].TablePath);
}, (h) => {
}, vPInfo.ToArray());
}
static void TestPreparedStatements(CSqlServer sql)
{
string sql_insert_parameter = "INSERT INTO company(ID,NAME,ADDRESS,Income)VALUES(@ID,@NAME,@ADDRESS,@Income)";
CParameterInfoArray vInfo = new CParameterInfoArray();
CParameterInfo info = new CParameterInfo();
info.ParameterName = "@ID";
info.DataType = tagVariantDataType.sdVT_INT;
vInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@NAME";
info.DataType = tagVariantDataType.sdVT_BSTR;
info.ColumnSize = 64;
vInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@ADDRESS";
info.DataType = tagVariantDataType.sdVT_BSTR;
info.ColumnSize = 256;
vInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@Income";
info.DataType = tagVariantDataType.sdVT_R8; //double
vInfo.Add(info);
bool ok = sql.Prepare(sql_insert_parameter, dr, vInfo.ToArray());
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 = sql.Execute(vData, er);
}
static void TestPreparedStatements_2(CSqlServer sql)
{
CParameterInfoArray vInfo = new CParameterInfoArray();
CParameterInfo info = new CParameterInfo();
info.DataType = tagVariantDataType.sdVT_CLSID;
info.ParameterName = "@myguid";
vInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@myxml";
info.DataType = tagVariantDataType.sdVT_BSTR;
info.ColumnSize = uint.MaxValue;
vInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@myvariant";
info.DataType = tagVariantDataType.sdVT_VARIANT;
vInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@mydateimeoffset";
info.DataType = tagVariantDataType.sdVT_DATE;
vInfo.Add(info);
//if a prepared statement contains UUID or sql_variant, you must specify an array of parameter definitions
string sql_insert_parameter = "INSERT INTO test_rare1(myguid,myxml,myvariant,mydateimeoffset)VALUES(@myguid,@myxml,@myvariant,@mydateimeoffset)";
bool ok = sql.Prepare(sql_insert_parameter, dr, vInfo.ToArray());
CDBVariantArray vData = new CDBVariantArray();
vData.Add(Guid.NewGuid());
vData.Add("<myxmlroot />");
vData.Add(23.456);
vData.Add(DateTime.Now);
vData.Add(Guid.NewGuid());
vData.Add("<myxmlroot_2 />");
vData.Add("马拉阿歌俱乐部");
vData.Add(DateTime.Now.AddSeconds(123.45));
ok = sql.Execute(vData, er);
}
static void TestStoredProcedure(CSqlServer sql, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra, CDBVariantArray vPData)
{
CParameterInfoArray vPInfo = new CParameterInfoArray();
CParameterInfo info = new CParameterInfo();
info.ParameterName = "RetVal";
info.DataType = tagVariantDataType.sdVT_INT;
info.Direction = tagParameterDirection.pdReturnValue;
vPInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@p_company_id";
info.DataType = tagVariantDataType.sdVT_INT;
vPInfo.Add(info);
//return direction can be ignorable
info = new CParameterInfo();
info.ParameterName = "@p_sum_salary";
info.DataType = tagVariantDataType.sdVT_R8;
info.Direction = tagParameterDirection.pdInputOutput;
vPInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@p_last_dt";
info.DataType = tagVariantDataType.sdVT_DATE;
info.Direction = tagParameterDirection.pdOutput;
vPInfo.Add(info);
bool ok = sql.Prepare("sqltestdb.dbo.sp_TestProc", dr, vPInfo.ToArray());
//process multiple sets of parameters in one shot
ok = sql.Execute(vPData, er, (h, v) =>
{
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> p = ra[ra.Count - 1];
p.Value.AddRange(v);
}, (h) =>
{
CDBColumnInfoArray v = h.ColumnInfo;
ra.Add(new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(v, new CDBVariantArray()));
Console.WriteLine("dbPath={0}, tablePath={1}", v[0].DBPath, v[0].TablePath);
});
}
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 InsertBLOBByPreparedStatement(CSqlServer sql)
{
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(@EMPLOYEEID,@CompanyId,@name,@JoinDate,@myimage,@DESCRIPTION,@Salary)";
CParameterInfoArray vInfo = new CParameterInfoArray();
CParameterInfo info = new CParameterInfo();
info.ParameterName = "@EMPLOYEEID";
info.DataType = tagVariantDataType.sdVT_INT;
vInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@CompanyId";
info.DataType = tagVariantDataType.sdVT_INT;
vInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@name";
info.ColumnSize = 64;
info.DataType = tagVariantDataType.sdVT_BSTR;
vInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@JoinDate";
info.DataType = tagVariantDataType.sdVT_DATE;
vInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@myimage";
info.ColumnSize = uint.MaxValue;
info.DataType = tagVariantDataType.sdVT_UI1 | tagVariantDataType.sdVT_ARRAY;
vInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@DESCRIPTION";
info.ColumnSize = uint.MaxValue;
info.DataType = tagVariantDataType.sdVT_BSTR;
vInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@Salary";
info.ColumnSize = uint.MaxValue;
info.DataType = tagVariantDataType.sdVT_DECIMAL;
info.Precision = 15;
info.Scale = 2;
vInfo.Add(info);
bool ok = sql.Prepare(sqlInsert, dr, vInfo.ToArray());
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);
byte[] bytes = sbBlob.UQueue.GetBuffer();
vData.Add(bytes);
vData.Add(wstr);
vData.Add(254000.2460d);
//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);
bytes = sbBlob.UQueue.GetBuffer();
vData.Add(bytes);
vData.Add(str);
vData.Add(20254000.197d);
//third set of data
vData.Add(3);
vData.Add(2); //Microsoft company id
vData.Add("Hillary Clinton");
vData.Add(DateTime.Now);
sbBlob.Save(wstr);
bytes = sbBlob.UQueue.GetBuffer();
vData.Add(bytes);
vData.Add(wstr);
vData.Add(6254000.5d);
//execute multiple sets of parameter data in one short
ok = sql.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();
using (CSocketPool <CSqlServer> spSql = new CSocketPool <CSqlServer>())
{
CConnectionContext cc = new CConnectionContext(host, 20903, "sa", "Smash123");
if (!spSql.StartSocketPool(cc, 1, 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 ......");
CSqlServer 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 = 10000;
DateTime start = DateTime.Now;
for (int n = 0; n < count; ++n)
{
//set meta to false so that server side doesn't return meta, which will improve query performance
ok = mysql.Execute(sql, er, r, rh, false);
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);
//you need to compile and run the sample project test_sharp before running the below code
ok = mysql.Execute("USE sqltestdb;delete from company where id > 3");
string sql_insert_parameter = "INSERT INTO company(ID,NAME,ADDRESS,Income)VALUES(@ID,@NAME,@ADDRESS,@Income)";
CParameterInfoArray vInfo = new CParameterInfoArray();
CParameterInfo info = new CParameterInfo();
info.ParameterName = "@ID";
info.DataType = tagVariantDataType.sdVT_INT;
vInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@NAME";
info.DataType = tagVariantDataType.sdVT_BSTR;
info.ColumnSize = 64;
vInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@ADDRESS";
info.DataType = tagVariantDataType.sdVT_BSTR;
info.ColumnSize = 256;
vInfo.Add(info);
info = new CParameterInfo();
info.ParameterName = "@Income";
info.DataType = tagVariantDataType.sdVT_R8; //double
vInfo.Add(info);
ok = mysql.Prepare(sql_insert_parameter, dr, vInfo.ToArray());
ok = mysql.WaitAll();
int index = 0;
count = 50000;
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();
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();
}
}
