CODEWITHSUNDEEP
c#sqlsql-serverprocess
Snake Eyes
Snake Eyes

Reputation: 16764

Execute multiple command (using BCP) with same ProcessStartInfo instance in C#

I know that my title seems with others from StackOverflow but my intention is other.

I created a C# function which creates BCP files for every table from my database using BCP command. The below code is a sample, part of function.

List<string> tables = GetTables(); // a function which takes all tables from my database

string command = @"bcp ADatabase.dbo.{0} out {0}.bcp -n -Usa -Ppassword";

ProcessStartInfo processInfo = null;
     try {
        foreach ( string table in tables ) {
           command = string.Format( command, table );
           processInfo = new ProcessStartInfo( "cmd", "/c " + command );
           processInfo.RedirectStandardOutput = true;
           processInfo.CreateNoWindow = false;
           Process process = new Process();
           process.StartInfo = processInfo;
           process.Start();
           string result = process.StandardOutput.ReadToEnd();
           Console.WriteLine( result );
        }
     } catch ( Exception e ) {
        Console.WriteLine( e.Message );
     }

I want to avoid executing multiple windows and I want to execute each command line in same window.

My code is good or is an alternative ?

Thanks

Upvotes: 0

Views: 3337

Answers (2)

SmithMart
SmithMart

Reputation: 2801

I'm not sure if there's a way to open one command window and then keep posting your commands to it, that would be nice if someone had a clever way of doing that.

You could use Streamwriter to write a batch file with all the commands you want in it, and then run the batch file. That would at least only open one process.

StreamWriter sw = new StreamWriter("MyBatchFile.bat");
            string command = @"bcp ADatabase.dbo.{0} out {0}.bcp -n -Usa -%1";
            ProcessStartInfo processInfo = null;
            try
            {
                foreach (string table in tables)
                {
                    command = string.Format(command, table);
                    sw.WriteLine(command);                                        
                }
                sw.Flush();
                sw.Close();
                processInfo = new ProcessStartInfo("cmd", "/c MyBatchFile.Bat Ppassword");
                processInfo.RedirectStandardOutput = true;
                processInfo.CreateNoWindow = false;
                Process process = new Process();
                process.StartInfo = processInfo;
                process.Start();
                string result = process.StandardOutput.ReadToEnd();
                Console.WriteLine(result);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

The batch file has the "%1" in the command line so when you start the process you pass the password for the database to the batch file

ProcessStartInfo("cmd", "/c MyBatchFile.Bat Ppassword");

This should make sure that if anyone ever found the batch file, it just has %1 where the password would be.

Not sure if this is better than what you have already... but it's only opening one command window :)

Martyn

Upvotes: 2

Roland Mai
Roland Mai

Reputation: 31077

A really simple option is to build a huge command using the && operator. Here's a snippet to illustrate the idea: cmd /c echo hello && echo world. The disadvantage here is that if any of your tasks fails, it is a bit difficult to figure out which one.

You could always write the commands to a local *.bat file, and process that file instead. Then if one fails, you have a better idea of where it failed, and you want to switch the cmd parameter from /c to /k so you can keep the window open to see what happened.

Upvotes: 0

Related Questions