Running the SQL Script File By Using The System.Diagnostics.Process Class

Introduction

While deploying the any application we need to run our SQL script on Server machine. If we pass the SQL file it will execute on server machine.

Using the code

Process Class Provides access to local and remote processes and enables you to start and stop local system processes.

A Process component provides access to a process that is running on a computer. A process, in the simplest terms, is a running application. A thread is the basic unit to which the operating system allocates processor time. A thread can execute any part of the code of the process, including parts currently being executed by another thread.

The "Process.StartInfo" that represents the data with which to start the process. These arguments include the name of the executable file or document used to start the process.

myProcess.StartInfo.FileName = "sqlplus.exe";

The name of the application to start, or the name of a document of a file type that is associated with an application and that has a default open action available to it. The default is an empty string (""). We need to set "sqlplus.exe" or "sqlplus" to start the oracle command prompt.

myProcess.StartInfo.UseShellExecute = false;

true to use the shell when starting the process; otherwise, the process is created directly from the executable file. Here we need to set as "false".

myProcess.StartInfo.WorkingDirectory = "";

The WorkingDirectory property behaves differently when UseShellExecute is true than when UseShellExecute is false. When UseShellExecute is true, the WorkingDirectory property specifies the location of the executable. If WorkingDirectory is an empty string, the current directory is understood to contain the executable.

myProcess.StartInfo.Arguments = "scott/tiger@mydatabase"

"myProcess.StartInfo.Arguments" Gets or sets the set of command-line arguments to use when starting the application. Here we are passing the oracle database User Id, Password and Schema Name to connect to the database.

myProcess.StartInfo.RedirectStandardInput = true;

It is indicating the input for an application is read from the Process.StandardInput stream.

myProcess.StartInfo.RedirectStandardOutput = true;

It is indicating the output of an application is written to the Process.StandardOutput stream.

myProcess.StartInfo.CreateNoWindow = true;

"myProcess.StartInfo.CreateNoWindow" Set true to start the process without creating a new window to contain it.

myProcess.OutputDataReceived += newSystem.Diagnostics.DataReceivedEventHandler(myProcess_OutputDataReceived);

"myProcess.OutputDataReceived" will be fired when an application writes to its redirected StandardOutput stream.

myProcess.StandardInput.WriteLine("@D:\\ex.sql");

A Process can read input text from its standard input stream, typically the keyboard. By redirecting the StandardInput stream, you can programmatically specify the input. For example, instead of using keyboard input, you can provide text from the contents of a designated file or output from another application. Here the above method "WriteLine("@D:\\ex.sql")" is passing the sql command to the oracle command prompt then it will execute the sql file.


Complete Sample Code

        System.Diagnostics.Process myProcess;

        private void button1_Click(object sender, EventArgs e)
        {
            myProcess = new System.Diagnostics.Process();
            myProcess.StartInfo.FileName = "sqlplus.exe";
            myProcess.StartInfo.WorkingDirectory = "";
            myProcess.StartInfo.Arguments = "<a href="mailto:dev/dev@pulsed">dev/dev@pulsed</a>";
            myProcess.StartInfo.RedirectStandardInput = true;
            myProcess.StartInfo.RedirectStandardOutput = true;
            myProcess.StartInfo.RedirectStandardError = true;
            myProcess.StartInfo.UseShellExecute = false;
            myProcess.StartInfo.CreateNoWindow = true;
            myProcess.OutputDataReceived += newSystem.Diagnostics.DataReceivedEventHandler(myProcess_OutputDataReceived);
            myProcess.ErrorDataReceived += newSystem.Diagnostics.DataReceivedEventHandler(myProcess_ErrorDataReceived);
            myProcess.Exited += new EventHandler(myProcess_Exited);
            myProcess.Start();
            myProcess.BeginErrorReadLine();
            myProcess.BeginOutputReadLine();
            myProcess.StandardInput.WriteLine("@D:\\ex.sql");

            myProcess.Close();

        }
        void myProcess_OutputDataReceived(object sender, System.Diagnostics.DataReceivedEventArgs e)
        {
            MessageBox.Show(e.Data);
        }

        void myProcess_Exited(object sender, EventArgs e)
        {
            MessageBox.Show("Exit");
        }
 
        void myProcess_ErrorDataReceived(object sender, System.Diagnostics.DataReceivedEventArgs e)
        {
            MessageBox.Show(e.Data);
        }

Points of Interest

It is very easy to run the SQL Script.

History

1st July 2014 - Initial post