如何从存储过程检索表到数据表?

I created a stored procedure so as to return me a table.

我创建了一个存储过程，以便返回一个表。

Something like this:

是这样的:

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?

当我在前端调用这个存储过程时，我需要编写什么代码来在一个datatable对象中检索它呢?

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的对象中。我所有的查询都在运行，但是我不知道如何通过存储过程将表检索到datatable中

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?

在这段代码中，一个命令被绑定到存储过程名及其参数。它会从存储过程中返回一个datatable吗?

2 个解决方案

#1

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

Modified from Java Schools Example

从Java学校的例子中修改。

#2

Set the CommandText as well, and call Fill on the SqlAdapter to retrieve the results in a DataSet:

同样设置命令文本，并调用SqlAdapter上的Fill以检索数据集中的结果:

var con = new SqlConnection();
con.ConnectionString = "connection string";
var com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "sp_returnTable";
var adapt = new SqlDataAdapter();
adapt.SelectCommand = com;
var dataset = new DataSet();
adapt.Fill(dataset);

(Example is using parameterless constructors for clarity; can be shortened by using other constructors.)

(例如，使用无参数构造函数来实现清晰性;可以通过使用其他构造函数来缩短。