예제 #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
    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
    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
    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);
        });
    }
예제 #5
0
    static void Main(string[] args)
    {
        Console.WriteLine("Remote host: ");
        string             host = Console.ReadLine();
        CConnectionContext cc   = new CConnectionContext(host, 20901, "sa", "Smash123");

#if DEBUG
        using (CSocketPool <CSqlServer> spSql = new CSocketPool <CSqlServer>(true, 3600 * 1000))
#else
        using (CSocketPool <CSqlServer> spSql = new CSocketPool <CSqlServer>())
#endif
        {
            if (!spSql.StartSocketPool(cc, 1))
            {
                Console.WriteLine("Failed in connecting to remote async sql server. Press any key to close the application ......");
                Console.Read();
                return;
            }
            CSqlServer sql = spSql.Seek();

            //track all DML (DELETE, INSERT and UPDATE) events
            sql.Socket.Push.OnPublish += (sender, messageSender, group, msg) => {
                if (group[0] == DB_CONSTS.STREAMING_SQL_CHAT_GROUP_ID)
                {
                    object[]       vMsg     = (object[])msg;
                    tagUpdateEvent ue       = (tagUpdateEvent)(int)(vMsg[0]);
                    string         server   = (string)vMsg[1];
                    string         user     = (string)vMsg[2];
                    string         database = (string)vMsg[3];
                    Console.WriteLine("DML event={0}, server={1}, database={2}, user={3}, table={4}", ue, server, database, user, vMsg[4].ToString());
                }
            };
            List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra = new List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> >();

            //enable monitoring DML events through triggers by flag DB_CONSTS.ENABLE_TABLE_UPDATE_MESSAGES
            bool ok = sql.Open("", dr, DB_CONSTS.ENABLE_TABLE_UPDATE_MESSAGES);
            sql.WaitAll();

            CAsyncDBHandler.DRowsetHeader rh = (h) => {
                CDBColumnInfoArray v = h.ColumnInfo;
                if (v.Count > 0)
                {
                    Console.WriteLine("dbPath={0}, tablePath={1}", v[0].DBPath, v[0].TablePath);
                    ra.Add(new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(v, new CDBVariantArray()));
                }
            };
            CAsyncDBHandler.DRows rows = (h, vData) => {
                int endIndex = ra.Count - 1;
                ra[endIndex].Value.AddRange(vData);
            };

            //bring all table data which have USqlStream trigger (usqlserver.USqlStream.PublishDMLEvent) with an empty sql input string when opening with the flag DB_CONSTS.ENABLE_TABLE_UPDATE_MESSAGES
            ok = sql.Execute("", er, rows, rh);

            TestCreateTablesAndStoredProcedures(sql);
            ok = sql.Execute("select * from SpatialTable", er, rows, rh);
            ok = sql.Execute("delete from employee;delete from company;delete from test_rare1;delete from SpatialTable;INSERT INTO SpatialTable(mygeometry, mygeography)VALUES(geometry::STGeomFromText('LINESTRING(100 100,20 180,180 180)',0),geography::Point(47.6475,-122.1393,4326))", er);
            ok = sql.Execute("INSERT INTO test_rare1(mybool,mymoney,myxml,myvariant,mydateimeoffset)values(1,23.45,'<sometest />', N'美国总统川普下个星期四','2017-05-02 00:00:00.0000000 -04:00');INSERT INTO test_rare1(mybool,mymoney,myvariant)values(0,1223.45,'This is a test for ASCII string inside sql_variant');INSERT INTO test_rare1(myvariant)values(283.45)", er);
            TestPreparedStatements(sql);
            TestPreparedStatements_2(sql);
            InsertBLOBByPreparedStatement(sql);
            CDBVariantArray vPData = new CDBVariantArray();
            //first set
            vPData.Add(0); //retval
            vPData.Add(1);
            vPData.Add(21.2);
            vPData.Add(null);
            //2nd set
            vPData.Add(0); //retval
            vPData.Add(2);
            vPData.Add(11.42);
            vPData.Add(null);
            TestStoredProcedure(sql, ra, vPData);
            sql.WaitAll();

            vPData.Clear();
            //first set
            vPData.Add(-1);                   //return int
            vPData.Add(1);                    //@testid
            vPData.Add(DateTime.Now);
            vPData.Add("<test_sqlserver />"); //@myxml
            Guid guid = Guid.NewGuid();
            vPData.Add(guid);                 //@tuuid
            vPData.Add(true);                 //@myvar

            //2nd set
            vPData.Add(-2);                         //return int
            vPData.Add(4);                          //@testid
            vPData.Add(DateTime.Now);
            vPData.Add("<test_sqlserver_again />"); //@myxml
            Guid guid2 = Guid.NewGuid();
            vPData.Add(guid2);                      //@tuuid
            vPData.Add(false);                      //@myvar
            TestStoredProcedure_2(sql, ra, vPData);
            sql.WaitAll();
            TestBatch(sql, ra);

            CDBVariantArray vParam = new CDBVariantArray();
            //first set
            vParam.Add(1);    //ID

            vParam.Add(0);    //retval
            //last three data will be updated with outputs
            vParam.Add(1);    //input @p_company_id, output retval
            vParam.Add(21.2); //input @p_sum_salary, output @p_sum_salary
            vParam.Add(null); //output @p_last_dt

            vParam.Add(2);    //EMPLOYEEID

            //2nd set
            vParam.Add(2);     //ID

            vParam.Add(0);     //retval
            //last three data will be updated with outputs
            vParam.Add(2);     //input @p_company_id, output retval
            vParam.Add(11.42); //input @p_sum_salary, output @p_sum_salary
            vParam.Add(null);  //output @p_last_dt

            vParam.Add(3);     //EMPLOYEEID
            TestBatch2(sql, ra, vParam);
            sql.WaitAll();
            int index = 0;
            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();
            Console.WriteLine("Press any key to close the application ......");
            Console.Read();
        }
    }
