Socal Media Productions

Here is how you will execute a stored procedure in the code behind script in .Net.  The parameter you pass to the procedure can come from controls, forms, defined variable, etc... In this example I will use the profile class to grab my values.

First step is to define the connection string.  Mine happens to be stored in the web.config file.

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["localconnstring"].
ConnectionString);

Define the command that used your stored procedure.

        SqlCommand cmd = new SqlCommand("stroredprocedure", conn);
        cmd.CommandType = CommandType.StoredProcedure;

Define the parameters.

        SqlParameter pr1 = new SqlParameter("@p1", SqlDbType.NVarChar, 50);
        pr1.Value = Profile.p1;//this is where you will set the value of the parameter passed to the query.  I happened to be calling on the profile for this value.
        cmd.Parameters.Add(pr1);

        SqlParameter pr2 = new SqlParameter("@p2", SqlDbType.NVarChar, 10);
        pr2.Value = Profile.p2;
        cmd.Parameters.Add(pr2);

And open the connection.

        conn.Open();

Thats it!  Now you can fill the resulting data into a dataset and bind it to a gridview.

        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = cmd;

        DataSet ds = new DataSet();
        adapter.Fill(ds);

      //  DataRow drow = ds.Tables[0].Rows[0]; use this to get after the column values.

         GridView1.DataSource = ds;
         GridView1.DataBind();