How can i retrieve a table from stored procedure to a datatable

Question

I created a stored procedure so as to return me a table something like that

create procedure sp_returnTable
body of procedure
select * from table
end

When i call this sp on frontend what code i need to write to retrieve it in a datatable object.

I wrote code something like this one. I basically want to know retrieving and storing table into an object of datatable. My all queries are running but i dont know how to retrieve table into a datatable thru 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 cmd has been binded with stored procedure name and its parameters. Will it be returning me a datatable from stored procedure

Answer 1

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

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

Answer 2

string connString = "<your connection string>";
string sql = "name of your sp";

SqlConnection conn = new SqlConnection(connString);

try {
   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(Exception e) {
   Console.WriteLine("Error: " + e);
} finally {
   conn.Close();
}

Modified from Java Schools Example