예제 #6
0
    private ulong ExecuteParameters(bool rowset, bool meta, bool lastInsertId, ulong index, out long affected, out int res, out string errMsg, out object vtId)
    {
        ulong fail_ok = 0;

        m_indexCall = index;
        affected    = 0;
        res         = 0;
        errMsg      = "";
        vtId        = null;
        ulong fails      = m_fails;
        ulong oks        = m_oks;
        bool  ok         = true;
        bool  HeaderSent = false;

        do
        {
            if (m_sqlPrepare == null || m_sqlPrepare.Parameters.Count == 0 || m_vParam.Count == 0)
            {
                res    = -2;
                errMsg = "No parameter specified";
                ++m_fails;
                break;
            }
            int cols = m_sqlPrepare.Parameters.Count;
            if ((m_vParam.Count % cols) != 0)
            {
                res    = -2;
                errMsg = "Bad parameter data array size";
                ++m_fails;
                break;
            }
            if (m_trans != null)
            {
                m_sqlPrepare.Transaction = m_trans;
            }
            int rows = m_vParam.Count / cols;
            for (int r = 0; r < rows; ++r)
            {
                try
                {
                    int c = 0;
                    foreach (SqlParameter p in m_sqlPrepare.Parameters)
                    {
                        p.Value = m_vParam[r * cols + c];
                        ++c;
                    }
                    if (rowset)
                    {
                        SqlDataReader reader = m_sqlPrepare.ExecuteReader(meta ? CommandBehavior.KeyInfo : CommandBehavior.Default);
                        while (reader.FieldCount > 0)
                        {
                            ok         = PushToClient(reader, meta);
                            HeaderSent = true;
                            if (reader.RecordsAffected > 0)
                            {
                                affected += reader.RecordsAffected;
                            }
                            if (!ok || !reader.NextResult())
                            {
                                break;
                            }
                        }
                        reader.Close();
                    }
                    else
                    {
                        int ret = m_sqlPrepare.ExecuteNonQuery();
                        if (ret > 0)
                        {
                            affected += ret;
                        }
                    }
                    if (ok && m_outputs > 0)
                    {
                        CDBColumnInfoArray v = new CDBColumnInfoArray();
                        uint ret             = SendResult(DB_CONSTS.idRowsetHeader, v, index, (uint)m_outputs);
                        ok         = (ret != SOCKET_NOT_FOUND && ret != REQUEST_CANCELED);
                        HeaderSent = true;
                        if (ok)
                        {
                            using (CScopeUQueue sb = new CScopeUQueue())
                            {
                                CUQueue q = sb.UQueue;
                                foreach (SqlParameter p in m_sqlPrepare.Parameters)
                                {
                                    if (p.Direction != ParameterDirection.Input)
                                    {
                                        q.Save(p.Value);
                                    }
                                }
                                ok = (SendResult(DB_CONSTS.idOutputParameter, q.IntenalBuffer, q.GetSize()) == q.GetSize());
                            }
                        }
                    }
                    ++m_oks;
                }
                catch (SqlException err)
                {
                    if (res == 0)
                    {
                        res    = err.ErrorCode;
                        errMsg = err.Message;
                    }
                    ++m_fails;
                }
                catch (Exception err)
                {
                    if (res == 0)
                    {
                        res    = -1;
                        errMsg = err.Message;
                    }
                    ++m_fails;
                }
                if (!ok)
                {
                    break;
                }
            }
        } while (false);
        if (!HeaderSent && ok)
        {
            CDBColumnInfoArray v = new CDBColumnInfoArray();
            SendResult(DB_CONSTS.idRowsetHeader, v, index);
        }
        fail_ok  = ((m_fails - fails) << 32);
        fail_ok += (m_oks - oks);
        return(fail_ok);
    }
