예제 #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);
    }
예제 #2
0
    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);
    }
예제 #3
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));
    }
예제 #4
0
    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);
        }
    }
예제 #5
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));
        }
    }
예제 #6
0
    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();
        }
    }