4

Apologies if this has been asked before. I have searched for an hour and not found the exact problem I am having.

I am using SMO to run some queries against SQL Server, because I have read this can handle GO statements, as opposed to System.Data.SqlClient.

I am running this query:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO  

UPDATE Program
SET ENABLED = '1'
WHERE Program_ID = '64' AND Program_Name = 'DoesSomething'

I am capturing the "rows affected" by:

int numberOfRows = db.ConnectionContext.ExecuteNonQuery(s.Query);

The problem I am having is that this returns a value of -1 every time. I am checking the database behind the scenes and it DOES update the value every time, and when I manually run the query in SSMS, I receive the (1 row affected) confirmation.

After reading some other posts I have come to think the problem may be in the first four lines of this query. I removed the GO statements, and the query returned with a value of 1 instead of -1.

Most queries/scripts my group writes has GO, SET ANSI_NULLS ON, and SET QUOTED_IDENTIFIER ON pretty much as standard so its everywhere (another problem for another day- I'm aware it is excessive/irrelevant in this case). Is this affecting my row count somehow? If so can you provide some direction for a workaround or perhaps another route to get this result?

And yes... I am aware ExecuteNonQuery should return the number of rows affected. I just need to know why it is not returning what I think it should (in this case 1).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
shindigwagon
  • 45
  • 4
  • Are you using entity framework? – Emdad Apr 05 '18 at 19:13
  • `Is this affecting my row count somehow?` You have proven that it is, and you have the solution. I don't know why it makes a difference, but you have said that the extra statements are `excessive/irrelevant in this case`. – Dan Wilson Apr 05 '18 at 19:14
  • `GO` is **not** a (SQL) statement - it's just a **delimiter** used by SQL Server Management Studio (and that's the reason why using "normal" SQL won't work - since `GO` really isn't a SQL statement....) – marc_s Apr 05 '18 at 19:20
  • @marc_s that is why OP uses SMO which _can_ handle GO. – Evk Apr 05 '18 at 19:23
  • @DanWilson I have set this query up as a test. So in my case these statements are useless, but I'm trying to create a small program that can run them because I know for a fact these statements will need to be used on bigger queries my team builds, so that is why I am trying to run this one to iron things out. – shindigwagon Apr 05 '18 at 19:38

1 Answers1

4

GO is a the batch seaparator used by SQLCMD, OSQL and other tools. Since SMO's ServerConnection.ExecuteNonQuery happens to recognize the SQLCMD commands, it handles the batch separator for you and executes every batch you issue in your text. The 'return' value of a series is not a clear command, since each batch may have its own return (not to mention that every batch may contain multiple statements). So I would take that 'return' value with a grain of salt. If you want to confirm the number of rows updated, use an OUTPUT clause in the UPDATE and have a result set, or assign @@ROWCOUNT to an output variable.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • This clears up some stuff. Thank you for the clarity. Let me pose this question- Suppose for the sake of argument nobody adds the output clause to the end of the update statement. Can I programmatically add it to the end? If so, would there be issues with this? Asking because currently nobody writes queries with the output clauses , and it would be difficult to get this as a standard across all our teams. – shindigwagon Apr 05 '18 at 19:45
  • No, you cannot. It has to be embedded in the statement, at the right position, and changes semantics. I would rather explore split the text into batches myself (ie. seek the `GO`s) and make sure you execute only one batch in every call. Then you can decide what 'return' means for each. – Remus Rusanu Apr 05 '18 at 19:50
  • I don't undestand why your team writes queries in this way and then expects that you run them from C#. If they want to run these queries from code then they should fix the way in which they write the queries. – Steve Apr 05 '18 at 19:51
  • just FYI, long ago I wrote a library to handle `GO` and other SQLCMD commands in 'plain' C#, see https://github.com/rusanu/com.rusanu.dbutil. – Remus Rusanu Apr 05 '18 at 19:52
  • @RemusRusanu awesome. That is the direction I will go. THANK YOU! – shindigwagon Apr 05 '18 at 19:53