VB.NET — передовая практика обработки сложных SQL-запросов с необязательными параметрами
В моем приложении у пользователя должна быть возможность просматривать заказы, размещенные клиентами, и должна быть возможность применять к поиску несколько фильтров. Это означает, что мне нужен динамический SQL-запрос, в котором можно применять переменное количество параметров. Как лучше всего справиться с этим в стандартном приложении WinForms?
До сих пор я работал с TableAdapters и хранимыми процедурами, но, насколько мне известно, я не могу использовать их с необязательными параметрами. Так, например, если пользователь хочет видеть все заказы клиентов, это не проблема. Но также должна быть возможность сказать, например, «Показать все заказы, которые были размещены за последние 2 недели и где хотя бы один продукт содержит слово «подарочный код»». Таким образом, дата и название продукта будут необязательными параметрами, но если я оставлю их пустыми в хранимой процедуре, я получу ошибку.
Чтобы исправить это, я начал создавать свои собственные запросы в отдельном классе, используя SqlCommands и параметры. Я динамически генерирую commandText для каждой команды в зависимости от параметров, переданных в функцию, затем я добавляю параметры в SqlCommand, выполняю его и перебираю SqlDataReader, чтобы создать список элементов, которые я верну в свою программу. Например (упрощенно):
Dim cmd As New SqlCommand With {.Connection = con}
cmd.commandText = "SELECT o.id, o.customer_name, o.date, p.productName FROM orders o JOIN order_positions p ON o.id = p.order_id WHERE o.date >= @pDate"
cmd.Parameters.Add("@pDate", SqlDbType.DateTime).Value = searchDate
Dim reader As SqlDataReader = cmd.ExecuteReader()
Dim lstOrderItems As New List(Of OrderDisplayItem)
while reader.read
dim orderId as Integer = reader.Item(0)
dim customerName as String = reader.Item(1)
dim date as Date = reader.Item(2)
dim productName as String = reader.Item(3)
lstOrderItems.add(New OrderDisplayItem With{.id = orderId, .customerName = customerName, .date = date, .productName = productName})
End While
return lstOrderItems
Очевидно, это просто для того, чтобы показать, как я поступаю. На самом деле мне приходится создавать дополнительные циклы, потому что в одном заказе может быть один или несколько товаров и т. д.
Мой вопрос будет: это правильный способ справиться с этим? Кажется, что весь этот класс станет действительно большим, потому что у меня есть и другие запросы, такие как поиск счетов, продажи в магазине и т. д., и для каждого запроса я должен писать эти циклы чтения, которые мне придется изменять снова и снова, если небольшое вещь в моей базе данных меняется. Неужели невозможно справиться с этим в Visual Studio tableAdapters?
Ответы 1
Это означает, что мне нужен динамический SQL-запрос, в котором можно применять переменное количество параметров.
Нет, это не так. Вы можете использовать один запрос с одним набором параметров и просто указать NULL для тех параметров, которые вы хотите игнорировать, если вы структурируете свой SQL следующим образом:
SELECT *
FROM MyTable
WHERE (@Column1 IS NULL OR Column1 = @Column1)
AND (@Column2 IS NULL OR Column2 = @Column2)
Тогда ваш код VB может выглядеть примерно так:
Using connection As New SqlConnection("connection string here"),
command As New SqlCommand(query, connection)
command.Parameters.Add("@Column1", SqlDbType.VarChar, 50).Value = If(TextBox1.TextLength = 0, CObj(DBNull.Value), TextBox1.Text)
command.Parameters.Add("@Column2", SqlDbType.VarChar, 50).Value = If(TextBox2.TextLength = 0, CObj(DBNull.Value), TextBox2.Text)
'...
End Using
Когда вы предоставляете NULL для параметра, который фактически соответствует каждой записи, и этот параметр фактически игнорируется. Вы можете сделать это с любым количеством параметров любого типа данных, который вам нравится.
Используете ли вы типизированный DataSet и связанные с ним адаптеры таблиц, не имеет значения. Вы можете создавать такие запросы в любом случае.
Спасибо, это очень полезно. Я занимаюсь этой проблемой уже 2 дня и нигде не могу найти сопоставимое решение. Итак, я предполагаю, что тогда мне не нужно чувствовать себя плохо из-за того, что я не использую tableAdapters, верно?