Более щадящий запрос со списком параметров

Question

Я пытаюсь выполнить запрос с Dapper с известным набором параметров, но со списком значений для этих параметров. Простой пример того, что я пытаюсь сделать:

DateTime endDate = DateTime.Now;
DateTime startDate = endDate.AddHours(-24);

string query = "select COUNT(*) from Test where Status = @Status AND DateCreated <= @Hour;";
var stuff = con.Query(query, (startDate).ByHourTo(endDate).Select(hour => new
{
     Status = 1,
     Hour = hour,
}));

Dapper генерирует исключение, и "Параметр" @Status "должен быть определен". Я знаю, что Dapper может обрабатывать списки параметров при массовых вставках и обновлениях, но не может ли он сделать это для выбора?

Answer 1

Ах, я думаю, я понимаю, что ты имеешь в виду...

Да, есть сценарий, который мы поддерживаем Execute в частности, это не поддерживается в Query: последовательно выполнять одну и ту же операцию с диапазоном значений различных параметров. Это имеет смысл для Execute, но для запроса это, вероятно, означает, что вы должны смотреть на другой запрос, используя in, Кроме того, просто цикл и конкат.

Вместо этого он просматривает объект с одним параметром и ищет общедоступные значения - перечисляемый не имеет подходящих значений параметров для dapper.

Answer 2

Попробуй это:

List<string> names = new List<string> { "Bob", "Fred", "Jack" };
string query = "select * from people where Name in @names";
var stuff = connection.Query<ExtractionRecord>(query, new {names});

Answer 3

Я знаю, что опаздываю на эту вечеринку, но, думаю, я понимаю, что этот запрос означает, что вы просто хотите передать некоторые свойства и сгенерировать свой запрос на основе этих динамических свойств.

с помощью приведенного ниже кода я могу использовать любой тип, а затем просто заполнить и передать объект этого типа с несколькими установленными значениями (я называю это моим объектом запроса), и запрос будет сгенерирован для поиска объектов, соответствующих значениям, которые Вы устанавливаете в своем объекте запроса.

* будьте осторожны с bools и вещами, которые имеют значения по умолчанию.

Пример динамического запроса

    public IEnumerable<T> Query<T>(T templateobject) {
        var sql = "SELECT * From " + typeof(T).Name + " Where ";

        var list = templateobject.GetType().GetProperties()
             .Where(p => p.GetValue(templateobject) != null)
             .ToList();

        int i = 0;

        Dictionary<string, object> dbArgs = new Dictionary<string, object>();

        list.ForEach(x =>
        {
            sql += x.Name + " = @" +  x.Name;

            dbArgs.Add(x.Name, x.GetValue(templateobject));

            if (list.Count > 1 && i < list.Count - 1) {
                sql += " AND ";
                i++;
            }
        });

        Debug.WriteLine(sql);

        return _con.Query<T>(sql, dbArgs).ToList();
    }

использование

*repo - это класс, который содержит вышеуказанную функцию

var blah = repo.Query<Domain>(new Domain() { Id = 1, IsActive=true });

Выход

SELECT * From Domain Where Id = @Id AND IsActive = @IsActive

тогда он выплевывает любые "домены", которые соответствуют вышеуказанному запросу.

Answer 4

DECLARE @Now datetime
SET @Now = getdate()

SELECT
    DATEADD( hh, -n, @Now ) AS StartDate,
    DATEADD( hh, -n+1, @Now ) AS EndDate
INTO
    #DateRanges
FROM 
    Numbers
WHERE
    n <= 24

SELECT
    COUNT(*) AS [Count],
    #DateRanges.StartDate
FROM
    Test
        JOIN
    #DateRanges
        ON Test.DateCreated >= #DateRanges.StartDate
        AND Test.DateCreated < #DateRanges.EndDate
GROUP BY
    #DateRanges.StartDate

Вот как я бы это сделал, но это предполагает одно: в вашей базе данных есть таблица с именем "Numbers", в которой содержится произвольное число целых чисел, по одному на строку, начиная с 1, с минимум 24 числами.

То есть таблица выглядит так:

