コード例 #1
0
    static void TestStoredProcedure_2(CSqlServer sql, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra, CDBVariantArray vPData)
    {
        CParameterInfoArray vPInfo = new CParameterInfoArray();

        CParameterInfo info = new CParameterInfo();

        info.ParameterName = "RetVal";
        info.DataType      = tagVariantDataType.sdVT_INT;
        info.Direction     = tagParameterDirection.pdReturnValue;
        vPInfo.Add(info);

        info = new CParameterInfo();
        info.ParameterName = "@testid";
        info.DataType      = tagVariantDataType.sdVT_INT;
        vPInfo.Add(info);

        info = new CParameterInfo();
        info.ParameterName = "@dot";
        info.DataType      = tagVariantDataType.sdVT_DATETIMEOFFSET;
        vPInfo.Add(info);

        info = new CParameterInfo();
        info.ParameterName = "@myxml";
        info.DataType      = tagVariantDataType.sdVT_XML;
        info.ColumnSize    = uint.MaxValue;
        info.Direction     = tagParameterDirection.pdInputOutput;
        vPInfo.Add(info);

        info = new CParameterInfo();
        info.ParameterName = "@tuuid";
        info.DataType      = tagVariantDataType.sdVT_CLSID;
        info.Direction     = tagParameterDirection.pdInputOutput;
        vPInfo.Add(info);

        info = new CParameterInfo();
        info.ParameterName = "@myvar";
        info.DataType      = tagVariantDataType.sdVT_VARIANT;
        info.Direction     = tagParameterDirection.pdOutput;
        vPInfo.Add(info);

        bool ok = sql.Prepare("sp_TestRare1", dr, vPInfo.ToArray());

        //process multiple sets of parameters in one shot
        ok = sql.Execute(vPData, er, (h, v) =>
        {
            KeyValuePair <CDBColumnInfoArray, CDBVariantArray> p = ra[ra.Count - 1];
            p.Value.AddRange(v);
        }, (h) =>
        {
            CDBColumnInfoArray v = h.ColumnInfo;
            ra.Add(new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(v, new CDBVariantArray()));
            Console.WriteLine("dbPath={0}, tablePath={1}", v[0].DBPath, v[0].TablePath);
        });
    }
コード例 #2
0
ファイル: Program.cs プロジェクト: udaparts/socketpro
    static void TestBatch2(CSqlServer sql, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra, CDBVariantArray vParam)
    {
        CParameterInfoArray vPInfo = new CParameterInfoArray();
        CParameterInfo      info   = new CParameterInfo();

        info.ParameterName = "@ID";
        info.DataType      = tagVariantDataType.sdVT_INT;
        vPInfo.Add(info);

        info = new CParameterInfo();
        info.ParameterName = "RetVal";
        info.DataType      = tagVariantDataType.sdVT_INT;
        info.Direction     = tagParameterDirection.pdReturnValue;
        vPInfo.Add(info);

        info = new CParameterInfo();
        info.ParameterName = "@p_company_id";
        info.DataType      = tagVariantDataType.sdVT_INT;
        vPInfo.Add(info);
        //return direction can be ignorable

        info = new CParameterInfo();
        info.ParameterName = "@p_sum_salary";
        info.DataType      = tagVariantDataType.sdVT_R8;
        info.Direction     = tagParameterDirection.pdInputOutput;
        vPInfo.Add(info);

        info = new CParameterInfo();
        info.ParameterName = "@p_last_dt";
        info.DataType      = tagVariantDataType.sdVT_DATE;
        info.Direction     = tagParameterDirection.pdOutput;
        vPInfo.Add(info);

        info = new CParameterInfo();
        info.ParameterName = "@EMPLOYEEID";
        info.DataType      = tagVariantDataType.sdVT_INT;
        vPInfo.Add(info);

        //@sqltestdb.dbo.sp_TestProc@@@ -- one return (@) plus three parameters (@@@)

        //there is no manual transaction if isolation is tiUnspecified
        sql.ExecuteBatch(tagTransactionIsolation.tiUnspecified,
                         "select getdate();select * from company where id=@ID;@sqltestdb.dbo.sp_TestProc@@@;select * from employee where EMPLOYEEID=@EMPLOYEEID",
                         vParam, er, (h, v) => {
            KeyValuePair <CDBColumnInfoArray, CDBVariantArray> p = ra[ra.Count - 1];
            p.Value.AddRange(v);
        }, (h) => {
            CDBColumnInfoArray v = h.ColumnInfo;
            ra.Add(new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(v, new CDBVariantArray()));
            Console.WriteLine("dbPath={0}, tablePath={1}", v[0].DBPath, v[0].TablePath);
        }, (h) => {
        }, vPInfo.ToArray());
    }
