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> 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));
}
static void Main(string[] args)
{
Console.WriteLine("Remote host: ");
string host = Console.ReadLine();
#if FOR_MIDDLE_SERVER
CConnectionContext cc = new CConnectionContext(host, 20901, "root", "Smash123");
#else
CConnectionContext cc = new CConnectionContext(host, 20902, "root", "Smash123");
#endif
using (CSocketPool <CMysql> spMysql = new CSocketPool <CMysql>(true, 600000))
{
if (!spMysql.StartSocketPool(cc, 1, 1))
{
Console.WriteLine("Failed in connecting to remote async mysql server");
Console.WriteLine("Press any key to close the application ......");
Console.Read();
return;
}
CMysql mysql = spMysql.Seek();
#if FOR_MIDDLE_SERVER
bool ok = mysql.Open("user=root;pwd=Smash123;db=mysqldb", dr);
#else
bool ok = mysql.Open("", dr);
#endif
List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra = new List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> >();
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);
};
TestCreateTables(mysql);
ok = mysql.Execute("delete from employee;delete from company", er);
TestPreparedStatements(mysql);
InsertBLOBByPreparedStatement(mysql);
ok = mysql.Execute("SELECT * from company;select * from employee;select curtime()", er, r, rh);
CDBVariantArray vPData = TestStoredProcedure(mysql, ra);
ok = mysql.WaitAll();
Console.WriteLine();
Console.WriteLine("There are {0} output data returned", mysql.Outputs * 2);
CDBVariantArray vData = TestBatch(mysql, ra);
ok = mysql.WaitAll();
Console.WriteLine();
Console.WriteLine("There are {0} output data returned", mysql.Outputs * 3);
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 CDBVariantArray TestBatch(CMysql mysql, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra)
{
//sql with delimiter '|'
string sql = @" delete from employee;delete from company|
INSERT INTO company(ID,NAME,ADDRESS,Income)VALUES(?,?,?,?)|
insert into employee(CompanyId,name,JoinDate,image,DESCRIPTION,Salary)values(?,?,?,?,?,?)|
SELECT * from company;select * from employee;select curtime()|
call sp_TestProc(?,?,?)";
string wstr = ""; //make test data
while (wstr.Length < 128 * 1024)
{
wstr += "广告做得不那么夸张的就不说了，看看这三家，都是正儿八经的公立三甲，附属医院，不是武警，也不是部队，更不是莆田，都在卫生部门直接监管下，照样明目张胆地骗人。";
}
string str = ""; //make test data
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.";
}
CDBVariantArray vData = new CDBVariantArray();
using (CScopeUQueue sbBlob = new CScopeUQueue()) {
//first set
vData.Add(1);
vData.Add("Google Inc.");
vData.Add("1600 Amphitheatre Parkway, Mountain View, CA 94043, USA");
vData.Add(66000000000.0);
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);
vData.Add(1.4);
vData.Add(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);
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);
vData.Add(2.5);
vData.Add(0);
//third set
vData.Add(3);
vData.Add("Apple Inc.");
vData.Add("1 Infinite Loop, Cupertino, CA 95014, USA");
vData.Add(234000000000.0);
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(0);
vData.Add(4.5);
vData.Add(0);
}
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);
};
//first, execute delete from employee;delete from company
//second, three sets of INSERT INTO company(ID,NAME,ADDRESS,Income)VALUES(?,?,?,?)
//third, three sets of insert into employee(CompanyId,name,JoinDate,image,DESCRIPTION,Salary)values(?,?,?,?,?,?)
//fourth, SELECT * from company;select * from employee;select curtime()
//last, three sets of call sp_TestProc(?,?,?)
bool ok = mysql.ExecuteBatch(tagTransactionIsolation.tiUnspecified, sql, vData, er, r, rh, (h) => {
//called before rh, r and er
//ra.Clear();
}, null, tagRollbackPlan.rpDefault, (h, canceled) => {
//called when canceling or socket closed if client queue is NOT used
}, "|");
return(vData);
}
static Task <CAsyncDBHandler.SQLExeInfo> TestBatch(CMysql mysql, List <KeyValue> ra, out CDBVariantArray vData)
{
//sql with delimiter '|'
string sql = @" delete from employee;delete from company|
INSERT INTO company(ID,NAME,ADDRESS,Income)VALUES(?,?,?,?)|
insert into employee(CompanyId,name,JoinDate,image,DESCRIPTION,Salary)values(?,?,?,?,?,?)|
SELECT * from company;select * from employee;select curtime()|
call sp_TestProc(?,?,?)";
vData = new CDBVariantArray();
using (CScopeUQueue sbBlob = new CScopeUQueue())
{
//first set
vData.Add(1);
vData.Add("Google Inc.");
vData.Add("1600 Amphitheatre Parkway, Mountain View, CA 94043, USA");
vData.Add(66000000000.15);
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.26);
vData.Add(1); //input
vData.Add(1.4); //input-output
vData.Add(0); //output
//second set
vData.Add(2);
vData.Add("Microsoft Inc.");
vData.Add("700 Bellevue Way NE- 22nd Floor, Bellevue, WA 98804, USA");
vData.Add(93600000000.37);
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.85);
vData.Add(2); //input
vData.Add(2.5); //input-output
vData.Add(0); //output
//third set
vData.Add(3);
vData.Add("Apple Inc.");
vData.Add("1 Infinite Loop, Cupertino, CA 95014, USA");
vData.Add(234000000000.09);
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.55);
vData.Add(0); //input
vData.Add(4.5); //input-output
vData.Add(0); //output
}
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
KeyValue item = new KeyValue(handler.ColumnInfo, new CDBVariantArray());
ra.Add(item);
};
//first, start a transaction with ReadCommited isolation
//second, execute delete from employee;delete from company
//third, prepare and execute three sets of
// INSERT INTO company(ID,NAME,ADDRESS,Income)VALUES(?,?,?,?)
//fourth, prepare and execute three sets of
//insert into employee(CompanyId,name,JoinDate,image,DESCRIPTION,Salary)values(?,?,?,?,?,?)
//fifth, SELECT * from company;select * from employee;select curtime()
//sixth, prepare and three sets of call sp_TestProc(?,?,?)
//last, commit transaction if there is no error, and rollback if there is one or more errors
return(mysql.executeBatch(tagTransactionIsolation.tiReadCommited, sql, vData, r, rh, "|"));
}
static void Main(string[] args)
{
Console.WriteLine("Remote host: ");
string host = Console.ReadLine();
#if FOR_MIDDLE_SERVER
CConnectionContext cc = new CConnectionContext(host, 20901, "root", "Smash123");
#else
CConnectionContext cc = new CConnectionContext(host, 20902, "root", "Smash123");
#endif
using (CSocketPool <CMysql> spMysql = new CSocketPool <CMysql>())
{
//spMysql.QueueName = "qmysql";
if (!spMysql.StartSocketPool(cc, 1))
{
Console.WriteLine("Failed in connecting to remote async mysql server");
Console.WriteLine("Press any key to close the application ......");
Console.Read();
return;
}
CMysql mysql = spMysql.Seek();
CDBVariantArray vPData = null, vData = null;
List <KeyValue> ra = new List <KeyValue>();
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);
};
try
{
//stream all requests with in-line batching for the best network efficiency
var tOpen = mysql.open("");
var vT = TestCreateTables(mysql);
var tDs = mysql.execute("delete from employee;delete from company");
var tP0 = TestPreparedStatements(mysql);
var tP1 = TestBLOBByPreparedStatement(mysql);
var tSs = mysql.execute("SELECT * from company;select * from employee;select curtime()", r, rh);
var tStore = TestStoredProcedure(mysql, ra, out vPData);
var tB = TestBatch(mysql, ra, out vData);
Console.WriteLine();
Console.WriteLine("All SQL requests are streamed, and waiting results in order ......");
Console.WriteLine(tOpen.Result);
foreach (var t in vT)
{
Console.WriteLine(t.Result);
}
Console.WriteLine(tDs.Result);
Console.WriteLine(tP0.Result);
Console.WriteLine(tP1.Result);
Console.WriteLine(tSs.Result);
Console.WriteLine(tStore.Result);
Console.WriteLine("There are {0} output data returned", 2 * 2);
Console.WriteLine(tB.Result);
Console.WriteLine("There are {0} output data returned", 2 * 3);
}
catch (AggregateException ex)
{
foreach (Exception e in ex.InnerExceptions)
{
//An exception from server (CServerError), Socket closed after sending a request (CSocketError) or request canceled (CSocketError),
Console.WriteLine(e);
}
}
catch (CSocketError ex)
{
//Socket is already closed before sending a request
Console.WriteLine(ex);
}
catch (Exception ex)
{
//bad operations such as invalid arguments, bad operations and de-serialization errors, and so on
Console.WriteLine(ex);
}
int index = 0;
Console.WriteLine();
Console.WriteLine("+++++ Start rowsets +++");
foreach (KeyValue 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();
}
}