How to Send Database Table Contents to API?

Question

I have records in a SQL Server database table that need to be transmitted to a payment processing API. Only table records where Transmitted = 0 are to be sent to the API. I found this question which helps me to understand the error at hand.

I want to read the records from my database table into a C# class, parse into JSON, and send via API call. I've tried using Entity Framework Core but receive Null Reference exception when I called the stored procedure and add the results to my model class as follows:

        public async Task<IEnumerator<Onboarding>> GetOnboardingList()
    {
        try
        {
            using (var context = new SOBOContext())
            {
                var ob = await context.Onboarding
                    //.Where(o => o.Transmitted == false)
                    .FromSql("Execute GetUnsentOnboardingRecords_sp")
                    .ToListAsync();
                Console.WriteLine(ob.ToArray());
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }

        return new OnboardingList().GetEnumerator();
    }

When attempting to use Entity Framework Core, I created the OnboardingList class as below:

public class OnboardingList : IEnumerable<Onboarding>
{
    private readonly List<Onboarding> _onboarding;

    public IEnumerator<Onboarding> GetEnumerator()
    {

        return _onboarding?.GetEnumerator();
    }        

    IEnumerator IEnumerable.GetEnumerator()
    {
        return _onboarding.GetEnumerator();
    }
}

I then reverted to SqlDataReader that calls the stored procedure. The method, in part, is as follows:

var listOnboardingModel = new List<Onboarding>();
        try
        {
            using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("GetUnsentOnboardingRecords_sp", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };
                connection.Open();

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        listOnboardingModel.Add(new Onboarding
                        {
                            OnboardingId = reader[1] as int? ?? 0,
                            UserId = reader[2] as int?,
                            UserName = reader[3].ToString(),
                            FirstName = reader[4].ToString(),
                            MiddleInitial = reader[5].ToString(),
                            Lastname = reader[6].ToString(),
                            DateOfBirth = reader[7].ToString(),
                            Ssn = reader[8].ToString(),
                            Email = reader[9].ToString(),
                            Address1Line1 = reader[10].ToString(),
                            Address1Line2 = reader[11].ToString(),
                            Address1ApartmentNumber = reader[12].ToString(),
                            Address1City = reader[13].ToString(),
                            Address1State = reader[14].ToString(),
                            Address1ZipCode = reader[15].ToString(),
                            Address1Country = reader[16].ToString(),
                            DayPhone = reader[17].ToString(),
                            EveningPhone = reader[18].ToString(),
                            PhonePin = reader[19].ToString(),
                            MerchantSourceIp = reader[20].ToString(),
                            ThreatMetrixPolicy = reader[21].ToString(),
                            SessionId = reader[22].ToString(),
                            BankAccount1CountryCode = reader[23].ToString(),
                            BankAccount1Name = reader[24].ToString(),
                            BankAccount1Description = reader[25].ToString(),
                            BankAccount1Number = reader[26].ToString(),
                            BankAccount1OwnershipType = reader[27].ToString(),
                            BankAccount1Type = reader[28].ToString(),
                            BankAccount1BankName = reader[29].ToString(),
                            BankAccount1RoutingNumber = reader[30].ToString(),
                            BankAccount2CountryCode = reader[31].ToString(),
                            BankAccount2Name = reader[32].ToString(),
                            BankAccount2Number = reader[33].ToString(),
                            BankAccount2OwnershipType = reader[34].ToString(),
                            BankAccount2Type = reader[35].ToString(),
                            BankAccount2BankName = reader[36].ToString(),
                            BankAccount2Description = reader[37].ToString(),
                            BankAccount2RoutingNumber = reader[38].ToString(),
                            AuthSginerFirstName = reader[39].ToString(),
                            AuthSignerLastName = reader[40].ToString(),
                            AuthSignerTitle = reader[41].ToString(),
                            AverageTicket = reader[42] as int? ?? 0,
                            BusinessLegalName = reader[43].ToString(),
                            BusinessApartmentNumber = reader[44].ToString(),
                            BusinessAddressLine1 = reader[45].ToString(),
                            BusinessAddressLine2 = reader[46].ToString(),
                            BusinessCity = reader[47].ToString(),
                            BusinessState = reader[48].ToString(),
                            BusinessZipCode = reader[49].ToString(),
                            BusinessCountry = reader[50].ToString(),
                            BusinessDescription = reader[51].ToString(),
                            DoingBusinessAs = reader[52].ToString(),
                            Ein = reader[53].ToString(),
                            HighestTicket = reader[54] as int? ?? 0,
                            MerchantCategoryCode = reader[55].ToString(),
                            MonthlyBankCardVolume = reader[56] as int? ?? 0,
                            OwnerFirstName = reader[57].ToString(),
                            OwnerLastName = reader[58].ToString(),
                            OwnerSsn = reader[59].ToString(),
                            OwnerDob = reader[60].ToString(),
                            OwnerApartmentNumber = reader[61].ToString(),
                            OwnerAddress = reader[62].ToString(),
                            OwnerAddress2 = reader[63].ToString(),
                            OwnerCity = reader[64].ToString(),
                            OwnerRegion = reader[65].ToString(),
                            OwnerZipCode = reader[66].ToString(),
                            OwnerCountry = reader[67].ToString(),
                            OwnerTitle = reader[68].ToString(),
                            OwnerPercentage = reader[69].ToString(),
                            BusinessUrl = reader[70].ToString(),
                            CreditCardNumber = reader[71].ToString(),
                            ExpirationDate = reader[72].ToString(),
                            NameOnCard = reader[73].ToString(),
                            PaymentMethodId = reader[74].ToString(),
                            PaymentBankAccountNumber = reader[75].ToString(),
                            PaymentBankRoutingNumber = reader[76].ToString(),
                            PaymentBankAccountType = reader[77].ToString(),
                            Transmitted = reader[78] as bool?,
                            TransmitDate = reader[79].ToString(),
                            InternationalId = reader[80].ToString(),
                            DriversLicenseVersion = reader[81].ToString(),
                            DocumentType = reader[82].ToString(),
                            DocumentExpDate = reader[83].ToString(),
                            DocumentIssuingState = reader[84].ToString(),
                            MedicareReferenceNumber = reader[85].ToString(),
                            MedicareCardColor = reader[86].ToString(),
                            PaymentBankAccountName = reader[87].ToString(),
                            PaymentBankAccountDescription = reader[88].ToString(),
                            PaymentBankCountryCode = reader[89].ToString(),
                            PaymentBankName = reader[90].ToString(),
                            PaymentBankOwnershipType = reader[91].ToString()
                        });
                    }

                }

                connection.Close();

            }
            Console.WriteLine(listOnboardingModel);

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        return listOnboardingModel;

When I run the console application, I receive the Error Exception thrown: 'System.IndexOutOfRangeException' in System.Data.SqlClient.dll Index was outside the bounds of the array.

How do I prevent the above out of range exception and also ensure that all qualifying records from my database table are included for transmission?

Answer 1

You appear to have two distinct issues:

1. You .ToString() a null value, which causes null reference exception.
2. An ordinal is likely out of bounds, a column does not match the numeric value provided causing it to be out of bounds.

Check for DBNull.Value otherwise bullet point one will occur. My assumption would be that you start with one, while index's are zero based. So the first column would start at zero not one.

You should lookup Dapper or some built in utilities for SqlDataReader to leverage cleaner code calls to build your object. Dapper would condense the code by simply doing:

IEnumerable<Sample> GetSamples() => dbConnection.Query<Sample>(...);

As long as the column name matches the property name, it'll populate. Based on the entity provided it looks pretty simple, but checkout the documentation.

Also you should wrap command in using statement like, that way you do not have someone accidentally call a command.Dispose() in the middle of your connection block on accident:

using(var connection = new SqlConnection(...))
using(var command = new SqlCommand(..., ...))
{
     connection.Open();
     ...
     using(var reader = command.ExecuteReader())
         while(reader.Read())
         {

         }
}