SqlDataReader is lost after posting request using RestSharp

Question

I'm writing a Windows service. It periodically connects to a SQL database and looks for new records. I can read the SQL database without an issue and it gets the data using a SqlDataReader. I then have a while loop that just does a while Read. Part way through the while loop I create a web request to send that data to a server side component. For that I am using RestSharp v107. When I call ExecuteAsync with the request, I can see the data being sent and it ends up on my server side without an issue. However, when the response comes back, it has wiped out my SqlDataReader so on the next loop I get "Exception: Invalid attempt to call Read when reader is closed." as the reader is null. Here is the code, truncated where necessary for brevity.

        private async Task AddUpdateCustomers(SqlConnection poConn)
        {
            string sQuery = "select * from customers";

            try
            {
                SqlCommand oCmd = new SqlCommand(sQuery, poConn);
                poConn.Open();

                using (SqlDataReader dataReader = oCmd.ExecuteReader())
                {
                    if (dataReader.HasRows)
                    {
                        while (dataReader.Read())
                        {
                            int iRecordId = dataReader.GetInt32(dataReader.GetOrdinal("ID"));

                            Customer customer = new Customer()
                            {
                                CustomerId = dataReader.GetString(dataReader.GetOrdinal("CustomerCodeFinancialReference")),
                                Name = dataReader.GetString(dataReader.GetOrdinal("Name"))
                                // more fields here removed for brevity
                            };

                            RestRequest request = new RestRequest();

                            request.AddHeader("Profile", aCLIntacctClientServiceConfiguration.ProfileName);
                            request.AddHeader("Entity", aCLIntacctClientServiceConfiguration.EntityName);
                            request.AddHeader("CreateUpdate", dataReader.GetBoolean(dataReader.GetOrdinal("IsNew")) ? "create" : "update");
                            request.AddHeader("Type", "Customer");
                            request.AddHeader("CorrelationId", Guid.NewGuid().ToString());

                            string body = ObjectToString(customer);

                            request.AddStringBody(body, DataFormat.Json);

                            var options = new RestClientOptions(ClientURI)
                            {
                                ThrowOnAnyError = false,
                                Timeout = 30000
                            };

                            RestClient client = new RestClient(options);

                            RestResponse response = await client.ExecuteAsync(request);

                            if (!string.IsNullOrEmpty(response.Content))
                            {
                                try
                                {
                                    CustomResponse customerResponseObject = JsonConvert.DeserializeObject<CustomResponse>(response.Content);

                                    if (customerResponseObject.ExitCode == 0)
                                    {
                                        WriteBackToStagingTable(iRecordId, true, "");
                                    }
                                    else
                                    {
                                        string sMessages = "";

                                        foreach (CustomResponseMessage message in customerResponseObject.Messages)
                                        {
                                            sMessages += $"Record ID '{message.RecordId}' failed with the following error: '{message.MessageValue}'" + Environment.NewLine;
                                        }

                                        WriteBackToStagingTable(iRecordId, false, sMessages);
                                    }
                                }
                                catch (Exception ex)
                                {
                                    WriteBackToStagingTable(iRecordId, false, ex.Message + Environment.NewLine + response.Content);
                                }
                            }
                        }

                        if (!dataReader.IsClosed)
                        {
                            dataReader.Close();
                        }
                    }
                    else
                    {
                        // No records read from db
                    }
                }
            }
            catch (Exception ex)
            {
                HandleLogRequest("Exception: " + ex.Message);
            }
        }

I've tried using PostAsync but that didn't work and reading the documentation, it isn't what I want. I cannot see why after calling client.ExecuteAsync the dataReader is suddenly null. I'm using RestSharp v107 and so many examples on the web but they are earlier versions and there are a lot of differences.

Answer 1

Rather than pass the connection round as it wasn't particularly necessary, I moved the connection into each function and created it as necessary. With the SQL data reader, I didn't bother with the using block. I also changed it so that it was executing asynchronously.

SqlDataReader dataReader = await oCmd.ExecuteReaderAsync();

Once I'd done that, calling the rest client execute method, it returned and the data reader was still in scope.