选择列=值或列为空的行(当前的解决方案是草率和缓慢的)

本文关键字：解决方案 缓慢 草率 选择 | 更新日期: 2023-09-27 18:03:06

我目前正在开发一个web表单，Inserts customer data into a database。插入前，数据库必须为queried for duplicates。我正在检查的字段是Name, Shipping Address, and Billing Address。Name是required field，至少是one address is also required。现在我的(非常混乱的)代码看起来像这样:

string partialQuery;
SqlConnection sqlConn = new SqlConnection(connString);
if(/*both addresses fillied out*/)  {
    partialQuery = "BillingAddress=@BillingAddress AND ShippingAddress=@ShippingAddress";
}
else if(/*only billing address is filled out*/)  {
    partialQuery = "BillingAddress=@BillingAddress AND ShippingAddress IS NULL";
}
else if(/*only shipping address is filled out*/)  {
    partialQuery = "BillingAddress IS NULL AND ShippingAddress=@ShippingAddress";
}
else  {  throw new Exception("error...");  }
SqlCommand cmd = new SqlCommand("SELECT * FROM Customers WHERE Name=@Name AND " + partalQuery, sqlConn);
cmd.Parameters.AddWithValue("@Name", Name.Text);
cmd.Parameters.AddWithValue("@BillingAddress", BillingAddress.Text, null);
cmd.Parameters.AddWithValue("@ShippingAddress", ShippingAddress.Text, null);

我一直在试图找到一个更有效的方法来做这个与一个SQL命令，我认为这段代码会做到这一点:

SELECT * 
FROM Customers 
 WHERE Name = @Name
 AND BillingAccount = ISNULL(@BillingAccount, BillingAccount)
 AND ShippingAccount = ISNULL(@ShippingAccount, ShippingAccount)

这个想法是检查@BillingAccount/@ShippingAccount是否为空，如果是，表达式将计算为BillingAccount = BillingAccount/ShippingAccount = ShippingAccount(然后计算为true)。

我遇到的问题是，虽然value = value为真，null = null为假，使整个WHERE子句为假(因为我使用and)。

我真的不知道从哪里开始。

旁注:如果你想知道为什么我的AddWithValue方法使用3个参数，它是一个扩展方法，将c#空值转换为SQL空值。如果您感兴趣，可以参考代码:http://www.codeproject.com/Tips/310792/Handling-null-values-in-SqlParameter-parameter-obj

选择列=值或列为空的行(当前的解决方案是草率和缓慢的)

在两边都使用IsNull 

  WHERE Name = @Name
    AND IsNull(BillingAccount, -1) = 
            Coalesce(@BillingAccount, BillingAccount, -1)
    AND IsNull(ShippingAccount, -1)  =
            Coalesce(@ShippingAccount, ShippingAccount, -1)

或者包括检查参数和字段是否都为空

   WHERE Name = @Name
    AND (Coalesce(@BillingAccount, BillingAccount) is Null Or
       BillingAccount = Coalesce(@BillingAccount, BillingAccount, -1))
    AND Coalesce(@ShippingAccount, ShippingAccount) is Null Or
       ShippingAccount = Coalesce(@ShippingAccount, ShippingAccount)

防止重复寄存器的一种方法:

<>之前 INSERT INTO Customers ( CustomerId ) SELECT 1 AS CustomerId WHERE NOT EXISTS ( SELECT 1 FROM Customers WHERE CustomerId = 1 ); 之前

避免null = null的一种方法是false

<>之前 SELECT * FROM Customers WHERE Name = @Name AND ISNULL(BillingAccount, '_') = ISNULL(@BillingAccount, '_') AND ISNULL(ShippingAccount, '_') = ISNULL(@ShippingAccount, '_')

另一种方式

 WHERE Name = @Name
 AND ( BillingAccount = @BillingAccount or  @BillingAccount is null )
 AND ( ShippingAccount = @ShippingAccount or  @ShippingAccount is null)

基于你的评论

where Name = @Name
and(
(BillingAccount = @BillingAccount and ShippingAccount = @ShippingAccount)or
(@BillingAccount is null and @ShippingAccount is null)
)

你能试试吗?

<>之前 SELECT * FROM Customers WHERE Name = @Name AND ISNULL(BillingAccount, '_') = ISNULL(@BillingAccount, ISNULL(BillingAccount, '_')) AND ISNULL(ShippingAccount, '_') = ISNULL(@ShippingAccount, ISNULL(ShippingAccount, '_'))

一个COALESCE优于ISNULL的例子

SELECT * 
FROM Customers 
 WHERE Name = @Name
 AND COALESCE(BillingAccount, 0) = COALESCE(@BillingAccount, BillingAccount, 0)
 AND COALESCE(ShippingAccount,0) = COALESCE(@ShippingAccount, ShippingAccount, 0)
相关文章：