using System;
using System.Data;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
![](/Images/OutliningIndicators/None.gif)
namespace Sample77
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
// 演示通过结果集的锁顶来更新 LOB 数据
class Program
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
static void Main(string[] args)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
// Connect
string constr = "User Id=scott;Password=tiger;Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.24)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = bjoracle.oracle10g.mynet)))";
OracleConnection conn = Connect(constr);
![](/Images/OutliningIndicators/InBlock.gif)
if (conn == null)
return;
![](/Images/OutliningIndicators/InBlock.gif)
// Setup
Setup(conn);
![](/Images/OutliningIndicators/InBlock.gif)
// BeginTransaction
OracleTransaction txn = conn.BeginTransaction();
OracleCommand cmd = new OracleCommand("", conn);
![](/Images/OutliningIndicators/InBlock.gif)
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
// Lock the result set using FOR UPDATE clause
cmd.CommandText = "select story from multimedia_tab for update";
![](/Images/OutliningIndicators/InBlock.gif)
OracleDataReader reader = cmd.ExecuteReader();
reader.Read();
OracleClob clob = reader.GetOracleClob(0);
Console.WriteLine("Old Data: {0}", clob.Value);
![](/Images/OutliningIndicators/InBlock.gif)
// Modify the clob column of the row
string ending = " The end.";
clob.Append(ending.ToCharArray(), 0, ending.Length);
![](/Images/OutliningIndicators/InBlock.gif)
// Release the lock
txn.Commit();
![](/Images/OutliningIndicators/InBlock.gif)
// Fetch the new data; transaction or locking not required.
cmd.CommandText = "select story from multimedia_tab";
reader = cmd.ExecuteReader();
while (reader.Read())
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
clob = reader.GetOracleClob(0);
Console.WriteLine("New Data: {0}", clob.Value);
}
}
catch (Exception ex)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Console.WriteLine("Error: {0}", ex.Message);
}
finally
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
// Dispose OracleCommand object
cmd.Dispose();
![](/Images/OutliningIndicators/InBlock.gif)
// Close and Dispose OracleConnection object
conn.Close();
conn.Dispose();
}
![](/Images/OutliningIndicators/InBlock.gif)
// Waiting
Console.ReadLine();
}
![](/Images/OutliningIndicators/InBlock.gif)
// Opening a new Connection
public static OracleConnection Connect(string connectStr)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
OracleConnection conn = new OracleConnection(connectStr);
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Open();
}
catch (Exception ex)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Console.WriteLine("Error: {0}", ex.Message);
conn = null;
}
return conn;
}
![](/Images/OutliningIndicators/InBlock.gif)
// Setup the necessary Tables & Test Data
public static void Setup(OracleConnection conn)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
StringBuilder blr;
OracleCommand cmd = new OracleCommand("", conn);
![](/Images/OutliningIndicators/InBlock.gif)
blr = new StringBuilder();
blr.Append("DROP TABLE multimedia_tab");
cmd.CommandText = blr.ToString();
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Console.WriteLine("Warning: {0}", ex.Message);
}
![](/Images/OutliningIndicators/InBlock.gif)
blr = new StringBuilder();
blr.Append("CREATE TABLE multimedia_tab(thekey NUMBER(4) PRIMARY KEY,");
blr.Append("story CLOB, sound BLOB)");
cmd.CommandText = blr.ToString();
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Console.WriteLine("Error: {0}", ex.Message);
}
![](/Images/OutliningIndicators/InBlock.gif)
blr = new StringBuilder();
blr.Append("INSERT INTO multimedia_tab values(");
blr.Append("1,");
blr.Append("'This is a long story.Once upon a time
',");
blr.Append("'898787787874454654564578978971114544897')");
cmd.CommandText = blr.ToString();
![](/Images/OutliningIndicators/InBlock.gif)
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Console.WriteLine("Error: {0}", ex.Message);
}
![](/Images/OutliningIndicators/InBlock.gif)
blr = new StringBuilder();
blr.Append("INSERT INTO multimedia_tab values(");
blr.Append("2,");
blr.Append("'This is the second long story.Once upon a time
',");
blr.Append("'898787787874454654564578978971114544897')");
cmd.CommandText = blr.ToString();
![](/Images/OutliningIndicators/InBlock.gif)
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Console.WriteLine("Error: {0}", ex.Message);
}
}
}
}
注意:
1、创建OracleTransaction对象
2、利用 for update 条款来锁定结果集:cmd.CommandText = "select story from multimedia_tab for update";
3、更新结果集中的第一条记录 reader.Read(); clob.Append(ending.ToCharArray(), 0, ending.Length);
4、提交事务,并释放锁:txn.Commit();
5、循环显示结果:while (reader.Read())
引用:
ODP.NET SAMPLE