C#对Oracle Clob对象的读写

在网上参考了一些资料,得到了...

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

方式一:

该方式进行的操作是,将一大于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)”

 

以上代码仅供参考,在此帖出来,还望诸位指点!谢谢

展开阅读全文

没有更多推荐了,返回首页