static void TestCreateTables(CMysql mysql)
{
string create_database = "Create database if not exists mysqldb character set utf8 collate utf8_general_ci;USE mysqldb";
bool ok = mysql.Execute(create_database, er);
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)";
ok = mysql.Execute(create_table, er);
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))";
ok = mysql.Execute(create_table, er);
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";
ok = mysql.Execute(create_proc, er);
}
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 void TestPreparedStatements(CMysql mysql)
{
string sql_insert_parameter = "INSERT INTO company(ID, NAME, ADDRESS, Income) VALUES (?, ?, ?, ?)";
bool ok = mysql.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 = mysql.Execute(vData, er);
}
protected void Application_Start(object sender, System.EventArgs e)
{
//suppose sp_config.json inside C:\Program Files\IIS Express
//CSpConfig config = SpManager.SetConfig(false,
//@"D:\cyetest\socketpro\samples\web_two\web_two\sp_config.json");
Master = SpManager.GetPool("masterdb") as CSqlMasterPool <CMysql, CDataSet>;
CMysql handler = Master.Seek();
if (handler != null) //create a test database
{
string sql = @"CREATE DATABASE IF NOT EXISTS mysample character set
utf8 collate utf8_general_ci;USE mysample;CREATE TABLE IF NOT EXISTS
COMPANY(ID BIGINT PRIMARY KEY NOT NULL,Name CHAR(64)NOT NULL);CREATE TABLE
IF NOT EXISTS EMPLOYEE(EMPLOYEEID BIGINT PRIMARY KEY AUTO_INCREMENT,
CompanyId BIGINT NOT NULL,Name NCHAR(64)NOT NULL,JoinDate DATETIME(6)
DEFAULT NULL,FOREIGN KEY(CompanyId)REFERENCES COMPANY(id));USE sakila;
INSERT INTO mysample.COMPANY(ID,Name)VALUES(1,'Google Inc.'),
(2,'Microsoft Inc.'),(3,'Amazon Inc.')";
handler.Execute(sql);
}
Cache = Master.Cache;
Slave = SpManager.GetPool("slavedb0") as
CSqlMasterPool <CMysql, CDataSet> .CSlavePool;
Master_No_Backup = SpManager.GetPool("db_no_backup") as
CSqlMasterPool <CMysql, CDataSet> .CSlavePool;
}
private 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, 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
bool ok = mysql.Open("sakila", null, DB_CONSTS.ENABLE_TABLE_UPDATE_MESSAGES);
m_ds = new DataSet("real-time cache");
DataTable dt = null;
string errMsg = "";
//query all cached tables into client side for intial cache data
ok = mysql.Execute("", (h, ret, err_msg, affected, fail_ok, id) =>
{
//this callback is fired from worker thread from socket pool thread
ok = (ret == 0);
errMsg = err_msg;
}, (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);
});
ok = mysql.WaitAll();
txtMessage.Text = errMsg;
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 = ok;
btnConnect.Enabled = !ok;
}
static void InsertBLOBByPreparedStatement(CMysql mysql)
{
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(CompanyId, name, JoinDate, image, DESCRIPTION, Salary) values (?, ?, ?, ?, ?, ?)";
bool ok = mysql.Prepare(sqlInsert, dr);
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(wstr);
vData.Add(sbBlob.UQueue.GetBuffer());
vData.Add(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(str);
vData.Add(sbBlob.UQueue.GetBuffer());
vData.Add(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(wstr);
vData.Add(sbBlob.UQueue.GetBuffer());
vData.Add(wstr);
vData.Add(6254000.0);
//send three sets of parameterized data in one shot for processing
ok = mysql.Execute(vData, er);
}
}
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();
}
}
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 void Main(string[] args)
{
const int sessions_per_host = 2; const int cycles = 10000; string[] vHost = { "localhost", "192.168.2.172" };
using (CSocketPool <CMysql> sp = new CSocketPool <CMysql>()) {
sp.QueueName = "ar_sharp"; //set a local message queue to backup requests for auto fault recovery
CConnectionContext[,] ppCc = new CConnectionContext[1, vHost.Length *sessions_per_host]; //one thread enough
for (int n = 0; n < vHost.Length; ++n)
{
for (int j = 0; j < sessions_per_host; ++j)
{
ppCc[0, n *sessions_per_host + j] = new CConnectionContext(vHost[n], 20902, "root", "Smash123");
}
}
bool ok = sp.StartSocketPool(ppCc);
if (!ok)
{
Console.WriteLine("There is no connection and press any key to close the application ......");
Console.Read(); return;
}
string sql = "SELECT max(amount), min(amount), avg(amount) FROM payment";
Console.WriteLine("Input a filter for payment_id"); string filter = Console.ReadLine();
if (filter.Length > 0)
{
sql += (" WHERE " + filter);
}
var v = sp.AsyncHandlers;
foreach (var h in v)
{
ok = h.Open("sakila", (hsqlite, res, errMsg) => {
if (res != 0)
{
Console.WriteLine("Error code: {0}, error message: {1}", res, errMsg);
}
});
}
int returned = 0; double dmax = 0.0, dmin = 0.0, davg = 0.0;
SocketProAdapter.UDB.CDBVariantArray row = new SocketProAdapter.UDB.CDBVariantArray();
CAsyncDBHandler.DExecuteResult er = (h, res, errMsg, affected, fail_ok, lastId) => {
if (res != 0)
{
Console.WriteLine("Error code: {0}, error message: {1}", res, errMsg);
}
else
{
dmax += double.Parse(row[0].ToString());
dmin += double.Parse(row[1].ToString());
davg += double.Parse(row[2].ToString());
}
++returned;
};
CAsyncDBHandler.DRows r = (h, vData) => {
row.Clear(); row.AddRange(vData);
};
CMysql mysql = sp.SeekByQueue(); //get one handler for querying one record
ok = mysql.Execute(sql, er, r); ok = mysql.WaitAll();
Console.WriteLine("Result: max = {0}, min = {1}, avg = {2}", dmax, dmin, davg);
returned = 0; dmax = 0.0; dmin = 0.0; davg = 0.0;
Console.WriteLine("Going to get {0} queries for max, min and avg", cycles);
for (int n = 0; n < cycles; ++n)
{
mysql = sp.SeekByQueue(); ok = mysql.Execute(sql, er, r);
}
foreach (var h in v)
{
ok = h.WaitAll();
}
Console.WriteLine("Returned = {0}, max = {1}, min = {2}, avg = {3}", returned, dmax, dmin, davg);
Console.WriteLine("Press any key to close the application ......"); Console.Read();
}
}