예제 #7
0
    private ulong Execute(string sql, bool rowset, bool meta, bool lastInsertId, ulong index, out long affected, out int res, out string errMsg, out object vtId)
    {
        ulong fail_ok = 0;

        m_indexCall = index;
        affected    = 0;
        res         = 0;
        errMsg      = "";
        vtId        = null;
        ulong         fails      = m_fails;
        ulong         oks        = m_oks;
        bool          HeaderSent = false;
        bool          ok         = true;
        SqlDataReader reader     = null;

        try
        {
            if ((sql == null || sql.Length == 0) && m_EnableMessages)
            {
                sql = GenerateSqlForCachedTables();
            }
            SqlCommand cmd = new SqlCommand(sql, m_conn, m_trans);
            if (rowset)
            {
                reader = cmd.ExecuteReader(meta ? CommandBehavior.KeyInfo : CommandBehavior.Default);
                while (reader.FieldCount > 0 && ok)
                {
                    ok         = PushToClient(reader, meta);
                    HeaderSent = true;
                    if (!ok || !reader.NextResult())
                    {
                        break;
                    }
                }
                if (reader.RecordsAffected > 0)
                {
                    affected += reader.RecordsAffected;
                }
                reader.Close();
            }
            else
            {
                int ret = cmd.ExecuteNonQuery();
                if (ret > 0)
                {
                    affected += ret;
                }
            }
            ++m_oks;
        }
        catch (SqlException err)
        {
            if (res == 0)
            {
                res    = err.ErrorCode;
                errMsg = err.Message;
            }
            ++m_fails;
        }
        catch (Exception err)
        {
            if (res == 0)
            {
                res    = -1;
                errMsg = err.Message;
            }
            ++m_fails;
        }
        finally
        {
            if (reader != null)
            {
                reader.Close();
            }
        }
        if (!HeaderSent && ok)
        {
            CDBColumnInfoArray v = new CDBColumnInfoArray();
            SendResult(DB_CONSTS.idRowsetHeader, v, index);
        }
        fail_ok  = ((m_fails - fails) << 32);
        fail_ok += (m_oks - oks);
        return(fail_ok);
    }