コード例 #3
0
ファイル: Program.cs プロジェクト: udaparts/socketpro
    static void TestBatch(CSqlServer sql, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra)
    {
        CDBVariantArray vParam = new CDBVariantArray();

        vParam.Add(1); //ID
        vParam.Add(1); //EMPLOYEEID
        CParameterInfoArray vPInfo = new CParameterInfoArray();
        CParameterInfo      info   = new CParameterInfo();

        info.ParameterName = "@ID";
        info.DataType      = tagVariantDataType.sdVT_INT;
        vPInfo.Add(info);
        info = new CParameterInfo();
        info.ParameterName = "@EMPLOYEEID";
        info.DataType      = tagVariantDataType.sdVT_INT;
        vPInfo.Add(info);

        //there is no manual transaction if isolation is tiUnspecified
        sql.ExecuteBatch(tagTransactionIsolation.tiUnspecified, "select getdate();select * from company where id=@ID;select * from employee where EMPLOYEEID=@EMPLOYEEID", vParam, er, (h, v) => {
            KeyValuePair <CDBColumnInfoArray, CDBVariantArray> p = ra[ra.Count - 1];
            p.Value.AddRange(v);
        }, (h) => {
            CDBColumnInfoArray v = h.ColumnInfo;
            ra.Add(new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(v, new CDBVariantArray()));
            Console.WriteLine("dbPath={0}, tablePath={1}", v[0].DBPath, v[0].TablePath);
        }, (h) => {
        }, vPInfo.ToArray());

        vParam = new CDBVariantArray();
        vParam.Add(1); //ID
        vParam.Add(2); //EMPLOYEEID
        vParam.Add(2); //ID
        vParam.Add(3); //EMPLOYEEID
        //Same as sqlite.BeginTrans();
        //Select getdate();select * from COMPANY where ID=1;select * from COMPANY where ID=2;getdate();
        //select * from EMPLOYEE where EMPLOYEEID=2;select * from EMPLOYEE where EMPLOYEEID=3
        //ok = sqlite.EndTrans();
        sql.ExecuteBatch(tagTransactionIsolation.tiUnspecified, "select getdate();select * from company where id=@ID;select getdate();select * from employee where EMPLOYEEID=@EMPLOYEEID", vParam, er, (h, v) => {
            KeyValuePair <CDBColumnInfoArray, CDBVariantArray> p = ra[ra.Count - 1];
            p.Value.AddRange(v);
        }, (h) => {
            CDBColumnInfoArray v = h.ColumnInfo;
            ra.Add(new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(v, new CDBVariantArray()));
            Console.WriteLine("dbPath={0}, tablePath={1}", v[0].DBPath, v[0].TablePath);
        }, (h) => {
        }, vPInfo.ToArray());
    }
コード例 #4
0
ファイル: Program.cs プロジェクト: udaparts/socketpro
    static void TestPreparedStatements(CSqlServer sql)
    {
        string sql_insert_parameter = "INSERT INTO company(ID,NAME,ADDRESS,Income)VALUES(@ID,@NAME,@ADDRESS,@Income)";
        CParameterInfoArray vInfo   = new CParameterInfoArray();
        CParameterInfo      info    = new CParameterInfo();

        info.ParameterName = "@ID";
        info.DataType      = tagVariantDataType.sdVT_INT;
        vInfo.Add(info);
        info = new CParameterInfo();
        info.ParameterName = "@NAME";
        info.DataType      = tagVariantDataType.sdVT_BSTR;
        info.ColumnSize    = 64;
        vInfo.Add(info);
        info = new CParameterInfo();
        info.ParameterName = "@ADDRESS";
        info.DataType      = tagVariantDataType.sdVT_BSTR;
        info.ColumnSize    = 256;
        vInfo.Add(info);
        info = new CParameterInfo();
        info.ParameterName = "@Income";
        info.DataType      = tagVariantDataType.sdVT_R8; //double
        vInfo.Add(info);
        bool ok = sql.Prepare(sql_insert_parameter, dr, vInfo.ToArray());

        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 = sql.Execute(vData, er);
    }
