(原) ODP.NET 演示通过结果集的锁顶来更新 LOB 数据
using System;
using System.Data;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace Sample77
{
// 演示通过结果集的锁顶来更新 LOB 数据
class Program
{
static void Main(string[] args)
{
// 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);
if (conn == null)
return;
// Setup
Setup(conn);
// BeginTransaction
OracleTransaction txn = conn.BeginTransaction();
OracleCommand cmd = new OracleCommand("", conn);
try
{
// Lock the result set using FOR UPDATE clause
cmd.CommandText = "select story from multimedia_tab for update";
OracleDataReader reader = cmd.ExecuteReader();
reader.Read();
OracleClob clob = reader.GetOracleClob(0);
Console.WriteLine("Old Data: {0}", clob.Value);
// Modify the clob column of the row
string ending = " The end.";
clob.Append(ending.ToCharArray(), 0, ending.Length);
// Release the lock
txn.Commit();
// Fetch the new data; transaction or locking not required.
cmd.CommandText = "select story from multimedia_tab";
reader = cmd.ExecuteReader();
while (reader.Read())
{
clob = reader.GetOracleClob(0);
Console.WriteLine("New Data: {0}", clob.Value);
}
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
finally
{
// Dispose OracleCommand object
cmd.Dispose();
// Close and Dispose OracleConnection object
conn.Close();
conn.Dispose();
}
// Waiting
Console.ReadLine();
}
// Opening a new Connection
public static OracleConnection Connect(string connectStr)
{
OracleConnection conn = new OracleConnection(connectStr);
try
{
conn.Open();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
conn = null;
}
return conn;
}
// Setup the necessary Tables & Test Data
public static void Setup(OracleConnection conn)
{
StringBuilder blr;
OracleCommand cmd = new OracleCommand("", conn);
blr = new StringBuilder();
blr.Append("DROP TABLE multimedia_tab");
cmd.CommandText = blr.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Warning: {0}", ex.Message);
}
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
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
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();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
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();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
}
}
}
using System.Data;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace Sample77
{
// 演示通过结果集的锁顶来更新 LOB 数据
class Program
{
static void Main(string[] args)
{
// 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);
if (conn == null)
return;
// Setup
Setup(conn);
// BeginTransaction
OracleTransaction txn = conn.BeginTransaction();
OracleCommand cmd = new OracleCommand("", conn);
try
{
// Lock the result set using FOR UPDATE clause
cmd.CommandText = "select story from multimedia_tab for update";
OracleDataReader reader = cmd.ExecuteReader();
reader.Read();
OracleClob clob = reader.GetOracleClob(0);
Console.WriteLine("Old Data: {0}", clob.Value);
// Modify the clob column of the row
string ending = " The end.";
clob.Append(ending.ToCharArray(), 0, ending.Length);
// Release the lock
txn.Commit();
// Fetch the new data; transaction or locking not required.
cmd.CommandText = "select story from multimedia_tab";
reader = cmd.ExecuteReader();
while (reader.Read())
{
clob = reader.GetOracleClob(0);
Console.WriteLine("New Data: {0}", clob.Value);
}
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
finally
{
// Dispose OracleCommand object
cmd.Dispose();
// Close and Dispose OracleConnection object
conn.Close();
conn.Dispose();
}
// Waiting
Console.ReadLine();
}
// Opening a new Connection
public static OracleConnection Connect(string connectStr)
{
OracleConnection conn = new OracleConnection(connectStr);
try
{
conn.Open();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
conn = null;
}
return conn;
}
// Setup the necessary Tables & Test Data
public static void Setup(OracleConnection conn)
{
StringBuilder blr;
OracleCommand cmd = new OracleCommand("", conn);
blr = new StringBuilder();
blr.Append("DROP TABLE multimedia_tab");
cmd.CommandText = blr.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Warning: {0}", ex.Message);
}
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
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
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();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
}
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();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
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
posted on 2007-12-28 10:25 mjgforever 阅读(379) 评论(0) 编辑 收藏 举报