我的C#代码:
int count = 0;
OracleCommand cmd = new OracleCommand();
OracleConnection oracleConn = null;
try
{
oracleConn = new OracleConnection(conn);
cmd.Connection = oracleConn;
oracleConn.Open();
//string dateString = date.ToString();
string sql = @"
Declare
COUNTER number;
Begin
SELECT count(0) into COUNTER FROM tb WHERE
pubtime = '{0}' and name='ABC';
if COUNTER < 1 then
INSERT INTO tb(name, text, pubtime, time)
values('ABC','{1}', '{0}', to_date('{2}', 'yyyy-mm-dd hh24:mi:ss'));
end if;
END;";
cmd.CommandText = string.Format(sql, ((DateTime)date).ToString("yyyy-MM-dd HH:mm:ss"), text, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
int ret = cmd.ExecuteNonQuery();
if (ret > 0) count++;
}
catch (Exception ex)
{
}
finally
{
oracleConn.Close();
}
return count;
使用ExecuteNonQuery 返回的都是-1, 是因为使用的Declare Begin End;的原因吗? 有办法获得影响行数吗?
求大神们给小弟解答下。
------解决方案--------------------
调整下语句,不要begin end了,估计就好了
string sql = @"
INSERT INTO tb(name, text, pubtime, time)
select 'ABC','{1}', '{0}', to_date('{2}', 'yyyy-mm-dd hh24:mi:ss') from dual
where 1>(SELECT count(0) FROM tb
WHERE pubtime = '{0}' and name='ABC' )“