How can I retrieve a table from stored procedure to a datatable?

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

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

Explaining if any one want to send some parameters while calling stored procedure as below,

using (SqlConnection con = new SqlConnection(connetionString))            {                using (var command = new SqlCommand(storedProcName, con))                {                    foreach (var item in sqlParams)                    {                        item.Direction = ParameterDirection.Input;                        item.DbType = DbType.String;                        command.Parameters.Add(item);                    }                    command.CommandType = CommandType.StoredProcedure;                    using (var adapter = new SqlDataAdapter(command))                    {                        adapter.Fill(dt);                    }                }            }