codb.cs

来自「oracle的oci驱动,在c#中定义,速度非常快,尤其可以批量的读取,插入数据」· CS 代码 · 共 697 行 · 第 1/3 页

            //printf("写入前lob长度=%d\n",nSizeOfLob); 
            status = OCILobWrite(svchp, errhp, blob,
                           ref amt, 1, ref array_msg[0], nSieOfMsg,
                             OCI_ONE_PIECE,
                            IntPtr.Zero,
                            IntPtr.Zero,
                         (UInt16)0, SQLCS_IMPLICIT);
            if (status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO)
            {
                OciReportError(errhp);
                OCILobClose(svchp, errhp, blob);
                OCIDescriptorFree(blob, OCI_DTYPE_LOB);
                status = OCITransRollback(svchp, errhp, OCI_DEFAULT);
                return false;
            }
            status = OCILobGetLength(svchp, errhp, blob, out nSizeOfLob);
            //printf("写入后lob长度=%d\n", nSizeOfLob);
            status = OCILobClose(svchp, errhp, blob);
            status = OCITransCommit(svchp, errhp, OCI_DEFAULT);
            status = OCIDescriptorFree(blob, OCI_DTYPE_LOB);
            return true;
        }
        public Boolean StoreMessage(String user_id, String user_name, byte[] array_msg, Int32 off, Int32 count, byte nType, out Int32 nIndex_msg)
        {
            // TRUNCATE TABLE scott.MSGTRANSFER 
            Int32 nFid = GetSelectInt32("select message_idx.nextval from dual");
            nIndex_msg = nFid;

            DateTime date = System.DateTime.Now;
            String str_datetime = date.ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.DateTimeFormatInfo.InvariantInfo);
            String sql_insert = String.Format("INSERT INTO MSGTRANSFER(FID,FUSERID,FUSERNAME,FTIME,FMESSAGE,FTYPE) VALUES ({0},'{1}','{2}',to_date('{3}','yyyy-mm-dd hh24:mi:ss'),empty_blob(),{4})", nFid, user_id, user_name, str_datetime, nType);
            //准备 insert sql语句
            Int32 status = OCIStmtPrepare(stmthp, errhp, sql_insert, sql_insert.Length << 1, OCI_NTV_SYNTAX, OCI_DEFAULT);
            if (status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO)
            {
                OciReportError(errhp);
                return false;
            }
            //执行 insert sql 语句 Insert an empty locator
            status = OCIStmtExecute(svchp, stmthp, errhp, 1, 0, IntPtr.Zero, IntPtr.Zero, OCI_DEFAULT);
            if (status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO)
            {
                OciReportError(errhp);
                status = OCITransRollback(svchp, errhp, OCI_DEFAULT);
                return false;
            }
            //select for update
            String sql_update = String.Format("SELECT FMESSAGE FROM MSGTRANSFER WHERE FID={0} FOR UPDATE", nFid);
            status = OCIStmtPrepare(stmthp, errhp, sql_update, sql_update.Length << 1, OCI_NTV_SYNTAX, OCI_DEFAULT);

            //分配lob定位符句柄
            IntPtr blob;
            status = OCIDescriptorAlloc(envhp, out blob, OCI_DTYPE_LOB, 0, IntPtr.Zero);
            IntPtr p_Define;
            status = OCIDefineByPos(stmthp, out p_Define, errhp, 1, ref blob, -1,
                 SQLT_BLOB, IntPtr.Zero, IntPtr.Zero, IntPtr.Zero, OCI_DEFAULT);
            status = OCIStmtExecute(svchp, stmthp, errhp, 1, 0,
                      IntPtr.Zero, IntPtr.Zero, OCI_DEFAULT);
            if (status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO)
            {
                OciReportError(errhp);
                OCIDescriptorFree(blob, OCI_DTYPE_LOB);
                status = OCITransRollback(svchp, errhp, OCI_DEFAULT);
                return false;
            }
            //打开描述符，以读写方式
            status = OCILobOpen(svchp, errhp, blob, OCI_LOB_READWRITE);
            UInt32 nSizeOfLob = 0;
            UInt32 amt = (UInt32)count; UInt32 nSieOfMsg = amt;
            status = OCILobGetLength(svchp, errhp, blob, out nSizeOfLob);
            //printf("写入前lob长度=%d\n",nSizeOfLob); 
            status = OCILobWrite(svchp, errhp, blob,
                           ref amt, 1, ref array_msg[off], nSieOfMsg,
                             OCI_ONE_PIECE,
                            IntPtr.Zero,
                            IntPtr.Zero,
                         (UInt16)0, SQLCS_IMPLICIT);
            if (status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO)
            {
                OciReportError(errhp);
                OCILobClose(svchp, errhp, blob);
                OCIDescriptorFree(blob, OCI_DTYPE_LOB);
                status = OCITransRollback(svchp, errhp, OCI_DEFAULT);
                return false;
            }
            status = OCILobGetLength(svchp, errhp, blob, out nSizeOfLob);
            //printf("写入后lob长度=%d\n", nSizeOfLob);
            status = OCILobClose(svchp, errhp, blob);
            status = OCITransCommit(svchp, errhp, OCI_DEFAULT);
            status = OCIDescriptorFree(blob, OCI_DTYPE_LOB);
            return true;
        }
        public byte[] GetMessage(String user_id, String user_name,out Int32 type,out Int32 nIndex_msg)
        {
            nIndex_msg = 0; type = 0;
            if (String.Empty == user_id)
                return null;
            String sql_select = String.Format("SELECT FID,FTYPE,FMESSAGE FROM MSGTRANSFER WHERE FUSERID='{0}' AND ROWNUM=1", user_id);
            //分配lob定位符句柄
            IntPtr blob;
            Int32 status = OCIDescriptorAlloc(envhp, out blob, OCI_DTYPE_LOB, 0, IntPtr.Zero);
            if (0 != status)
            {
                OciReportError(errhp);
                OCIDescriptorFree(blob, OCI_DTYPE_LOB);
            }
            IntPtr p_Define;
            //printf("准备读取blob字段值\n");
            status = OCIStmtPrepare(stmthp, errhp, sql_select, sql_select.Length << 1, OCI_NTV_SYNTAX, OCI_DEFAULT);
            if (0 != status)
            {
                OciReportError(errhp);
                OCIDescriptorFree(blob, OCI_DTYPE_LOB);
            }
            char indp = (char)0, rlenp = (char)4;
            
            status = OCIDefineByPos(stmthp, out p_Define, errhp, 1,
                ref nIndex_msg, 4,
                SQLT_INT, ref indp, ref rlenp, IntPtr.Zero, OCI_DEFAULT);
            status = OCIDefineByPos(stmthp, out p_Define, errhp, 2,
                ref type, 4,
                SQLT_INT, ref indp, ref rlenp, IntPtr.Zero, OCI_DEFAULT);
            status = OCIDefineByPos(stmthp, out p_Define, errhp, 3,
                ref blob, -1,
                SQLT_BLOB, IntPtr.Zero, IntPtr.Zero, IntPtr.Zero, OCI_DEFAULT);
            //准备读出数据
            status = OCIStmtExecute(svchp, stmthp, errhp, 1, 0, IntPtr.Zero, IntPtr.Zero, OCI_DEFAULT);
            if (status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO)
            {
                OciReportError(errhp);
                OCIDescriptorFree(blob, OCI_DTYPE_LOB);
                return null;
            }
            //打开描述符，以读方式
            status = OCILobOpen(svchp, errhp, blob, OCI_LOB_READONLY);
            if (0 != status)
            {
                OciReportError(errhp);
                status = OCILobClose(svchp, errhp, blob);
                OCIDescriptorFree(blob, OCI_DTYPE_LOB);
            }
            UInt32 nSizeOfLob;
            status = OCILobGetLength(svchp, errhp, blob, out nSizeOfLob);
            byte[] arrayMsg = null;
            if (nSizeOfLob > 0)
            {
                arrayMsg = new byte[nSizeOfLob];
                UInt32 amtp = nSizeOfLob;
                status = OCILobRead(svchp, errhp, blob,
                              ref amtp, 1, ref arrayMsg[0], nSizeOfLob,
                              IntPtr.Zero,
                              IntPtr.Zero,
                              (UInt16)0, SQLCS_IMPLICIT);

            }
            status = OCILobClose(svchp, errhp, blob);
            status = OCIDescriptorFree(blob, OCI_DTYPE_LOB);
            return arrayMsg;
        }
        public Boolean DeleteMsg(UInt32 nFid)
        {
            String sql_delete = String.Format("DELETE FROM MSGTRANSFER WHERE FID={0}", nFid);
            Int32 status = OCIStmtPrepare(stmthp, errhp, sql_delete, sql_delete.Length << 1, OCI_NTV_SYNTAX, OCI_DEFAULT);
            status = OCIStmtExecute(svchp, stmthp, errhp, 1, 0,
                     IntPtr.Zero, IntPtr.Zero, OCI_COMMIT_ON_SUCCESS);
            if (OCI_SUCCESS == status)
                return true;
            else
                return false;
        }
        public void loginError(String msg)
        {
        }

        public void UpdateMsg(UInt32 nFid, UInt16 ntype)
        {
            String sql_update = String.Format("UPDATE MSGTRANSFER SET FTYPE={0} WHERE FID={1}", ntype, nFid);
            Int32 status = OCIStmtPrepare(stmthp, errhp, sql_update, sql_update.Length << 1, OCI_NTV_SYNTAX, OCI_DEFAULT);
            status = OCIStmtExecute(svchp, stmthp, errhp, 1, 0,
                     IntPtr.Zero, IntPtr.Zero, OCI_COMMIT_ON_SUCCESS);
        }

        public Int32 Rollback()
        {
            Int32 status = OCITransRollback(svchp, errhp, OCI_DEFAULT);
            if (0 != status && status != OCI_SUCCESS_WITH_INFO)
            {
                OciReportError(errhp);
                return status;
            }
            else
                return 0;
        }
        public Int32 Commit()
        {
            Int32 status = OCITransCommit(svchp, errhp, OCI_DEFAULT);
            if (0 != status && status != OCI_SUCCESS_WITH_INFO)
            {
                OciReportError(errhp);
                return status;
            }
            else
                return 0;
        }

        public byte[] result_buffer = null;
        public CFieldInfo[] list_fieldInfo = new CFieldInfo[256];
        private UInt32 capacity_field_arry = 0;
        public UInt32 m_nCurrentNumOfColumn = 0;
        public UInt32 m_nPerFetch = 20;
        public UInt32 m_nMaxSizeOfLBI = 50;

        private bool m_bIsIncludeLobOrLbi = false;
        private UInt32 capacity_result_buffer = 0;
        private char[] m_pInp4LBI = null;
        private UInt32 capacity_pInp4LBI = 0;

        private static IntPtr envhp = IntPtr.Zero; //　环境句柄
        private IntPtr srvhp = IntPtr.Zero;	//　服务器句柄
        private IntPtr svchp = IntPtr.Zero;			//　服务上下文句柄
        private IntPtr errhp = IntPtr.Zero;			//　错误句柄
        private IntPtr usrhp = IntPtr.Zero;			//　用户会语句柄
        private IntPtr stmthp = IntPtr.Zero;        //  语句句柄

        private bool isOpened = false;



    }
}