当前位置:  数据库>oracle

C#对Oracle Clob对象的读写

    来源: 互联网  发布时间:2017-04-16

    本文导语: 以下是我的两种实现方式: 方式一: 该方式进行的操作是,将一大于4Kb的txt文件写入Clob对象或者从该Clob字段读取出来并创建一个txt文件来保存。这些操作通过分段读取来实现。         public void SaveMassiveData()        {  ...

以下是我的两种实现方式:

方式一:

该方式进行的操作是,将一大于4Kb的txt文件写入Clob对象或者从该Clob字段读取出来并创建一个txt文件来保存。这些操作通过分段读取来实现。

        public void SaveMassiveData()
        {
            // Declare Oracle objects
            OracleConnection connection = new OracleConnection(ConnStr);
            OracleCommand cmd = new OracleCommand("", connection);
            OracleTransaction transaction;
            OracleDataReader reader;
            OracleClob clob;
            try
            {
                connection.Open();
                Console.WriteLine("Connected to database.../n");
 
                // Start a transaction
                transaction = connection.BeginTransaction();













                 // Lock the result set using the "FOR UPDATE" clause
                cmd.CommandText = "SELECT " + Clobcol + " FROM " + Table + " FOR UPDATE";
                reader = cmd.ExecuteReader();

                reader.Read();
                clob = reader.GetOracleClob(0);
                clob.Erase();

                string content = string.Empty;
                using (StreamReader sr = new StreamReader(srcPath))
                {
                    content = sr.ReadToEnd();
                }



                char[] buffer = new char[BufferLength];
                // Save the content into memory
                using (MemoryStream ms = new MemoryStream(Encoding.GetEncoding("UTF-8").GetBytes(content)))
                {
                    int readCounts = 0;
                    using (StreamReader sr = new StreamReader(ms))
                    {
                        while (sr.Peek() > -1)
                        {
                            readCounts = sr.Read(buffer, 0, BufferLength);
                            clob.Write(buffer, 0, readCounts);
                        }
                        clob.Flush();
                    }
                }
                Console.WriteLine("Save Massive data Succeeded!");














                // Commit transaction
                transaction.Commit();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
                connection.Dispose();
            }
        }
 
        public void FetchMassiveData()
        {
            // Declare Oracle objects
            OracleConnection connection = new OracleConnection(ConnStr);
            OracleCommand cmd = new OracleCommand("", connection);
            OracleTransaction transaction;
            OracleDataReader reader;
            OracleClob clob;
            try
            {
                connection.Open();
                Console.WriteLine("Connected to database.../n");
 
                // Start a transaction
                transaction = connection.BeginTransaction();




























                cmd.CommandText = "SELECT " + Clobcol + " FROM " + Table;
                reader = cmd.ExecuteReader();

                reader.Read();
                clob = reader.GetOracleClob(0);

                int readCounts;
                char[] buffer = new char[BufferLength];
                using (FileStream fs = new FileStream(desPath, FileMode.Create))
                {
                    StreamWriter sw = new StreamWriter(fs);
                    while ((readCounts = clob.Read(buffer, 0, BufferLength)) > 0)
                    {
                        sw.Write(buffer, 0, readCounts);
                    }
                    sw.Flush();
                    sw.Close();
                }
                Console.WriteLine("Fetch Massive data Succeeded!");











                // Commit transaction
                transaction.Commit();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
                connection.Dispose();
            }
        }












 

方式二:

事实上Oracle官方提供的ODAC112011beta插件中的Oracle.DataAccess命名空间已经提供了类似于ADO.NET的相关接口,因此针对Oracle中各种类型字段的操作可以使用这些方法直接实现,而无需考虑字段大小的问题。

 

方式二实现的操作是从Oracle数据库中读取Clob大字段(大于4Kb)并返回一个DataTable,及将大于4Kb的txt文件存入Oracle数据库中的Clob字段。

 

public DataTable FetchDBClobFiled(string sql)
        {
            if ((sql == null) || (sql.Length == 0))
            {
                return null;
            }




 

            OracleTransaction transaction;
            DataTable dt = new DataTable();

            try
            {
                conn.Open();

                // Start a transaction
                transaction = conn.BeginTransaction();

                adapter.Fill(dt);

                // Commit transaction
                transaction.Commit();

                return dt;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;
            }
            finally
            {
                adapter.Dispose();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }













以上代码省略了connection,adapter及command的定义,其中传入的查询语句为简单的Select语句如“"SELECT ID, CLOBCOL, CLOBCOL2, CLOBCOL3, CLOBCOL4 FROM TableName"”。


  public void UpdateDBWithClobField(string sql)
        {
            if ((sql == null) || (sql.Length == 0))
            {
                return;
            }





 

            OracleTransaction transaction;
            OracleParameter[] clobParams = new OracleParameter[4];

            try
            {
                conn.Open();

                // Start a transaction
                transaction = conn.BeginTransaction();

                string content = string.Empty;
                using (StreamReader sr = new StreamReader(srcPath))
                {
                    content = sr.ReadToEnd();
                }
                clobParams[0] = cmd.Parameters.Add("CLOBCOL", OracleDbType.Clob, content, ParameterDirection.Input);
                clobParams[1] = cmd.Parameters.Add("CLOBCOL2", OracleDbType.Clob, content,ParameterDirection.Input);
                clobParams[2] = cmd.Parameters.Add("CLOBCOL3", OracleDbType.Clob, content,ParameterDirection.Input);
                clobParams[3] = cmd.Parameters.Add("CLOBCOL4", OracleDbType.Clob, content,ParameterDirection.Input);







 

                cmd.ExecuteNonQuery();

                // Commit transaction
                transaction.Commit();

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                foreach (OracleParameter parameter in clobParams)
                {
                    parameter.Dispose();
                }
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }














传入的sql语句为“insert into TableName values(0,:CLOBCOL,:CLOBCOL2,:CLOBCOL3,:CLOBCOL4)”


    
 
 
 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • Oracle exp 导出用户对象
  • oracle查看会话锁定的所有对象代码分享
  • 测试添加Oracle中Blob数据类型对象
  • 基于Oracle的面向对象技术入门基础简析开发者网络Oracle
  • Oracle中关数据库对象的统计分析
  • Oracle使用PL/SQL操作COM对象
  • 浅析Oracle对象和数据的导入导出
  • Oracle PL/SQL编程有关的数据库对象
  • Oracle中重新编译无效的存储过程, 或函数、触发器等对象
  • 大家帮帮忙解决oracle大对象的问题吧。来者有分。
  • ORACLE 常用的SQL语法和数据对象
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动!也就是让oracle那个服务自动启动,不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同?
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍
  • SCO unix下安装oracle,但没有光盘,请大家推荐一个oracle下载站点(unix版本的)。谢谢!!!!
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • Oracle 数据库(oracle Database)性能调优技术详解
  • Linux /$ORACLE_HOME $ORACLE_HOME
  • ORACLE日期相关操作
  • Linux系统下Oracle的启动与Oracle监听的启动
  • ORACLE数据库常用字段数据类型介绍
  • 请问在solaris下安装ORACLE,用root用户和用oracle用户安装有什么区别么?


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3