ODP.Net基础应用——CLOB类型数据

• BLOB类型
• CLOB/NCLOB类型
• BFILE类型
• RAW类型

---

BLOB类型

BLOB类型数据常用来存储图像、视频和音频

BLOB数据的插入

string imagePath = @"D:\Image\Icons\COMS_icon.png";
byte[] _imageStream = System.IO.File.ReadAllBytes(imagePath);  

OracleConnection _conn = new OracleConnection(connString);
_conn.Open();
OracleCommand _cmd = _conn.CreateCommand();
_cmd.CommandText = "INSERT INTO PRODUCTFILES(PRODUCTID,FILEATTACHMENT)
         VALUES(:v_ProductId,:v_FileAttachment)";
_cmd.Parameters.Add(new OracleParameter("v_ProductId","E1"));
OracleBlob _image = new OracleBlob(_conn);
_image.Write(_imageStream, 0, _imageStream.Length);
_cmd.Parameters.Add(new OracleParameter("v_FileAttachment", _image));
int _result = _cmd.ExecuteNonQuery();
if(_result == 1) { MessageBox.Show("done"); }

_conn.Close();
_conn.Dispose();
_conn = null;

BLOB数据的查询显示

拖一个picturebox控件进来

OracleConnection _conn = new OracleConnection(connString);
_conn.Open();
OracleCommand _cmd = _conn.CreateCommand();
_cmd.CommandText = "SELECT FILEATTACHMENT FROM PRODUCTFILES WHERE PRODUCTID=:v_ProductId";
_cmd.Parameters.Add(new OracleParameter("v_ProductId", TBX.Text));
OracleDataReader _reader = _cmd.ExecuteReader();

if(_reader.HasRows)
{
    if(_reader.Read())
    {
        OracleBlob _image = _reader.GetOracleBlob(_reader.GetOrdinal("FILEATTACHMENT"));
        pictureBox.Image = Image.FromStream(new System.IO.MemoryStream(_image.Value ));
    }
}
_conn.Close();
_conn.Dispose();
_conn = null;
Console.WriteLine("done");

输入索引E1，点击按钮：

CLOB/NCLOB类型

CLOB/NCLOB类型数据常用来存储大型文本

CLOB数据的插入

与BLOB类型基本相似，下面只贴出核心代码

OracleCommand _cmd = _conn.CreateCommand();
_cmd.CommandText = "INSERT INTO PRODUCTFILES(PRODUCTID,REMARKS)
         VALUES(:v_ProductId,:v_Remarks)";
_cmd.Parameters.Add(new OracleParameter("v_ProductId","E1"));
OracleClob _text = new OracleClob(_conn);
_text.Write(TBX.Text.ToCharArray(), 0, TBX.Text.Length);
_cmd.Parameters.Add(new OracleParameter("v_Remarks",_text));

CLOB数据的获取

OracleClob _text = _reader.GetOracleClob(_reader.GetOrdinal("REMARKS"));
TBX.Text = _text.Value;

BFILE类型

BFILE指向存储在操作系统外部的数据。Your SQL statements refer to these folders not by path but by logical directory name.
首先要使账户获得相应的权限，以SYSDBA的身份登录SQL Plus，授权：

GRANT CREATE ANY DIRECTORY TO 用户名;

登录到用户，创建目录：

GRANT OR REPLACE DIRECTORY "PRODUCTFILESFOLDER" AS 'D:\Data\productfiles';

BFILE数据的插入

与BLOB类型基本相似，下面只贴出核心代码

OracleConnection _conn = new OracleConnection(connString);
_conn.Open();
OracleCommand _cmd = _conn.CreateCommand();
_cmd.CommandText = "INSERT INTO PRODUCTFILES(PRODUCTID,FILEATTACHMENT2) " +
    "VALUES(:v_ProductId,BFILENAME('PRODUCTFILESFOLDER',:v_FileName))";
_cmd.Parameters.Add(new OracleParameter("v_ProductId","E1"));
OracleBlob _image = new OracleBlob(_conn);
_image.Write(_imageStream, 0, _imageStream.Length);
_cmd.Parameters.Add(new OracleParameter("v_FileName", "icon.jpg"));
int _result = _cmd.ExecuteNonQuery();

BFILE数据的获取

if(_reader.HasRows)
{
    if(_reader.Read())
    {
        OracleBFile _image = _reader.GetOracleBFile(_reader.GetOrdinal("FILEATTACHMENT2"));
        if(_image.FileExists)
        {
            pictureBox.Image = Image.FromStream(new System.IO.MemoryStream(_image.Value));
        }
        Console.WriteLine("The name of the file is"+_image.FileName);
    }
}

RAW类型

RAW类型用来存储定长字节的数据

RAW数据的插入

OracleParameter _para = new OracleParameter("GUID", OracleDbType.Raw);
_para.Value = System.Guid.NewGuid().ToByteArray();
_cmd.Parameters.Add(_para);

RAW数据的获取

if(_reader.HasRows)
{
     if(_reader.Read())
     {
         OracleBinary _binary = _reader.GetOracleBinary(_reader.GetOrdinal("GUID"));
         System.Guid _guid = new Guid(_binary.Value);
         Console.WriteLine("The GUID is" + _guid.ToString());
     }
 }