예제 #8
0
    private bool PushRows(SqlDataReader reader, CDBColumnInfoArray vCol)
    {
        using (CScopeUQueue sb = new CScopeUQueue())
        {
            CUQueue q = sb.UQueue;
            while (reader.Read())
            {
                if (q.GetSize() >= DB_CONSTS.DEFAULT_RECORD_BATCH_SIZE && !SendRows(q, false))
                {
                    return(false);
                }
                int col = 0;
                foreach (CDBColumnInfo info in vCol)
                {
                    if (reader.IsDBNull(col))
                    {
                        q.Save((ushort)tagVariantDataType.sdVT_NULL);
                        ++col;
                        continue;
                    }
                    switch (info.DataType)
                    {
                    case tagVariantDataType.sdVT_BSTR:
                        if (info.DeclaredType == "xml")
                        {
                            string xml = reader.GetSqlXml(col).Value;
                            if (xml.Length <= DB_CONSTS.DEFAULT_BIG_FIELD_CHUNK_SIZE)
                            {
                                q.Save((ushort)info.DataType).Save(xml);
                            }
                            else
                            {
                                if (q.GetSize() != 0 && !SendRows(q, true))
                                {
                                    return(false);
                                }
                                if (!PushText(xml))
                                {
                                    return(false);
                                }
                            }
                        }
                        else if (info.DeclaredType == "datetimeoffset")
                        {
                            DateTimeOffset dto = reader.GetDateTimeOffset(col);
                            q.Save((ushort)info.DataType).Save(dto.ToString());
                        }
                        else if (info.ColumnSize == 0)     //for example, case "time"
                        {
                            object obj = reader.GetValue(col);
                            q.Save((ushort)info.DataType).Save(obj.ToString());
                        }
                        else
                        {
                            string s = reader.GetString(col);
                            if (s.Length <= DB_CONSTS.DEFAULT_BIG_FIELD_CHUNK_SIZE)
                            {
                                q.Save((ushort)info.DataType).Save(s);
                            }
                            else     ////text, ntext, varchar(max), nvarchar(max)
                            {
                                if (q.GetSize() != 0 && !SendRows(q, true))
                                {
                                    return(false);
                                }
                                if (!PushText(s))
                                {
                                    return(false);
                                }
                            }
                        }
                        break;

                    case (tagVariantDataType.sdVT_UI1 | tagVariantDataType.sdVT_ARRAY):
                    {
                        SqlBinary bytes = reader.GetSqlBinary(col);
                        if (bytes.Length <= 2 * DB_CONSTS.DEFAULT_BIG_FIELD_CHUNK_SIZE)
                        {
                            q.Save((ushort)info.DataType).Save(bytes.Value);
                        }
                        else         //image, varbinary(max) or file?
                        {
                            if (q.GetSize() != 0 && !SendRows(q, true))
                            {
                                return(false);
                            }
                            if (!PushBlob(bytes.Value, (uint)bytes.Length))
                            {
                                return(false);
                            }
                        }
                    }
                    break;

                    case tagVariantDataType.sdVT_I8:
                        q.Save((ushort)info.DataType).Save(reader.GetInt64(col));
                        break;

                    case tagVariantDataType.sdVT_I4:
                    case tagVariantDataType.sdVT_INT:
                        q.Save((ushort)info.DataType).Save(reader.GetInt32(col));
                        break;

                    case tagVariantDataType.sdVT_I2:
                        q.Save((ushort)info.DataType).Save(reader.GetInt16(col));
                        break;

                    case tagVariantDataType.sdVT_UI1:
                        q.Save((ushort)info.DataType).Save(reader.GetByte(col));
                        break;

                    case tagVariantDataType.sdVT_R4:
                        q.Save((ushort)info.DataType).Save(reader.GetFloat(col));
                        break;

                    case tagVariantDataType.sdVT_R8:
                        q.Save((ushort)info.DataType).Save(reader.GetDouble(col));
                        break;

                    case tagVariantDataType.sdVT_BOOL:
                        q.Save((ushort)info.DataType);
                        if (reader.GetBoolean(col))
                        {
                            q.Save((short)-1);
                        }
                        else
                        {
                            q.Save((short)0);
                        }
                        break;

                    case tagVariantDataType.sdVT_DATE:
                        q.Save((ushort)info.DataType).Save(reader.GetDateTime(col));
                        break;

                    case tagVariantDataType.sdVT_DECIMAL:
                        q.Save((ushort)info.DataType).Save(reader.GetDecimal(col));
                        break;

                    case tagVariantDataType.sdVT_CLSID:
                        q.Save((ushort)info.DataType).Save(reader.GetGuid(col));
                        break;

                    case tagVariantDataType.sdVT_VARIANT:
                        q.Save(reader.GetValue(col));
                        break;

                    default:
                        break;
                    }
                    ++col;
                }
            }
            uint ret = SendResult(DB_CONSTS.idEndRows, q.IntenalBuffer, q.GetSize());
            if (ret != q.GetSize())
            {
                return(false); //socket closed or request canceled
            }
        }
        return(true);
    }
