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 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 Task <CAsyncDBHandler.SQLExeInfo>[] TestCreateTables(CMysql mysql)
{
Task <CAsyncDBHandler.SQLExeInfo>[] v = new Task <CAsyncDBHandler.SQLExeInfo> [4];
string create_database = "Create database if not exists mysqldb character set utf8 collate utf8_general_ci;USE mysqldb";
v[0] = mysql.execute(create_database);
string create_table = "CREATE TABLE IF NOT EXISTS company(ID bigint PRIMARY KEY NOT NULL,name CHAR(64)NOT NULL,ADDRESS varCHAR(256)not null,Income decimal(15,2)not null)";
v[1] = mysql.execute(create_table);
create_table = "CREATE TABLE IF NOT EXISTS employee(EMPLOYEEID bigint AUTO_INCREMENT PRIMARY KEY NOT NULL unique,CompanyId bigint not null,name CHAR(64)NOT NULL,JoinDate DATETIME default null,IMAGE MEDIUMBLOB,DESCRIPTION MEDIUMTEXT,Salary decimal(15,2),FOREIGN KEY(CompanyId)REFERENCES company(id))";
v[2] = mysql.execute(create_table);
string create_proc = "DROP PROCEDURE IF EXISTS sp_TestProc;CREATE PROCEDURE sp_TestProc(in p_company_id int,inout p_sum_salary decimal(20,2),out p_last_dt datetime)BEGIN select * from employee where companyid>=p_company_id;select sum(salary)+p_sum_salary into p_sum_salary from employee where companyid>=p_company_id;select now()into p_last_dt;END";
v[3] = mysql.execute(create_proc);
return(v);
}
private async void btnConnect_Click(object sender, EventArgs e)
{
CConnectionContext cc = new CConnectionContext(txtHost.Text, 20902, txtUser.Text, txtPassword.Text);
m_spMysql = new CSocketPool <CMysql>(false);
//set event for MySQL/Mariadb database shutdown
m_spMysql.SocketPoolEvent += new CSocketPool <CMysql> .DOnSocketPoolEvent(m_spMysql_SocketPoolEvent);
if (!m_spMysql.StartSocketPool(cc, 1))
{
txtMessage.Text = "No connection to " + txtHost.Text;
return;
}
CMysql mysql = m_spMysql.AsyncHandlers[0];
//set event for tracking all database table update events, delete, update and insert
m_spMysql.Sockets[0].Push.OnPublish += new DOnPublish(Push_OnPublish);
//create a DB session with default to sample database sakil
var task = mysql.open("sakila", DB_CONSTS.ENABLE_TABLE_UPDATE_MESSAGES);
m_ds = new DataSet("real-time cache");
DataTable dt = null;
//query all cached tables into client side for intial cache data
var res = await mysql.execute("", (h, data) =>
{
//this callback is fired from worker thread from socket pool thread
CMysql.AppendRowDataIntoDataTable(data, dt);
}, (h) =>
{
//this callback is fired from worker thread from socket pool thread
dt = CMysql.MakeDataTable(h.ColumnInfo);
string name = h.ColumnInfo[0].DBPath + "." + h.ColumnInfo[0].TablePath;
dt.TableName = name;
m_ds.Tables.Add(dt);
});
txtMessage.Text = res.em;
lstTables.Items.Clear();
foreach (DataTable table in m_ds.Tables)
{
lstTables.Items.Add(table.TableName);
}
if (m_ds.Tables.Count > 0)
{
lstTables.SelectedIndex = 0;
}
btnDisconnect.Enabled = (res.ec == 0);
btnConnect.Enabled = (res.ec != 0);
}
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 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();
}
}
