C # Read and Write Oracle Clob objects

Source: Internet
Author: User
Tags oracleconnection

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 )"

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.