有没有办法将简洁的查询添加到列表中并执行全部？

Question

我正在使用 Dapper 对数据库执行查询。我想知道是否有一种方法可以创建一个包含多个方法的列表，并通过循环它们来执行它们。我包括两种显示我正在使用的查询的方法。我有另一种方法连接到数据库并在其中调用方法。

private void DeleteCar(SqlConnection connection, string databaseName)

{

    Console.WriteLine($@"Starting Delete for CarInfo for {databaseName}");

    connection.Query($"DELETE FROM dbo.CarInfo WHERE ParentCarId IS NULL OR ParentDriverId IS NULL");

}

private void DeleteHouse(SqlConnection connection, string databaseName)

{

    Console.WriteLine($@"Starting Delete for HouseInfo for {databaseName}");

    connection.Query($"DELETE FROM dbo.HouseInfo WHERE ParentHouseId IS NULL OR ParentOwnerId IS NULL");

}

Answer 1

不。当针对多个输入发出相同的查询时，通过传递 an作为参数对象，&nbsp;Dapper确实有一个IEnumerable<T>工具 - 它本质上是在内部展开它，就好像您依次foreach使用每个项目发出了一个 - 但对于不同的查询：不，只需将它们一个接一个地发出即可。

Answer 2

您可以为每个方法创建一个操作，但操作需要具有相同的原型（参数）。一个动作相当于一个指向函数的指针。为了简单起见，这里我使用 lambda 初始化操作。一旦你有了一系列的动作，你就可以循环遍历它并一一调用它们。&nbsp; &nbsp; var methods = new List<Action<SqlConnection, string>>&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; (connection,databaseName) => DeleteCar(connection, databaseName),&nbsp; &nbsp; &nbsp; &nbsp; (connection,databaseName) => DeleteHouse(connection, databaseName),&nbsp; &nbsp; };&nbsp; &nbsp; SqlConnection myDb;&nbsp; &nbsp; string dbName;&nbsp; &nbsp; foreach (var action in methods)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; action(myDb, dbName);&nbsp; &nbsp; }我还可以直接使用方法组初始化数组&nbsp; &nbsp; var methods = new List<Action<SqlConnection, string>>&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; DeleteCar,&nbsp; &nbsp; &nbsp; &nbsp; DeleteHouse,&nbsp; &nbsp; };不同之处在于，对于方法组，您可以直接给出方法，而对于 lambda，您可以调整参数（例如始终给出相同的 dbName）：&nbsp; &nbsp; string dbName = "toto";&nbsp;&nbsp; &nbsp; var methods = new List<Action<SqlConnection, string>>&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; (connection) => DeleteCar(connection, dbName),&nbsp; &nbsp; &nbsp; &nbsp; (connection) => DeleteHouse(connection, dbName),&nbsp; &nbsp; };&nbsp; &nbsp; SqlConnection myDb;&nbsp; &nbsp; foreach (var action in methods)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; action(myDb);&nbsp; &nbsp; }

Answer 3

实际上，您可以在存储过程中使用表类型，因此在 C# 中您可以创建如下模型：public class IntIdTableType{&nbsp; &nbsp; public int Id { get; set; }}然后通过您的连接，您可以执行以下操作：connection.QueryAsync<myViewModel>("[dbo].[spName]", param: new&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; ((List<IntIdTableType>)model.YourIListPropertyModel).ToDataTable(),&nbsp; &nbsp; }, commandType: CommandType.StoredProcedure);然后在你的SQL中创建表类型CREATE TYPE intHelper AS TABLE(&nbsp;&nbsp; &nbsp; Id INT)最后在您的参数中的存储过程中：&nbsp; &nbsp; @MyIdTableType [intHelper] READONLY询问：&nbsp; &nbsp; DELETE FROM dbo.HouseInfo WHERE ParentHouseId IS NULL OR ParentOwnerId IS NULL AND SomeColumn IN (@MyIdTableType)