How to call store procedure in C#

kalit sikka

Definition of store procedure:

CREATE OR REPLACE PACKAGE BODY packperson
         AS

         PROCEDURE allperson
                     (ssn    OUT     tssn,
                      fname OUT     tfname,
                      lname OUT     tlname)
         IS
             CURSOR person_cur IS
                     SELECT ssn, fname, lname
                     FROM person;

             percount NUMBER DEFAULT 1;

         BEGIN
             FOR singleperson IN person_cur
             LOOP
                     ssn(percount) := singleperson.ssn;
                     fname(percount) := singleperson.fname;
                     lname(percount) := singleperson.lname;
                     percount := percount + 1;
             END LOOP;
         END;

         END;
         /

I want to call this procedure in code to fill a datagrid.

Please provide me a code snippet for this.

Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Joe Dickinson18y ago
these days I just create a linq to sql class, and drag drop the stored proc onto the component (form) and call the function in like 1 line of code.

.net 3.5 is cool.
Niradhip Chakraborty18y ago
SqlConnection con = new SqlConnection();
con.ConnectionString = "Enter the connection string";
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "put tour store procedure name here";
SqlDataAdapter da = new SqlDataAdapter(cmd, con);
cmd.Parameters.Add("@parametername", SqlDbType.VarChar);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();