C# 如何通过存储过程获取数据表
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/1249091/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
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 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!
我强烈建议您从过程中的 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)
{
}
}