コード例 #5
0
ファイル: Program.cs プロジェクト: udaparts/socketpro
    static void TestPreparedStatements_2(CSqlServer sql)
    {
        CParameterInfoArray vInfo = new CParameterInfoArray();

        CParameterInfo info = new CParameterInfo();

        info.DataType      = tagVariantDataType.sdVT_CLSID;
        info.ParameterName = "@myguid";
        vInfo.Add(info);

        info = new CParameterInfo();
        info.ParameterName = "@myxml";
        info.DataType      = tagVariantDataType.sdVT_BSTR;
        info.ColumnSize    = uint.MaxValue;
        vInfo.Add(info);

        info = new CParameterInfo();
        info.ParameterName = "@myvariant";
        info.DataType      = tagVariantDataType.sdVT_VARIANT;
        vInfo.Add(info);

        info = new CParameterInfo();
        info.ParameterName = "@mydateimeoffset";
        info.DataType      = tagVariantDataType.sdVT_DATE;
        vInfo.Add(info);

        //if a prepared statement contains UUID or sql_variant, you must specify an array of parameter definitions
        string sql_insert_parameter = "INSERT INTO test_rare1(myguid,myxml,myvariant,mydateimeoffset)VALUES(@myguid,@myxml,@myvariant,@mydateimeoffset)";
        bool   ok = sql.Prepare(sql_insert_parameter, dr, vInfo.ToArray());

        CDBVariantArray vData = new CDBVariantArray();

        vData.Add(Guid.NewGuid());
        vData.Add("<myxmlroot />");
        vData.Add(23.456);
        vData.Add(DateTime.Now);

        vData.Add(Guid.NewGuid());
        vData.Add("<myxmlroot_2 />");
        vData.Add("马拉阿歌俱乐部");
        vData.Add(DateTime.Now.AddSeconds(123.45));

        ok = sql.Execute(vData, er);
    }
コード例 #6
0
    static void TestStoredProcedure(CSqlServer sql, List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra, CDBVariantArray vPData)
    {
        CParameterInfoArray vPInfo = new CParameterInfoArray();

        CParameterInfo info = new CParameterInfo();

        info.ParameterName = "RetVal";
        info.DataType      = tagVariantDataType.sdVT_INT;
        info.Direction     = tagParameterDirection.pdReturnValue;
        vPInfo.Add(info);

        info = new CParameterInfo();
        info.ParameterName = "@p_company_id";
        info.DataType      = tagVariantDataType.sdVT_INT;
        vPInfo.Add(info);
        //return direction can be ignorable

        info = new CParameterInfo();
        info.ParameterName = "@p_sum_salary";
        info.DataType      = tagVariantDataType.sdVT_R8;
        info.Direction     = tagParameterDirection.pdInputOutput;
        vPInfo.Add(info);

        info = new CParameterInfo();
        info.ParameterName = "@p_last_dt";
        info.DataType      = tagVariantDataType.sdVT_DATE;
        info.Direction     = tagParameterDirection.pdOutput;
        vPInfo.Add(info);
        bool ok = sql.Prepare("sqltestdb.dbo.sp_TestProc", dr, vPInfo.ToArray());

        //process multiple sets of parameters in one shot
        ok = sql.Execute(vPData, er, (h, v) =>
        {
            KeyValuePair <CDBColumnInfoArray, CDBVariantArray> p = ra[ra.Count - 1];
            p.Value.AddRange(v);
        }, (h) =>
        {
            CDBColumnInfoArray v = h.ColumnInfo;
            ra.Add(new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(v, new CDBVariantArray()));
            Console.WriteLine("dbPath={0}, tablePath={1}", v[0].DBPath, v[0].TablePath);
        });
    }
