How to get datatable as a result of stored procedure

Question

hi guys, Following is my stored procedure.

ALTER PROCEDURE SP_GetModels 
(
    @CategoryID bigint
)
AS
BEGIN
    Select ModelID,ModelName From Model where CategoryID=@CategoryID
END

and i am calling stored procedure in code behind as

public SqlConnection conn;
 public SqlDataReader   GetModels()
        { 


         DataTable dt = new DataTable();
     public void DbConnection()
            {
                conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleCs"].ConnectionString);
                conn.Open();
            }
                DbConnection();
                SqlCommand cmd = new SqlCommand("SP_GetModels", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CategoryID", SqlDbType.BigInt, 10).Value = CategoryID;
               // SqlDataAdapter madap = new SqlDataAdapter(cmd, conn);
                SqlDataReader dreader= cmd.ExecuteReader();

                //madap.Fill(dt);
                return dreader;
            }

I have a dropdownlist to which i have to bind datareader object which contain modelname. how can i set datasource to dropdownlist as datareader

Answer 1

You should be able to directly bind the SqlDataReader to the drop down list like this:

MyDropDownList.DataSource = GetModels();
MyDropDownList.DataTextField = "ModelName";
MyDropDownList.DataValueField = "ModelID";

You need to also specify which member (property) is going to be displayed (DataTextField), and which one will be used as value when an entry is selected in the drop down list (DataValueField).

I would strongly recommend you grab the data from the SqlDataReader in your GetModels() procedure, create instances of a Model class which will hold those fields you have and need, close the SqlDataReader, and then return it as a List<Model> and bind that list to the drop down list. MUCH better than directly binding a SqlDataReader!

public class Model
{
  public int ModelID { get; set; }
  public string ModelName { get; set; }
}

And in your GetModels():

public List<Model> GetModels()
{
  List<Model> result = new List<Model>();

  using(SqlConnection conn = new SqlConnection(ConfigurationManager.
                                     ConnectionStrings["SampleCs"].ConnectionString))
  {
     using(SqlCommand cmd = new SqlCommand("SP_GetModels", conn))
     {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@CategoryID", SqlDbType.BigInt, 10).Value = CategoryID;

        conn.Open();

        using(SqlDataReader dreader = cmd.ExecuteReader())
        { 
           while(dreader.Read())
           {
               Model workItem = new Model() 
                                { ModelID = dreader.GetInt(0), 
                                  ModelName = dreader.GetString(1) };
               result.Add(workItem);
           }
           reader.Close();
        }

        conn.Close();
    }
  }
  return result;
}

Marc

Answer 2

First, make sure you have the datareader automatically close when returning it:

 SqlDataReader dreader= cmd.ExecuteReader(CommandBehavior.CloseConnection);

Then to bind to a list:

 DropDownList1.DataSource = GetModels();
 DropDownList1.DataValueField = "ModelID";
 DropDownList1.DataTextField = "ModelName";
 DropDownList1.DataBind();

Answer 3

I don't think SqlDataReader inherits from IListSource, and if I remember correctly, you can only use classes the inherit from IListSource for data binding. If you want to get a DataTable, you should use a SqlDataAdapter to execute the command instead. Expanding on Marc's solution:

public void BindData()
{
    dropDownList1.DataSource = LoadModelData();
    dropDownList1.DataValueField = "ModelID";
    dropDownList1.DataTextField = "ModelName";
    dropDownList1.DataBind();
}
public DataTable LoadModelData()
{ 
    DataSet dataset = new DataSet();
    using (conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleCs"].ConnectionString))
    {
        SqlCommand cmd = new SqlCommand("SP_GetModels", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@CategoryID", SqlDbType.BigInt, 10).Value = CategoryID;
        SqlDataAdapter adapter = new SqlDataAdapter(cmd, conn);
        adapter.Fill(dataset);
    }
    return dataset.Tables[0];
}

Answer 4

how about this one

SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DropDownList1.Items.Add(new ListItem(dr["ModelName"].ToString(), dr["ModelID"].ToString()));

            }
            con.Close();