I am using the below code to query the count of the table in the table list.
List<string> tablelist = //some value
foreach( string table in tablelist)
{
var rowcount = string.Format("select count(*) from " + view + " WITH (NOLOCK)");
int countresult = con.Query<int>(rowcount).FirstOrDefault();
if(countresult >0)
{ //someoperation }
}
Here the call to DB is made every time. IS there any option where I can call DB only once and get the output of all the select statements in a single variable. Please help am new to dapper.net. How can I achieve this
Dapper's parameter expansion code doesn't handle IN queries with Lists however DapperExtensions' predicate system does allow IN queries and will convert an equals query predicate to a IN query if the parameter is a list type.
If you want to stick with Dapper for mapping, you can extend Jonathan Willcock's sysobjects/sysindexes query as a view and create a class to represent the results. You could then assemble a DapperExtensions GetList query with the appropriate parameters
View Creation:
CREATE VIEW TableRowCounts
AS
SELECT t.name TableName, i.rows Records
FROM sysobjects t INNER JOIN sysindexes i ON i.ID = t.ID where t.xtype = 'U' and i.indid in (0,1)
Class for Results:
public class TableRowCounts
{
public string TableName { get; set; }
public int Records { get; set; }
}
DapperExtensions query to hydrate the results:
PredicateGroup query = new PredicateGroup();
List<string> tables;
tables = new List<string> { "table1", "table2" };
query.Predicates = new List<IPredicate>();
query.Predicates.Add(Predicates.Field<TableRowCounts>(f => f.TableName, Operator.Eq, tables));
List<TableRowCounts> results;
results = _connection.GetList<TableRowCounts>(query).ToList();
As a side note regarding IN performance, you may see performance drop if your list of parameters (table names in this instance) is greater than around 200 entries.