c#调用oracle存储过程,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();

}

复制代码

标签： C#

顶一下

(0)

0%

踩一下

(0)

0%