Esempio n. 1
0
    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);
    }
Esempio n. 2
0
    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));
    }
Esempio n. 3
0
    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();
        }
    }
Esempio n. 4
0
    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);
    }
Esempio n. 5
0
    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, "|"));
    }
Esempio n. 6
0
    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();
        }
    }