ADO.NET 3.5 Cookbook, 2nd Edition

10.17. Reading and Writing Oracle Large Data

Problem

You need to read and write large data type values from and to an Oracle database.

Solution

The solution shows how to store, retrieve, and output Oracle BLOB, CLOB, and NCLOB lengths and values from both a DataTable and a DataReader.

The solution uses a single table ReadWriteLargeData in the AdoDotNet35Cookbook schema. Execute the following SQL statement to create the table:

	CREATE TABLE "ADODOTNET35COOKBOOK"."READWRITELARGEDATA" (
	  "ID" INTEGER NOT NULL,
	  "BLOBFIELD" BLOB,
	  "CLOBFIELD" CLOB,
	  "NCLOBFIELD" NCLOB,
	  PRIMARY KEY ("ID") VALIDATE )

You need to add a reference to the System.Data.OracleClient assembly to the solution.

The C# code in Program.cs in the project ReadWriteLargeOracleData is shown in Example 10-28.

Example 10-28. File: Program.cs for ReadWriteLargeOracleData solution

using System; using System.Data; using System.Data.OracleClient; using System.Text; using System.IO; namespace ReadWriteLargeOracleData { class Program { static void Main(string[] args) { string oracleConnectString = "Data Source=ORCL;" + "User Id=AdoDotNet35Cookbook;Password=password;"; string sqlSelect = "SELECT ID, BLOBFIELD, CLOBFIELD, NCLOBFIELD " + "FROM ADODOTNET35COOKBOOK.READWRITELARGEDATA"; // create DataTable and fill with schema and data OracleDataAdapter da = new OracleDataAdapter(sqlSelect, oracleConnectString); OracleCommandBuilder cb = new OracleCommandBuilder(da); DataTable dt = new DataTable(); da.FillSchema(dt, SchemaType.Source); da.Fill(dt); ...