コード例 #7
0
ファイル: Program.cs プロジェクト: udaparts/socketpro
    static void InsertBLOBByPreparedStatement(CSqlServer sql)
    {
        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(EMPLOYEEID, CompanyId,name,JoinDate,myimage,DESCRIPTION,Salary)values(@EMPLOYEEID,@CompanyId,@name,@JoinDate,@myimage,@DESCRIPTION,@Salary)";

        CParameterInfoArray vInfo = new CParameterInfoArray();
        CParameterInfo      info  = new CParameterInfo();

        info.ParameterName = "@EMPLOYEEID";
        info.DataType      = tagVariantDataType.sdVT_INT;
        vInfo.Add(info);
        info = new CParameterInfo();
        info.ParameterName = "@CompanyId";
        info.DataType      = tagVariantDataType.sdVT_INT;
        vInfo.Add(info);
        info = new CParameterInfo();
        info.ParameterName = "@name";
        info.ColumnSize    = 64;
        info.DataType      = tagVariantDataType.sdVT_BSTR;
        vInfo.Add(info);
        info = new CParameterInfo();
        info.ParameterName = "@JoinDate";
        info.DataType      = tagVariantDataType.sdVT_DATE;
        vInfo.Add(info);
        info = new CParameterInfo();
        info.ParameterName = "@myimage";
        info.ColumnSize    = uint.MaxValue;
        info.DataType      = tagVariantDataType.sdVT_UI1 | tagVariantDataType.sdVT_ARRAY;
        vInfo.Add(info);
        info = new CParameterInfo();
        info.ParameterName = "@DESCRIPTION";
        info.ColumnSize    = uint.MaxValue;
        info.DataType      = tagVariantDataType.sdVT_BSTR;
        vInfo.Add(info);
        info = new CParameterInfo();
        info.ParameterName = "@Salary";
        info.ColumnSize    = uint.MaxValue;
        info.DataType      = tagVariantDataType.sdVT_DECIMAL;
        info.Precision     = 15;
        info.Scale         = 2;
        vInfo.Add(info);

        bool ok = sql.Prepare(sqlInsert, dr, vInfo.ToArray());

        CDBVariantArray vData = new CDBVariantArray();

        using (CScopeUQueue sbBlob = new CScopeUQueue()) {
            //first set of data
            vData.Add(1);
            vData.Add(1); //google company id
            vData.Add("Ted Cruz");
            vData.Add(DateTime.Now);
            sbBlob.Save(wstr);
            byte[] bytes = sbBlob.UQueue.GetBuffer();
            vData.Add(bytes);
            vData.Add(wstr);
            vData.Add(254000.2460d);

            //second set of data
            vData.Add(2);
            vData.Add(1); //google company id
            vData.Add("Donald Trump");
            vData.Add(DateTime.Now);
            sbBlob.UQueue.SetSize(0);
            sbBlob.Save(str);
            bytes = sbBlob.UQueue.GetBuffer();
            vData.Add(bytes);
            vData.Add(str);
            vData.Add(20254000.197d);

            //third set of data
            vData.Add(3);
            vData.Add(2); //Microsoft company id
            vData.Add("Hillary Clinton");
            vData.Add(DateTime.Now);
            sbBlob.Save(wstr);
            bytes = sbBlob.UQueue.GetBuffer();
            vData.Add(bytes);
            vData.Add(wstr);
            vData.Add(6254000.5d);

            //execute multiple sets of parameter data in one short
            ok = sql.Execute(vData, er);
        }
    }