예제 #9
0
    private bool PushRowsetHeader(SqlDataReader reader, bool meta, out CDBColumnInfoArray vCol)
    {
        bool      b;
        DataTable dt = null;
        DataRow   dr = null;

        vCol = new CDBColumnInfoArray();
        if (meta)
        {
            dt = reader.GetSchemaTable();
        }
        int cols = reader.FieldCount;

        for (int n = 0; n < cols; ++n)
        {
            CDBColumnInfo info = new CDBColumnInfo();
            if (meta)
            {
                dr = dt.Rows[n];
                b  = (bool)dr["AllowDBNull"];
                if (!b)
                {
                    info.Flags = CDBColumnInfo.FLAG_NOT_NULL;
                }
                string dbName = dr["BaseCatalogName"].ToString();
                if (dbName.Length == 0)
                {
                    dbName = m_conn.Database;
                }
                info.DBPath = dbName;
                string schema = dr["BaseSchemaName"].ToString();
                if (schema.Length == 0)
                {
                    schema = "dbo";
                }
                info.TablePath = schema + "." + dr["BaseTableName"];
                b = (bool)dr["IsAutoIncrement"];
                if (b)
                {
                    info.Flags |= CDBColumnInfo.FLAG_PRIMARY_KEY;
                    info.Flags |= CDBColumnInfo.FLAG_UNIQUE;
                    info.Flags |= CDBColumnInfo.FLAG_AUTOINCREMENT;
                }
                object isKey = dr["IsKey"];
                if (!(isKey is DBNull))
                {
                    b = (bool)isKey;
                    if (b)
                    {
                        info.Flags |= CDBColumnInfo.FLAG_PRIMARY_KEY;
                    }
                }
                b = (bool)dr["IsUnique"];
                if (b)
                {
                    info.Flags |= CDBColumnInfo.FLAG_UNIQUE;
                }
                b = (bool)dr["IsRowVersion"];
                if (b)
                {
                    info.Flags |= CDBColumnInfo.FLAG_ROWID;
                }
                info.DisplayName  = dr["ColumnName"].ToString();
                info.OriginalName = dr["BaseColumnName"].ToString();
            }
            string data_type = reader.GetDataTypeName(n);
            info.DeclaredType = data_type;
            switch (data_type)
            {
            case "bigint":
                info.DataType = tagVariantDataType.sdVT_I8;
                break;

            case "bit":
                info.DataType = tagVariantDataType.sdVT_BOOL;
                info.Flags   |= CDBColumnInfo.FLAG_IS_BIT;
                break;

            case "timestamp":
            case "rowversion":
            case "binary":
                info.DataType = (tagVariantDataType.sdVT_UI1 | tagVariantDataType.sdVT_ARRAY);
                if (meta)
                {
                    info.ColumnSize = uint.Parse(dr["ColumnSize"].ToString());
                }
                break;

            case "nchar":
            case "char":
                info.DataType = tagVariantDataType.sdVT_BSTR;
                if (meta)
                {
                    info.ColumnSize = uint.Parse(dr["ColumnSize"].ToString());
                }
                else
                {
                    info.ColumnSize = 255;
                }
                break;

            case "smalldatetime":
            case "date":
            case "datetime":
                info.DataType = tagVariantDataType.sdVT_DATE;
                break;

            case "datetime2":
                info.DataType = tagVariantDataType.sdVT_DATE;
                if (meta)
                {
                    info.Precision = byte.Parse(dr["NumericPrecision"].ToString());
                }
                break;

            case "datetimeoffset":
                info.DataType   = tagVariantDataType.sdVT_BSTR;   //!!!! use string instead GetDateTimeOffset()
                info.ColumnSize = 64;
                break;

            case "smallmoney":
            case "numeric":
            case "money":
            case "decimal":
                info.DataType = tagVariantDataType.sdVT_DECIMAL;
                if (meta)
                {
                    info.Precision = byte.Parse(dr["NumericPrecision"].ToString());
                    info.Precision = byte.Parse(dr["NumericScale"].ToString());
                }
                break;

            case "float":
                info.DataType = tagVariantDataType.sdVT_R8;
                break;

            case "image":     //!!!! FILESTREAM attribute
                info.DataType   = (tagVariantDataType.sdVT_UI1 | tagVariantDataType.sdVT_ARRAY);
                info.ColumnSize = uint.MaxValue;
                break;

            case "int":
                info.DataType = tagVariantDataType.sdVT_INT;
                break;

            case "text":
            case "ntext":
                info.DataType   = tagVariantDataType.sdVT_BSTR;
                info.ColumnSize = uint.MaxValue;
                break;

            case "varchar":
            case "nvarchar":
                info.DataType = tagVariantDataType.sdVT_BSTR;
                if (meta)
                {
                    b = (bool)dr["IsLong"];
                    if (b)
                    {
                        info.ColumnSize = uint.MaxValue;
                    }
                    else
                    {
                        info.ColumnSize = uint.Parse(dr["ColumnSize"].ToString());
                    }
                }
                else
                {
                    info.ColumnSize = uint.MaxValue;
                }
                break;

            case "real":
                info.DataType = tagVariantDataType.sdVT_R4;
                break;

            case "smallint":
                info.DataType = tagVariantDataType.sdVT_I2;
                break;

            case "tinyint":
                info.DataType = tagVariantDataType.sdVT_UI1;
                break;

            case "uniqueidentifier":
                info.DataType = tagVariantDataType.sdVT_CLSID;
                break;

            case "varbinary":
                info.DataType = (tagVariantDataType.sdVT_UI1 | tagVariantDataType.sdVT_ARRAY);
                if (meta)
                {
                    b = (bool)dr["IsLong"];
                    if (b)
                    {
                        info.ColumnSize = uint.MaxValue;
                    }
                    else
                    {
                        info.ColumnSize = uint.Parse(dr["ColumnSize"].ToString());
                    }
                }
                else
                {
                    info.ColumnSize = uint.MaxValue;
                }
                break;

            case "xml":
                info.DataType   = tagVariantDataType.sdVT_BSTR;   //!!!! use string instead GetSqlXml
                info.ColumnSize = uint.MaxValue;
                info.Flags     |= CDBColumnInfo.FLAG_XML;
                break;

            case "sql_variant":
                info.DataType = tagVariantDataType.sdVT_VARIANT;
                break;

            case "time":
            default:
                info.DataType   = tagVariantDataType.sdVT_BSTR; //!!!! use string instead GetValue
                info.ColumnSize = 0;                            //default
                break;
            }
            vCol.Add(info);
        }
        uint res = SendResult(DB_CONSTS.idRowsetHeader, vCol, m_indexCall);

        if (res == SOCKET_NOT_FOUND || res == REQUEST_CANCELED)
        {
            return(false);
        }
        return(true);
    }