How to Populate a DataTable from a Stored Procedure

Question

Possible Duplicate:
How can i retrieve a table from stored procedure to a datatable

I am trying to populate my datatable. I have created a datatable tmpABCD but i need to populate this with the values from a stored procedure. I am not able to proceed further.

SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString);
sqlcon.Open();
SqlCommand cmd = new SqlCommand("usp_GetABCD", sqlcon);

DataTable dt = new DataTable("tmpABCD");

dt.Columns.Add(new DataColumn("A"));
dt.Columns.Add(new DataColumn("B"));
dt.Columns.Add(new DataColumn("C"));
dt.Columns.Add(new DataColumn("D"));

Can't. Not enough Rep. Either way, the answers here ended up being better. — MikeSmithDev, Nov 15 '12 at 16:47

Tim Schmelter · Accepted Answer · 2018-07-30T08:01:42.520

You don't need to add the columns manually. Just use a DataAdapter and it's simple as:

DataTable table = new DataTable();
using(var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString))
using(var cmd = new SqlCommand("usp_GetABCD", con))
using(var da = new SqlDataAdapter(cmd))
{
   cmd.CommandType = CommandType.StoredProcedure;
   da.Fill(table);
}

Note that you even don't need to open/close the connection. That will be done implicitly by the DataAdapter.

The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.

score 15 · Answer 2 · edited Jan 18 '17 at 17:10

15

Use an SqlDataAdapter instead, it's much easier and you don't need to define the column names yourself, it will get the column names from the query results:

using (SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand("usp_GetABCD", sqlcon))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            DataTable dt = new DataTable();

            da.Fill(dt);
        }
    }
}

edited Jan 18 '17 at 17:10

gunr2171

16,104
25
61
88

answered Nov 15 '12 at 16:38

Sean Airey

6,352
1
20
38

This did the job for me. Thank you – Fokwa Best Aug 25 '23 at 21:48

BFree · Answer 3 · 2012-11-15T16:42:33.700

2

You can use a SqlDataAdapter:

    SqlDataAdapter adapter = new SqlDataAdapter();
    SqlCommand cmd = new SqlCommand("usp_GetABCD", sqlcon);
    cmd.CommandType = CommandType.StoredProcedure;
    adapter.SelectCommand = cmd;
    DataTable dt = new DataTable();
    adapter.Fill(dt);

edited Nov 15 '12 at 16:42

answered Nov 15 '12 at 16:35

BFree

102,548
21
159
201

1

Me too, although this code won't work, the data adapter should use cmd in it's constructor or it should be set after the command is built. In its current state this data adapter will throw an error, firstly telling you that the connection isn't initialised, then once you fix that, that it has no command text for the select command. ;] – Sean Airey Nov 15 '12 at 16:41
-1 for no `using` blocks. "Two wrongs don't make an upvote". – John Saunders Nov 15 '12 at 16:43
1

@JohnSaunders Lame man, very lame. The point of the answer was to show that this can be done using a DataAdapter. It wasn't about proper resource handling of SQL Connection. Maybe I should also explain how hardcoding the SPROC name is a bad idea? Or the short variable names aren't the best either. And what does "Two wrongs don't make an upvote" even mean? – BFree Nov 15 '12 at 16:49
4

@BFree: no, you should simply not post bad code that some unsuspecting reader will copy and paste into his real application. I also downvote for bad exception handling. – John Saunders Nov 15 '12 at 16:52
That's cool, thought it was fair to the OP to point it out though in case they used it and were all like "ARGH WHY ISN'T THIS WORKING?!". No hard feelings =] – Sean Airey Nov 15 '12 at 16:53
2

@JohnSaunders Way to push your weight around.... I can afford the -2 rep points, but on principle I couldn't disagree more. My code answers the question, properly disposing of resources is something everyone should be familiar with; it is outside the scope of this targeted answer to educate people on that. – BFree Nov 15 '12 at 16:56

score 2 · Answer 4 · edited Sep 22 '17 at 05:50

2

Use the SqlDataAdapter, this would simplify everything.

//Your code to this point
DataTable dt = new DataTable();

using(var cmd = new SqlCommand("usp_GetABCD", sqlcon))
{
  using(var da = new SqlDataAdapter(cmd))
  {
      da.Fill(dt):
  }
}

and your DataTable will have the information you are looking for, so long as your stored proceedure returns a data set (cursor).

edited Sep 22 '17 at 05:50

Musakkhir Sayyed

7,012
13
42
65

answered Nov 15 '12 at 16:37

iMortalitySX

1,478
1
9
23

@JohnSaunders Fixed. Please remove, as I was more interested in the data adapter object not the implementation. You are totally correct though – iMortalitySX Nov 15 '12 at 16:45
`using` for the `SqlDataAdapter` also, please, then I'll remove. – John Saunders Nov 15 '12 at 16:47

How to Populate a DataTable from a Stored Procedure

4 Answers4

Linked