C # Read and Write Oracle Clob objects

The following are two implementation methods:

Method 1:

This method writes a txt file larger than 4 kb to a Clob object or reads it from the Clob field and creates a txt file to save the operation. These operations are achieved through multipart reading.

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 ();
}
}

 

Method 2:

In fact, Oracle in the ODAC112011beta plug-in officially provided by Oracle. the DataAccess namespace provides..

 

Method 2: Read the large Clob field (greater than 4 kb) from the Oracle database, return a able, and store the txt file greater than 4 kb into the Clob field in the Oracle database.

 

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 ();
}
}

The preceding Code omitting the definitions of connection, adapter, and command. The input query statements are simple Select statements such as "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 ();
}
}

The input SQL statement is "insert into TableName values (0,: CLOBCOL,: CLOBCOL2,: CLOBCOL3,: CLOBCOL4 )"