c# - 如何将数据库表内容发送到 API？

Question

我在 SQL Server 数据库表中有需要传输到支付处理 API 的记录。只有 Transmitted = 0 的表记录才会发送到 API。我发现这个问题有助于我理解手头的错误。

我想将数据库表中的记录读入 C# 类，解析为 JSON，然后通过 API 调用发送。我尝试使用 Entity Framework Core，但在调用存储过程并将结果添加到我的模型类时收到空引用异常，如下所示：

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

尝试使用 Entity Framework Core 时，我创建了OnboardingList如下类：

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

    public IEnumerator<Onboarding> GetEnumerator()
    {

        return _onboarding?.GetEnumerator();
    }        

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

然后我恢复到调用存储过程的 SqlDataReader。该方法部分如下：

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;

当我运行控制台应用程序时，我收到错误Exception thrown: 'System.IndexOutOfRangeException' in System.Data.SqlClient.dll Index was outside the bounds of the array.

如何防止上述超出范围异常并确保包含我的数据库表中的所有合格记录以进行传输？

标签： c#sql-server

Answer 1

您似乎有两个不同的问题：

1. 你.ToString()一个空值，这会导致空引用异常。
2. 序数可能超出范围，列与提供的数值不匹配导致它超出范围。

检查DBNull.Value否则会出现要点一。我的假设是你从一个开始，而索引是从零开始的。所以第一列将从零开始，而不是从一开始。

您应该查找 Dapper 或一些内置实用程序，SqlDataReader以利用更简洁的代码调用来构建您的对象。Dapper 将通过简单地执行以下操作来压缩代码：

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

只要列名与属性名匹配，它就会被填充。根据提供的实体，它看起来很简单，但请查看文档。

此外，您应该将命令包装在using语句中，这样您就不会有人command.Dispose()在您的连接块中间意外调用 a ：

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

         }
}