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(); } }