最近使用odp.net连oracle数据库,传参数插入是没问题的,但就是在更新的时候,通过传参数的方式就没成功,执行结果受影响的行数总不能为1。
数据表及存储过程如下:--表
create table Producer (
Id CHAR(32) not null,
Name NVARCHAR2(50) not null,
NickName NVARCHAR2(20),
Telephone NVARCHAR2(30),
Nationality NVARCHAR2(30),
constraint PK_PRODUCER primary key (Id)
)
--存储过程
create or replace procedure Proc_Producer_Update_ById (Id IN CHAR,Name IN NVARCHAR2,NickName IN NVARCHAR2,Telephone IN NVARCHAR2,Nationality IN NVARCHAR2) as
begin
update Producer
set Producer.Name = Name, Producer.NickName = NickName, Producer.Telephone = Telephone, Producer.Nationality = Nationality
where (Producer.Id = Id);
end;C#代码如下:void PersistUpdatedItem(Producer item)
{
#region 传参数
string strUpdate = "Update Producer Set Name=:Name,NickName=:NickName,Telephone=:Telephone,Nationality=:Nationality Where Id=:Id";
using (OracleConnection con = new OracleConnection(ConnectionString))
{
con.Open(); using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = con;
cmd.CommandText = "Update Producer Set Name=:Name,NickName=:NickName,Telephone=:Telephone,Nationality=:Nationality Where Id=:Id";
cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("Id", OracleDbType.Char, item.Key, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("Name", OracleDbType.NVarchar2, item.Name, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("NickName", OracleDbType.NVarchar2, item.NickName, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("Telephone", OracleDbType.NVarchar2, item.Telephone, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("Nationality", OracleDbType.NVarchar2, item.Nationality, System.Data.ParameterDirection.Input); int recordsAffected = cmd.ExecuteNonQuery(); //此处返回的是 0
}
con.Close();
}
#endregion
#region 存储过程
using (OracleConnection con = new OracleConnection(ConnectionString))
{
con.Open(); using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = con;
cmd.CommandText = "Proc_Producer_Update_ById";
cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("Id", OracleDbType.Char, item.Key, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("Name", OracleDbType.NVarchar2, item.Name, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("NickName", OracleDbType.NVarchar2, item.NickName, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("Telephone", OracleDbType.NVarchar2, item.Telephone, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("Nationality", OracleDbType.NVarchar2, item.Nationality, System.Data.ParameterDirection.Input); int recordsAffected = cmd.ExecuteNonQuery(); //此处返回的是 -1
}
con.Close();
}
#endregion #region 拼接字符串
using (OracleConnection con = new OracleConnection(ConnectionString))
{
con.Open(); using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = con;
cmd.CommandText = string.Format("Update Producer Set Name='{0}',NickName='{1}',Telephone='{2}',Nationality='{3}' Where ProducerId = '{4}'",
item.Name,
item.NickName,
item.Telephone,
item.Nationality,
item.Key);
cmd.CommandType = System.Data.CommandType.Text;
int recordsAffected = cmd.ExecuteNonQuery(); // 此处返回的是 1,但这样数据库会被注入
}
con.Close();
}
#endregion
}
数据表及存储过程如下:--表
create table Producer (
Id CHAR(32) not null,
Name NVARCHAR2(50) not null,
NickName NVARCHAR2(20),
Telephone NVARCHAR2(30),
Nationality NVARCHAR2(30),
constraint PK_PRODUCER primary key (Id)
)
--存储过程
create or replace procedure Proc_Producer_Update_ById (Id IN CHAR,Name IN NVARCHAR2,NickName IN NVARCHAR2,Telephone IN NVARCHAR2,Nationality IN NVARCHAR2) as
begin
update Producer
set Producer.Name = Name, Producer.NickName = NickName, Producer.Telephone = Telephone, Producer.Nationality = Nationality
where (Producer.Id = Id);
end;C#代码如下:void PersistUpdatedItem(Producer item)
{
#region 传参数
string strUpdate = "Update Producer Set Name=:Name,NickName=:NickName,Telephone=:Telephone,Nationality=:Nationality Where Id=:Id";
using (OracleConnection con = new OracleConnection(ConnectionString))
{
con.Open(); using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = con;
cmd.CommandText = "Update Producer Set Name=:Name,NickName=:NickName,Telephone=:Telephone,Nationality=:Nationality Where Id=:Id";
cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("Id", OracleDbType.Char, item.Key, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("Name", OracleDbType.NVarchar2, item.Name, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("NickName", OracleDbType.NVarchar2, item.NickName, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("Telephone", OracleDbType.NVarchar2, item.Telephone, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("Nationality", OracleDbType.NVarchar2, item.Nationality, System.Data.ParameterDirection.Input); int recordsAffected = cmd.ExecuteNonQuery(); //此处返回的是 0
}
con.Close();
}
#endregion
#region 存储过程
using (OracleConnection con = new OracleConnection(ConnectionString))
{
con.Open(); using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = con;
cmd.CommandText = "Proc_Producer_Update_ById";
cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("Id", OracleDbType.Char, item.Key, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("Name", OracleDbType.NVarchar2, item.Name, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("NickName", OracleDbType.NVarchar2, item.NickName, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("Telephone", OracleDbType.NVarchar2, item.Telephone, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("Nationality", OracleDbType.NVarchar2, item.Nationality, System.Data.ParameterDirection.Input); int recordsAffected = cmd.ExecuteNonQuery(); //此处返回的是 -1
}
con.Close();
}
#endregion #region 拼接字符串
using (OracleConnection con = new OracleConnection(ConnectionString))
{
con.Open(); using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = con;
cmd.CommandText = string.Format("Update Producer Set Name='{0}',NickName='{1}',Telephone='{2}',Nationality='{3}' Where ProducerId = '{4}'",
item.Name,
item.NickName,
item.Telephone,
item.Nationality,
item.Key);
cmd.CommandType = System.Data.CommandType.Text;
int recordsAffected = cmd.ExecuteNonQuery(); // 此处返回的是 1,但这样数据库会被注入
}
con.Close();
}
#endregion
}
解决方案 »
- 在进行oracle练习的时候,监听器总是自己关闭,这是怎么回事?
- 这样delete可以吗?
- SQL笔试题,第7题挂了,和公司的面试无缘。看大家能做几道(非常简单) 提供给大家希望能从中受益
- 高手来看看优化查询
- oracle金牌认证都没解决的问题哦~!!!!!
- 如何指定一个过程在个一个时间执行一次????
- Oracle的初级问题,为何使用表的时候还要加方案名,如dk.tusers
- 数据类型date的格式??
- 通过脚本建表出错,大虾来看看...
- ??ORA-12571: "TNS:packet writer failure"??
- 到底应该怎么办?事业到底要怎么走?
- 一条sql语句(从mysql转oracle)
加上SQL异常捕捉,看有异常抛出?
我加了个异常,不知道这样写对不对,但执行的话也没抛异常Exception
when others then
Raise_Application_Error(-20001,'更新失败');