CODEWITHSUNDEEP
c#asp.netsql-serverdata-binding
user42348
user42348

Reputation: 4319

How to get datatable as a result of stored procedure

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

Upvotes: 5

Views: 72658

Answers (5)

ABHISHEK CHAKLADAR
ABHISHEK CHAKLADAR

Reputation: 101

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)
    {

    }
}

Upvotes: 10

anishMarokey
anishMarokey

Reputation: 11397

how about this one

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

            }
            con.Close();

Upvotes: 0

Jeremy S
Jeremy S

Reputation: 1043

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];
}

Upvotes: 1

marc_s
marc_s

Reputation: 754438

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

Upvotes: 2

Dan Diplo
Dan Diplo

Reputation: 25339

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();

Upvotes: 1

Related Questions