C# won't read updated version of SQL variable

microsoft c# tutorial
c# custom attributes
c# custom attribute with parameters
c# attributes
httpcontext.current c#
attributes and reflection in c#
c# 9
c# httpcontext example
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).

Tutorial: Use attributes, However, when creating an attribute type, the C# compiler won't stop you from creating those parameters. In the below example, I've created an  Working with C#. The C# support in Visual Studio Code is optimized for cross-platform .NET Core development (see working with .NET Core and VS Code for another relevant article). Our focus with VS Code is to be a great editor for cross-platform C# development. VS Code supports debugging of C# applications running on either .NET Core or Mono.

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();
    }
}

HttpContext.Current Property (System.Web), C# Copy. public static System.Web.HttpContext Current { get; set; } member this.​Current : System.Web. NET won't handle them. context.ClearError(); } To add a method to verify whether the player won Add a CheckForWinner() method to the bottom of your code, below the timer1_Tick() event handler, as shown in the following code. /// <summary> /// Check every icon to see if it is matched, by /// comparing its foreground color to its background color.

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.

Learn to Program with Visual C# 2008 Express, "I tried to type something into the Immediate window," Ward said, "but C# won't let me." A quick trip to Ward's PC revealed that he had forgotten to pause the  NET won't handle them. context.ClearError(); } The method uses another foreach loop in C# or For Each loop in Visual Basic to go through each label in the TableLayoutPanel. It uses the equality operator ( == in C# and = in Visual Basic) to check each label's icon color to verify whether it matches the background.

Programming C# 4.0: Building Windows, Web, and RIA Applications , So C# won't let us use our properties until the underlying fields have been initialized, and we can't do that because these are auto properties—the C# compiler  What's happening is that, after the user enters 'Y', the outer loop (namely, while (continueChar != 'N')) will continue to loop indefinitely, but none of the inner loops (starting from while (initialCost == 0)) will have their conditions satisfied, since their variables (such as initialCost) would retain the values that were assigned in the former iteration.

Learn to Program with Visual C# (2014 Edition), You probably won't fully appreciate the value of the Command/Immediate "I tried to type something into the Immediate window," Ward said, "but C# won't let  Starting with C# 8.0, a constructed value type is unmanaged if it contains fields of unmanaged types only. For example, given the following definition of the generic Coords<T> type: public struct Coords<T> { public T X; public T Y; } the Coords<int> type is an unmanaged type in C# 8.0 and later.

How I taught Katy Perry (and others) to program in Visual C#, "I tried to type something into the Immediate window," Pitbull said, "but C# won't let me." A quick trip to Pitbull's PC revealed that he had forgotten to pause the  I have been searching up and down the web and unfortunately never came across an issue quite like mine, so here goes: My C# WPF application won't show me no OpenFileDialogs or SafeFileDialogs. pr

Comments
  • We need code, please post what you've tried.
  • In c# you have two commands 1) Insert 2) Update. When using ExecuteNonQuery() you have to check the return value which is the number of rows affected. If you get a return value of zero it means no rows changed. If you are doing an INSERT it means the key already exists in the table and then you have to use UPDATE to change the existing key value. If you are doing UPDATE it means the key doesn't exist in table and then you have to do a INSERT to put a new key into the table.
  • no matter how I try to load the result in C# the original value always gets returned where is this C# you talk of?
  • I've updated my answer based on the edits...
  • Also, make sure you're executing the query on the same database that C# is; wouldn't be the first time I've had someone say "oh, i was testing the query on my prod DB with 4252 rows but I forgot my local debug DB that c# was using is actually empty..."
  • Oh, i dunno.. ExecuteNonQuery on a CRUD is pretty vital for getting optimistic concurrency to work out properly
  • @CaiusJard nothing wrong with ExecuteNonQuery, but the return value is not guaranteed to be set at all; if you want to know a rowcount, for example, then I would strongly recomment select @@rowcount - or if you really want to use ExecuteNonQuery, set @someOutArg = @@rowcount, or return @@rowcount, which is accessed virtually identically to the @someOutArg API, and is not the return value of ExecuteNonQuery
  • OK.. Though have you ever run an plain old INSERT/UPDATE/DELETE query and NOT had ExecuteNonQuery return the number of rows affected?
  • thanks for the reply, unfortunately (int)cmd.ExecuteScalar() still returns 0
  • @CaiusJard yes, very often; a: a lot of places make it standard practice to SET NOCOUNT ON to slightly reduce TDS traffic; b: if tables have triggers, those triggers can cause very confusing results from this
  • have you confirmed zero is not the value in the variable? Try doing SET @COUNT=1; the line before you SELECT it, as a check.