C# 如何通过存储过程获取数据表

How to get datatable as a result of stored procedure

提问by user42348

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

我有一个下拉列表，我必须将包含模型名称的数据读取器对象绑定到该下拉列表。我如何将数据源设置为下拉列表作为数据读取器

回答by marc_s

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

您应该能够像这样直接将 SqlDataReader 绑定到下拉列表：

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

您还需要指定要显示的成员（属性）（DataTextField），以及在下拉列表（DataValueField）中选择条目时将使用哪个成员作为值。

I would strongly recommendyou grab the data from the SqlDataReader in your GetModels()procedure, create instances of a Modelclass 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!

我强烈建议您从过程中的 SqlDataReader 中获取数据GetModels()，创建一个Model类的实例来保存您拥有和需要的那些字段，关闭 SqlDataReader，然后将其作为 a 返回并将List<Model>该列表绑定到下拉列表。比直接绑定 SqlDataReader 好多了！

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

And in your GetModels():

在你的 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

马克

回答by Dan Diplo

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

回答by Jeremy S

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:

我不认为 SqlDataReader 继承自 IListSource，如果我没记错的话，您只能使用从 IListSource 继承的类进行数据绑定。如果要获取 DataTable，则应改用 SqlDataAdapter 来执行命令。扩展 Marc 的解决方案：

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

回答by anishMarokey

how about this one

这个怎么样

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

            }
            con.Close();

回答by ABHISHEK CHAKLADAR

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

    }
}