110,534
社区成员
发帖
与我相关
我的任务
分享
using System;
using System.Collections.Generic;
using System.Text;
// using oracle ado.net instead of MS ado.net for oracle
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace oracledemo
{
class TestOracle
{
OracleConnection _conn;
public void test()
{
string connstr = "User Id=scott;Password=tiger;Data Source=ora102";
try
{
_conn = new OracleConnection(connstr);
_conn.Open();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
return;
}
// setup
StringBuilder blr;
OracleCommand cmd = new OracleCommand("", _conn);
blr = new StringBuilder();
blr.Append("DROP TABLE TBExam");
cmd.CommandText = blr.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Warning: {0}", e.Message);
}
blr = new StringBuilder();
blr.Append("CREATE TABLE TBExam(ID varchar(32) PRIMARY KEY,");
blr.Append("story CLOB, Result BLOB)");
cmd.CommandText = blr.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e.Message);
}
blr = new StringBuilder();
blr.Append("INSERT INTO TBExam values(");
blr.Append("'001',");
blr.Append("'This is a long story. Once upon a time ...',");
blr.Append("empty_blob())");
cmd.CommandText = blr.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e.Message);
}
// update the blob value
string res = "终于,写进来了";
byte[] b = System.Text.Encoding.Unicode.GetBytes(res);
cmd.Dispose();
OracleTransaction txn = _conn.BeginTransaction();
cmd = new OracleCommand("", _conn);
try
{
cmd.CommandText = "select result from TBExam where id='001' for update";
OracleDataReader reader = cmd.ExecuteReader();
reader.Read();
OracleBlob blob = reader.GetOracleBlob(0);
blob.Write(b, 0, b.Length);
blob.Flush();
txn.Commit();
cmd.CommandText = "select result from TBExam where id='001'";
reader = cmd.ExecuteReader();
reader.Read();
blob = reader.GetOracleBlob(0);
byte[] bout = new byte[blob.Length];
blob.Read(bout, 0, bout.Length);
Console.WriteLine("blob length = " + blob.Length);
Console.WriteLine("result = " + new string(System.Text.Encoding.Unicode.GetChars(bout)) );
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e.Message);
}
finally
{
cmd.Dispose();
_conn.Close();
_conn.Dispose();
}
}
static void Main(string[] args)
{
new TestOracle().test();
}
}
}
using (OracleCommand cmd = new OracleCommand())
{
OracleConnection connection = conn as OracleConnection;
OracleTransaction transaction = connection.BeginTransaction();
cmd.Connection = connection;
cmd.Transaction = transaction;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;";
cmd.Parameters.Add(new OracleParameter("tempblob", OracleType.Blob)).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
OracleLob tempLob = (OracleLob)cmd.Parameters[0].Value;
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
byte[] tempbuff = System.Text.Encoding.Unicode.GetBytes("终于,写进来了");
tempLob.Write(tempbuff, 0, tempbuff.Length);
tempLob.EndBatch();
OracleParameter p1 = new OracleParameter("b",tempLob);
cmd.Parameters.Clear();
cmd.Parameters.Add(p1);
cmd.CommandText = "update TBExam set Result=:b where ID=001";
cmd.ExecuteNonQuery();
tempLob.Dispose();
transaction.Commit();
}