c# - 使用具有多个参数化条件的 SELECT WHERE 查询检索表中的所有行

Question

我正在尝试从 SQLite 数据库中检索一些格式如下的数据:

表格

Customers

列

id | firstName | lastName | address | phone | email | notes

我有几个 TextBox 和一个 DataGridView 来显示来自客户的数据。我想做的是检索并在 DataGridView 中显示与任何 TextBox 的内容匹配的任何行。代码如下，但我无法弄清楚我的 SQL 语句做错了什么:

public static DataTable RecoverCustomer(Customer customer)
    {
        var connection = new SQLiteConnection("Data Source = prova.sqlite; Version=3;");

        var command = connection.CreateCommand();
        command.CommandText = "SELECT * FROM customers WHERE firstName = @firstName OR lastName = @lastName OR address = @address OR phone = @phone OR email = @email OR notes = @notes";
        command.Parameters.AddWithValue("@firstName", customer.FirstName);
        command.Parameters.AddWithValue("@lastName", customer.LastName);
        command.Parameters.AddWithValue("@address", customer.Address);
        command.Parameters.AddWithValue("@phone", customer.Phone);
        command.Parameters.AddWithValue("@email", customer.Email);
        command.Parameters.AddWithValue("@notes", customer.Notes);

        var dataAdapter = new SQLiteDataAdapter();
        var dataTable = new DataTable();

        connection.Open();
        dataAdapter.SelectCommand = command;
        dataAdapter.Fill(dataTable);
        connection.Close();

        return dataTable;
    }

我的问题是此代码返回的行数比应有的行数多。难道是因为在添加新客户时我没有检查是否有任何空字段，因此当我使用此代码搜索客户时返回这些行？如果是，我该如何缓解它？这是我用来向表中添加新客户的代码:

public static void CreateCustomer(Customer customer)
    {
        var connection = new SQLiteConnection("Data Source = prova.sqlite; Version=3;");
        var command = connection.CreateCommand();
        command.CommandText = "INSERT INTO customers (firstName, lastName, address, phone, email, notes) VALUES (@firstName, @lastName, @address, @phone, @email, @notes)";
        command.Parameters.AddWithValue("@firstName", customer.FirstName);
        command.Parameters.AddWithValue("@lastName", customer.LastName);
        command.Parameters.AddWithValue("@address", customer.Address);
        command.Parameters.AddWithValue("@phone", customer.Phone);
        command.Parameters.AddWithValue("@email", customer.Email);
        command.Parameters.AddWithValue("@notes", customer.Notes);

        connection.Open();
        command.ExecuteNonQuery();
        connection.Close();
    }

编辑

感谢@Haldo，我更正了与空格匹配的 SQL 语句，并且它有效。正确的说法是:

SELECT * FROM customers WHERE (IFNULL(@firstName, '') <> '' AND firstName = @firstName) OR (IFNULL(@lastName, '') <> '' AND lastName = @lastName) OR (IFNULL(@address, '') <> '' AND address = @address) OR (IFNULL(@phone, '') <> '' AND phone = @phone) OR (IFNULL(@email, '') <> '' AND email = @email) OR (IFNULL(@notes, '') <> '' AND notes = @notes)

Answer 1

根据您的评论，看起来发生的情况是 SQL Select 语句匹配空/空值，这就是返回的行数比预期多的原因。

您希望仅当参数具有值时才匹配结果。因此，将 select 语句修改为以下内容应该可行:

SELECT * FROM customers 
    WHERE (IFNULL(@firstName, '') <> '' AND firstName = @firstName) 
    OR (IFNULL(@lastName, '') <> '' AND lastName = @lastName)
    OR (IFNULL(@address, '') <> '' AND address = @address)
    OR (IFNULL(@phone, '') <> '' AND phone = @phone)
    OR (IFNULL(@email, '') <> '' AND email = @email)
    OR (IFNULL(@notes, '') <> '' AND notes = @notes)

这使用IFNULL(对于sqlite)，但在SQL Server中使用ISNULL也可以达到相同的结果。使用 IFNULL 将处理参数为空字符串 '' 或 NULL 的情况。