5

I'm having a problem with running an SQL script in my program. This is the code for the program

        string databaseName = "TestDB";
        Server srv1 = new Server(@"TestServer/ABCUser");   // connects to default instance  
        srv1.ConnectionContext.LoginSecure = false;   // set to true for Windows Authentication  
        srv1.ConnectionContext.Login = "sa";
        srv1.ConnectionContext.Password = "abcde123*";
try
        {
            Console.WriteLine(srv1.Information.Version);   // connection is established  
            if(srv1.Databases[databaseName] == null)
            {
                Microsoft.SqlServer.Management.Smo.Database db;
                db = new Microsoft.SqlServer.Management.Smo.Database(srv1, databaseName);
                db.Create();
                db = srv1.Databases[databaseName];
                Console.WriteLine(db.CreateDate);
            }

            else
            {
                Console.WriteLine("DB Already Exists!");
            }
            string sqlScript = File.ReadAllText(@"D:\Projects\TrainingProject\src\TrainingDatabase\Scripts\Tables\Script01-table.sql");
            Console.WriteLine(sqlScript);
            srv1.ConnectionContext.ExecuteNonQuery(sqlScript);

        }
        catch (ConnectionFailureException e)
        {
            Console.WriteLine("Error in connecting to the sql server " + e.Message);
        }

And this is the SQL Script

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FileTypes') BEGIN
CREATE TABLE FileTypes (
    FileTypeID tinyint IDENTITY(1,1) NOT NULL,
    TypeName varchar(500) NOT NULL

    CONSTRAINT PK_FileTypes PRIMARY KEY(FileTypeID)
)

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FileTypes')
    PRINT 'Created table FileTypes'
ELSE
    PRINT 'Failed to create table FileTypes'

END

So far, I've tested that:

  1. I've managed to get the server information version(connection to server is successful)

  2. I can create the database from the program

  3. The script is working when I uses it as a query on Microsoft SQL Server Management Studio

  4. TheConsole.WriteLine(sqlScript); command does show the correct SQL script

The problem is that the srv1.ConnectionContext.ExecuteNonQuery(sqlScript); is not working.

Anyone knows the answer?

These are the stackoverflow questions I used for reference: Execute a large SQL script (with GO commands)

3
  • 1
    Not an answer to your question, but you seem to be recreating the functionality of DACPACs and/or sqlcmd. Consider just using those. Commented Oct 19, 2018 at 8:05
  • @JeroenMostert I'm not allowed to change it sorry Commented Oct 19, 2018 at 8:16
  • What do you get for an error message? Commented Oct 19, 2018 at 10:10

1 Answer 1

5

Turns out that I made a mistake in this line: srv1.ConnectionContext.ExecuteNonQuery(sqlScript);

It created the table in the master DB, when it should be this:

srv1.Databases[databaseName].ExecuteNonQuery(sqlScript);
Sign up to request clarification or add additional context in comments.

1 Comment

If that solves your problem, then you can accept your own answer as the right answer of the question

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.