コード例 #8
0
    static void Main(string[] args)
    {
        Console.WriteLine("SocketPro performance test against a remote MS SQL backend DB");
        Console.WriteLine("Remote host: ");
        string host = Console.ReadLine();

        using (CSocketPool <CSqlServer> spSql = new CSocketPool <CSqlServer>())
        {
            CConnectionContext cc = new CConnectionContext(host, 20903, "sa", "Smash123");
            if (!spSql.StartSocketPool(cc, 1, 1))
            {
                Console.WriteLine("Failed in connecting to remote helloworld server. Press any key to close the application ......");
                Console.Read();
                return;
            }

            Console.WriteLine("Database name: ");
            string dbName = Console.ReadLine();
            Console.WriteLine("Table name: ");
            string tableName = Console.ReadLine();
            Console.WriteLine("sql filter: ");
            string filter = Console.ReadLine();
            Console.WriteLine("Asynchronous execution (0) or synchronous execution (1) ?");
            bool sync = (Console.ReadKey().KeyChar != '0');
            Console.WriteLine("");
            Console.WriteLine("Computing ......");
            CSqlServer mysql           = spSql.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)
            {
                //set meta to false so that server side doesn't return meta, which will improve query performance
                ok = mysql.Execute(sql, er, r, rh, false);
                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 sqltestdb;delete from company where id > 3");
            string sql_insert_parameter = "INSERT INTO company(ID,NAME,ADDRESS,Income)VALUES(@ID,@NAME,@ADDRESS,@Income)";
            CParameterInfoArray vInfo   = new CParameterInfoArray();
            CParameterInfo      info    = new CParameterInfo();
            info.ParameterName = "@ID";
            info.DataType      = tagVariantDataType.sdVT_INT;
            vInfo.Add(info);
            info = new CParameterInfo();
            info.ParameterName = "@NAME";
            info.DataType      = tagVariantDataType.sdVT_BSTR;
            info.ColumnSize    = 64;
            vInfo.Add(info);
            info = new CParameterInfo();
            info.ParameterName = "@ADDRESS";
            info.DataType      = tagVariantDataType.sdVT_BSTR;
            info.ColumnSize    = 256;
            vInfo.Add(info);
            info = new CParameterInfo();
            info.ParameterName = "@Income";
            info.DataType      = tagVariantDataType.sdVT_R8; //double
            vInfo.Add(info);
            ok = mysql.Prepare(sql_insert_parameter, dr, vInfo.ToArray());
            ok = mysql.WaitAll();
            int index = 0;
            count = 50000;
            Console.WriteLine();
            Console.WriteLine("Going to insert {0} records into the table sqltestdb.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 sqltestdb.company", index);
                    ok    = mysql.BeginTrans();
                    index = 0;
                }
            }
            if (vData.Count > 0)
            {
                ok = mysql.Execute(vData, er);
                Console.WriteLine("Commit {0} records into the table sqltestdb.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();
        }
    }
