C#操作Oracle的CLOB列

pldjokcywl

　　Oracle clob列的大小超过32M时使用分割发送，小于32M时正常发送
　　
　　
　　Oracle clob列性能测试的测试用例：
　　

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace db
{
class Program
{
// private OracleConnection mycon = new OracleConnection("Data Source=DB1;User Id = system;Password= asdf1234;");
private OracleConnection mycon = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.167.133.89)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=db1)));User Id = system;Password= asdf1234;");
private OracleCommand mycom = null;
private void open()
{
mycon.Open();
}
private void close()
{
mycon.Close();
}
private void delete()
{
mycom = new OracleCommand("delete from system.tb1", mycon);
mycom.ExecuteNonQuery();
}
private void insert(int num, Int32 length)
{            
String insertStr = "insert into system.tb1(CINT,CCLOB) values(:p1,:p2)";
mycom = new OracleCommand(insertStr, mycon);
string s = "a";
DateTime dt1, dt2;
OracleClob clob = null;
OracleDataReader dr;
if (length>=16)
{               
if (length == 128)
{
for (int i = 0; i < 27; i++)
{
s += s;
}
}
else if (length == 32)
{
for (int i = 0; i < 25; i++)
{
s += s;
}
}
else
{                    
s = "";
string tmp = "a";
for (int i = 0; i < 20; i++)
{
tmp += tmp;
}
for (int j = 0; j < length; j++)
{
s += tmp;
}
}
dt1 = DateTime.Now;
char[] charArray = null;
for (int i = 0; i < num; i++)
{
mycom.CommandText = "insert into system.tb1(CINT,CCLOB) values(" + i + ",'a')";
mycom.ExecuteNonQuery();
mycom.CommandText = "select CINT,CCLOB from system.tb1 where CINT=" + i;
dr = mycom.ExecuteReader();
dr.Read();
clob = dr.GetOracleClobForUpdate(1);
clob.Erase();
charArray = s.ToCharArray();
for (int j = charArray.Length; j >0; j -= 1024 * 1024)
{
clob.Write(charArray, 0, 1024 * 1024);
}
}
dt2 = DateTime.Now;
mycom = new OracleCommand("select * from system.tb1", mycon);
dr = mycom.ExecuteReader();
dr.Read();
Console.WriteLine(dr.GetOracleClob(1).Length / 1024 / 1024 / 2 + "M");
}
else
{
s = "";
string tmp = "a";
for (int i = 0; i < 20; i++)
{
tmp += tmp;
}
for (int j = 0; j < length; j++)
{
s += tmp;
}
dt1 = DateTime.Now;
for (int i = 0; i < num; i++)
{
mycom.Parameters.Clear();
/*
para1 = new OracleParameter("p1", OracleDbType.Int32);
para2 = new OracleParameter("p2", OracleDbType.Clob);
para1.Value = i;
para2.Value = s;
mycom.Parameters.Add(para1);
mycom.Parameters.Add(para2);
*/
mycom.Parameters.Add("p1", OracleDbType.Int32).Value=i;
mycom.Parameters.Add("p2", OracleDbType.Clob).Value=s;
mycom.ExecuteNonQuery();
}
dt2 = DateTime.Now;
mycom = new OracleCommand("select * from system.tb1", mycon);
dr = mycom.ExecuteReader();
dr.Read();
Console.WriteLine(dr.GetString(1).Length / 1024 / 1024 + "M");
}
TimeSpan ts = dt2 - dt1;
Console.Out.WriteLine("insert time: " + ts.TotalSeconds);
}
private void select(int num)
{
OracleDataReader dr = null;
string selStr = "";
mycom = new OracleCommand(selStr, mycon);
DateTime dt1 = DateTime.Now;
String s;
for (int i = 0; i < num; i++)
{
mycom.CommandText = "select * from system.tb1 where CINT=" + i;
dr = mycom.ExecuteReader();
while(dr.Read())
s=dr.GetString(1);
//Console.Out.WriteLine(s.ToString().Length);
dr.Close();
}
DateTime dt2 = DateTime.Now;
TimeSpan ts = dt2 - dt1;
Console.Out.WriteLine("select time: " + ts.TotalSeconds);
}
static void Main(string[] args)
{
try
{
Program p = new Program();
Console.Out.WriteLine("please input 3 parameters:");
Console.Out.WriteLine("1.sql type (insert/select)");
string sqlType;
sqlType = Console.In.ReadLine();
while (!sqlType.Equals("insert") && !sqlType.Equals("select"))
{
Console.Out.WriteLine("sql type is not insert or select,please input insert or select");
sqlType = Console.In.ReadLine();
}
Console.Out.WriteLine("2.loop time");
string loopNumStr = Console.In.ReadLine();
int loopNum = 0;
try
{
loopNum=Convert.ToInt32(loopNumStr);
}
catch (System.Exception e)
{
System.Console.WriteLine(e.Message.ToString());
}
Console.Out.WriteLine("3.data length(MB)");
string dataLengthStr = Console.In.ReadLine();
int dataLength = 0;
try
{
dataLength=Convert.ToInt32(dataLengthStr);
}
catch (System.Exception e)
{
System.Console.WriteLine(e.Message.ToString());
}
p.open();
if (sqlType.Equals("insert"))
{
p.delete();
p.insert(loopNum, dataLength);
}
else if (sqlType.Equals("select"))
{
p.select(loopNum);
}
p.close();
Console.In.Read();
}
catch (System.Exception e)
{
System.Console.WriteLine(e.Message.ToString());
}
}
}
}