使用OracleClient操作CLOB字段~

今天在做数据库安装包,碰到一个Oracle的SQL问题,直接用Insert语句插入超过4000的字符时会报错,不管用什么字段类型(VARCHAR2\LONG\CLOB都试了,不能解决问题),在dev-club数据库版上问了一下,潇湘剑公子说Oracle是不能直接插入4000以上字符串的,并介绍了一个以前asp语法的插入4000以上字符的文章,不过我需要的是.net版哦,看了一下思路,MSDN中查找AppendChunk方法,发现ASP.NET已经废弃掉了,但它提供了另外一种操作方式,如下:

SqlConnection con = new SqlConnection("Server=Darkover;uid=sa;pwd=Password1;database=northwind");
SqlDataAdapter da = new SqlDataAdapter("Select * From MyImages", con);
SqlCommandBuilder MyCB = new SqlCommandBuilder(da);
DataSet ds = new DataSet("MyImages");

da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
FileStream fs = new FileStream(@"C:\winnt\Gone Fishing.BMP", FileMode.OpenOrCreate, FileAccess.Read);

byte[] MyData= new byte[fs.Length];
fs.Read(MyData, 0, System.Convert.ToInt32(fs.Length));

fs.Close();

da.Fill(ds,"MyImages");

DataRow myRow;
myRow=ds.Tables["MyImages"].NewRow();

myRow["Description"] = "This would be description text";
myRow["imgField"] = MyData;
ds.Tables["MyImages"].Rows.Add(myRow);
da.Update(ds, "MyImages");

con.Close();

这个代码是操作的SQLServer,不过已经有思路了,我改成了操作Oracle的语法,如下:

string strCon = "Data Source = EHR;user id = ehr;password = ehr";
   OC.OracleConnection Con = new System.Data.OracleClient.OracleConnection(strCon);
   Con.Open();

   OC.OracleCommand Cmd2 = new System.Data.OracleClient.OracleCommand("SELECT * FROM PAYCALCULATEMAINLOGIC",Con);
   OC.OracleDataAdapter _ADP = new System.Data.OracleClient.OracleDataAdapter(Cmd2);
   _ADP.InsertCommand = new System.Data.OracleClient.OracleCommand(
     "INSERT INTO PAYCALCULATEMAINLOGIC(MAINLOGICNAME,MAINLOGICID,MAINSCRIPT,ISDELETED,MAINLOGICVERSIONID) " +
     "VALUES ('MainLogic','11111111-e639-4a12-a2be-d2f63a2536ea',:pMAINSCRIPT ,0,'MAINLOGICVERSIONID')",Con);
   OC.OracleParameter _OP1 = new OC.OracleParameter("pMAINSCRIPT",OC.OracleType.Clob);
   _OP1.Value = getsql();
   _ADP.InsertCommand.Parameters.Add(_OP1);
   _ADP.InsertCommand.ExecuteNonQuery();

   OC.OracleCommand Cmd3 = new System.Data.OracleClient.OracleCommand("SELECT * FROM PAYCALCULATEMAINLOGIC",Con);
   _ADP = new System.Data.OracleClient.OracleDataAdapter(Cmd3);
   DataSet _DS2 = new DataSet();
   _ADP.Fill(_DS2,"LOGIC");

   this.dataGrid1.DataSource = _DS2.Tables[0];

   Con.Close();

All is ok.

PS : Oracle传参数不是SQLServer中的@+参数名,而是:+参数名,冒号哦。


posted @ 2004-06-01 16:24  mp3 swf  阅读(6891)  评论(21编辑  收藏  举报