Пример #1
0
    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);
    }
Пример #2
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);
    }
Пример #3
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);
    }
Пример #4
0
        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;
        }
Пример #5
0
        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;
        }
Пример #6
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);
        }
    }
Пример #7
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();
        }
    }
Пример #8
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();
        }
    }
Пример #9
0
    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();
        }
    }