转载自:chwei_cson http://blog.csdn.net/chwei_cson/article/details/7785536
本文写的是最基本的C#调用存储过程操作数据表的东西,相当基础,如果你已经对存储过程有一定学习,那就不要再浪费时间往下看啦!
首先说一下存储过程的写法(没有相关的判断啊,最最简单的存储过程)
- --学生信息表;
- create table scott.stumsg
- (
- stuname varchar(20),
- stunum char(12)primary key,
- stusex char(2)
- )
- --插入学生信息的存储过程;
- create procedure stu_pro_ins
- (
- name in varchar,
- num in char,
- sex in char
- )
- as
- begin
- insert into scott.stumsg
- values(name,num,sex);
- commit;
- end;
- --修改学生信息的存储过程
- create or replace procedure stu_pro_upd
- (
- name in varchar,
- num in char,
- sex in char
- )
- as
- begin
- update stumsg
- set
- stuname=name,stunum=num,stusex=sex
- where stunum=num;
- commit;
- end;
- ---按学号删除学生信息的存储过程
- create or replace procedure stu_pro_del
- (
- num in char
- )
- as
- begin
- delete from stumsg
- where stunum=num;
- commit;
- end;
- -- 显示全部的学生信息的存储过程;
- create or replace procedure stu_pro_sel_all
- (result out sys_refcursor)
- as
- begin
- open result for select * from stumsg;
- end;
C#调用存储过程时的代码(只写了显示和插入的代码,其它的和这差不多一样的,就不写了)
- //显示所有信息;
- private void button1_Click(object sender, EventArgs e)
- {
- try
- {
- OracleConnection conn = new OracleConnection("server=Chweiorc;uid=scott;pwd=Chwei926a");// 建立一个新的连接对象
- OracleCommand cmd = new OracleCommand("stu_pro_sel_all", conn);
- // 声明 cmd的类型为 存储类型;
- cmd.CommandType = CommandType.StoredProcedure;
- OracleParameter p1 = new OracleParameter("result", OracleType.Cursor);
- p1.Direction = System.Data.ParameterDirection.Output;
- cmd.Parameters.Add(p1);
- OracleDataAdapter da = new OracleDataAdapter(cmd);
- DataSet ds = new DataSet();
- da.Fill(ds);
- this.dgvShowAll.DataSource = ds.Tables[0];
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.Message);
- }
- }
- // 像数据库中插入数据;
- private void button2_Click(object sender, EventArgs e)
- {
- string stuName = tb_name.Text.Trim();
- string stuNum = tb_num.Text.Trim();
- string stuSex = tb_sex.Text.Trim();
- MessageBox.Show(stuSex.Length.ToString());
- //连接字符串
- string connStr = "Data Source=Chweiorc;User ID=scott;Password=Chwei926a";
- OracleConnection connection = new OracleConnection(connStr);
- OracleCommand cmd = connection.CreateCommand();
- cmd.CommandType = CommandType.StoredProcedure; // 指明用调用存储过程的方式来操作数据库;
- cmd.CommandText = "stu_pro_ins";
- cmd.Parameters.AddWithValue("name", stuName);
- cmd.Parameters.AddWithValue("num",stuNum);
- cmd.Parameters.AddWithValue("sex",stuSex);
- connection.Open();
- try
- {
- MessageBox.Show(cmd.ExecuteNonQuery().ToString());
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.Message);
- }
- finally
- {
- connection.Close();
- cmd.Dispose();
- MessageBox.Show("更新数据完成~~~~");
- }
- }