如何将表从存储过程检索到数据表? [英] How can I retrieve a table from stored procedure to a datatable?
问题描述
我创建了一个存储过程以便为我返回一个表。
I created a stored procedure so as to return me a table.
类似这样的事情:
create procedure sp_returnTable
body of procedure
select * from table
end
当我在前端调用此存储过程时,需要写什么代码才能在数据表对象中检索它?
When I call this stored procedure on the frontend what code do I need to write to retrieve it in a datatable object?
我写了类似下面的代码。我基本上想知道如何将表检索和存储到数据表的对象中。我所有的查询都在运行,但是我不知道如何通过存储过程将表检索到数据表中。
I wrote code something like the following. I basically want to know retrieving and storing table into an object of datatable. All my queries are running, but I don't know how to retrieve table into a datatable through a stored procedure
DataTable dtable = new DataTable();
cmd.Connection = _CONN;
cmd.CommandText = SPNameOrQuery;
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
OpenConnection();
adp.Fill(dtTable);
CloseConnection();
在此代码中,命令已与存储过程名称及其参数绑定在一起。会从存储过程中返回数据表吗?
Here in this code a command has been bound with the stored procedure name and its parameters. Will it be returning me a datatable from the stored procedure?
推荐答案
string connString = "<your connection string>";
string sql = "name of your sp";
using(SqlConnection conn = new SqlConnection(connString))
{
try
{
using(SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = new SqlCommand(sql, conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
da.Fill(ds, "result_name");
DataTable dt = ds.Tables["result_name"];
foreach (DataRow row in dt.Rows) {
//manipulate your data
}
}
}
catch(SQLException ex)
{
Console.WriteLine("SQL Error: " + ex.Message);
}
catch(Exception e)
{
Console.WriteLine("Error: " + e.Message);
}
}
从 Java学校示例
这篇关于如何将表从存储过程检索到数据表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!