C# oracle Clob与Blob数据的读写

在C#中对oracle数据中clob字段类型及blob字段类型读写怎么处理呢？最初使用update语句更新表之间的关系时，发现clob及blob的数据都没有更新掉。在百度上百度半天时间，发现是clob及blob的数据，不能直接处理，只能用代码的逻辑去处理。而且获取的逻辑还不是很一样。现在就把这两种处理逻辑记录下来，为各位同学及以后的我使用。

第一步：oracle数据连接参数：

 public string orclconnstr = "User ID={2};Password={3};Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = {0})(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = {1})))";

第二步：数据库连接方法：

 public OracleConnection connect(string orclServer, string oracleOrcl, string orclUser, string orclPassword)
        {
            orclconnstr = string.Format(orclconnstr, orclServer, oracleOrcl, orclUser, orclPassword);
            orclconn = new OracleConnection(orclconnstr);
            return orclconn;

        }

第三步：CLOB数据读取：

/**
         * 读取clob数据
         * */
        public string getClobContext( string sql)
        {
            string context = "";
           OracleConnection  orclconn = connect("127.0.0.1","orcl","test","1");
         if (orclconn != null)
         {
             using (OracleConnection conn = new OracleConnection(orclconn.ConnectionString))
             {
                 try
                 {
                     conn.Open();
                     OracleCommand cmd = new OracleCommand(sql, conn);
                     OracleDataReader reader = cmd.ExecuteReader();
                     reader.Read();
                     OracleLob clob = reader.GetOracleLob(0); 
                     context = StringHelper.FormatStringByObject(clob.Value);
                     reader.Close();
                 }
                 catch (Exception ex)
                 {
                    // LogHelper.LogHelper.logHelper.ErrorLog(ex.Message, "");
                 }
                 finally
                 {
                     conn.Close();
                 }
             }
         }
         return context;
        }

第四步：blob数据读取

 public byte[] getBlobContext( string sql)
        {
            Byte[] blob = null;
            OracleConnection  orclconn = connect("127.0.0.1","orcl","test","1");
            if (orclconn != null)
            {
                using (OracleConnection conn = new OracleConnection(orclconn.ConnectionString))
                {
                    try
                    {
                        conn.Open();
                        OracleCommand cmd = new OracleCommand(sql, conn);
                        OracleDataReader reader = cmd.ExecuteReader();
                        reader.Read();
                        blob = new Byte[(reader.GetBytes(0, 0, null, 0, int.MaxValue))];
                        reader.GetBytes(0, 0, blob, 0, blob.Length);
                        reader.Close();
                    }
                    catch (Exception ex)
                    {
                       // LogHelper.LogHelper.logHelper.ErrorLog(ex.Message, "");
                        //LogHelper.LogHelper.logHelper.ErrorLog(sql, "");                 
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            return blob;
        }

第五步：数据插入

/**
         * 插入特殊字符信息
         * */
        public bool InsertSpecialInfo( string sql, List<OracleSpecialFields> lst)
        {
            OracleConnection  orclconn = connect("127.0.0.1","orcl","test","1");
            if (orclconn != null)
            {
                using (OracleConnection conn = new OracleConnection(orclconn.ConnectionString))
                {
                    try
                    {
                        conn.Open();
                        OracleCommand cmd = new OracleCommand(sql, conn);
                        foreach(OracleSpecialFields sf in lst)
                        {
                            OracleParameter oracleParameter = new OracleParameter(sf.Fieldname, sf.Fieldtype);
                            if (sf.FieldValue == "" || sf.FieldValue == null)
                            {
                                oracleParameter.Value = DBNull.Value;
                            }
                            else
                            {
                                oracleParameter.Value = sf.FieldValue;
                            }
                            cmd.Parameters.Add(oracleParameter);
                        }
                        //LogHelper.LogHelper.logHelper.InfoLog(sql, "");
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();             
             
                        return true;
                    }
                    catch (Exception ex)
                    {
                       // LogHelper.LogHelper.logHelper.ErrorLog(ex.Message, "");
                        //LogHelper.LogHelper.logHelper.ErrorLog(sql, "");
                        return false;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            return false;
        }

第六步：测试用例

oracle数据库中的两个字段，一个blob,一个clob字段

第七步：创建一个对象类

    public class OracleSpecialFields
    {
        private string fieldname;

        public string Fieldname
        {
            get { return fieldname; }
            set { fieldname = value; }
        }
        private OracleType fieldtype;

        public OracleType Fieldtype
        {
            get { return fieldtype; }
            set { fieldtype = value; }
        }
        private object fieldValue;

        public object FieldValue
        {
            get { return fieldValue; }
            set { fieldValue = value; }
        }
    }

第八步：处理结果

/**
        * 更新房产分户图数据到历史层
        * */
        public static void InsertFcfht(string prj_id)
        {
             string layout = getClobContext( "SELECT LAYOUT  FROM TEST WHERE ID='" +prj_id + "'");//获取clob
                byte[] fcfht = getBlobContext( "SELECT FCFHT  FROM TEST WHERE ID='" + prj_id + "'");//获取blob

                string sql = "INSERT INTO TEST (ID,LAYOUT,FCFHT) VALUES(':ID',':BUILDING_ID',':VERSION',:CREATE_DATE,:LAYOUT,:FCFHT)";
                sql = sql.Replace(":ID", System.Guid.NewGuid().ToString());
                List<OracleSpecialFields> lst = new List<OracleSpecialFields>();
                OracleSpecialFields field_layout = new OracleSpecialFields();
                field_layout.FieldValue = layout;
                field_layout.Fieldtype = OracleType.Clob;
                field_layout.Fieldname = "LAYOUT";
                lst.Add(field_layout);
                OracleSpecialFields field_fcfht = new OracleSpecialFields();
                field_fcfht.Fieldname = "FCFHT";
                field_fcfht.FieldValue = fcfht;
                field_fcfht.Fieldtype = OracleType.Blob;
                lst.Add(field_fcfht);
                InsertSpecialInfo(constEnum.DBNAME.BDC, sql, lst);//插入数据
    }