Update SQL table through C# code

Question

I want to update a table through sql code executed in a c# application. To do this, I've used the alter data generated my MSSMS and manually saved it as an sql-file. The c# then reads the file and tries to execute it but it can't. If I use the sql code by itself it works, but not when read by the c# funtion. What's wrong with my c# code?

The sql code generated my MSSMS:

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.tTest ADD
    NewColumn int NULL
GO
ALTER TABLE dbo.tTest SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

The c# code that reads it:

string content = string.Empty;
try
{
    content = File.ReadAllText(string.Format(@"C:\temp\{0}.sql", name));
    SqlConnection conn = new SqlConnection(ConnectionString);
    SqlCommand command = new SqlCommand(content, conn);
    command.Connection.Open();
    command.ExecuteNonQuery();
    command.Connection.Close();
}
catch (Exception e)
{
    Console.WriteLine(e.Message);
}

The output that comes from the c# function (the console message):

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.

Szymon · Accepted Answer · 2014-09-22 07:05:19Z

2

Each batch (ended with GO) should be sent separately in one command.ExecuteNonQuery(). This method is not to be used for multiple batches.

Split your query into several pieces (where GO is) and execute it step by step.

answered Sep 22, 2014 at 7:05

Szymon

43k16 gold badges99 silver badges115 bronze badges

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

Comments

Filip De Vos · Accepted Answer · 2014-09-22 07:22:47Z

0

The core issue with the script you are trying to run is wrapping a transaction around query batches (A query batch is terminated with a GO statement in SQL Server Management Studio).

To do the operations in C# you can execute both statements in one query batch. They do not require to be separate. If you wrap them in one query in a SqlCommand object you do not need to handle transactions as there is an "implicit" transaction created.

A last point to look out for is proper disposal of the objects implementing IDisposable. The easiest way to do this in C# is by wrapping them in a using clause. Once you did that, there is no more need to call the Close method on the command/connection objects.

Combining all these remarks gives you the following code:

        try
        {
            using(var conn = new SqlConnection(ConnectionString))
            using(var command = new SqlCommand(
                    @"ALTER TABLE dbo.tTest ADD NewColumn int NULL;
                    ALTER TABLE dbo.tTest SET (LOCK_ESCALATION = TABLE);", conn))
            {
                conn.Open();
                command.ExecuteNonQuery();
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

answered Sep 22, 2014 at 7:22

Filip De Vos

12k1 gold badge50 silver badges61 bronze badges

1 Comment

Dave Over a year ago

The sql code I had is autogenerated from MSSQL and I dont wish people to edit it for every update of the db

Collectives™ on Stack Overflow

Update SQL table through C# code

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related