DataAdapter returns a empty rowset, but DataReader returns data.

Question

Hi,

I execute a query that calls a SP, that SP returns data... but when I call it with a DataAdapter I get no result data, if use a DataReader instead... then I get data. The database is SQL Server and the code is using OleDb for reasons I cannot change.

These two calls returns diferent things:

    String commandText = "Declare @return_value int;  exec dbo.copyTemplate ? , ? , ? , ? , ? ,  ? ,   ?, Null , 0 , @return_value;";

    Console.WriteLine("Data Adapter");
    using (OleDbConnection con = new OleDbConnection(connectionString))
    {
        using (OleDbDataAdapter da = new OleDbDataAdapter(commandText, con))
        using (DataTable table = new DataTable("table"))
        {
            da.SelectCommand.Parameters.AddWithValue("?",9).DbType = DbType.Int32;
            da.SelectCommand.Parameters.AddWithValue("?", "AAAAB").DbType = DbType.String;
            da.SelectCommand.Parameters.AddWithValue("?", 1).DbType = DbType.Int32;
            da.SelectCommand.Parameters.AddWithValue("?", 1).DbType = DbType.Int32;
            da.SelectCommand.Parameters.AddWithValue("?", 2).DbType = DbType.Int32;
            da.SelectCommand.Parameters.AddWithValue("?", true).DbType = DbType.Boolean;
            da.SelectCommand.Parameters.AddWithValue("?", DateTime.Now.Date).DbType = DbType.DateTime;

            da.Fill(table);

            foreach (DataRow dr in table.Rows)
            {
                foreach (DataColumn dc in table.Columns)
                {
                    Console.Write(dr[dc].ToString());
                    Console.Write(" ");
                }
                Console.WriteLine();
            }
        }
    }
    Console.WriteLine("Data Reader");
    using (OleDbConnection con = new OleDbConnection(connectionString))
    {
        using (OleDbCommand cmd = new OleDbCommand(commandText, con))
        {
            cmd.Parameters.AddWithValue("?", 9).DbType = DbType.Int32;
            cmd.Parameters.AddWithValue("?", "AAAAC").DbType = DbType.String;
            cmd.Parameters.AddWithValue("?", 1).DbType = DbType.Int32;
            cmd.Parameters.AddWithValue("?", 1).DbType = DbType.Int32;
            cmd.Parameters.AddWithValue("?", 2).DbType = DbType.Int32;
            cmd.Parameters.AddWithValue("?", true).DbType = DbType.Boolean;
            cmd.Parameters.AddWithValue("?", DateTime.Now.Date).DbType = DbType.DateTime;

            con.Open();

            using (OleDbDataReader reader = cmd.ExecuteReader())
                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        Console.Write(reader.GetValue(i) ?? "null");
                        Console.Write(" ");
                    }
                    Console.WriteLine();
                }
        }
    }

    Console.ReadKey(true);

This code returns:

Data Adapter
Data Reader
1057

I can call this code hundreds of times and I always get a value in Data Reader a nothing in Data Adapter, and I can have whatever in the second parameter, it doesn't change anything in the result of the SP. I could swap the parameter values between the two calls, or alter the order... and the result would be still the same :(

I don't understand why is this happening.

Has anybody any idea about what could be the problem?

Cheers.

UPDATE: If I fill a DataSet instead a DataTable I get the result:

    Console.WriteLine("Data Adapter with DataSet");
    using (OleDbConnection con = new OleDbConnection(connectionString))
    {
        using (OleDbDataAdapter da = new OleDbDataAdapter(commandText, con))
        using (DataSet ds = new DataSet("table"))
        {
            da.SelectCommand.Parameters.AddWithValue("?",9).DbType = DbType.Int32;
            da.SelectCommand.Parameters.AddWithValue("?", "AAAAB").DbType = DbType.String;
            da.SelectCommand.Parameters.AddWithValue("?", 1).DbType = DbType.Int32;
            da.SelectCommand.Parameters.AddWithValue("?", 1).DbType = DbType.Int32;
            da.SelectCommand.Parameters.AddWithValue("?", 2).DbType = DbType.Int32;
            da.SelectCommand.Parameters.AddWithValue("?", true).DbType = DbType.Boolean;
            da.SelectCommand.Parameters.AddWithValue("?", DateTime.Now.Date).DbType = DbType.DateTime;

            da.Fill(ds);

            foreach (DataTable table in ds.Tables)
                foreach (DataRow dr in table.Rows)
                {
                    foreach (DataColumn dc in table.Columns)
                    {
                        Console.Write(dr[dc].ToString());
                        Console.Write(" ");
                    }
                    Console.WriteLine();
                }
        }
    }

But the DataSet contains only one table, so I still don't understand why DataAdapter.Fill(DataTable) is not working.

Answer 1

The problem is that the store procedure is (as @leppie pointed out) returning several result sets, the first is empty and the second one contains the actual result.

DataAdapter.Fill(DataTable) gets only the first result set and put it on a DataTable, as that result is empty, you get an empty DataTable. You cannot get “null” because the DataTable is already created, the adapter just fills the DataTable.

DataReader goes through all result sets, for that reason you got the result here, but it is wrong use it like that. Because the first result set is null, there is no problem right now, but if the store procedure or ad hoc query returns several result sets, you could get two or more different tables, with different columns set and messing everything up in one DataTable.

DataAdapter.Fill(DataSet) gets a DataTable per result set, as the first one is null, and there is only data in the second, you get a DataSet with only one DataTable.

The correct and painless way to solve this, is fix the stored procedure and returns just one result set, probably there is something that is returning a empty variable or something like that in the SP. If you want to support several results sets, you will have to use DataAdapter.Fill(DataSet) and to cope with the idea that maybe there is more than one DataTable in the filled DataSet.

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/8fdbaa2d-1f1e-461f-8505-b80ea0c415f2