有没有办法创建接受

Suppose I have a search screen that is intended for looking up items. There are various optional search options on the screen that will cause the SQL query statement to vary.

假设我有一个用于查找项目的搜索屏幕。屏幕上有各种可选的搜索选项,这些选项将导致SQL查询语句发生变化。

Here are some example searches:

以下是一些示例搜索:

Description search
Description search + item supplier id

说明搜索+项目供应商ID

Description search + item supplier id + item hierarchy level 1 id

说明搜索+项目供应商ID +项目层次结构级别1 id

Description search + item supplier id + item hierarchy level 1 id + level 2 id

说明search + item supplier id + item hierarchy level 1 id + level 2 id

item hierarchy level 1 id + level 2 id (no description, no item supplier id)

项目层次结构级别1标识+级别2标识(无描述,没有项目供应商标识)

...you get the idea. There are quite a number of possible combinations. I was hoping to use parameterized queries for the performance benefits and such (plus I'm using them for the rest of the queries throughout the program).

......你明白了。有许多可能的组合。我希望使用参数化查询来获得性能优势等(而且我将它们用于整个程序中的其余查询)。

Is there a way to do this or am I forced to either create each possible query and matching SQLiteCommand object or build the query string dynamically with a StringBuilder based on the options selected?

有没有办法做到这一点,还是我*创建每个可能的查询和匹配SQLiteCommand对象或基于所选的选项动态构建查询字符串?

I'm using using the SQLite.NET data provider with C# 3.0 (on the 3.5 compact framework).

我正在使用SQLite.NET数据提供程序和C#3.0(在3.5紧凑框架上)。

UPDATE

Based on some of the suggestions with null default values for the parameters and using (@param isnull or column = @param), I think I should be able to get this to work. I'll keep you posted.

基于一些建议参数和使用的默认值为null(@param isnull或column = @param),我想我应该可以让它工作。我会及时向大家发布。

NOTE: I am avoiding using stored procedures because the rest of the code uses parameterized queries instead of stored procedures. I'd like to keep everything consistent for the sanity of future maintenance programmers. It shouldn't make too much of a difference anyway.

注意:我正在避免使用存储过程,因为其余代码使用参数化查询而不是存储过程。我想保持一致性,以保证未来维护程序员的理智。无论如何,它不应该产生太大的差别。

UPDATE 2

This worked great on a desktop system (which is where I did my initial testing for the queries). However, it was very slow on the Windows CE Device I was using. Unusably slow. All the same, I can definitely use this in the future and it's very handy. Just not when running queries on a mobile device.

这在桌面系统上很有用(我在那里对查询进行了初步测试)。但是,我使用的Windows CE设备上的速度非常慢。不可思议的慢。尽管如此,我肯定可以在将来使用它,它非常方便。只是不在移动设备上运行查询时。

Thanks

3 个解决方案

#1

From the stored procedure side you can default values to null then build your where clause to accommodate this null value.

从存储过程一侧,您可以将默认值设置为null,然后构建您的where子句以适应此null值。

ALTER Procedure FooProcedure 
    @SupplierID INT = NULL,
    @LevelOne INT = NULL 
AS
BEGIN

    SELECT SupplierID, LevelOne 
      FROM FooTable
     WHERE @SupplierID IS NULL OR SupplierID = @SupplierID
       AND @LevelOne IS NULL OR LevelOne = @LevelOne 

END

#2

You can assign the parameters default values and handle the logic within your stored procedure:

您可以分配参数默认值并处理存储过程中的逻辑:

create procedure mySproc(@description nvarchar(20) = null, @supplierid int = null, etc...)

handle the logic of whether the parameters are null in the body of the sproc.

处理sproc正文中参数是否为空的逻辑。

#3

Your probably best off creating stored procedures for each case.

您可能最好为每种情况创建存储过程。

If you have inline SQL code in your c# built with a StringBuilder then the execution plans will never be cached and it will not perform as well as it would with stored procedures.

如果在使用StringBuilder构建的c#中有内联SQL代码,则执行计划将永远不会被缓存,并且执行计划的性能不如存储过程。

#1