none
DataAdapter returns a empty rowset, but DataReader returns data. RRS feed

  • Question

  • Hi,

    I've asked the same question here and I will appreciate any hint.

    I execute this SQL (that contains a SP) :

     

    String commandText = "Declare @return_value int; exec dbo.copyTemplate ? , ? , ? , ? , ? , ? , ?, Null , 0 , @return_value;"
    ;
    
    This code gets a empty DataTable:

     

     

       Console.WriteLine("DataAdapter"
    );
       using
     (OleDbConnection con = new
     OleDbConnection(connectionString))
       {
        using
     (OleDbDataAdapter da = new
     OleDbDataAdapter(commandText, con))
        using
     (DataTable singleTable = new
     DataTable())
        {
         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(singleTable);
    
         foreach
     (DataRow dr in
     singleTable.Rows)
         {
          foreach
     (DataColumn dc in
     singleTable.Columns)
          {
           Console.Write(dr[dc].ToString());
           Console.Write(" "
    );
          }
          Console.WriteLine();
         }
        }
       }
    

     

     

    This other code gets a DataSet with a non-empty DataTable

     

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

     

    These returns:

    Data Adapter

    Data Adapter with DataSet
    1066

    The problem here is that DataAdapter.Fill(DataTable) is returnig an empty DataTable and DataAdapter.Fill(DataSet) it is returning a DataSet with one non-empty DataTable, and I'd like to know why that difference, because obiously, that is not right. If I use a DataReader, I got the same result that with a DataSet, so apparently it's not working properly with the DataTable.

    The second parameter is different but it makes no difference in the result. I can put whatever value there, the result will be the same. I can execute these calls in different order too, and the result will stand.

    Kind regards.


    • Edited by vtortola Wednesday, October 13, 2010 12:02 PM
    Wednesday, October 13, 2010 11:02 AM

Answers

  • Hi vtortola,

    Just as Mike mentioned, the overload of Fill that takes DataTable as a parameter only obtains the first result. Use an overload of Fill that takes DataSet as a parameter to obtain multiple results. Since your commandtext is a batch of SQL Statements, so when you use Fill method that takes DataTable as a parameter, only the first result will be obtained, and you got nothing because you just declare a return value in the first part of the statement. When you use an overload of Fill that takes DataSet as a parameter, multiple results will be obtained, so you get the correct returned value. And because DataReader can also be used to get multiple result sets from the data source, so you can got the correct result with a DataReader.

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by vtortola Thursday, October 14, 2010 2:51 PM
    Thursday, October 14, 2010 3:00 AM
    Moderator

All replies

  • You would need to post code. You do not need to format it and you can unformatted text, but without code it is hard to say what is wrong
    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, October 13, 2010 11:23 AM
    Moderator
  • Why are you using 'using' statement all over the place?, you are suppose to use it only with 'connection' object. The problem is with 'using' is  as soon as control leaves the block it will be marked for garbage collection, so that object will no longer available for service.

    The using statement allows the programmer to specify when objects that use resources should release them. The object provided to the using statement must implement the IDisposable interface. This interface provides the Dispose method, which should release the object's resources.

    A using statement can be exited either when the end of the using statement is reached or if an exception is thrown and control leaves the statement block before the end of the statement.

      

     using (OleDbDataAdapter da = new OleDbDataAdapter(commandText, con)); //<---- here adapter object will marked for collection
    
    Wednesday, October 13, 2010 11:33 AM
  • Why are you using 'using' statement all over the place?,

    Because that code is just for issue demostration purpouse, it's not production code.

    I know how to use the "using" statement, and you will see that code works perfectly for what it is for  if you try it :)

    The problem here is that DataAdapter.Fill(DataTable) is returnig an empty DataTable and DataAdapter.Fill(DataSet) it is returning a DataSet with one non-empty DataTable, and I'd like to know why that difference, because obiously, that is not right.

    @VMazur I tried to simply paste it, but when I submit appears each word in a different line. I'm gonna try again.

    Regards.


    .: Valeriano Tórtola MCTS WPF :.: http://www.vtortola.net :.
    Wednesday, October 13, 2010 11:54 AM
  • the diffrence i see is the overload of Fill that takes DataTable as a parameter only obtains the first result and overload of Fill that takes DataSet as a parameter to obtain multiple results.
    Wednesday, October 13, 2010 12:17 PM
  • the diffrence i see is the overload of Fill that takes DataTable as a parameter only obtains the first result and overload of Fill that takes DataSet as a parameter to obtain multiple results.

    And what a DataReader does? Because I got result with a DataReader, that is what is confusing me.

    Cheers.


    .: Valeriano Tórtola MCTS WPF :.: http://www.vtortola.net :.
    Wednesday, October 13, 2010 1:27 PM
  • Hi vtortola,

    Just as Mike mentioned, the overload of Fill that takes DataTable as a parameter only obtains the first result. Use an overload of Fill that takes DataSet as a parameter to obtain multiple results. Since your commandtext is a batch of SQL Statements, so when you use Fill method that takes DataTable as a parameter, only the first result will be obtained, and you got nothing because you just declare a return value in the first part of the statement. When you use an overload of Fill that takes DataSet as a parameter, multiple results will be obtained, so you get the correct returned value. And because DataReader can also be used to get multiple result sets from the data source, so you can got the correct result with a DataReader.

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by vtortola Thursday, October 14, 2010 2:51 PM
    Thursday, October 14, 2010 3:00 AM
    Moderator
  • Thanks Alex, apparently that is the problem, the multiple return sets.

    I didn't know that a DataReader can go through multiple result sets as well.

    Kind regards.


    .: Valeriano Tórtola MCTS WPF :.: http://www.vtortola.net :.
    Thursday, October 14, 2010 2:52 PM