How can I write this DOS command in C# code?

Question

I want to execute all sql files in folders. I found the answer in this question is helpful. I can use their command and it works as expect.

I want to run it from C# code. i tried a couple ways but I couldn't get the result. Below is the code that I have tried but not success.

Process process = new Process();
Process process = new Process();
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardOutput = true;
process.StartInfo.RedirectStandardError = true;
process.StartInfo.CreateNoWindow = true;
process.StartInfo.FileName = "cmd.exe";
process.StartInfo.Arguments = string.Format("for %f in ({2}/*.sql) do sqlcmd /S {0} /d {1} /U {3} /P {4} /E /i {5}",
sqlServerName, databaseName, folder, sqlUserName, sqlPassword, @"""%f""");
process.StartInfo.WorkingDirectory = @"C:\";
process.Start();
process.WaitForExit();

I want to know how can I move this DOS command for %f in (*.sql) do sqlcmd /S <servername> /d <dbname> /E /i "%f" into C# code

Scott Chamberlain · Accepted Answer · 2012-10-03 06:02:48Z

2

If you are using C# you should use the tools available to you in C#. Sql Server has a SDK that is included with the install of Sql Server.

You will need to add at least Microsoft.SqlServer.Smo.dll and Microsoft.SqlServer.ConnectionInfo.dll to use the following code.

ServerConnection connection = new ServerConnection(sqlServerName, sqlUserName, sqlPassword);
Server server = new Server(connection);
Database database = server.Databases[databaseName];

foreach (var sqlFile in Directory.EnumerateFiles(folder, "*.sql"))
{
    //Parses the file and runs the batches
    database.ExecuteNonQuery(File.ReadAllText(sqlFile));
}

This will read each file, break the file apart the batches (GO statements), then run the queries.

answered Oct 3, 2012 at 6:02

Scott Chamberlain

128k37 gold badges299 silver badges447 bronze badges

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

2 Comments

Anonymous Over a year ago

I tried your code but it throw an exception - "ExecuteNonQuery failed for Database 'xx'." Do you know what the problem is?

Scott Chamberlain Over a year ago

Click "Copy Exception details to clipboard" then edit your question and include the text from the copied error.

Amir · Accepted Answer · 2012-10-03 05:32:24Z

1

This code may help...

var SqlFile = @"...";
var Command = new SqlCommand
{
  CommandType = CommandType.Text,
  Connection = new SqlConnection()
};
Command.Connection.Open();
foreach (var CommandText in File.ReadAllText(SqlFile).Replace("GO", ";").Split(';'))
{
  Command.CommandText = CommandText;
  Command.ExecuteNonQuery();
}
Command.Connection.Close();

answered Oct 3, 2012 at 5:32

Amir

7888 silver badges21 bronze badges

3 Comments

Scott Chamberlain Over a year ago

+1 for doing it the right way, but can you post some more of a explanation of why this way is better.

Scott Chamberlain Over a year ago

:( Removing the +1 due to the fact that the line Select 'What if the script contains a ; or a GO inside it?' would bomb your code.

Amir Over a year ago

When ; is used we eliminate a command but as you said GO is the problem so we should check new line character(s) before and after that (OR) use ReadAllLines and replace lines with GO with ;

USER_NAME · Accepted Answer · 2012-10-03 05:05:00Z

0

If you want to run all commands together then the easy way is create batch file
and use this -

Process myProcess = new Process();
myProcess = Process.Start(@"Test.bat");

But if you want to run some command then some C# code then again some Command and so on
then u will need multiple batch file and that will be messy way.

answered Oct 3, 2012 at 5:05

USER_NAME

1,0371 gold badge14 silver badges33 bronze badges

Collectives™ on Stack Overflow

How can I write this DOS command in C# code?

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related