如何避免重复代码处理订单可空字段的DBNull值

Great question! Your current approach is creating duplicate OrderDTO instances for each nullable case, which leads to tons of redundant code. The fix is to initialize all shared properties once, then handle the nullable fields (ArriveTime and Paymethod) separately. Here's a clean, maintainable refactor:

public List<OrderDTO> GetAllOrders() 
{ 
    using (SqlConnection con = new SqlConnection(connectionString)) 
    { 
        string query = "SELECT * FROM [dbo].[Order]"; 
        using (SqlCommand cmd = new SqlCommand(query, con)) 
        { 
            con.Open(); 
            // Wrap reader in using to ensure proper disposal (your original code missed this)
            using (SqlDataReader reader = cmd.ExecuteReader()) 
            { 
                List<OrderDTO> orders = new List<OrderDTO>(); 
                
                while (reader.Read()) 
                { 
                    // Initialize OrderDTO with all common properties first
                    OrderDTO orderDTO = new OrderDTO 
                    { 
                        OrderID = Convert.ToInt32(reader["OrderID"]), 
                        TotalPrice = Convert.ToInt32(reader["TotalPrice"]), 
                        UserID = Convert.ToInt32(reader["UserID"]), 
                        OrderDate = Convert.ToDateTime(reader["OrderDate"]), 
                        To_Adress = reader["To_Adress"].ToString(), 
                        ItemCount = Convert.ToInt32(reader["ItemCount"]), 
                        Status = (OrderStatus.Orderstatus)Enum.Parse(typeof(OrderStatus.Orderstatus), reader["Status"].ToString())
                    }; 

                    // Handle ArriveTime nullable case
                    // Use column name instead of hardcoded index for readability
                    int arriveTimeOrdinal = reader.GetOrdinal("ArriveTime");
                    orderDTO.ArriveTime = reader.IsDBNull(arriveTimeOrdinal) ? null : (DateTime?)Convert.ToDateTime(reader[arriveTimeOrdinal]);

                    // Handle Paymethod nullable case
                    int payMethodOrdinal = reader.GetOrdinal("Paymethod");
                    if (reader.IsDBNull(payMethodOrdinal))
                    {
                        orderDTO.Paymethod = PayMethod.Paymethod.iDeal;
                    }
                    else
                    {
                        orderDTO.Paymethod = (PayMethod.Paymethod)Enum.Parse(typeof(PayMethod.Paymethod), reader[payMethodOrdinal].ToString());
                    }

                    orders.Add(orderDTO); 
                } 
                return orders; 
            }
        } 
    } 
}

Key Improvements: #

• No duplicate code: All shared properties are set once, with only the nullable fields handled as edge cases.
• Better maintainability: Replaced hardcoded indexes (like 5 and 8) with reader.GetOrdinal("ColumnName")—this avoids breakage if your table schema changes later.
• Proper resource management: Added a using statement for the SqlDataReader to ensure it's disposed correctly.

Even Cleaner: Use Extension Methods #

To make the code more reusable and concise, create extension methods for SqlDataReader to handle these nullable conversions:

public static class SqlDataReaderExtensions
{
    public static DateTime? GetNullableDateTime(this SqlDataReader reader, string columnName)
    {
        int ordinal = reader.GetOrdinal(columnName);
        return reader.IsDBNull(ordinal) ? null : (DateTime?)reader.GetDateTime(ordinal);
    }

    public static PayMethod.Paymethod GetPayMethod(this SqlDataReader reader, string columnName)
    {
        int ordinal = reader.GetOrdinal(columnName);
        return reader.IsDBNull(ordinal) 
            ? PayMethod.Paymethod.iDeal 
            : (PayMethod.Paymethod)Enum.Parse(typeof(PayMethod.Paymethod), reader[ordinal].ToString());
    }
}

Now your main code becomes super clean:

while (reader.Read()) 
{ 
    OrderDTO orderDTO = new OrderDTO 
    { 
        OrderID = reader.GetInt32(reader.GetOrdinal("OrderID")), 
        TotalPrice = reader.GetInt32(reader.GetOrdinal("TotalPrice")), 
        UserID = reader.GetInt32(reader.GetOrdinal("UserID")), 
        OrderDate = reader.GetDateTime(reader.GetOrdinal("OrderDate")), 
        To_Adress = reader.GetString(reader.GetOrdinal("To_Adress")), 
        ItemCount = reader.GetInt32(reader.GetOrdinal("ItemCount")), 
        Status = (OrderStatus.Orderstatus)Enum.Parse(typeof(OrderStatus.Orderstatus), reader["Status"].ToString()),
        ArriveTime = reader.GetNullableDateTime("ArriveTime"),
        Paymethod = reader.GetPayMethod("Paymethod")
    }; 

    orders.Add(orderDTO); 
}

Bonus Tip: Safer Enum Parsing #

Instead of Enum.Parse, use Enum.TryParse to avoid runtime exceptions if the database value doesn't match your enum:

if (Enum.TryParse(reader["Status"].ToString(), out OrderStatus.Orderstatus status))
{
    orderDTO.Status = status;
}
else
{
    // Handle invalid status (e.g., set a default or log an error)
    orderDTO.Status = OrderStatus.Orderstatus.Unknown;
}

内容的提问来源于stack exchange，提问作者zhrgci