ĵͨ ODP.NET ʹý LOB
ĵͨ ODP.NET ʹý LOB

ڣ2004 10 11

Ŀ

Ķ˷ĵӦܹ

  • ѡ͸ Oracle ݿе LOB

  • ʹ ODP.NET Ľ

Ⱦ

Ϥ MS Visual Studio.NET˽ ODP.NET ݿĻ֪ʶ

ODP.NET ṩһּ򵥵ѵķʽʺʹ LobODP.NET ṩڲ LOB ݵ LOB 󣬼 OracleBlobOracleClob OracleBfileҪʹЩ LOBҪȣѡȡ LOB ֮ǰһΣפбзʽΪһֱ

ĵʾʹý LOB ݡͨ OracleCommand ɡLOB Ϊ󶨵 SQL 䡣ɹݿ֮󣬼һ CLOB еݿñɳʼ CLOB 䡣ϵͳһ OracleTransactionݿмҪµСʹ SELECT ..FOR UPDATE ʹ OracleDataReader иµ OracleClob Ͷлü CLOB ʹ OracleClob.append ޸е CLOB ݡ OracleClob и֮ϵͳύʹ read OracleDataReader ڿ̨ʾݿѾµݡʹ OracleClob.erase Ѿ޸ĵݡȻʹ OracleClob.write д clob С

ֵλ

һӦóͿʹּеһ — ӦЩڸ LOB ʱκ“”д

  • ʹ٣ҲΣգΪܱ“”д

  • ммضСȻ“”Ҫȫö࣬ȫֹ“”дҪעǣ˶УôмȽٵá

  • ڷֹ“”дڴϳܲ⡣

ݿ

˷ĵʹ multimedia_tab ʹ SQL*Plus ûӵݿ⣨ scott/tigerȻ

DROP TABLE multimedia_tab;

CREATE TABLE multimedia_tab(thekey NUMBER(4) PRIMARY KEY, story CLOB, sound BLOB);


INSERT INTO multimedia_tab values(1,'This is a long story.Once upon a time ...',
                                    '656667686970717273747576777879808182838485');

commit;

Ԥ

ռ䣺 .cs .vb ļе‘һ’Ӷռ÷dzֵãɱԺڽű޶ʹã

using System;
using System.Text;
using Oracle.DataAccess.Types;
using Oracle.DataAccess.Client;
Imports System
Imports System.Text
Imports Oracle.DataAccess.Types
Imports Oracle.DataAccess.Client

1. һݿӣ

//  1 
// ʹ ODP.NET 
// ע⣺ݿ
// ޸û IdԴ
string connectStr = "User Id=Scott;Password=tiger;Data Source=orcl9i";

OracleConnection connection = new OracleConnection(connectStr);
connection.Open();
Console.WriteLine("connected to database");
Console.WriteLine(" ");
'  1 
' ʹ ODP.NET 
' ע⣺ݿ
' ޸û IdԴ
Dim connectStr As String = "User Id=Scott;Password=tiger;Data Source=orcl9i"

' ʼ
Dim connection As OracleConnection = New OracleConnection(connectStr)
connection.Open()
Console.WriteLine("connected to database")
Console.WriteLine(" ")

2. Oracle

//  2 
//  Oracle 
OracleTransaction txn;
OracleCommand cmd = new OracleCommand("",connection);
OracleDataReader reader;
OracleClob clob;
'  2 
'  Oracle 
Dim txn As OracleTransaction
Dim cmd As OracleCommand = New OracleCommand("", connection)
Dim reader As OracleDataReader
Dim clob As OracleClob

3. κ LOB ¶Ҫһ Oracle ԣ濪ʼ

//  3 
// 

txn = connection.BeginTransaction();	
'  3 
' 
txn = connection.BeginTransaction()
			

4. ʹ FOR UPDATE ڸµȻִ䣺

//  4 
// ʹ FOR UPDATE Ӿ
cmd.CommandText = "SELECT story FROM multimedia_tab FOR UPDATE";

reader = cmd.ExecuteReader();
'  4 
' ʹ FOR UPDATE Ӿ
cmd.CommandText = "SELECT story FROM multimedia_tab FOR UPDATE"

reader = cmd.ExecuteReader()