コード例 #9
0
    private uint Prepare(string sql, CParameterInfoArray vColInfo, out int res, out string errMsg)
    {
        res       = 0;
        errMsg    = "";
        m_outputs = 0;
        uint parameters = 0;

        try
        {
            m_sqlPrepare = new SqlCommand(sql, m_conn, m_trans);
            if (vColInfo != null)
            {
                SqlParameter param = null;
                foreach (CParameterInfo info in vColInfo)
                {
                    switch (info.DataType)
                    {
                    case tagVariantDataType.sdVT_BYTES:
                    case tagVariantDataType.sdVT_ARRAY | tagVariantDataType.sdVT_UI1:
                        if (info.ColumnSize == uint.MaxValue)
                        {
                            if (info.Direction == tagParameterDirection.pdInput)
                            {
                                param = new SqlParameter(info.ParameterName, SqlDbType.Image, int.MaxValue);
                            }
                            else
                            {
                                param = new SqlParameter(info.ParameterName, SqlDbType.Image, (int)DB_CONSTS.DEFAULT_BIG_FIELD_CHUNK_SIZE);
                            }
                        }
                        else
                        {
                            param = new SqlParameter(info.ParameterName, SqlDbType.VarBinary, (int)info.ColumnSize);
                        }
                        break;

                    case tagVariantDataType.sdVT_STR:
                    case tagVariantDataType.sdVT_ARRAY | tagVariantDataType.sdVT_I1:
                        if (info.ColumnSize == uint.MaxValue)
                        {
                            if (info.Direction == tagParameterDirection.pdInput)
                            {
                                param = new SqlParameter(info.ParameterName, SqlDbType.Text, int.MaxValue);
                            }
                            else
                            {
                                param = new SqlParameter(info.ParameterName, SqlDbType.Text, (int)DB_CONSTS.DEFAULT_BIG_FIELD_CHUNK_SIZE);
                            }
                        }
                        else
                        {
                            param = new SqlParameter(info.ParameterName, SqlDbType.VarChar, (int)info.ColumnSize);
                        }
                        break;

                    case tagVariantDataType.sdVT_WSTR:
                    case tagVariantDataType.sdVT_BSTR:
                        if (info.ColumnSize == uint.MaxValue)
                        {
                            if (info.Direction == tagParameterDirection.pdInput)
                            {
                                param = new SqlParameter(info.ParameterName, SqlDbType.NText, int.MaxValue);
                            }
                            else
                            {
                                param = new SqlParameter(info.ParameterName, SqlDbType.NText, (int)DB_CONSTS.DEFAULT_BIG_FIELD_CHUNK_SIZE);
                            }
                        }
                        else
                        {
                            param = new SqlParameter(info.ParameterName, SqlDbType.NVarChar, (int)info.ColumnSize);
                        }
                        break;

                    case tagVariantDataType.sdVT_BOOL:
                        param = new SqlParameter(info.ParameterName, SqlDbType.Bit);
                        break;

                    case tagVariantDataType.sdVT_CLSID:
                        param = new SqlParameter(info.ParameterName, SqlDbType.UniqueIdentifier);
                        break;

                    case tagVariantDataType.sdVT_DECIMAL:
                        param           = new SqlParameter(info.ParameterName, SqlDbType.Decimal);
                        param.Precision = info.Precision;
                        param.Scale     = info.Scale;
                        break;

                    case tagVariantDataType.sdVT_UI1:
                        param = new SqlParameter(info.ParameterName, SqlDbType.TinyInt);
                        break;

                    case tagVariantDataType.sdVT_I2:
                        param = new SqlParameter(info.ParameterName, SqlDbType.SmallInt);
                        break;

                    case tagVariantDataType.sdVT_I4:
                    case tagVariantDataType.sdVT_INT:
                        param = new SqlParameter(info.ParameterName, SqlDbType.Int);
                        break;

                    case tagVariantDataType.sdVT_I8:
                        param = new SqlParameter(info.ParameterName, SqlDbType.BigInt);
                        break;

                    case tagVariantDataType.sdVT_R4:
                        param = new SqlParameter(info.ParameterName, SqlDbType.Real);
                        break;

                    case tagVariantDataType.sdVT_R8:
                        param = new SqlParameter(info.ParameterName, SqlDbType.Float);
                        break;

                    case tagVariantDataType.sdVT_DATE:
                        if (info.Precision == 0)
                        {
                            param = new SqlParameter(info.ParameterName, SqlDbType.DateTime);
                        }
                        else
                        {
                            param           = new SqlParameter(info.ParameterName, SqlDbType.DateTime2);
                            param.Precision = info.Precision;
                        }
                        break;

                    case tagVariantDataType.sdVT_VARIANT:
                        param = new SqlParameter(info.ParameterName, SqlDbType.Variant);
                        break;

                    case tagVariantDataType.sdVT_XML:
                        param = new SqlParameter(info.ParameterName, SqlDbType.Xml);
                        break;

                    case tagVariantDataType.sdVT_TIMESPAN:
                        param = new SqlParameter(info.ParameterName, SqlDbType.Time);
                        break;

                    case tagVariantDataType.sdVT_DATETIMEOFFSET:
                        param           = new SqlParameter(info.ParameterName, SqlDbType.DateTimeOffset);
                        param.Precision = info.Precision;
                        break;

                    default:
                        res          = -2;
                        errMsg       = "Unsupported data type: " + info.DataType.ToString();
                        m_sqlPrepare = null;
                        parameters   = 0;
                        return(parameters);
                    }
                    switch (info.Direction)
                    {
                    case tagParameterDirection.pdInput:
                        param.Direction = ParameterDirection.Input;
                        break;

                    case tagParameterDirection.pdInputOutput:
                        param.Direction = ParameterDirection.InputOutput;
                        ++m_outputs;
                        break;

                    case tagParameterDirection.pdOutput:
                        param.Direction = ParameterDirection.Output;
                        ++m_outputs;
                        break;

                    case tagParameterDirection.pdReturnValue:
                        param.Direction = ParameterDirection.ReturnValue;
                        ++m_outputs;
                        break;

                    default:
                        res          = -2;
                        errMsg       = "Unsupported direction: " + info.Direction.ToString();
                        m_sqlPrepare = null;
                        parameters   = 0;
                        return(parameters);
                    }
                    m_sqlPrepare.Parameters.Add(param);
                }
                parameters   = m_outputs;
                parameters <<= 16;
                parameters  += (ushort)vColInfo.Count;
            }
            if (m_outputs > 0)
            {
                m_sqlPrepare.CommandType = CommandType.StoredProcedure;
            }
            m_sqlPrepare.Prepare();
        }
        catch (SqlException err)
        {
            res          = err.ErrorCode;
            errMsg       = err.Message;
            m_sqlPrepare = null;
            parameters   = 0;
            m_outputs    = 0;
        }
        catch (Exception err)
        {
            res          = -1;
            errMsg       = err.Message;
            m_sqlPrepare = null;
            parameters   = 0;
            m_outputs    = 0;
        }
        return(parameters);
    }