How to get datatable as a result of stored procedure

Question

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

private void PopDataBaseName()
{
    try
    {
        SqlCommand cmd = new SqlCommand("sp_generate_report", con);
        cmd.Parameters.Add("@TABLE_NAME", SqlDbType.VarChar,100).Value = TextBox1.Text;
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adp.Fill(ds);

    }
    catch (Exception ex)
    {

    }
}

Answer 2

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