C#调用Oracle存储过程
1.创建表
create table test
(ID number,
NAME varchar2(10),
SEX varchar2(4),
AGE number,
ADDRESS varchar2(200)
);
2.创建不带参数的存储过程
create or replace procedure proc1
is
begin insert into test(ID,NAME,SEX,AGE) values
(1,‘moses‘,‘man‘,25);
commit;
end;
/
3.写C#代码调用这个不带参数的存储过程
protected void Button2_Click(object sender, EventArgs e)
{
String oc = ConfigurationManager.ConnectionStrings["conn"].ToString();
OracleConnection conn = new OracleConnection(oc);
conn.Open();
OracleCommand orm = conn.CreateCommand();
orm.CommandType = CommandType.StoredProcedure;
orm.CommandText = "proc1";
orm.ExecuteNonQuery();
conn.Close();
}
4.写一个没有返回值的带参数的存储过程
create or replace proc2
(v_id number,
v_name varchar2
)
is begin insert into test(id,name)
values(v_id,v_name);
commit;
end;
/
5.C#调用这个带参数无返回值的存储过程
protected void Button1_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(this.TextBox2.Text))
{
this.TextBox2.Text = "编号不能为空";
this.TextBox2.Focus();
return;
}
if (string.IsNullOrEmpty(this.TextBox3.Text))
{
this.TextBox3.Text = "姓名不能为空";
this.TextBox3.Focus();
return;
}
String or=ConfigurationManager.ConnectionStrings["conn"].ToString();
OracleConnection oc = new OracleConnection(or);
oc.Open();
OracleCommand om = oc.CreateCommand();
om.CommandType = CommandType.StoredProcedure;
om.CommandText = "proc2";
om.Parameters.Add("v_id", OracleType.Number).Direction = ParameterDirection.Input;
om.Parameters["v_id"].Value = this.TextBox2.Text.Trim();
om.Parameters.Add("v_name", OracleType.NVarChar).Direction = ParameterDirection.Input;
om.Parameters["v_name"].Value = this.TextBox3.Text.Trim();
om.ExecuteNonQuery();
oc.Close();
}
6.写一个带参数有返回值的存储过程
create or replace procedure proc3 (recount out number
)
is
begin
select count(*) into reccount from test;
commit;
end;
/
7.C#调用这个带参数有返回值的存储过程
protected void Button1_Click(object sender, EventArgs e)
{
String or = ConfigurationManager.ConnectionStrings["conn"].ToString();
OracleConnection oc = new OracleConnection(or);
oc.Open();
OracleCommand ocm = oc.CreateCommand();
ocm.CommandType = CommandType.StoredProcedure;
ocm.CommandText = "proc3";
ocm.Parameters.Add("reccount", OracleType.Number).Direction = ParameterDirection.Output;
ocm.ExecuteNonQuery();
this.TextBox1.Text = ocm.Parameters["reccount"].Value.ToString();
}
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。