insert into DemarcateFile(ID,Name,DFValue,ImportTime) values(fordemarcatefile.nextval,'107 394 01 11.HEX',System.Byte[],to_date('2008-8-25 11:05:10','yyyy-mm-dd hh24:mi:ss'))我要向oracle数据库中写入一条记录,ID字段是用一个自增长的字段,用序列和触发器来实现的,dfvalue是一个二进制的文件,我要拼sql语句是这样的:
insertSql = "insert into DemarcateFile(ID,Name,DFValue,ImportTime) values(fordemarcatefile.nextval,'"+demarcateFile.Name+"',"+demarcateFile.DFValue+","+"to_date('"+demarcateFile.ImportTime.ToString()+ "','yyyy-mm-dd hh24:mi:ss')"+")";
但是demarcatefile.dfvalue这会报错;
如果我要传参的话,这个fordemarcatefile.nextval又不知道怎么传进去.
该怎么来做呢?迷茫中,还请各位帮帮忙,谢谢了!
insertSql = "insert into DemarcateFile(ID,Name,DFValue,ImportTime) values(fordemarcatefile.nextval,'"+demarcateFile.Name+"',"+demarcateFile.DFValue+","+"to_date('"+demarcateFile.ImportTime.ToString()+ "','yyyy-mm-dd hh24:mi:ss')"+")";
但是demarcatefile.dfvalue这会报错;
如果我要传参的话,这个fordemarcatefile.nextval又不知道怎么传进去.
该怎么来做呢?迷茫中,还请各位帮帮忙,谢谢了!
OracleCommand oracleCommand = new OracleCommand();
oracleConnection.Open();
oracleTransaction = oracleConnection.BeginTransaction();
oracleCommand.Connection = oracleConnection;
oracleCommand.Transaction = oracleTransaction;
oracleCommand.Parameters.Add("dfvalue",OracleType.Blob);
oracleCommand.Parameters["DFValue"].Value = (把demarcateFile.DFValue转成byte[]类型); oracleCommand.CommandText = insertSql;
oracleCommand.ExecuteNonQuery();
insert into DemarcateFile(ID,Name,CPID,Edition,EditionComment,IsActive,DFVaue,Op
erator,ImportTime,"comment") values(fordemarcatefile.nextval,'107 394 01 11.HEX'
,1,'2','','True',System.Byte[],'Administrator',to_date('2008-8-25 11:05:10','yyy
y-mm-dd hh24:mi:ss'),'无')
报错:ERROR 位于第 1 行:
ORA-00917: 缺少逗号然后把System.Byte[],去掉执行:insert into DemarcateFile(ID,Name,CPID,Edition,EditionComment,IsActive,Oper
ator,ImportTime,"comment") values(fordemarcatefile.nextval,'107 394 01 11.HEX',1
,'2','','True','Administrator',to_date('2008-8-25 11:05:10','yyyy-mm-dd hh24:mi:
ss'),'无');
已创建 1 行。
我认为是那个二进制字段的问题,不知道该怎么写了!
system.Byte[] 是我那个字段的值,是一个byte类型的数组.
执行的时候就显示成那样的了.
"insert into DemarcateFile(ID,Name,CPID,Edition,EditionComment,IsActive,DFVaue,Operator,ImportTime,\"comment\") values(fordemarcatefile.nextval,@1,@2,@3,@4,@5,@6,@7,@8,@9)";
//string insertSql = "insert into DemarcateFile(ID,Name,CPID,Edition,EditionComment,IsActive,DFValue,Operator,ImportTime,\"comment\") values(fordemarcatefile.nextval,'"+demarcateFile.Name+"',"+demarcateFile.CPID+",'"+demarcateFile.Edtion+"','"+demarcateFile.EditonComment+"','"+demarcateFile.IsActive.ToString()+"',"+demarcateFile.DFValue+",'"+demarcateFile.Operator+"',"+"to_date('"+demarcateFile.ImportTime.ToString()+ "','yyyy-mm-dd hh24:mi:ss')"+",'"+demarcateFile.Comment+"')";
using (OleSql oleSql = new OleSql(conn))
{
OleDbParameter[] parameters = new OleDbParameter[9];
parameters[0] = new OleDbParameter("@1", demarcateFile.Name);
parameters[1] = new OleDbParameter("@2", demarcateFile.CPID);
parameters[2] = new OleDbParameter("@3", demarcateFile.Edtion);
parameters[3] = new OleDbParameter("@4", demarcateFile.EditonComment);
parameters[4] = new OleDbParameter("@5", demarcateFile.IsActive);
parameters[5] = new OleDbParameter("@6", demarcateFile.DFValue);
parameters[6] = new OleDbParameter("@7", demarcateFile.Operator);
parameters[7] = new OleDbParameter("@8", demarcateFile.ImportTime);
parameters[8] = new OleDbParameter("@9", demarcateFile.Comment);
oleSql.Execute(insertSql, parameters);
//oleSql.Execute(insertSql);
insertID = demarcateFile.ID;
}
可能要用dbms_lob方法来出来你的这个列的值。
这个要传的话得进行两部数据库操作吧1先在程序中,用 select fordemarcatefile.nextval from 表 取出这个序列。
2然后将这个值通过程序变量传到第二个 insert 的sql里去。
"insert into DemarcateFile(ID,Name,CPID,Edition,EditionComment,IsActive,DFVaue,Operator,ImportTime,\"comment\") values(@0,@1,@2,@3,@4,@5,@6,@7,@8,@9)";
//string insertSql = "insert into DemarcateFile(ID,Name,CPID,Edition,EditionComment,IsActive,DFValue,Operator,ImportTime,\"comment\") values(fordemarcatefile.nextval,'"+demarcateFile.Name+"',"+demarcateFile.CPID+",'"+demarcateFile.Edtion+"','"+demarcateFile.EditonComment+"','"+demarcateFile.IsActive.ToString()+"',"+demarcateFile.DFValue+",'"+demarcateFile.Operator+"',"+"to_date('"+demarcateFile.ImportTime.ToString()+ "','yyyy-mm-dd hh24:mi:ss')"+",'"+demarcateFile.Comment+"')";
using (OleSql oleSql = new OleSql(conn))
{
OleDbParameter[] parameters = new OleDbParameter[10];
parameters[0] = new OleDbParameter("@0", "fordemarcatefile.nextval");
parameters[1] = new OleDbParameter("@1", demarcateFile.Name);
parameters[2] = new OleDbParameter("@2", demarcateFile.CPID);
parameters[3] = new OleDbParameter("@3", demarcateFile.Edtion);
parameters[4] = new OleDbParameter("@4", demarcateFile.EditonComment);
parameters[5] = new OleDbParameter("@5", demarcateFile.IsActive);
parameters[6] = new OleDbParameter("@6", (byte[])demarcateFile.DFValue);
parameters[7] = new OleDbParameter("@7", demarcateFile.Operator);
parameters[8] = new OleDbParameter("@8", demarcateFile.ImportTime);
parameters[9] = new OleDbParameter("@9", demarcateFile.Comment);
oleSql.Execute(insertSql, parameters);
//oleSql.Execute(insertSql);
insertID = demarcateFile.ID;
}
现在就是不知道该怎么把这个植传进去,必须分两步吗?
没有直接传的办法吗?
"insert into DemarcateFile(ID,Name,CPID,Edition,EditionComment,IsActive,DFVaue,Operator,ImportTime,\"comment\") values(@0,@1,@2,@3,@4,@5,@6,@7,@8,@9)";
using (OleSql oleSql = new OleSql(conn))
{
OleDbParameter[] parameters = new OleDbParameter[10];
parameters[0] = new OleDbParameter("@0", "fordemarcatefile.nextval");
parameters[1] = new OleDbParameter("@1", demarcateFile.Name);
parameters[2] = new OleDbParameter("@2", demarcateFile.CPID);
parameters[3] = new OleDbParameter("@3", demarcateFile.Edtion);
parameters[4] = new OleDbParameter("@4", demarcateFile.EditonComment);
parameters[5] = new OleDbParameter("@5", demarcateFile.IsActive);
parameters[6] = new OleDbParameter("@6", (byte[])demarcateFile.DFValue);
parameters[7] = new OleDbParameter("@7", demarcateFile.Operator);
parameters[8] = new OleDbParameter("@8", demarcateFile.ImportTime);
parameters[9] = new OleDbParameter("@9", demarcateFile.Comment);
oleSql.Execute(insertSql, parameters);
insertID = demarcateFile.ID;
}
救命呀救命!
byte[] fileBytes = binaryReader.ReadBytes((int) selectFile.PostedFile.InputStream.Length);string insertSql = "insert into DemarcateFile(ID,Name,DFValue,ImportTime) values((select TO_CHAR(fordemarcatefile.nextval) from dual),'"+demarcateFile.Name+"',:dfvalue,"+"to_date('"+demarcateFile.ImportTime.ToString()+ "','yyyy-mm-dd hh24:mi:ss')"+")"; string conn = System.Configuration.ConfigurationSettings.AppSettings["OracleConnectionString"].ToString();
OracleConnection oracleConnection = new OracleConnection(conn);
OracleTransaction oracleTransaction;
OracleCommand oracleCommand = new OracleCommand();
oracleConnection.Open();
oracleTransaction = oracleConnection.BeginTransaction();
oracleCommand.Connection = oracleConnection;
oracleCommand.Transaction = oracleTransaction;
oracleCommand.Parameters.Add("dfvalue",OracleType.Blob);
oracleCommand.Parameters["dfvalue"].Value = fileBytes;
oracleCommand.CommandText = insertSql;
oracleCommand.ExecuteNonQuery();
oracleTransaction.Commit();