codb.cs

oracle的oci驱动,在c#中定义,速度非常快,尤其可以批量的读取,插入数据.

using System;
using System.Collections.Generic;
using System.Text;
using ClassLibraryOci;
using System.Threading;


namespace TransferServer
{
    public class Codb : Coci
    {

        public String user = String.Empty;
        public String pwd = String.Empty;
        public String dblink = String.Empty;
            
        public String OciReportError(IntPtr errhp)
        {
            char[] msg_buf = new char[128];
            Int32 errcode;
            OCIErrorGet(errhp, 1, IntPtr.Zero, out errcode,
                      msg_buf, (UInt32)msg_buf.Length << 1, OCI_HTYPE_ERROR);
            Int32 off = 0;
            while (0 != msg_buf[++off]) ;
            String s = new String(msg_buf, 0, off);
            Console.WriteLine(s);

            return s;
        }

        public Int32 Open(String username, String password, String dblink, bool flag)
        {
            Close();
            
            Int32 status = 0;
            // 使用线程和对象模式来创建环境句柄
            if (IntPtr.Zero == envhp)
            {
                status = OCIEnvCreate(out envhp,
                    mode_env, IntPtr.Zero,
                    IntPtr.Zero, IntPtr.Zero, IntPtr.Zero, 0, IntPtr.Zero);
                if (OCI_ERROR == status)
                {
                    envhp = IntPtr.Zero;
                    return -1;
                }
                    
            }            

            // 分配错误句柄
            status = OCIHandleAlloc(envhp, out errhp, OCI_HTYPE_ERROR, 0, IntPtr.Zero);
            // 分配服务器句柄
            status = OCIHandleAlloc(envhp, out srvhp, OCI_HTYPE_SERVER, 0, IntPtr.Zero);
            //连接数据库服务
            Int32 strlen = dblink.Length << 1;
            status = OCIServerAttach(srvhp, errhp, dblink, strlen, OCI_DEFAULT);
            if (status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO)
            {
                OciReportError(errhp);//printf("Connection  failed \n");
               
                    //OCIHandleFree(envhp, OCI_HTYPE_ENV);
                    //envhp = IntPtr.Zero;
               
                return status;
            }
            // 分配服务器上下文句柄
            status = OCIHandleAlloc(envhp, out svchp, OCI_HTYPE_SVCCTX, 0, IntPtr.Zero);
            // 设置服务器上下文句柄的服务器句柄属性
            status = OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, srvhp, 0, OCI_ATTR_SERVER, errhp);
            // 分配用户会话句柄
            status = OCIHandleAlloc(envhp, out usrhp, OCI_HTYPE_SESSION, 0, IntPtr.Zero);
            // 为用户会话句柄设置用户名和密码属性           
            status = OCIAttrSet(usrhp, OCI_HTYPE_SESSION, username,
                (UInt32)username.Length << 1, OCI_ATTR_USERNAME, errhp);
            status = OCIAttrSet(usrhp, OCI_HTYPE_SESSION, password,
                (UInt32)password.Length << 1, OCI_ATTR_PASSWORD, errhp);
            //按照用户名和密码登录oracle
            if (flag)
                status = OCISessionBegin(svchp, errhp, usrhp, OCI_CRED_RDBMS, OCI_SYSDBA);
            else
                status = OCISessionBegin(svchp, errhp, usrhp, OCI_CRED_RDBMS, OCI_DEFAULT);
            //
            if (status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO)
            {
                OciReportError(errhp);//printf("Connection  failed \n");
              
                    //OCIHandleFree(envhp, OCI_HTYPE_ENV);
                    //envhp = IntPtr.Zero;
               
                return status;
            }
            // 在服务器上下文环境中设置用户会话属性
            status = OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, usrhp, 0, OCI_ATTR_SESSION, errhp);
            //分配语句句柄
            status = OCIHandleAlloc(envhp, out stmthp, OCI_HTYPE_STMT, 0, IntPtr.Zero);
            if (null == list_fieldInfo[0])
                list_fieldInfo[0] = new CFieldInfo();