5. ݶ Oracle LOB

//  5 
// ʹȷ Oracle LOB Ĵȡ
// ȡ OracleDataReader е
reader.Read();
clob = reader.GetOracleClob(0);
Console.WriteLine("Old Data:{0}", clob.Value);
Console.WriteLine(" ");
'  5 
' ʹȷ Oracle LOB Ĵȡ
' ȡ OracleDataReader е
reader.Read()
clob = reader.GetOracleClob(0)
Console.WriteLine("Old Data:{0}", clob.Value)
Console.WriteLine(" ")
		

6. ޸Ȼύ OracleCLOB ǰеݡԶύͷŴ

//  6 
// ޸ĸе CLOB 
string ending = " The end.";

//  CLOB ׷ӵǰ OracleCLOB ʵ
clob.Append(ending.ToCharArray(), 0, ending.Length);

// ͷ
txn.Commit();
Console.Write("Updated to new data:");
'  6 
' ޸ĸе CLOB 
Dim ending As String = " The end."

'  CLOB ׷ӵǰ OracleCLOB ʵ
clob.Append(ending.ToCharArray(), 0, ending.Length)

' ͷ

txn.Commit()
Console.Write("Updated to new data:")

7. ݿȡ޸˵Ȼʾڿ̨ϣ

//  7 
// ݿȡ޸ĵ
// ʹ FOR UPDATE Ӿ
cmd.CommandText = "SELECT story FROM multimedia_tab FOR UPDATE";

reader = cmd.ExecuteReader();
reader.Read();
clob = reader.GetOracleClob(0);
Console.WriteLine(clob.Value);
Console.WriteLine(" ");
'  7 
' ݿȡ޸ĵ
' ʹ FOR UPDATE Ӿ
cmd.CommandText = "SELECT story FROM multimedia_tab FOR UPDATE"

reader = cmd.ExecuteReader()
reader.Read()
clob = reader.GetOracleClob(0)
Console.WriteLine(clob.Value)
Console.WriteLine(" ")

8. CLOB ĵǰʵΪݣ

//  8 
// ʹþֵ
//  Oracle 
txn = connection.BeginTransaction();

//  OracleCLOB
// ǰʵ
clob.Erase();

// ַдΪֵ
StringBuilder blr1 = new StringBuilder();
blr1.Append("'This is a long story.Once upon a time ...',");

String oldData = blr1.ToString();
'  8 
' ʹþֵ
'  Oracle 
txn = connection.BeginTransaction()



'  OracleCLOB
' ǰʵ
clob.Erase()


' ַдΪֵ
Dim blr1 As StringBuilder = New StringBuilder()
blr1.Append("'This is a long story.Once upon a time ...',")

Dim oldData As String = blr1.ToString()

9. ύԶͷŴڿ̨ʾݣ

//  9 
// ֽд OracleCLOB 
clob.Write(oldData.ToCharArray(),0, oldData.Length);


// ݡͷ
txn.Commit();

Console.WriteLine("Old Data again:{0}", clob.Value);
'  9 
' ֽд OracleCLOB 
clob.Write(oldData.ToCharArray(), 0, oldData.Length)


' ݡͷ
txn.Commit()

Console.WriteLine("Old Data again:{0}", clob.Value)

òд˷ĵĿ

1. Visual Studio.NET

2. ̨ӦóĿ

C# һ̨ӦóĿĬϵͳ Class1.cs ӵĿС
Visual Basic .NET ̨ӦóĿĬϵͳ Module1.vb ӵĿС

3. ȷĿ SystemOracle.DataAccess System.Data ռáЩòڣӶЩռá

4. ƴ룺

ʹ Solution Explorer Class1.csйΪ˷ C# дĴ嵥ƴ˴벢 Class1.cs ݡ

ʹ Solution Explorer Module1.vbйΪ˷ VB.NET дĴ嵥ƴ˴벢 Module1.vb ݡ

5. ĵ 1 еݿ޸û IdԴ

6. Ҫ벢дӦó밴 Ctrl+F5⽫ʾݺݣͼ 1.1 ʾ


ͼ 1.1 – Ļͼ

Դ


OTN ʾ̳йش˷ĵ

寄送此页面
Printer View 打印机视图