http://topic.csdn.net/u/20091015/11/5f41b718-7918-4992-8795-2cccc5e957e8.html?seed=1756713731&r=60435954#r_60435954等级不够只能发100分,上面这个贴还有50分。不说废话了,请看代码,问题就是下面的注释处string sql = @"select POINT_SET from WELLBORE_LOG where ID='1' For Update";
OracleConnection oraOc = new OracleConnection("Integrated Security=no;User ID=ADMIN;Data Source=MyBASE;Password=ADMIN");
oraOc.Open();
OracleCommand ocmd = new OracleCommand(sql, oraOc);
OracleDataReader oraReader = ocmd.ExecuteReader();
using (oraReader)
{
if (oraReader.HasRows)
{
oraReader.Read();
OracleLob clob = oraReader.GetOracleLob(0);
//这里得到的clob.Connection 总是为null,非常让人郁闷,如何解决
if (clob.Connection != null && clob != OracleLob.Null)
{
clob.Write(bytes, 0, bytes.Length);
}
clob.Position = 0;
ocmd.Transaction.Commit();
}
}
OracleConnection oraOc = new OracleConnection("Integrated Security=no;User ID=ADMIN;Data Source=MyBASE;Password=ADMIN");
oraOc.Open();
OracleCommand ocmd = new OracleCommand(sql, oraOc);
OracleDataReader oraReader = ocmd.ExecuteReader();
using (oraReader)
{
if (oraReader.HasRows)
{
oraReader.Read();
OracleLob clob = oraReader.GetOracleLob(0);
//这里得到的clob.Connection 总是为null,非常让人郁闷,如何解决
if (clob.Connection != null && clob != OracleLob.Null)
{
clob.Write(bytes, 0, bytes.Length);
}
clob.Position = 0;
ocmd.Transaction.Commit();
}
}
http://forum.csdn.net/SList/Oracle_Develop
clob.Connection值就是null
select POINT_SET from WELLBORE_LOG where ID='1' For Update
这个语句是干什么用的,为什么要加上for update?
直接select POINT_SET from WELLBORE_LOG where ID='1'不行吗?
string sql = @"select POINT_SET from WELLBORE_LOG where ID='1' For Update";
using (OracleConnection con = new OracleConnection("Integrated Security=no;User ID=ADMIN;Data Source=MyBASE;Password=ADMIN"))
{
using(OracleCommand ocmd = new OracleCommand(sql, con))
{
OracleDataReader oraReader = ocmd.ExecuteReader();
if (oraReader.Read())
{
OracleLob clob = (OracleLob)oraReader[0];
if (clob.Connection != null && clob != OracleLob.Null)
{
clob.Write(bytes, 0, bytes.Length);
}
clob.Position = 0;
ocmd.Transaction.Commit();
}
}
}不知道对不对。
有可能是这个类实例初始化问题
public static void WriteLobExample(OracleCommand cmd)
{
//Note: Updating LOB data requires a transaction.
cmd.Transaction = cmd.Connection.BeginTransaction();
// Select some data.
// Table Schema:
// "CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";
// "INSERT INTO tablewithlobs values (1, 'AA', 'AAA', N'AAAA')";
cmd.CommandText = "SELECT * FROM tablewithlobs FOR UPDATE";
OracleDataReader reader = cmd.ExecuteReader();
using(reader)
{
// Obtain the first row of data.
reader.Read(); // Obtain a LOB.
OracleLob blob = reader.GetOracleLob(1/*0:based ordinal*/); // Example - Writing binary data (directly to the backend).
// (for example, even number of bytes - 2 bytes for every
// Unicode character).
byte[] buffer = new byte[100];
buffer[0] = 0xCC;
buffer[1] = 0xDD;
blob.Write(buffer, 0, 2);
blob.Position = 0;
Console.WriteLine(blob.LobType + ".Write(
" + buffer + ", 0, 2) => " + blob.Value);
// Example - Obtaining a temp LOB and copying data
// into it from another LOB.
OracleLob templob = CreateTempLob(cmd, blob.LobType);
long actual = blob.CopyTo(templob);
Console.WriteLine(blob.LobType + ".CopyTo(
" + templob.Value + ") => " + actual); // Commit the transaction now that everything succeeded.
// Note: On error, Transaction.Dispose is called
// (from the using statement)
// and will automatically roll back the pending transaction.
cmd.Transaction.Commit();
}
}
你的 BeginTransaction 在哪呢?
/**//// <summary>
/// 文件写入到 Oracle Blob 字段中。
/// </summary>
/// <param name="idData">id 值</param>
/// <param name="fileName">文件名</param>
/// <param name="id">id 键</param>
/// <param name="blob">blob 键</param>
/// <param name="tableName">表名</param>
public void WriteBlob(int idData, string fileName, string id, string blob, string tableName)
{
string connString = "server=oratest;User ID=kttest;Password=test";
using(conn = new OracleConnection(connString))
{
try
{
conn.Open();
OracleCommand cmd = conn.CreateCommand(); // 利用事务处理(必须)
OracleTransaction transaction = cmd.Connection.BeginTransaction();
cmd.Transaction = transaction; // 获得 OracleLob 指针
cmd.CommandText = "select " + blob + " from " + tableName + " where " + id + " = " + idData + " FOR UPDATE";
OracleDataReader reader = cmd.ExecuteReader();
using(reader)
{
//Obtain the first row of data.
reader.Read();
//Obtain a LOB.
OracleLob tempLob = reader.GetOracleLob(0); // 将文件写入 BLOB 中
FileStream fs = new FileStream(fileName,FileMode.Open);
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
int length = 10485760;
byte[] Buffer = new byte[length];
int i;
while((i = fs.Read(Buffer,0,length)) > 0)
{
tempLob.Write(Buffer,0,i);
}
fs.Close();
tempLob.EndBatch();
cmd.Parameters.Clear();
}
// 提交事务
transaction.Commit();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
}
2、更新或者插入时,采用OracleCommand:
a) OracleCommand.CommandText:就当大字段列为普通列那样则和基本的SQL语句,其中,大字段列的值赋值为":clm1"格式,即带冒号的一个参数,参数名自己随便起。
b) 使用OracleCommand.Parameters.Add(【有几个参数】)方式为上述冒号参数逐一设置关联值;
c) 调用OracleCommand.ExecuteNonQuery()方法执行入库;附数据类型对照:(直接从代码里面拷贝的,请或略case部分,没整理,见谅) case "LONG":
drInfo["ParameterType"] = OracleType.LongVarChar;
drInfo["CSharpType"] = typeof(System.String);
break;
case "FLOAT":
drInfo["ParameterType"] = OracleType.Float;
drInfo["CSharpType"] = typeof(float);
break;
case "NVARCHAR2":
drInfo["ParameterType"] = OracleType.NVarChar;
drInfo["CSharpType"] = typeof(System.String);
break;
case "NUMBER":
drInfo["ParameterType"] = OracleType.Number;
drInfo["CSharpType"] = typeof(System.Decimal);
break;
case "INTEGER":
drInfo["ParameterType"] = OracleType.Int32;
drInfo["CSharpType"] = typeof(System.Int32);
break;
case "CHAR":
drInfo["ParameterType"] = OracleType.Char;
drInfo["CSharpType"] = typeof(System.String);
break;
case "CLOB":
drInfo["ParameterType"] = OracleType.Clob;
drInfo["CSharpType"] = typeof(System.String);
break;
case "NCHAR":
drInfo["ParameterType"] = OracleType.NChar;
drInfo["CSharpType"] = typeof(System.String);
break;
case "DATE":
drInfo["ParameterType"] = OracleType.DateTime;
drInfo["CSharpType"] = typeof(System.DateTime);
break;
case "VARCHAR2":
drInfo["ParameterType"] = OracleType.VarChar;
drInfo["CSharpType"] = typeof(System.String);
break;
case "BLOB":
drInfo["ParameterType"] = OracleType.Blob;
drInfo["CSharpType"] = typeof(System.Byte[]);
break;
case "LONG RAW":
drInfo["ParameterType"] = OracleType.LongRaw;
drInfo["CSharpType"] = typeof(System.Byte[]);
break;
case "RAW":
drInfo["ParameterType"] = OracleType.Raw;
drInfo["CSharpType"] = typeof(System.Byte[]);
break;
就当大字段列为普通列那样则和基本的SQL语句-->>就当大字段列为普通列那样整和基本的SQL语句
【MSDN】
Add(String, OracleType, Int32, String) 在给定参数名称、数据类型、列长和源列名称的情况下,将 OracleParameter 添加到 OracleParameterCollection 中。 这里的“参数名称”即带冒号的自定义参数名,“数据类型”为OracleType枚举值,“列长”不太清楚具体含义,用法为如果要入库的值为byte[]、string即为其Length,DBNull.Value则为1,其他类型可设置为0也无妨;“源列名称”为数据库中(也即SQL语句中)该参数对应的列名。参见我在29楼附的类型对应表,应该不难找到使用的方法。以上用法我在实际项目中就是这么用的,保证可行性。另外顺带说明一下,个人不喜欢Reader的方式访问数据库,一般查询就直接用DataTable接着,入库就组装DbCommand。
{
try
{
string strCon = "";
OracleConnection Con = new System.Data.OracleClient.OracleConnection(strCon);
Con.Open(); string cmdText = " update " + strTable + " set " + strFieldName + " = :pb " + strWhere;
OracleCommand cmd = new OracleCommand(cmdText, Con);
OracleParameter op = new OracleParameter("pb", OracleType.Clob);
op.Value = strText;
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();
Con.Close();
}
catch (Exception ex)
{
return ex.Message;
}
return "";
} OracleLob lob;
OracleTransaction ts= null;
OracleConnection conn = null;
OracleCommand cmd = null;
OracleDataReader dr = null;
string strSql = string.Empty;
string content = string.Empty;
string CONNECTSTRING = "";
try
{
conn = new OracleConnection(CONNECTSTRING);
conn.Open();
ts= conn.BeginTransaction();
cmd = new OracleCommand(strSql,conn, ts);
cmd.CommandText = "";
dr = cmd.ExecuteReader();
dr.Read();
while(dr.Read())
{
lob = dr.GetOracleLob(0);
if(lob!=OracleLob.Null)
{
content = lob.Value.ToString();
content = "";
byte[] buffer = System.Text.Encoding.Unicode.GetBytes(content);
lob.Write(buffer, 0, buffer.Length);
}
}
ts.Commit();
}
catch(Exception ex)
{
}
finally
{
dr.Close();
conn.Close();
cmd.Dispose();
}
参见
drInfo["CSharpType"] = typeof(System.String);
// WriteLobExample
public static void WriteLobExample(OracleCommand cmd)
{
//Note: Updating LOB data requires a transaction.
cmd.Transaction = cmd.Connection.BeginTransaction();
// Select some data.
// Table Schema:
// "CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";
// "INSERT INTO tablewithlobs values (1, 'AA', 'AAA', N'AAAA')";
cmd.CommandText = "SELECT * FROM tablewithlobs FOR UPDATE";
OracleDataReader reader = cmd.ExecuteReader();
using(reader)
{
// Obtain the first row of data.
reader.Read(); // Obtain a LOB.
OracleLob blob = reader.GetOracleLob(1/*0:based ordinal*/); // Perform any desired operations on the LOB
// (read, position, and so on). // Example - Writing binary data (directly to the backend).
// To write, you can use any of the stream classes, or write
// raw binary data using
// the OracleLob write method. Writing character vs. binary
// is the same;
// however note that character is always in terms of
// Unicode byte counts
// (for example, even number of bytes - 2 bytes for every
// Unicode character).
byte[] buffer = new byte[100];
buffer[0] = 0xCC;
buffer[1] = 0xDD;
blob.Write(buffer, 0, 2);
blob.Position = 0;
Console.WriteLine(blob.LobType + ".Write(
" + buffer + ", 0, 2) => " + blob.Value);
// Example - Obtaining a temp LOB and copying data
// into it from another LOB.
OracleLob templob = CreateTempLob(cmd, blob.LobType);
long actual = blob.CopyTo(templob);
Console.WriteLine(blob.LobType + ".CopyTo(
" + templob.Value + ") => " + actual); // Commit the transaction now that everything succeeded.
// Note: On error, Transaction.Dispose is called
// (from the using statement)
// and will automatically roll back the pending transaction.
cmd.Transaction.Commit();
}
}
=========================================================================
//这里得到的clob.Connection 总是为null,非常让人郁闷,如何解决
if (clob.Connection != null && clob != OracleLob.Null)
{
clob.Write(bytes, 0, bytes.Length);
}
clob.Position = 0;
ocmd.Transaction.Commit();
}
}
===========================================================
这里的顺序是不是有点问题啊,为什么要先判断clob.Connection != null
先看看clob有没有读出来再说吧
把原来的delete,然后insert新的不就行了么。这样简单多了。
clob字段很麻烦。