static void

private static List<Product> LoadViaDataReader(int catgeory)
 {
    List<Product> list = new List<Product>();
    string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        string sql = @"SELECT Id, Name, Price, AvailableFrom FROM Product WHERE Category_Id = @CATEGORY";
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            cmd.Parameters.AddWithValue("@CATEGORY", catgeory);
            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                        list.Add(CreateProduct(rdr));
                    //you could set DataSource = rdr directly
                    //rdr.NextResult(); //for multi-results
                }
            }
        }
    }
    return list;
 }
 
 private static Product CreateProduct(IDataRecord rdr)
 {
    int id = rdr.GetInt32(0);
    string name = rdr["Name"].ToString();
    decimal price = rdr.GetDecimal(2);
    DateTime? date = null;
    if (!rdr.IsDBNull(3)) date = rdr.GetDateTime(3);
    //or... if (rdr["AvailableFrom"] != DBNull.Value) date = rdr["AvailableFrom"];
 
    //create the object
    Product p = new Product(id, name);
    p.AvailableFrom = date;
    p.Price = price;
    return p;
 }

 
private DataTable LoadViaDataTable(int catgeory)
{
    string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        //no need to explicitly open/close connection unless doing several fills
        string sql = @"SELECT Id, Name, Price, AvailableFrom FROM Product WHERE Category_Id = @CATEGORY";
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            cmd.Parameters.AddWithValue("@CATEGORY", catgeory);
            DataTable dt = InitDataTable();
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                //da.SelectCommand = cmd
                da.Fill(dt);
            }
            return dt;
        }
    }
}
 
private static DataTable InitDataTable()
{
    DataTable dt = new DataTable("Product");
    dt.Columns.Add("Id", typeof(int));
    dt.Columns.Add("Name", typeof(string));
    dt.Columns.Add("Price", typeof(decimal));
    dt.Columns.Add("AvailableFrom", typeof(DateTime));
    return dt;
}

private void UseSqlDataSourceProgrammatically(int category)
{
    //you could also handle the OnSelecting event to set parameters (which have to be strings)
    SqlDataSource1.SelectParameters[0].DefaultValue = category.ToString();
    //SqlDataSource.Select returns DataView if DataSourceMode=DataSet otherwise a IDataReader
    DataView dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
    GridView1.DataSource = dv;
    GridView1.DataBind();
}

GridView1.DataSource = LoadViaDataReader(1);
GridView1.DataBind();

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Id">
    <Columns>
    <asp:BoundField DataField="Id" HeaderText="Id" InsertVisible="False" ReadOnly="True" SortExpression="Id" />
    <asp:BoundField DataField="Name" HeaderText="Name" />
    <asp:BoundField DataField="Price" HeaderText="Price" DataFormatString="{0:C}" HtmlEncode="false" />
    <asp:BoundField DataField="AvailableFrom" HeaderText="AvailableFrom" DataFormatString="{0:D}" HtmlEncode="false" />
    </Columns>
</asp:GridView>