private async Task ExecuteSql()
{
CDBVariantArray v = new CDBVariantArray();
v.Add(1); /*Google id*/ v.Add("Ted Cruz"); v.Add(DateTime.Now);
v.Add(1); /*Google id*/ v.Add("Donald Trump"); v.Add(DateTime.Now);
v.Add(2); /*MS id*/ v.Add("Hillary Clinton"); v.Add(DateTime.Now);
var handler = Global.Master.SeekByQueue();
var task = handler.executeBatch(tagTransactionIsolation.tiReadCommited,
"INSERT INTO mysample.EMPLOYEE(CompanyId,Name,JoinDate)VALUES(?,?,?)", v);
if (!handler.Socket.Connected)
{
txtResult.Text = "No session to master DB now but " +
"request is safely saved for processing later";
}
else
{
var res = await task;
if (res.ec == 0)
{
txtResult.Text = "Last employeeid: " + res.lastId;
}
else
{
txtResult.Text = res.em;
}
}
}
static CDBVariantArray TestStoredProcedure(CMysql mysql, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra)
{
CDBVariantArray vPData = new CDBVariantArray();
//first set
vPData.Add(1);
vPData.Add(1.4);
vPData.Add(0);
//second set
vPData.Add(2);
vPData.Add(2.5);
vPData.Add(0);
bool ok = mysql.Prepare("call sp_TestProc(?, ?, ?)", dr);
CMysql.DRows r = (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = ra[last];
item.Value.AddRange(rowData);
};
CMysql.DRowsetHeader rh = (handler) =>
{
//rowset header comes here
KeyValuePair <CDBColumnInfoArray, CDBVariantArray> item = new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
};
ok = mysql.Execute(vPData, er, r, rh);
return(vPData);
}
static Task <CAsyncDBHandler.SQLExeInfo> TestPreparedStatements(CMysql mysql)
{
string sql_insert_parameter = "INSERT INTO company(ID, NAME, ADDRESS, Income) VALUES (?, ?, ?, ?)";
mysql.Prepare(sql_insert_parameter);
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);
return(mysql.execute(vData));
}
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);
}
private Task <string> DoInserts(CDBVariantArray v)
{
TaskCompletionSource <string> tcs = new TaskCompletionSource <string>();
var handler = Global.Master.LockByMyAlgorithm(6000); //6 seconds
string s = ""; bool ok = handler.BeginTrans(); //start streaming multiple requests
ok = handler.Prepare("INSERT INTO mysample.EMPLOYEE(CompanyId, Name, JoinDate)VALUES(?,?,?)");
ok = handler.Execute(v, (h, res, errMsg, affected, fail_ok, vtId) => {
if (res != 0)
{
s = errMsg;
}
else
{
s = "Last employeeid=" + vtId.ToString();
}
});
ok = handler.EndTrans(tagRollbackPlan.rpRollbackErrorAll, (h, res, errMsg) => {
if (res != 0)
{
s = errMsg;
}
try {
tcs.SetResult(s);
} catch (System.Exception) { } //exception may happen when there is master auto reconnection
});
if (!handler.AttachedClientSocket.Connected)
{
tcs.SetResult("No session to master DB now but request is safely saved for processing later");
}
Global.Master.UnlockByMyAlgorithm(handler); //put handler back into pool for reuse
return(tcs.Task);
}
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 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 Task <CAsyncDBHandler.SQLExeInfo> TestStoredProcedure(CMysql mysql, List <KeyValue> ra, out CDBVariantArray vPData)
{
vPData = new CDBVariantArray();
//first set
vPData.Add(1); //input
vPData.Add(1.4); //input-output
//output not important and it's used for receiving a proper data from MySQL
vPData.Add(0); //output
//second set
vPData.Add(2); //input
vPData.Add(2.5); //input-output
//output not important and it's used for receiving a proper data from MySQL
vPData.Add(0); //output
mysql.Prepare("call sp_TestProc(?, ?, ?)");
CMysql.DRows r = (handler, rowData) =>
{
//rowset data come here
int last = ra.Count - 1;
KeyValue item = ra[last];
item.Value.AddRange(rowData);
};
CMysql.DRowsetHeader rh = (handler) =>
{
//rowset header comes here
KeyValue item = new KeyValue(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
};
return(mysql.execute(vPData, r, rh));
}
private async Task ExecuteSql()
{
CDBVariantArray v = new CDBVariantArray();
v.Add(1); /*Google id*/ v.Add("Ted Cruz"); v.Add(System.DateTime.Now);
v.Add(1); /*Google id*/ v.Add("Donald Trump"); v.Add(System.DateTime.Now);
v.Add(2); /*Microsoft id*/ v.Add("Hillary Clinton"); v.Add(System.DateTime.Now);
txtResult.Text = await DoInserts(v);
}
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 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 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 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 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 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 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);
}
private Task <string> DoInserts(CDBVariantArray v)
{
TaskCompletionSource <string> tcs = new TaskCompletionSource <string>();
var handler = Global.Master.SeekByQueue();
bool ok = handler.ExecuteBatch(tagTransactionIsolation.tiReadCommited,
"INSERT INTO mysample.EMPLOYEE(CompanyId,Name,JoinDate)VALUES(?,?,?)",
v, (h, res, errMsg, affected, fail_ok, vtId) => {
try {
tcs.SetResult((res != 0) ? errMsg : "Last employeeid=" + vtId.ToString());
} catch (System.Exception) { }
});
if (!handler.AttachedClientSocket.Connected)
{
tcs.SetResult("No session to master DB now but request is safely saved for processing later");
}
return(tcs.Task);
}
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);
}
private Task <string> DoInserts(CDBVariantArray v)
{
TaskCompletionSource <string> tcs = new TaskCompletionSource <string>();
var handler = Global.Master.LockByMyAlgorithm(6000); //6 seconds
bool ok = handler.ExecuteBatch(tagTransactionIsolation.tiReadCommited,
"INSERT INTO mysample.EMPLOYEE(CompanyId,Name,JoinDate)VALUES(?,?,?)",
v, (h, res, errMsg, affected, fail_ok, vtId) => {
try {
tcs.SetResult((res != 0) ? errMsg : "Last employeeid=" + vtId.ToString());
} finally { }
});
if (!handler.AttachedClientSocket.Connected)
{
tcs.SetResult("No session to master DB now but request is safely saved for processing later");
}
Global.Master.UnlockByMyAlgorithm(handler); //put handler back into pool for reuse
return(tcs.Task);
}
static CDBVariantArray TestBatch(COdbc odbc, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra)
{
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.
vPData.Add(1);
vPData.Add(2.35m); //input/output
vPData.Add(null);
//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.
vPData.Add(2);
vPData.Add(0.99m); //input/output
vPData.Add(null);
//Parameter info array can be ignored for some ODBC drivers like MySQL, MS SQL Server, etc but performance will be degraded for code simplicity
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);
};
bool ok = odbc.ExecuteBatch(tagTransactionIsolation.tiUnspecified, "select getdate();{?=call sp_TestRare1(?,?,?,?)};{call sqltestdb.dbo.sp_TestProc(?,?,?)}", vPData,
er, r, rh);
return(vPData);
}
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 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 Task <CAsyncDBHandler.SQLExeInfo> TestBLOBByPreparedStatement(CMysql mysql)
{
mysql.Prepare("insert into employee(CompanyId,name,JoinDate,image,DESCRIPTION,Salary)values(?,?,?,?,?,?)");
CDBVariantArray vData = new CDBVariantArray();
using (CScopeUQueue sbBlob = new CScopeUQueue())
{
//first set of data
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);
//second set of data
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);
//third set of data
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);
//send three sets of parameterized data in one shot for processing
return(mysql.execute(vData));
}
}
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 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 MySQL backend DB");
Console.WriteLine("Remote host: ");
string host = Console.ReadLine();
Console.WriteLine("Database name: ");
string dbName = Console.ReadLine();
Console.WriteLine("Table name: ");
string tableName = Console.ReadLine();
Console.WriteLine("sql filter: ");
string filter = Console.ReadLine();
CConnectionContext cc = new CConnectionContext(host, 20902, "root", "Smash123");
Console.WriteLine("Asynchronous execution (0) or synchronous execution (1) ?");
bool sync = (Console.ReadKey().KeyChar != '0');
using (CSocketPool <CMysql> spMysql = new CSocketPool <CMysql>())
{
if (!spMysql.StartSocketPool(cc, 1, 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 ......");
CMysql mysql = spMysql.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)
{
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);
//you need to compile and run the sample project test_sharp before running the below code
ok = mysql.Execute("USE mysqldb;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 = 50000;
Console.WriteLine();
Console.WriteLine("Going to insert {0} records into the table mysqldb.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 mysqldb.company", index);
ok = mysql.BeginTrans();
index = 0;
}
}
if (vData.Count > 0)
{
ok = mysql.Execute(vData, er);
Console.WriteLine("Commit {0} records into the table mysqldb.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();
}
}
void UploadEmployees(CUQueue q, ulong reqIndex)
{
uint ret;
KeyValuePair <int, string> error = new KeyValuePair <int, string>();
ss.CInt64Array vId = new ss.CInt64Array();
CDBVariantArray vData;
q.Load(out vData);
if (vData.Count == 0)
{
ret = SendResultIndex(reqIndex, ss.Consts.idUploadEmployees, (int)0, "", vId);
return;
}
else if ((vData.Count % 3) != 0)
{
ret = SendResultIndex(reqIndex, ss.Consts.idUploadEmployees, (int)-1, "Data array size is wrong", vId);
return;
}
//use master for insert, update and delete
var handler = CYourServer.Master.Lock(); //use Lock and Unlock to avoid SQL stream overlap on a session within a multi-thread environment
if (handler == null)
{
ret = SendResultIndex(reqIndex, ss.Consts.idUploadEmployees, (int)-2, "No connection to a master database", vId);
return;
}
CClientSocket cs = handler.AttachedClientSocket;
do
{
if (!handler.BeginTrans() || !handler.Prepare("INSERT INTO mysample.EMPLOYEE(CompanyId,Name,JoinDate)VALUES(?,?,?)"))
{
break;
}
bool ok = true;
CDBVariantArray v = new CDBVariantArray();
int rows = vData.Count / 3;
for (int n = 0; n < rows; ++n)
{
v.Add(vData[n * 3 + 0]);
v.Add(vData[n * 3 + 1]);
v.Add(vData[n * 3 + 2]);
ok = handler.Execute(v, (h, r, err, affected, fail_ok, vtId) => {
if (r != 0)
{
if (error.Key == 0)
{
error = new KeyValuePair <int, string>(r, err);
}
vId.Add(-1);
}
else
{
vId.Add(long.Parse(vtId.ToString()));
}
});
if (!ok)
{
break;
}
v.Clear();
}
if (!ok)
{
break;
}
ulong peer_handle = Handle;
if (!handler.EndTrans(tagRollbackPlan.rpRollbackErrorAll, (h, res, errMsg) => {
//send result if front peer not closed yet
if (peer_handle == Handle)
{
if (res != 0 && error.Key == 0)
{
error = new KeyValuePair <int, string>(res, errMsg);
}
ret = SendResultIndex(reqIndex, ss.Consts.idUploadEmployees, error.Key, error.Value, vId);
}
}, (h, canceled) => {
//send error message if front peer not closed yet
if (peer_handle == Handle)
{
//socket closed after requests are put on wire
if (error.Key == 0)
{
error = new KeyValuePair <int, string>(cs.ErrorCode, cs.ErrorMsg);
}
ret = SendResultIndex(reqIndex, ss.Consts.idUploadEmployees, error.Key, error.Value, vId);
}
}))
{
break;
}
//put handler back into pool as soon as possible for reuse as long as socket connection is not closed yet
CYourServer.Master.Unlock(handler);
return;
} while (false);
ret = SendResultIndex(reqIndex, ss.Consts.idUploadEmployees, cs.ErrorCode, cs.ErrorMsg, vId);
}
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 Main(string[] args)
{
Console.WriteLine("Remote host: ");
string host = Console.ReadLine();
CConnectionContext cc = new CConnectionContext(host, 20901, "sa", "Smash123");
#if DEBUG
using (CSocketPool <CSqlServer> spSql = new CSocketPool <CSqlServer>(true, 3600 * 1000))
#else
using (CSocketPool <CSqlServer> spSql = new CSocketPool <CSqlServer>())
#endif
{
if (!spSql.StartSocketPool(cc, 1))
{
Console.WriteLine("Failed in connecting to remote async sql server. Press any key to close the application ......");
Console.Read();
return;
}
CSqlServer sql = spSql.Seek();
//track all DML (DELETE, INSERT and UPDATE) events
sql.Socket.Push.OnPublish += (sender, messageSender, group, msg) => {
if (group[0] == DB_CONSTS.STREAMING_SQL_CHAT_GROUP_ID)
{
object[] vMsg = (object[])msg;
tagUpdateEvent ue = (tagUpdateEvent)(int)(vMsg[0]);
string server = (string)vMsg[1];
string user = (string)vMsg[2];
string database = (string)vMsg[3];
Console.WriteLine("DML event={0}, server={1}, database={2}, user={3}, table={4}", ue, server, database, user, vMsg[4].ToString());
}
};
List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra = new List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> >();
//enable monitoring DML events through triggers by flag DB_CONSTS.ENABLE_TABLE_UPDATE_MESSAGES
bool ok = sql.Open("", dr, DB_CONSTS.ENABLE_TABLE_UPDATE_MESSAGES);
sql.WaitAll();
CAsyncDBHandler.DRowsetHeader rh = (h) => {
CDBColumnInfoArray v = h.ColumnInfo;
if (v.Count > 0)
{
Console.WriteLine("dbPath={0}, tablePath={1}", v[0].DBPath, v[0].TablePath);
ra.Add(new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(v, new CDBVariantArray()));
}
};
CAsyncDBHandler.DRows rows = (h, vData) => {
int endIndex = ra.Count - 1;
ra[endIndex].Value.AddRange(vData);
};
//bring all table data which have USqlStream trigger (usqlserver.USqlStream.PublishDMLEvent) with an empty sql input string when opening with the flag DB_CONSTS.ENABLE_TABLE_UPDATE_MESSAGES
ok = sql.Execute("", er, rows, rh);
TestCreateTablesAndStoredProcedures(sql);
ok = sql.Execute("select * from SpatialTable", er, rows, rh);
ok = sql.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 = sql.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(sql);
TestPreparedStatements_2(sql);
InsertBLOBByPreparedStatement(sql);
CDBVariantArray vPData = new CDBVariantArray();
//first set
vPData.Add(0); //retval
vPData.Add(1);
vPData.Add(21.2);
vPData.Add(null);
//2nd set
vPData.Add(0); //retval
vPData.Add(2);
vPData.Add(11.42);
vPData.Add(null);
TestStoredProcedure(sql, ra, vPData);
sql.WaitAll();
vPData.Clear();
//first set
vPData.Add(-1); //return int
vPData.Add(1); //@testid
vPData.Add(DateTime.Now);
vPData.Add("<test_sqlserver />"); //@myxml
Guid guid = Guid.NewGuid();
vPData.Add(guid); //@tuuid
vPData.Add(true); //@myvar
//2nd set
vPData.Add(-2); //return int
vPData.Add(4); //@testid
vPData.Add(DateTime.Now);
vPData.Add("<test_sqlserver_again />"); //@myxml
Guid guid2 = Guid.NewGuid();
vPData.Add(guid2); //@tuuid
vPData.Add(false); //@myvar
TestStoredProcedure_2(sql, ra, vPData);
sql.WaitAll();
TestBatch(sql, ra);
CDBVariantArray vParam = new CDBVariantArray();
//first set
vParam.Add(1); //ID
vParam.Add(0); //retval
//last three data will be updated with outputs
vParam.Add(1); //input @p_company_id, output retval
vParam.Add(21.2); //input @p_sum_salary, output @p_sum_salary
vParam.Add(null); //output @p_last_dt
vParam.Add(2); //EMPLOYEEID
//2nd set
vParam.Add(2); //ID
vParam.Add(0); //retval
//last three data will be updated with outputs
vParam.Add(2); //input @p_company_id, output retval
vParam.Add(11.42); //input @p_sum_salary, output @p_sum_salary
vParam.Add(null); //output @p_last_dt
vParam.Add(3); //EMPLOYEEID
TestBatch2(sql, ra, vParam);
sql.WaitAll();
int index = 0;
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();
Console.WriteLine("Press any key to close the application ......");
Console.Read();
}
}