查询远程 SQL Server 上的表，以便以格式化方式进行处理和显示

Question

我正在尝试查询远程SQL Server上的表以处理并以格式化的内容显示数据。

当我尝试这个时，它不断抛出异常并说

指定的强制转换无效

我已经包含了我正在运行查询的类。出于显而易见的原因，我已经删除了连接到服务器的真实细节。这是我的数据库的数据库的内容。我不需要客户 ID，但我确实需要其余列。

任何帮助将不胜感激。

static class CustomerRepository
{
    private static string connString = "Server=xxxx.com;"
                                     + "Database=xxxxxx;"
                                     + "User ID=xxxxxx;"
                                     + "Password=xxxxxx;"
                                     + "Integrated Security=False;";
    public static CustomerCollection getAllCustomers()
    {
        CustomerCollection customers;
        using (SqlConnection conn = new SqlConnection(connString))
        {
            string query = string.Format("{0} {1} {2}"
                                        , "SELECT CompanyName, Address, City, Province, PostalCode, CreditHold"
                                        , "FROM Customer"
                                        , "ORDER BY CompanyName");
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = query;
                cmd.Connection = conn;
                conn.Open();
                customers = new CustomerCollection();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    string companyName;
                    string address = null;
                    string city = null;
                    string province = null;
                    string postalCode = null;
                    bool creditHold = false;
                    while (reader.Read())
                    {
                        companyName = reader["CompanyName"] as string;
                        if (!reader.IsDBNull(1))
                        {
                            address = reader[1] as string;
                        }
                        if (!reader.IsDBNull(2))
                        {
                            city = reader[2] as string;
                        }
                        if (!reader.IsDBNull(3))
                        {
                            province = reader[3] as string;
                        }
                        if (!reader.IsDBNull(4))
                        {
                            postalCode = reader[4] as string;
                        }
                        if (!reader.IsDBNull(5))
                        {
                            creditHold = (bool)reader[5];
                        }
                        customers.Add(new CustomerInfo(companyName, address, city, province, postalCode, creditHold));
                    }
                    return customers;
                }
            }
        }
    }
}

Answer 1

最可能的误差原因是将 Sql 类型Tinyint值强制转换为第 5
列中的 .NET 类型bool值无效首先从具有相应 .NET 类型的SqlDataReader检索数据（Tinyint 到 byte
）然后使用逻辑将Byte值转换为bool

if (!reader.IsDBNull(5))
{
    byte rawCreditHold = reader.GetByte(5);
    creditHold = (rawCreditHold == 1);
}

Answer 2

试试这个：

    while (reader.read())
    {
            if (reader["CompanyName"] != System.DBNull.Value) companyName = Convert.ToString(reader["CompanyName"]);
            if (reader["Address "] != System.DBNull.Value) address  = Convert.ToString(reader["Address"]);
            if (reader["City"] != System.DBNull.Value) city = Convert.ToString(reader["City"]);
            if (reader["PostalCode"] != System.DBNull.Value) postalCode  = Convert.ToString(reader["PostalCode"]);
            if (reader["Province"] != System.DBNull.Value) province  = Convert.ToString(reader["Province"]);
            if (reader["CreditHold"] != System.DBNull.Value) creditHold = Convert.ToBoolean(reader["CreditHold"]);
            customers.Add(new CustomerInfo(companyName, address, city, province, postalCode, creditHold));
    }

Answer 3

将creditHold = (bool)reader[5];替换为

if (reader[5].ToString() == "1")
{
    creditHold = true;
}
else
{
    creditHold = false;
}