Resolved - was querying the wrong DB by mistake

sorry for wasting your time! :(

I'm trying to read the results of a series of actions on a SQL variable, this is the query i'm trying to get the value out of:

DECLARE @Count INT = 0 

IF EXISTS(SELECT name FROM sys.sysobjects WHERE Name = N'Table1' AND xtype = N'U')

BEGIN   

    SET @Count = @Count + (SELECT COUNT(*) FROM Table1)

END 

IF EXISTS(SELECT name FROM sys.sysobjects WHERE Name = N'Table2' AND xtype = N'U')

BEGIN   

    SET @Count = @Count + (SELECT COUNT(*) FROM Table2)

END 

IF EXISTS(SELECT name FROM sys.sysobjects WHERE Name = N'Table3' AND xtype = N'U')

BEGIN   

    SET @Count = @Count + (SELECT COUNT(*) FROM Table3)

END 

SELECT @Count

Basically it checks if a bunch of tables (which might not exist) have rows in them.

C# Code

List<string> queries = new List<string>();

foreach (var table in tables)

{

    queries.Add(string.Format(@"IF EXISTS(SELECT name FROM sys.sysobjects WHERE Name = N'{0}' AND xtype = N'U')

                                BEGIN   

                                    SET @Count = @Count + (SELECT COUNT(*) FROM {0})

                                END", table.Name.Trim()));

}



var queryString = string.Format(@"DECLARE @Count INT = 0 {0} SELECT @Count

                                    GO", string.Join(" ", queries));



using (SqlConnection connection = new SqlConnection(connectionString))

{

    connection.Open();

    //I also tried these methods using Dapper:

    // int count = connection.Query<int>(queryString);

    // int count = (int)connection.ExecuteScalar(queryString);



    SqlCommand cmd = new SqlCommand(queryString, connection);

    using (SqlDataReader dr = cmd.ExecuteReader())

    {

        DataTable dt = new DataTable();

        dt.Load(dr);



        //dt has one row where the data is 0, not the sum as i want it

    }

    connection.Close();

}

The Problem

The problem is, no matter how I try to load the result in C# the original value always gets returned (in this case 0, but if i change it in the query the change gets reflected in the result).

I'd like for the returned value to be the sum of all these queries:

(SELECT COUNT(*) FROM X) 

What I tried

1. Searching online i thought it was a scope problem, so i tried running the query with and without SqlCommand parameters, which didn't change the outcome, maybe i'm not searching the right words


2. Adding 'GO' both after the SET commands and at the end


3. Trying different ways to parse the data in the ASP.NET application, including using an external library, called Dapper

Putting the query in a stored procedure is not possible unfortunately, anyone has an idea about what to try next? I've been stuck on this for a while...

SQL Server version is SQLServer 2014

Edit to reflect the question edits:

With the change shown, and:

//dt has one row where the data is 0, not the sum as i want it

then: the answer is zero. You're querying it in an OK way (the DataTable is massively overkill, but... meh), but: the answer is zero.

I'm pretty sure that if you run the same TSQL in SSMS (in the same database, as the same user): you'll get zero there, too.

Ultimately, the key factor here is: how are you returning the value? In this case, you are doing:

SELECT @Count

which means that it is going to be coming back as a results grid - i.e. it is a query; so: as long as you use a query API, you should be fine. This includes ExecuteScalar in ADO.NET, and QuerySingle<int> in dapper (which you mention).

What you can't do is use the return value of ExecuteNonQuery (Execute in dapper), as this is an unrelated value (and is usually wrong and/or empty and/or useless - don't use it pretty much ever).

Try this C# (might have minor syntax errors - didn't write/run it in VS). If you still get 0 out of it, you're querying an empty db:

int count = 0;

using (SqlConnection connection = new SqlConnection(connectionString))

{

    try{

        connection.Open();

        foreach (var table in tables)

        {



            try{

                var cmd = "SELECT COUNT(*) FROM " + table;

                using(var sc as new SqlCommand(cmd, connection)){

                    count += (int)sc.ExecuteScalar();

                }

            }

            catch {}

        }

    }finally{

        connection.Close();

    }

}

The best way here is to attach your sql to a command, then do

int retVal = cmd.ExecuteScalar();

should get the value back fine - it's one of several ways of doing it though.

ExecuteScalar is for instances where you want to return a single row with a single column, as you have done here.