n
-----
1
2
3
4
5
...

Если у вас нет такой таблицы, ее очень быстро и легко создать только для этой команды:

CREATE TABLE #Numbers
(
    n int
)

SET NOCOUNT ON

INSERT #Numbers values (1);
GO
INSERT #Numbers SELECT n + (SELECT COUNT(*) FROM #Numbers) FROM #Numbers
GO 16 --execute batch 16 times to create 2^16 integers.

Вы не можете иметь несколько пакетов в хранимой процедуре, но вы можете использовать текстовую команду. GO 16 запускает предыдущую партию 16 раз. Если вам нужно это в хранимой процедуре, вы можете просто повторить второй INSERT введите команду несколько раз вместо использования пакетов. 2^16 целых чисел является избыточным для этого конкретного запроса, но это команда, которую я копирую и вставляю при необходимости, и обычно 2 ^ 16 достаточно, и настолько быстро, что я, как правило, не пытаюсь ее изменить. GO 5 даст 32 целых числа, что достаточно для 24 диапазонов дат.

Вот целый скрипт, который иллюстрирует эту работу:

--Create a temp table full of integers. This could also be a static 
--table in your DB. It's very handy.
--The table drops let us run this whole script multiple times in SSMS without issue.
IF OBJECT_ID( 'tempdb..#Numbers' ) IS NOT NULL
    DROP TABLE #Numbers

CREATE TABLE #Numbers
(
    n int
)

SET NOCOUNT ON

INSERT #Numbers values (1);
GO
INSERT #Numbers SELECT n + (SELECT COUNT(*) FROM #Numbers) FROM #Numbers
GO 16 --execute batch 16 times to create 2^16 integers.

--Create our Test table. This would be the real table in your DB, 
-- so this would not go into your SQL command.
IF OBJECT_ID( 'tempdb..#Test' ) IS NOT NULL
    DROP TABLE #Test

CREATE TABLE #Test
(
    [Status] int,
    DateCreated datetime
)

INSERT INTO 
    #Test 
SELECT 
    1, 
    DATEADD( hh, -n, getdate() )
FROM 
    #Numbers
WHERE
    n <= 48

--#Test now has 48 records in it with one record per hour for 
--the last 48 hours.

--This drop would not be needed in your actual command, but I 
--add it here to make testing this script easier in SSMS.
IF OBJECT_ID( 'tempdb..#DateRanges' ) IS NOT NULL
    DROP TABLE #DateRanges

--Everything that follows is what would be in your SQL you send through Dapper 
--if you used a static Numbers table, or you might also want to include
--the creation of the #Numbers temp table.
DECLARE @Now datetime
SET @Now = getdate()

SELECT
    DATEADD( hh, -n, @Now ) AS StartDate,
    DATEADD( hh, -n+1, @Now ) AS EndDate
INTO
    #DateRanges
FROM 
    #Numbers
WHERE
    n <= 24

/* #DateRanges now contains 24 rows that look like this:

StartDate               EndDate
2016-08-04 15:22:26.223 2016-08-04 16:22:26.223
2016-08-04 14:22:26.223 2016-08-04 15:22:26.223
2016-08-04 13:22:26.223 2016-08-04 14:22:26.223
2016-08-04 12:22:26.223 2016-08-04 13:22:26.223
...

Script was run at 2016-08-04 16:22:26.223. The first row's end date is that time. 
This table expresses 24 one-hour datetime ranges ending at the current time. 
It's also  easy to make 24 one-hour ranges for one calendar day, or anything
similar.
*/

--Now we just join that table to our #Test table to group the rows those date ranges.

SELECT
    COUNT(*) AS [Count],
    #DateRanges.StartDate
FROM
    #Test
        JOIN
    #DateRanges
        ON #Test.DateCreated >= #DateRanges.StartDate
        AND #Test.DateCreated < #DateRanges.EndDate
GROUP BY
    #DateRanges.StartDate

/*
Since we used two different getdate() calls to populate our two tables, the last record of 
our #Test table is outside of the range of our #DateRange's last row by a few milliseconds,
so we only get 23 results from this query. This script is just an illustration.
*/