(原) ODP.NET 演示以流的形式来获取 LOB 数据
using System;
using System.Data;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.IO;
namespace Sample44
{
// 演示以流的形式来获取 LOB 数据
class Program
{
static void Main(string[] args)
{
// Connect
string constr = "User Id=scott;Password=tiger;Data Source=bjoracle";
OracleConnection conn = Connect(constr);
// Setup
Setup(conn);
//Set the command
OracleCommand cmd = new OracleCommand("select story from multimedia_tab where thekey=1");
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
// Execute
OracleDataReader reader;
try
{
// Create DataReader
reader = cmd.ExecuteReader();
//Read the first row
while (reader.Read())
{
// Set the OracleClob object to the CLOB selected
OracleClob clob = reader.GetOracleClob(0);
// Read data all data
System.Text.UnicodeEncoding converter = new System.Text.UnicodeEncoding();
//byte[] clob_data = new byte[1024];
Int64 amountRead = 0;
int readSize = 8;
Int64 totalRead = 0;
//do
//{
// amountRead = clob.Read(clob_data, (int)totalRead, readSize);
// Console.WriteLine("Actual read: {0} bytes", amountRead);
// Console.WriteLine("Current: {0}", converter.GetString(clob_data));
// totalRead += amountRead;
//} while (amountRead > 0);
string str = string.Empty;
do
{
byte[] clob_data = new byte[8];
amountRead = clob.Read(clob_data, 0, readSize);
str+=converter.GetString(clob_data);
totalRead += amountRead;
} while (amountRead > 0);
Console.WriteLine("Total number of bytes read: {0}", totalRead);
Console.WriteLine("Story: {0}", str);
// Dispose OracleClob object
clob.Dispose();
}
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
finally
{
// Dispose OracleCommand object
cmd.Dispose();
// Close and Dispose OracleConnection object
conn.Close();
conn.Dispose();
}
// Waiting
Console.ReadLine();
}
/// <summary>
/// 打开数据库连接
/// </summary>
/// <param name="connectStr"></param>
/// <returns></returns>
public static OracleConnection Connect(string connectStr)
{
OracleConnection conn = new OracleConnection(connectStr);
try
{
conn.Open();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
return conn;
}
/// <summary>
/// 创建必要的表和测试数据
/// </summary>
/// <param name="conn"></param>
public static void Setup(OracleConnection conn)
{
StringBuilder blr;
OracleCommand cmd = new OracleCommand("", conn);
blr = new StringBuilder();
blr.Append("DROP TABLE multimedia_tab");
cmd.CommandText = blr.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
blr = new StringBuilder();
blr.Append("CREATE TABLE multimedia_tab(thekey NUMBER(4) PRIMARY KEY,");
blr.Append("story CLOB, sound BLOB)");
cmd.CommandText = blr.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
// 插入大对象
string str = string.Empty;
using (StreamReader tr = new StreamReader(@"C:\arcgis9.2\tt.txt"))
{
str=tr.ReadToEnd();
}
blr = new StringBuilder();
blr.Append("INSERT INTO multimedia_tab values(");
blr.Append("1,");
blr.Append(":story,");
blr.Append("'123456789545454545454545454554545454545454')");
cmd.CommandText = blr.ToString();
//cmd.CommandText = "EXECMULT";
//cmd.CommandType = CommandType.StoredProcedure;
OracleParameter param = new OracleParameter("story", OracleDbType.Clob);
param.Direction = ParameterDirection.Input;
param.Value = str;
cmd.Parameters.Add(param);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
finally
{
cmd.Dispose();
str = null;
}
}
}
}
注意问题:using System.Data;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.IO;
namespace Sample44
{
// 演示以流的形式来获取 LOB 数据
class Program
{
static void Main(string[] args)
{
// Connect
string constr = "User Id=scott;Password=tiger;Data Source=bjoracle";
OracleConnection conn = Connect(constr);
// Setup
Setup(conn);
//Set the command
OracleCommand cmd = new OracleCommand("select story from multimedia_tab where thekey=1");
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
// Execute
OracleDataReader reader;
try
{
// Create DataReader
reader = cmd.ExecuteReader();
//Read the first row
while (reader.Read())
{
// Set the OracleClob object to the CLOB selected
OracleClob clob = reader.GetOracleClob(0);
// Read data all data
System.Text.UnicodeEncoding converter = new System.Text.UnicodeEncoding();
//byte[] clob_data = new byte[1024];
Int64 amountRead = 0;
int readSize = 8;
Int64 totalRead = 0;
//do
//{
// amountRead = clob.Read(clob_data, (int)totalRead, readSize);
// Console.WriteLine("Actual read: {0} bytes", amountRead);
// Console.WriteLine("Current: {0}", converter.GetString(clob_data));
// totalRead += amountRead;
//} while (amountRead > 0);
string str = string.Empty;
do
{
byte[] clob_data = new byte[8];
amountRead = clob.Read(clob_data, 0, readSize);
str+=converter.GetString(clob_data);
totalRead += amountRead;
} while (amountRead > 0);
Console.WriteLine("Total number of bytes read: {0}", totalRead);
Console.WriteLine("Story: {0}", str);
// Dispose OracleClob object
clob.Dispose();
}
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
finally
{
// Dispose OracleCommand object
cmd.Dispose();
// Close and Dispose OracleConnection object
conn.Close();
conn.Dispose();
}
// Waiting
Console.ReadLine();
}
/// <summary>
/// 打开数据库连接
/// </summary>
/// <param name="connectStr"></param>
/// <returns></returns>
public static OracleConnection Connect(string connectStr)
{
OracleConnection conn = new OracleConnection(connectStr);
try
{
conn.Open();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
return conn;
}
/// <summary>
/// 创建必要的表和测试数据
/// </summary>
/// <param name="conn"></param>
public static void Setup(OracleConnection conn)
{
StringBuilder blr;
OracleCommand cmd = new OracleCommand("", conn);
blr = new StringBuilder();
blr.Append("DROP TABLE multimedia_tab");
cmd.CommandText = blr.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
blr = new StringBuilder();
blr.Append("CREATE TABLE multimedia_tab(thekey NUMBER(4) PRIMARY KEY,");
blr.Append("story CLOB, sound BLOB)");
cmd.CommandText = blr.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
// 插入大对象
string str = string.Empty;
using (StreamReader tr = new StreamReader(@"C:\arcgis9.2\tt.txt"))
{
str=tr.ReadToEnd();
}
blr = new StringBuilder();
blr.Append("INSERT INTO multimedia_tab values(");
blr.Append("1,");
blr.Append(":story,");
blr.Append("'123456789545454545454545454554545454545454')");
cmd.CommandText = blr.ToString();
//cmd.CommandText = "EXECMULT";
//cmd.CommandType = CommandType.StoredProcedure;
OracleParameter param = new OracleParameter("story", OracleDbType.Clob);
param.Direction = ParameterDirection.Input;
param.Value = str;
cmd.Parameters.Add(param);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
finally
{
cmd.Dispose();
str = null;
}
}
}
}
1、从数据库中取大数据时,要一部分一部分的取,然后存入存储设备中,这样可以节省内存的消耗。
2、在插入 clob 类型的数据时,要看数据量的大小,当小于2000字符时,可以直接插入,否则参与参数的形式插入。
多于2000字符出现 ORA-01704:文字字符串过长 的错误,解决方案见:http://www.cnblogs.com/mjgforever/archive/2007/12/27/1016515.html
posted on 2007-12-27 10:51 mjgforever 阅读(406) 评论(0) 编辑 收藏 举报