Executing database script file (.sql) by using process class in .Net

Running the SQL script file by using the System.Diagnostics.Process class

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 += new System.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 = "dev/dev@pulsed";
myProcess.StartInfo.RedirectStandardInput = true;
myProcess.StartInfo.RedirectStandardOutput = true;
myProcess.StartInfo.RedirectStandardError = true;
myProcess.StartInfo.UseShellExecute = false;
myProcess.StartInfo.CreateNoWindow = true;
myProcess.OutputDataReceived += new System.Diagnostics.DataReceivedEventHandler(myProcess_OutputDataReceived);
myProcess.ErrorDataReceived += new System.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);
}


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: