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?

score 52 · Accepted Answer · edited Apr 10 '15 at 15:03

52

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

edited Apr 10 '15 at 15:03

FabianSilva

405
6
18

answered Dec 19 '09 at 19:20

GrayWizardx

19,561
2
30
43

I didn't understood, how the stored procedure is getting executed as I dont uses dataset or use rarely. "cmd.ExecuteQuery()" can u xplain me whether i can use this to do the same thing – Shantanu Gupta Dec 19 '09 at 19:24
3

When you call "Fill" it will run the command you have associated with the "Select" statement and put the results into a table called "result_name". This is the same behaviour as calling ExecuteReader(), having the rows read singularly and placed into a List> type structure. – GrayWizardx Dec 19 '09 at 19:50
1

Is it not better to close the connection soon after recieving the data in the dataset? – MarsOne Feb 26 '14 at 11:36
You dont need to open or close the connection wheh you use DataAdapter.Fill. Also, i would use use the using-statement instead. Its more readable. You should also dispose the SqlCommand and the SqlDataAdapter. – Tim Schmelter Jul 23 '14 at 17:35
@FabianSilva What about having output param from t sql beside datatable? how to get output param too? Is it possible? Sample? – Ahmad Ebrahimi Jul 30 '15 at 09:06
@AhmadEbrahimi I'm not are who answered question (edited it), but check http://stackoverflow.com/questions/10905782/using-stored-procedure-output-parameters-in-c-sharp http://www.aspsnippets.com/Articles/How-to-return-Output-parameter-from-Stored-Procedure-in-ASPNet-in-C-and-VBNet.aspx this have a example to use output params, hope that this help you – FabianSilva Jul 31 '15 at 18:37
@GrayWizardx What about having output param from t sql beside datatable? how to get output param too? Is it possible? Sample? – Ahmad Ebrahimi Aug 01 '15 at 13:40

Andomar · Answer 2 · 2009-12-19T19:21:03.933

6

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

edited Dec 19 '09 at 19:21

answered Dec 19 '09 at 19:15

Andomar

232,371
49
380
404

do i need to tell adapter for select command adapt.SelectCommand = com; ? – Shantanu Gupta Dec 19 '09 at 19:20
@Shantanu Gupta: Yeah, or you can use `SqlDataAdapter(com)` to pass `SelectCommand` as a parameter to the constructor – Andomar Dec 19 '09 at 19:22

score 1 · Answer 3 · answered Jul 30 '18 at 07:14

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

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

3 Answers3

Linked

Related