0

I have a very complex stored procedure that is definitely returning values when I run ExecuteReader but when I try to load the data, it seems to disappear.

        using (var dbContext = new SuperPortalDbContext())
        {
            // Create resulting data table
            System.Data.DataTable result = new DataTable(); 

            // Create command 
            var cmd = dbContext.Database.Connection.CreateCommand();
            cmd.CommandType = commandType;
            cmd.CommandText = sql;
            cmd.CommandTimeout = 120;

            // Add all parameters
            foreach (SqlParameter parameter in parameters)
            {
                var p = cmd.CreateParameter();
                p.ParameterName = parameter.ParameterName;
                p.Value = parameter.Value;
                cmd.Parameters.Add(p);
            }

            try
            {
                // Execute
                dbContext.Database.Connection.Open();
                System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
                result.Load(reader);
            }
            finally
            {
                // closes the connection
                dbContext.Database.Connection.Close();
            }

            // returns the DataSet
            return result;
        }
7
  • Is it returning multiple results? Check reader.NextResult() to see.learn.microsoft.com/en-us/dotnet/api/… Commented Mar 10, 2023 at 16:33
  • No, if I try reader.NextResult() I get an exception. Reader definitely has 18 fields which is correct and also HasRows is true so there is data there. I tried creating using this method DataSet dsCustomers = new DataSet(); dsCustomers.Tables.Add("Customers"); //Load DataReader into the DataTable. dsCustomers.Tables[0].Load(sdr); and managed to get data across, but only one field, this can't be the only way to load the data? Commented Mar 10, 2023 at 19:07
  • What exception? That's unexpected; dd that to your question. Also run it in SSMS and output to text mode to see exactly what gets returned. Commented Mar 10, 2023 at 19:21
  • System.InvalidOperationException, I get this if I try to run NextResult() after the Load() so I think the Load() is breaking the reader. Is there a limit on the number of rows this method can handle as I've got 270k. Commented Mar 10, 2023 at 19:47
  • There is no row limit. Commented Mar 10, 2023 at 19:58

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.