Getting results from a stored procedure to populate a GridView

Question

I have a windows aspx form that I have a TextBox, Button and a GridView. The TextBox is stored as a variable @subschedule and passed to a stored procedure. What I'd like to do is to populate the results of that procedure into my GridView. Can anyone suggest a way to do this?

Thank you

Solution 1

Add a reference to System.Data.SqlClient

Then create a method for your calling your stored procedure... Maybe wrap it up in a class for database calls.

public static class DataBase
{
    public static DataTable myProcedureName(String subSchedule)
    {
        var dt = new DataTable();

        using (var cnx = new SqlConnection("myConnectionString"))
        using (var cmd = new SqlCommand {
            Connection = cnx,
            CommandText = "myProcedureName", 
            CommandType = CommandType.StoredProcedure,
            Parameters = {
                new SqlParameter("@subSchedule", subSchedule)
            }
        })
        {
            try
            {
                cnx.Open();
                dt.Load(cmd.ExecuteReader());
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing MyProcedureName.", ex);
            }
        }
    }
}

Then call it...

gvMyGrid.DataSource = DataBase.myProcedureName(txtSubSchedule.Text);
gvMyGrid.DataBind();

Solution 2

Two popular options:

1.. Code Behind:

string subSchedule = txtSubSchedule.Text.Trim();

//you'll create a new class with a method to get a list of customers
//from your database as others answers have demonstrated
IEnumerable<Customer> custs = MyDataLayer.GetCustomers(subSchedule);

myGrid.DataSource = custs;
myGrid.DataBind();

2.. Use a SqlDataSource. This is a quick and dirty way to bind your ASP.NET server control to a stored procedure. It's got its easy implementation pros, and some other cons :

 <asp:GridView  id="myGrid"
            runat="server"
            AutoGenerateColumns="true"
            DataSourceID="ds1" />

        <asp:SqlDataSource
            id="ds1"
            runat="server"
            ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
            SelectCommandType="StoredProcedure"                
            SelectCommand="GetSchedule">
              <SelectParameters>
                  <asp:ControlParameter name="SubSchedule" 
                          ControlID="txtSubSchedule" Propertyname="Text"/>
              </SelectParameters>
        </asp:SqlDataSource>