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

score 2 · Answer 1 · answered Oct 03 '12 at 06:02

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.

I tried your code but it throw an exception - "ExecuteNonQuery failed for Database 'xx'." Do you know what the problem is? — Anonymous, Oct 03 '12 at 07:07
Click "Copy Exception details to clipboard" then edit your question and include the text from the copied error. — Scott Chamberlain, Oct 03 '12 at 20:35

score 1 · Answer 2 · answered Oct 03 '12 at 05:32

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 03 '12 at 05:32

Amir

770
8
21

+1 for doing it the right way, but can you post some more of a explanation of why this way is better. – Scott Chamberlain Oct 03 '12 at 05:33
:( 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. – Scott Chamberlain Oct 03 '12 at 05:35
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 ; – Amir Oct 03 '12 at 05:52

score 0 · Answer 3 · answered Oct 03 '12 at 05:05

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.

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

3 Answers3