.NET OracleLob 读写操作

向Oracle Clob字段写入数据

protected void Page_Load(object sender, EventArgs e)
{
    string connStr = "Data Source=ora11g;uid=scott;pwd=tiger;unicode=true";
    string id = string.Empty;
    OracleConnection conn = new OracleConnection(connStr);
    conn.Open();
    OracleCommand cmd = conn.CreateCommand();
    CreateTable(cmd);
    #region 字符串
    id = Guid.NewGuid().ToString();
    StringBuilder data = new StringBuilder();
    for (int i = 1; i <= 100; i++)
    {
        data.Append("a");
    }
    Response.Write(data.Length + " ");
    WriteDataByString(cmd, id, data.ToString());
    #endregion
    #region OracleParameter为aOracleType.Clob
    id = Guid.NewGuid().ToString();
    data.Remove(0, data.Length);
    for (int i = 1; i <= 100; i++)
    {
        data.Append("b");
    }
    Response.Write(data.Length + " ");
    WriteDataByClob(cmd, id, data.ToString());
    #endregion
    #region OracleParameter为aOracleType.NVarChar
    id = Guid.NewGuid().ToString();
    data.Remove(0, data.Length);
    for (int i = 1; i <= 100; i++)
    {
        data.Append("c");
    }
    Response.Write(data.Length + " ");
    WriteDataByNVarChar(cmd, id, data.ToString());
    #endregion
    cmd.Dispose();
    conn.Close();
    conn.Dispose();
}
public void CreateTable(OracleCommand cmd)
{
    try
    {
        cmd.CommandText = "DROP TABLE MYCLOBTABLE";
        cmd.ExecuteNonQuery();
    }
    catch
    {
    }
    cmd.CommandText = "CREATE TABLE myclobtable (a varchar2(36), b CLOB)";
    cmd.ExecuteNonQuery();
}
public void WriteDataByString(OracleCommand cmd, string id, string cData)
{
    try
    {
        cmd.CommandText = "INSERT INTO myclobtable(a,b) VALUES('" + id + "','" + cData.ToString() + "')";
        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();
    }
    catch (Exception ee)
    {
        Response.Write(ee.ToString());
    }
}
public void WriteDataByClob(OracleCommand cmd, string id, string cData)
{
    try
    {
        cmd.CommandText = "INSERT INTO myclobtable(a,b) VALUES('" + id + "'," + ":para)";
        cmd.CommandType = CommandType.Text;
        OracleParameter para = new OracleParameter("para", OracleType.Clob);
        para.Value = cData.ToString();
        cmd.Parameters.Add(para);
        cmd.ExecuteNonQuery();
    }
    catch (Exception ee)
    {
        Response.Write(ee.ToString());
    }
}
public void WriteDataByNVarChar(OracleCommand cmd, string id, string cData)
{
    try
    {
        cmd.CommandText = "INSERT INTO myclobtable(a,b) VALUES('" + id + "'," + ":para)";
        cmd.CommandType = CommandType.Text;
        OracleParameter para = new OracleParameter("para", OracleType.NVarChar);
        para.Value = cData.ToString();
        cmd.Parameters.Add(para);
        cmd.ExecuteNonQuery();
    }
    catch (Exception ee)
    {
        Response.Write(ee.ToString());
    }
}

说明

(1) 演示用三种方式向Clob字段写入内容，这三种方式都有各自的特点，或是说限制；
(2) 本例分别将三种写入方式封装到函数；
(2) 其中，函数CreateTable( )用来创建CLOB表；
(3) WriteDataByString( )是通过字符串直接向CLOB字段写入数据。以这种方式，在Oracle 9i中，超过2000个字符就会报错。而在Oracle 11g下，则是超过4000才会报错；
(4) WriteDataByClob( )是通过OracleType.Clob向CLOB字段写入数据。以这种方式，在Oracle 9i下，好像没什么限制，但如果写入4亿个字符，就会超出我电脑的内存(2G)，9千万个字符就执行得很慢。而在Oracle 11g下，执行9千万个字符似乎挺快的；
(5) WriteDataByNVarChar( )这种方式似乎也没什么限时。在Oracle 9i下，超过2000个字符就会出错，而在Oracle 11g下，超过9千万都没有事。

从Oracle Clob字段读取数据

protected void Page_Load(object sender, EventArgs e)
{
    string connStr = "Data Source=ora11g;uid=scott;pwd=tiger;unicode=true";
    string sqlStr = "SELECT a,b FROM myclobtable";
    #region 不使用OracleLob
    OracleConnection conn = new OracleConnection(connStr);
    conn.Open();
    OracleDataAdapter da = new OracleDataAdapter(sqlStr, conn);
    DataTable dt = new DataTable();
    da.Fill(dt);
    Response.Write("不使用OracleLob：");
    foreach (DataRow dr in dt.Rows)
    {
        Response.Write(dr["a"].ToString() + " ");
        Response.Write(dr["b"].ToString() + " ");
        Response.Write("");
    }
    #endregion
    #region 使用OracleLob
    int actual = 0;
    OracleCommand cmd = new OracleCommand(sqlStr, conn);
    cmd.CommandText = sqlStr;
    cmd.CommandType = CommandType.Text;
    OracleDataReader reader = cmd.ExecuteReader();
    Response.Write("使用OracleLob：");
    while (reader.Read())
    {
        OracleLob clob = reader.GetOracleLob(1);
        Response.Write(clob.Value + " ");
        StreamReader streamreader = new StreamReader(clob, Encoding.Unicode);
        char[] cbuffer = new char[9];
        while ((actual = streamreader.Read(cbuffer, 0, cbuffer.Length)) > 0)
        {
            Response.Write(clob.LobType + ".Read(" + new string(cbuffer, 0, actual) + ", " +
            cbuffer.Length + ") => " + actual + " ");
        }
    }
    #endregion
}

说明

(1) 演示用两种方法读取CLOB字段的内容，但如果仔细区分的话，则是三种；
(2) 第一种方法是不使用OracleLob类；第二种方法可以具体区分为两种，一是使用OracleLob.Value，二是使用StreamReader类；
(3) 因为OracleLob对象直接继承.Net stream对象，所以，所有操作streams的存在的类都可是被使用。.Net StreamReader使得把raw bytes转换成实际字符很容易。