Running a text file as if it was being run in SQL Server Management Studio.

Let's say you have the following text file with the next 2 lines:

Drop Table Test
GO

Using C#, how would you 'run' this file on SQL Server?

I think SQL Server is version 2012. SSMS is version 17.2. I'm using VIsual Studio 2015.
Andrew WerberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
You have to read the text file using FileInfo object and get the text and pass that text as the command text to the DB and execute the same using Execute non query. Also note that you need sufficient permission to drop a table. Also could you please let me know why you need it ?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andrew WerberAuthor Commented:
Thanks for the quick answer.

I want to say that SQLCMD as an answer to this question will not be accepted as I just found that.

I need this because my boss wants it and he determines how much I get paid. :)
Andrew WerberAuthor Commented:
Looking good. But one question back, why do I get an error after a GO? In the past, I would make scripts where a GO is needed.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You don't need the GO statement if you're running this in your program. You'd do something like this:

YourCommandObject.SQL = "DROP TABLE YourTable"
YourCommandObject.ExecuteNonquery

That should drop the table, assuming you have permissions to do so as Pawan mentioned.
ValentinoVBI ConsultantCommented:
Some additional info on "GO": this is not part of the T-SQL language, it's just a command that Microsoft has implemented in its SQL Server toolset.  That explains the error behaviour in C#.

Ref. SQL Server Utilities Statements - GO
Andrew WerberAuthor Commented:
Thanks. Since the original question did not ask about the GO, I'm assigning all points to this answer.
Andrew WerberAuthor Commented:
PS, there is a way to run scripts with GO statements
var connectionString = "connection string";
            var pathToScriptFile = fileToExec;
            var sqlScript = File.ReadAllText(pathToScriptFile);

            using (var connection = new SqlConnection(connectionString))
            {
                var server = new Server(new ServerConnection(connection));
                server.ConnectionContext.ExecuteNonQuery(sqlScript);
            }

Open in new window

You have to add references to Microsoft.SqlServer.ConnectionInfo and Microsoft.SqlServer.Smo.

I also had to add the following to my CONFIG file:
<startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
    <requiredRuntime version="v4.0.20506" />
  </startup>

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.