当表实体中的多个变量是 DBNull 时，尽量不获取重复的代码

Question

我有这段代码，可以将所有订单从数据库中获取，但是变量ArriveTime和Paymethod在数据库中可为空。如果Paymethod在数据库中返回DBNull，我希望它变得iDeal，当ArriveTime是DBNull时，我希望它null。如何在不获取重复代码的情况下执行此操作。

现在除了在代码中放入大量if语句之外，我想不出其他方法......

现在的代码 (5( = 到达时间，(8( = 付款方式：

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();
SqlDataReader reader = cmd.ExecuteReader();
List<OrderDTO> orders = new List<OrderDTO>();
int orderIDindex = 1;
while (reader.Read())
{
if (reader.IsDBNull(5))
{
OrderDTO orderDTO = new OrderDTO
{
OrderID = Convert.ToInt32(reader["OrderID"]),
TotalPrice = Convert.ToInt32(reader["TotalPrice"]),
UserID = Convert.ToInt32(reader["UserID"]),
OrderDate = Convert.ToDateTime(reader["OrderDate"]),
ArriveTime = null,
To_Adress = reader["To_Adress"].ToString(),
ItemCount = Convert.ToInt32(reader["ItemCount"]),
Status = (OrderStatus.Orderstatus)Enum.Parse(typeof(OrderStatus.Orderstatus), reader["Status"].ToString()),
};
orders.Add(orderDTO);
}
else if (reader.IsDBNull(8))
{
OrderDTO orderDTO = new OrderDTO
{
OrderID = Convert.ToInt32(reader["OrderID"]),
TotalPrice = Convert.ToInt32(reader["TotalPrice"]),
UserID = Convert.ToInt32(reader["UserID"]),
OrderDate = Convert.ToDateTime(reader["OrderDate"]),
Paymethod = PayMethod.Paymethod.iDeal,
To_Adress = reader["To_Adress"].ToString(),
ItemCount = Convert.ToInt32(reader["ItemCount"]),
Status = (OrderStatus.Orderstatus)Enum.Parse(typeof(OrderStatus.Orderstatus), reader["Status"].ToString()),
};
orders.Add(orderDTO);
}
}
return orders;
}
}
}

Answer 1

将重复的代码移出条件

//...omitted for brevity
while (reader.Read()) {
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()),
};
if (reader.IsDBNull(5)) orderDTO.ArriveTime = null;        
if (reader.IsDBNull(8)) orderDTO.Paymethod = PayMethod.Paymethod.iDeal,
orders.Add(orderDTO);
}
//...omitted for brevity

如果在未DBNull时使用这些值，请检查条件并提供适当的值。

OrderDTO orderDTO = new OrderDTO {
//...omitted for brevity
ArriveTime = reader.IsDBNull(5) ? null : Convert.ToDateTime(reader[5]),
Paymethod = reader.IsDBNull(8) ? PayMethod.Paymethod.iDeal : //...parse the value,
//...omitted for brevity
};

Answer 2

一个更好的替代方法是使用像dapper这样的ORM。您可以将 50+ LOC 降低到小于 5。

在dapper的帮助下，您需要做的就是

using (var connection = new SqlConnection(_sqlConnectionString))
{
var results = connection.Query<OrderDTO>(query).ToList();
if(results.Paymethod ==null){
results.Paymethod = PayMethod.Paymethod.iDeal
}
return results;
}

如果ArriveTime为空，则null。您不必将其重新分配为 null。