Converting a large CLOB object to .NET string to put into a DataGridView cell

Question

I have created a Windows Forms GUI in C# that can display tabular data (results of an SQL query against an Oracle Server DB) in a DataGridView. One of the field is an XML, potentially quite large, stored as a CLOB (Character Large OBject if I'm right). Of course, the user will not directly look at the cell, he will rather double-click the cell to see the file pretty-printed. The problem is that I need to convert the file (which is a CLOB) to a .NET String otherwise it generates an exception. I have tried, as a workaround, to use the Oracle to_char procedure, but it is limited to 4000 characters. So I take a substring of the file like this:

 select to_char(dbms_lob.substr(column_name, 4000, 1 ))

The problem is, it doesn't display the whole file if it contains more than 4000 characters. How could I circumvent this limitation?

hometoast · Accepted Answer · 2012-05-24 11:21:39Z

3

Don't store the CLOB, or the resultant string in the DataGridView.

Instead, capture the click event in the DataGridView then convert the CLOB to a string for viewing. Use an appropriate encoding from System.Text.Encoding.

I'm assuming your DataReader or DataAdapter (whichever way you're filling the DataSet) will store your CLOB in a byte-array. (As it is with SQLServer and Informix drivers).

byte[] clob;
// get it from your datarow/datagridview bound item
string thexml = System.Text.Encoding.UTF8.GetString(theclob)

edited May 24, 2012 at 11:21

answered May 24, 2012 at 11:10

hometoast

11.8k5 gold badges43 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pierre Gardin Over a year ago

And how do you populate the byte array from the OracleDataReader object? There is a getOracleLob() method but no getOracleClob()...

hometoast Over a year ago

I'm sorry -- I can't be specific to the Oracle driver as I've never used it.

Doug Porter · Accepted Answer · 2012-11-11 17:17:39Z

2

I used the simple:

if (reader.IsDBNull(i))
{
    cellValue = "NULL";
}
else
{
    OracleLob clob = reader.GetOracleLob(i);
    cellValue  = (string) clob.Value;
}

edited Nov 11, 2012 at 17:17

Doug Porter

7,9174 gold badges42 silver badges56 bronze badges

answered May 30, 2012 at 11:58

Pierre Gardin

6841 gold badge8 silver badges23 bronze badges

1 Comment

Pierre Gardin Over a year ago

It's too slow...is it possible to convert it to string on the server side?

Collectives™ on Stack Overflow

Converting a large CLOB object to .NET string to put into a DataGridView cell

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related