结果匿名
描述
可以使用扩展方法执行查询并使用动态类型映射结果。
匿名类型结果可以从以下扩展方法映射:
- Query
- QueryFirst
- QueryFirstOrDefault
- QuerySingle
- QuerySingleOrDefault
这些扩展方法可以从IDbConnection
类型的任意对象中调用。
案例 - Query
Query
方法可以执行查询并将结果映射到动态类型列表。
string sql = "SELECT * FROM Invoice;"; using (var connection = My.ConnectionFactory()) { connection.Open(); var invoices = connection.Query(sql).ToList(); }
案例 - QueryFirst
QueryFirst
方法可以执行查询并将第一个结果映射到动态类型列表。
string sql = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;"; using (var connection = My.ConnectionFactory()) { connection.Open(); var invoice = connection.QueryFirst(sql, new {InvoiceID = 1}); }
案例 - QueryFirstOrDefault
QueryFirstOrDefault
方法可以执行查询并将第一个结果映射到动态类型列表,如果序列不包含任何元素则为默认值
string sql = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;"; using (var connection = My.ConnectionFactory()) { connection.Open(); var invoice = connection.QueryFirstOrDefault(sql, new {InvoiceID = 1}); }
案例 - QuerySingle
QuerySingle
方法可以执行查询并将第一个结果映射到动态类型列表,如果序列中没有元素则会引发异常。
string sql = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;"; using (var connection = My.ConnectionFactory()) { connection.Open(); var invoice = connection.QuerySingle(sql, new {InvoiceID = 1}); }
案例 - QuerySingleOrDefault
QuerySingleOrDefault
方法可以执行查询并将第一个结果映射到动态类型列表,如果序列为空则为默认值;如果序列中有多个元素,则此方法将引发异常。
string sql = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;"; using (var connection = My.ConnectionFactory()) { connection.Open(); var invoice = connection.QuerySingleOrDefault(sql, new {InvoiceID = 1}); }
结果强类型
描述
可以使用扩展方法执行查询并使用动态类型映射结果。
强类型结果可以从以下扩展方法映射:
- Query
- QueryFirst
- QueryFirstOrDefault
- QuerySingle
- QuerySingleOrDefault
这些扩展方法可以从IDbConnection类型的任意对象中调用。
案例 - Query
Query
方法可以执行查询并将结果映射到强类型列表
string sql = "SELECT * FROM Invoice;"; using (var connection = My.ConnectionFactory()) { connection.Open(); var invoices = connection.Query<Invoice>(sql).ToList(); }
案例 - QueryFirst
QueryFirst
方法可以执行查询并将第一个结果映射到强类型列表
string sql = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;"; using (var connection = My.ConnectionFactory()) { connection.Open(); var invoice = connection.QueryFirst<Invoice>(sql, new {InvoiceID = 1}); }
案例 - QueryFirstOrDefault
QueryFirstOrDefault
方法可以执行查询并将第一个结果映射到强类型列表,如果序列不包含任何元素则为默认值。
string sql = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;"; using (var connection = My.ConnectionFactory()) { connection.Open(); var invoice = connection.QueryFirstOrDefault<Invoice>(sql, new {InvoiceID = 1}); }
案例 - QuerySingle
QuerySingle
方法可以执行查询并将第一个结果映射到强类型列表,如果序列中没有元素则会引发异常。
string sql = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;"; using (var connection = My.ConnectionFactory()) { connection.Open(); var invoice = connection.QuerySingle<Invoice>(sql, new {InvoiceID = 1}); }
案例 - QuerySingleOrDefault
QuerySingleOrDefault
方法可以执行查询并将第一个结果映射到强类型列表,如果序列为空则为默认值;如果序列中有多个元素,则此方法将引发异常。
string sql = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;"; using (var connection = My.ConnectionFactory()) { connection.Open(); var invoice = connection.QuerySingleOrDefault<Invoice>(sql, new {InvoiceID = 1}); }
结果多映射
描述
可以使用扩展方法执行查询并将结果映射到具有关系的强类型列表。
关系可以是:
- 一对一
- 一对多
这些扩展方法可以从IDbConnection
类型的任意对象中调用。
案例 - 查询多映射(一对一)
Query
方法可以执行查询并将结果映射到具有一对一关系的强类型列表。
string sql = "SELECT * FROM Invoice AS A INNER JOIN InvoiceDetail AS B ON A.InvoiceID = B.InvoiceID;"; using (var connection = My.ConnectionFactory()) { connection.Open(); var invoices = connection.Query<Invoice, InvoiceDetail, Invoice>( sql, (invoice, invoiceDetail) => { invoice.InvoiceDetail = invoiceDetail; return invoice; }, splitOn: "InvoiceID") .Distinct() .ToList(); }
案例 - 查询多映射(一对多)
Query
方法可以执行查询并将结果映射到具有一对多关系的强类型列表。
string sql = "SELECT * FROM Invoice AS A INNER JOIN InvoiceItem AS B ON A.InvoiceID = B.InvoiceID;"; using (var connection = My.ConnectionFactory()) { connection.Open(); var invoiceDictionary = new Dictionary<int, Invoice>(); var invoices = connection.Query<Invoice, InvoiceItem, Invoice>( sql, (invoice, invoiceItem) => { Invoice invoiceEntry; if (!invoiceDictionary.TryGetValue(invoice.InvoiceID, out invoiceEntry)) { invoiceEntry = invoice; invoiceEntry.Items = new List<InvoiceItem>(); invoiceDictionary.Add(invoiceEntry.InvoiceID, invoiceEntry); } invoiceEntry.Items.Add(invoiceItem); return invoiceEntry; }, splitOn: "InvoiceID") .Distinct() .ToList(); }
结果多结果
描述
QueryMultiple
是一个扩展方法,可以从IDbConnection
类型的任意对象中调用。它可以在同一命令中执行多个查询并映射结果。
string sql = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID; SELECT * FROM InvoiceItem WHERE InvoiceID = @InvoiceID;"; using (var connection = My.ConnectionFactory()) { connection.Open(); using (var multi = connection.QueryMultiple(sql, new {InvoiceID = 1})) { var invoice = multi.Read<Invoice>().First(); var invoiceItems = multi.Read<InvoiceItem>().ToList(); } }
结果多类型
描述
ExecuteReader
是一个扩展方法,可以从IDbConnection
类型的任意对象中调用。它可以执行查询并将结果映射到不同类型的列表。
string sql = "SELECT * FROM Invoice;"; using (var connection = My.ConnectionFactory()) { connection.Open(); var invoices = new List<Invoice>(); using (var reader = connection.ExecuteReader(sql)) { var storeInvoiceParser = reader.GetRowParser<StoreInvoice>(); var webInvoiceParser = reader.GetRowParser<WebInvoice>(); while (reader.Read()) { Invoice invoice; switch ((InvoiceKind) reader.GetInt32(reader.GetOrdinal("Kind"))) { case InvoiceKind.StoreInvoice: invoice = storeInvoiceParser(reader); break; case InvoiceKind.WebInvoice: invoice = webInvoiceParser(reader); break; default: throw new Exception(ExceptionMessage.GeneralException); } invoices.Add(invoice); } } }
引用:https://blog.csdn.net/WuLex/article/details/108317090
标签:InvoiceID,映射,var,connection,文档,invoice,sql,类型,Dapper From: https://www.cnblogs.com/friend/p/17935192.html