Getting data from multiple query without using foreach

Question

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

Which database are you using?

user6638270
– user6638270

2016-08-18 08:38:09 +00:00
Commented Aug 18, 2016 at 8:38 — user6638270
– user6638270, Commented Aug 18, 2016 at 8:38

score 1 · Accepted Answer · 2016-08-18 09:26:10Z

1

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21021

If you are using SQL Server, the following query returns all records from all tables. Obviously, if you only need a subset you can add "AND t.name IN('xxx','yyy','zzz')" to the where:

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1)
order by TableName;

To build the IN string, do

StringBuilder inSql = new StringBuilder(" AND t.name IN('";
bool firstTime = true;
foreach(string tname in tablelist)
{
    if (firstTime)
    {
        firstTime = false;
        inSql.Append(tname + "'");
    }
    else
    {
        inSql.Append(", '" +tname +"'");
    }
}
string sQL = "select t.name TableName, i.rows Records from sysobjects t, sysindexes i where t.xtype = 'U' and i.id = t.id and i.indid in (0,1)"
    + inSql.ToString() + ") order by TableName";

Please note this returns a recordset, so you will need to alter your call to con.Query. I do not use dapper, so you will need to change this yourself!

edited Aug 18, 2016 at 9:26

answered Aug 18, 2016 at 8:51

user6638270

Sign up to request clarification or add additional context in comments.

1 Comment

Manjari Over a year ago

Thanks for your suggestion. Here my t.name is a collection(list<string>) and is dynamic value. In that case how to handle.

G Davison · Accepted Answer · 2016-08-18 12:42:53Z

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.

Collectives™ on Stack Overflow

Getting data from multiple query without using foreach

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related