0

How would I get data from a SQL Server table where there is null and convert it to a string in Web API ?`

I get this error:

Unable to cast object of type 'System.DBNull' to type 'System.String'

My code:

SqlCommand AGetCommand = new()
        {
            Connection = DataSource,
            CommandType = CommandType.StoredProcedure,
            CommandText = "GetCustomers"
        };

SqlDataReader AGetCommandDataReader;
AGetCommandDataReader = AGetCommand.ExecuteReader();

List<Customer> ExampleCustomers = new();

if (AGetCommandDataReader.HasRows)
{
    while (AGetCommandDataReader.Read())
    {
        Customer ExampleCustomer = new()
                {
                    CustomerID = (string)AGetCommandDataReader["CustomerID"],
                    CompanyName = (string)AGetCommandDataReader["CompanyName"],
                    ContactName = (string)AGetCommandDataReader["ContactName"],
                    ContactTitle = (string)AGetCommandDataReader["ContactTitle"],
                    Address = (string)AGetCommandDataReader["Address"],
                    City = (string)AGetCommandDataReader["City"],
                    Region = (string)AGetCommandDataReader["Region"],
                    PostalCode = (string)AGetCommandDataReader["PostalCode"],
                    Country = (string)AGetCommandDataReader["Country"],
                    Phone = (string)AGetCommandDataReader["Phone"],
                    Fax = (string)AGetCommandDataReader["Fax"]
                };

        ExampleCustomers.Add(ExampleCustomer);
    }
}

AGetCommandDataReader.Close();
DataSource.Close();

return ExampleCustomers;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
dhwng7
  • 1
  • You should not be using `PascalCase` for locals. Use `camelCase`. – Dai Nov 25 '21 at 21:33
  • You need to manually check for SQL `NULL` by doing `CompanyName = AGetCommandDataReader.IsDbNull("CompanyName") ? (String?)null : AGetCommandDataReader.GetString("CompanyName")`. Don't use the `[String]` indexer because that will cause unnecessary boxing of value-types. – Dai Nov 25 '21 at 21:35
  • Try this solution: https://stackoverflow.com/questions/870697/unable-to-cast-object-of-type-system-dbnull-to-type-system-string – Mustafa Nov 25 '21 at 21:53

0 Answers0