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); ...
Get ADO.NET 3.5 Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.