今天在做数据库安装包,碰到一个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中的@+参数名,而是:+参数名,冒号哦。