Calling a stored procedure results in an incorrectly empty DataTable

Question

I have a functioning stored procedure which returns the correct data when executed manually. There are several rows of data in the output. However, the following code I have is always resulting in no rows of data being added to the DataTable.

        var commandString = string.Format(@"EXEC MyStoredProcedure {0}", SomeParameter);

        var dataTable = new DataTable();
        using (var connection = new SqlConnection(ConnectionString))
        {
            connection.Open();

            using (var adapter = new SqlDataAdapter(commandString, ConnectionString))
            {
                using (new SqlCommandBuilder(adapter))
                {
                    adapter.Fill(dataTable);
                    adapter.Update(dataTable);
                }
            }
        }

        var result = (from DataRow row in dataTable.Rows
            select new MyModelClass
            {
                SomeString = (string) row["SomeString"],
                SomeValue = (string) row["SomeValue"],

            }).ToList();

        Debug.WriteLine("Results: " + result.Count);

I am not sure why the code is resulting in no rows of data. Where am I going wrong? I suspect it is because I have an incorrect understanding of how DataTable works. How should I fix the code?

You don't need to call `Update` to fill the `DataSet`. That's only used to update the database with relevant changes to the `DataSet`. — D Stanley, Aug 11 '15 at 13:14
You need to parameterize your code instead of building up a string and executing it. What you have is vulnerable to sql injection. — Sean Lange, Aug 11 '15 at 14:01
See: [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/q/7505808/3744182) and [What's the best method to pass parameters to SQLCommand?](https://stackoverflow.com/q/293311/3744182). — dbc, Jan 30 '20 at 17:51

score 1 · Accepted Answer · answered Aug 11 '15 at 16:06

Basically, your code should look something like this:

string ConnectionString = "<Your connection string here>";
string procedureName = "<your stored procedure name here>";
string ParamName = "@<Parameter name>"; // NOTE: the '@' is INSIDE the string!
DataSet ds = new DataSet();

using (var connection = new SqlConnection(ConnectionString))
{
    var cmd = new SqlCommand(procedureName, connection);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(ParamName, SqlDbType.Int).Value = 5;

    using (var adapter = new SqlDataAdapter(cmd))
    { 
        adapter.Fill(ds);
    }
}

score 0 · Answer 2 · answered Jan 30 '20 at 16:58

Sorry to be late to the party but I just want to confirm something.

I noticed that in the OP the asker uses the adapter to fill a DataTable

In the accepted answer, however, the adapter is used to fill a DataSet

Well, that's great - I also discovered this myself. The DataTable will contain 0 rows ase well as 0 columns, but use it to fill a DataSet instead, and it works fine.

So the real question here is: Why does it work with a DataSet when it doesn't with a DataTable?

I have a suspicion and I'm really here to try and prove it right or wrong. In my case I found that the SP started returning this weird empty DataTable as soon as my SP contained anything like a DELETE or an INSERT. In my case, where the original SP was just a simple SELECT from some data, I had to add some complexity which meant I first had to populate a Table Variable using INSERTs, and then SELECT from that. And that's when I ran into this problem.

So, @user9993, if you're still around can you just tell me.... does your SP do more than just SELECT? Does it do updates or deletes even if it's just to something like a Temporary Table or a Table Variable?

Is this an answer, or a follow-up question? Please note this site [doesn't work like a discussion forum](https://meta.stackexchange.com/a/92110). It is a Q&A site where every post is either a question or an answer to a question. If a follow-up question you should delete this and ask a new question [here](https://stackoverflow.com/questions/ask). If an answer you might [edit] the question to clarify your solution,which would seem to be filling a `DataSet` rather than filling a `DataTable`. — dbc, Jan 30 '20 at 17:56
It is a bit of both. It is a follow up question but based on a hunch, and if my hunch is proven correct it may be the making of an actual answer, both to the OP and to my own, similar, issue. — , Jan 30 '20 at 18:03

Calling a stored procedure results in an incorrectly empty DataTable

2 Answers2

Linked