How can I retrieve a table from stored procedure to a datatable?
Solution 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
Solution 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.)
Solution 3
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);
}
}
}
Shantanu Gupta
Debugging Minds... Looking For Learning Opportunities "Opportunities are Often The Beginning of Great Enterprise..." LinkedIn: https://www.linkedin.com/in/shantanufrom4387/
Updated on July 30, 2020Comments
-
Shantanu Gupta almost 4 years
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?