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 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?

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?

Answer 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

Answer 2

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