            isOpened = true;
            ++count_ref;
            return 0;
        }
        public void Close()
        {
            if (false == isOpened)
                return;
            Int32 status = 0;
            //释放sql语句句柄
            status = OCIHandleFree(stmthp, OCI_HTYPE_STMT);
            stmthp = IntPtr.Zero; ;
            // 结束会话
            status = OCISessionEnd(svchp, errhp, usrhp, OCI_DEFAULT);
            // 释放会话句柄
            status = OCIHandleFree(usrhp, OCI_HTYPE_SESSION);
            usrhp = IntPtr.Zero;
            // 释放服务上下文句柄
            status = OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
            svchp = IntPtr.Zero;
            // 断开连接
            status = OCIServerDetach(srvhp, errhp, OCI_DEFAULT);
            // 释放服务器句柄
            status = OCIHandleFree(srvhp, OCI_HTYPE_SERVER);
            srvhp = IntPtr.Zero;
            // 释放错误句柄
            status = OCIHandleFree(errhp, OCI_HTYPE_ERROR);
            errhp = IntPtr.Zero;
            isOpened = false;
            if (0 == --count_ref)
            {
                OCIHandleFree(envhp, OCI_HTYPE_ENV);
                envhp = IntPtr.Zero;
            }
            
        }
        public Int32 PrepareStatementQuery(String sql)
        {
            Int32 status = 0;
            // 准备SOL语句
            status = OCIStmtPrepare(stmthp, errhp, sql, sql.Length << 1, OCI_NTV_SYNTAX, OCI_DEFAULT);
            //检查SQL语句类型
            UInt32 m_nstmttype; UInt32 nsb;//Intptr.Zero
            status = OCIAttrGet(stmthp, OCI_HTYPE_STMT, out m_nstmttype, out nsb, OCI_ATTR_STMT_TYPE, errhp);
            //if ( OCI_STMT_SELECT == m_nstmttype) { }
            //执行sql语句
            status = OCIStmtExecute(svchp, stmthp, errhp,
                0,				//iter 如果最后参数mode是OCI_DEFAULT,则此参数必须置 0
                0,              //rowoff
               IntPtr.Zero,     //OCISnapshot *snap_in
               IntPtr.Zero,     //OCISnapshot *snap_out
                OCI_DEFAULT		//mode 如果此参数是OCI_DESCRIBE_ONLY,则iter参数可为任意值
               );
            if (0 > status)
            {
                OciReportError(errhp);
                return status;
            }
            //获取选择表的元信息  查询字段信息
            Int32 nParm_status = 0;		//状态码
            IntPtr paramhp = IntPtr.Zero;		//  参数句柄
            m_nCurrentNumOfColumn = 0;	//注意表列的起始数是1,不是0
            nParm_status = OCIParamGet(stmthp, OCI_HTYPE_STMT, errhp, out paramhp,
                1		//注意表列的起始数是1,不是0
                );

            while (OCI_SUCCESS == nParm_status)
            {
                if (capacity_field_arry < m_nCurrentNumOfColumn)
                {
                    list_fieldInfo[++capacity_field_arry] = new CFieldInfo();
                }
                //获取字段名                
                UInt32 attrsz = 0;
                status = OCIAttrGet(paramhp, OCI_DTYPE_PARAM, out list_fieldInfo[m_nCurrentNumOfColumn].strFieldName,
                     out attrsz,// 或 IntPtr.Zero
                     OCI_ATTR_NAME,
                    errhp);
                //char [] cc =new char[256];
                //System.Array.Copy(FieldName, cc, attrsz);
                //String s_FieldName =new String(FieldName,0,(int)attrsz/2);
                //获取数据类型 之 Field Type

                status = OCIAttrGet(paramhp, OCI_DTYPE_PARAM, out list_fieldInfo[m_nCurrentNumOfColumn].dwFieldType,
                    IntPtr.Zero,// 或0
                      OCI_ATTR_DATA_TYPE, errhp);
                //获取数据类型 之 Decimal 小数点位数               
                status = OCIAttrGet(paramhp, OCI_DTYPE_PARAM, out list_fieldInfo[m_nCurrentNumOfColumn].dwDecimal,
                    out attrsz, // 或0  如果不需要返回值, (ub4*)0  否则 &attrsz,
                     OCI_ATTR_SCALE, errhp);
                //获取数据类型 之 FieldLength 

                status = OCIAttrGet(paramhp, OCI_DTYPE_PARAM, out list_fieldInfo[m_nCurrentNumOfColumn].dwFieldLength,
                    out attrsz, // 或0  如果不需要返回值, (ub4*)0  否则 &attrsz,
                      OCI_ATTR_DATA_SIZE, errhp);

                //获取数据类型 之 OCI_ATTR_DISP_SIZE
                /*
                OCIAttrGet(	(dvoid*)paramhp,(ub4)OCI_DTYPE_PARAM,
                    (dvoid*)&dDispSize,
                    (ub4*)&attrsz, // 或0  如果不需要返回值, (ub4*)0  否则 &attrsz,
                    (ub4)OCI_ATTR_DISP_SIZE,
                    (OCIError*)errhp);
                // OCI_ATTR_PRECISION
                */
                ++m_nCurrentNumOfColumn;
                nParm_status = OCIParamGet(stmthp, OCI_HTYPE_STMT, errhp, out paramhp,
                    m_nCurrentNumOfColumn + 1		//注意表列的起始数是1,不是0
                    );
            }
            //计算记录长度，长度是等长的
            UInt32 m_nRecordLength = 0, i = 0;
            for (; i < m_nCurrentNumOfColumn; ++i)
            {
                switch (list_fieldInfo[i].dwFieldType)
                {
                    case SQLT_NUM:
                        if (0 == list_fieldInfo[i].dwDecimal)
                        {
                            list_fieldInfo[i].dwFieldType = SQLT_INT;
                            list_fieldInfo[i].dwFieldLength = 4;
                            m_nRecordLength += 4;
                            //printf("\ndouble %d\n",i);					
                        }
                        else
                        {
                            list_fieldInfo[i].dwFieldType = SQLT_FLT;
                            list_fieldInfo[i].dwFieldLength = 8;
                            m_nRecordLength += 8;
                        }
                        break;
                    case SQLT_LBI:
                        m_nRecordLength += m_nMaxSizeOfLBI;
                        list_fieldInfo[i].dwFieldLength = m_nMaxSizeOfLBI;
                        m_bIsIncludeLobOrLbi = true;