C# 带有参数列表的 Dapper 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13291589/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dapper query with list of parameters
提问by Jarrod
I am trying to run a query with Dapper with a known set of parameters, but with a list of values for those parameters. A simple example of what I am trying to do would be:
我正在尝试使用具有一组已知参数的 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 throws an exception with 'Parameter '@Status' must be defined'. I know Dapper can process lists of parameters when doing bulk inserts and updates, but can it not do this for selects?
Dapper 抛出一个异常，“必须定义参数‘@Status’”。我知道 Dapper 可以在进行批量插入和更新时处理参数列表，但它不能对选择执行此操作吗？
采纳答案by Marc Gravell
Ah, I think I see what you mean...
啊，我想我明白你的意思了......
Yes, there is a scenario we support for Executethat isn't supported for Query, specifically: to run the same operation sequentially with a range of different parameter values. This makes sense for Execute, but for query it probably means you should be looking at a different query using in. Alternatively, just loop and concat.
是的，有一种我们支持的场景Execute不支持 Query，具体来说：使用一系列不同的参数值按顺序运行相同的操作。这对 来说是有意义的Execute，但对于查询来说，这可能意味着您应该使用in. 或者，只需循环和连接。
Instead, it is looking at the single parameter object and looking for public values - an enumerable doesn't have any suitable parameter values for dapper.
相反，它正在查看单个参数对象并寻找公共值 - 可枚举没有任何适合 dapper 的参数值。
回答by RedFilter
Try this:
尝试这个：
List<string> names = new List<string> { "Bob", "Fred", "Hyman" };
string query = "select * from people where Name in @names";
var stuff = connection.Query<ExtractionRecord>(query, new {names});
回答by Dylan Hayes
I know I'm way late to this party but, I think I understand this request to mean that you just want to pass in some properties and generate your query based on those dynamic properties.
我知道我参加这个聚会已经很晚了，但是，我想我理解这个请求意味着您只想传入一些属性并根据这些动态属性生成您的查询。
with the code below I can use any Type and then just populate and pass in an object of that Type with a few values set (I call this my query object), and the query will be generated to go find objects that match the values that you set in your query object.
使用下面的代码，我可以使用任何类型，然后只需填充并传入该类型的对象并设置一些值（我称其为我的查询对象），然后将生成查询以查找与该值匹配的对象您在查询对象中设置。
*be careful of bools and things that have default values.
*小心布尔值和具有默认值的东西。
Dynamic Query Example
动态查询示例
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();
}
Usage
用法
*repo is the class that contains the above function
*repo 是包含上述函数的类
var blah = repo.Query<Domain>(new Domain() { Id = 1, IsActive=true });
Output
输出
SELECT * From Domain Where Id = @Id AND IsActive = @IsActive
then it spits out any "Domains" that match the above query.
然后它会吐出与上述查询匹配的任何“域”。
回答by Glazed
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
That's how I would do it, but this assumes one thing: You have a table in your database named "Numbers" that has an arbitrary number of integers in it, one per row, starting with 1, with at least 24 numbers in it.
这就是我的做法，但这假设一件事：您的数据库中有一个名为“Numbers”的表，其中包含任意数量的整数，每行一个，从 1 开始，其中至少有 24 个数字。
That is, the table looks like this:
也就是说，该表如下所示：
n
-----
1
2
3
4
5
...
If you don't have such a table, it's very fast and easy to make one just for this command:
如果你没有这样的表，那么只为这个命令制作一个非常快速和容易：
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.
You can't have multiple batches in a stored procedure, but you can in a text command. GO 16runs the preceding batch 16 times. If you needed this in a stored procedure, you can jut repeat the second INSERTcommand a number of times instead of using batches. 2^16 integers is overkill for this particular query, but it's a command I copy and paste when needed and 2^16 is usually enough, and so fast that I usually don't bother to change it. GO 5would yield 32 integers, which is enough for 24 date ranges.
存储过程中不能有多个批处理，但文本命令中可以。GO 16运行前一个批次 16 次。如果您在存储过程中需要这样做，您可以INSERT多次重复第二个命令，而不是使用批处理。2^16 整数对于这个特定查询来说是多余的，但这是我在需要时复制和粘贴的命令，2^16 通常就足够了，而且速度太快了，我通常不会费心去改变它。GO 5将产生 32 个整数，这对于 24 个日期范围来说已经足够了。
Here's an entire script that illustrates this working:
这是一个完整的脚本，说明了这一工作：
--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.
*/
