Running sqlPlus.exe as a System.Diagnostics.Process? Here's how to kill it.

I recently had a project that required me to execute the Oracle command line tool, sqlPlus.exe. To execute the process and pass the required parameters was no problem. However, I needed to run this process multiples times in a row for various files. As I was doing so, I would watch task manager and I noticed that I had multiple sqlPlus.exe processes running, even though my process that I wrote was only run once.

To start, lets look at my code that executs sqlPlus.exe.

System.Diagnostics.Process process = new System.Diagnostics.Process();
System.Diagnostics.ProcessStartInfo psiProc = new System.Diagnostics.ProcessStartInfo();
psiProc.WorkingDirectory = gcnfgOracle.SqlPlusLocation;
psiProc.FileName = "sqlPlus.exe";
psiProc.RedirectStandardOutput = true;
psiProc.RedirectStandardError = true;
psiProc.UseShellExecute = false;
process.StartInfo = psiProc;
dbProcess.Start();
dbProcess.WaitForExit();
string lstrStdOut = dbProcess.StandardOutput.ReadToEnd(); //Read my Output
int liExitCode = dbProcess.ExitCode; // What is my return code
dbProcess.Close();

So this looks all well and good, however, after looking a the sqlPlus.exe process myself, the process never exists on its ownAfter it processes my request, the process remains open waiting for user input, .WaitForExit() just waited.

I had attempted some workaround that required me to wait a certain number of seconds and then forcibly killing the process but I could be killing an inflight transaction and did not want to do that. So I came up with this solution.

Here is my solution. I will use the same code as above, just highlighting the changes:

System.Diagnostics.Process process = new System.Diagnostics.Process();
System.Diagnostics.ProcessStartInfo psiProc = new System.Diagnostics.ProcessStartInfo();
psiProc.WorkingDirectory = gcnfgOracle.SqlPlusLocation;
psiProc.FileName = "sqlPlus.exe";
psiProc.RedirectStandardOutput = true;
psiProc.RedirectStandardError = true;

psiProc.RedirectStandardInput = true; // Allow me to send input to this process

psiProc.UseShellExecute = false;
process.StartInfo = psiProc;
dbProcess.Start();
dbProcess.WaitForExit();

StreamWriter swConsoleInput = dbProcess.StandardInput; // Get the stream of the input
swConsoleInput.WriteLine("exit"); // Send in the command that will exit the sqlPlus.exe process

string lstrStdOut = dbProcess.StandardOutput.ReadToEnd(); // Read my Output
int liExitCode = dbProcess.ExitCode; // What is my return code
dbProcess.Close();

That's it.. just get a stream to the input and send it "Exit". Keep in mind that you need to press the Enter Key after typing exit. To do this, I am using the WriteLine Method of the StreamWriter. That is very important If you use Write vs WriteLine, you will need to send the enter key manually.

Easily split a string with Upper Case Letters

I am often finding myself having to take one long string, containing Upper and Lower case letters, and having to split it up by the Upper Case letter to make up the words. Here is a quick and easy way to do so:


string base_string = "ThisIsMyString";
string formated_string = ""; //This will hold our result of This Is My String
Array.ForEach
     (base_string.ToCharArray() // Take the base string and create an array of each charater
     , x=>
         {
         if(char.IsUpper(x))
         {
              formated_string += " ";
         };
         formated_string = string.Concat(formated_string , x.ToString());
         }
     );

Response is This Is My String

How to use LINQ to return one columns values (C#, LINQ)

I wanted to find a way to get all the data from one column in one pass.  I could do this with a loop or I can use LINQ to shorten my code.

To show the difference I will show you the manual loop way and the LINQ way.

DataTable ldtDataTable = new DataTable("Data");
ldtDataTable.Columns.Add("Column1", typeof(String));
ldtDataTable.Columns.Add("Column2", typeof(String));
ldtDataTable.Columns.Add("Column3", typeof(String));
ldtDataTable.Rows.Add(new object[] { "Row1Column1", "Row1Column2", "Row1Column2" });
ldtDataTable.Rows.Add(new object[] { "Row2Column1", "Row2Column2", "Row2Column2" });
ldtDataTable.Rows.Add(new object[] { "Row3Column1", "Row3Column2", "Row3Column2" });
ldtDataTable.Rows.Add(new object[] { "Row4Column1", "Row4Column2", "Row4Column2" });
ldtDataTable.Rows.Add(new object[] { "Row5Column1", "Row5Column2", "Row5Column2" });

/// I ONLY WANT TO GET THE VALUES IN Column2, separated with a ; (semi-colon)
///This is the variable that will hold my columns values
string lstrValues = string.Empty;
///Here is the manual loop
foreach(DataRow dr in ldtDataTable.Rows)
{
     lstrValues += Convert.ToString(dr["Column2"]) +"; ";
}
Console.WriteLine(lstrValues);

The output from the above code is:

Row1Column2; Row2Column2; Row3Column2; Row4Column2; Row5Column2; 

All though this is completely acceptable, there is a way to do this with out the loop. I am using the same DataTable as outlined as above as well as the same variable
Here is the LINQ way

var t = (from value in ldtDataTable.AsEnumerable() select value.Field("Column2"));
Array.ForEach((String[])t.ToArray(), z => lstrValues += z + "; ");

The output from the above code is:

Row1Column2; Row2Column2; Row3Column2; Row4Column2; Row5Column2;

As you can see this is a much cleaner and simpler way to get the same data.
If you wanted to shorten your code even more, you can do the following:

Array.ForEach((String[])(from value in ldtDataTable.AsEnumerable() select value.Field("Column2"))
.ToArray(), z => lstrValues += z + "; ");

How to use LINQ to join DataTables (C#, LINQ)

Its not often I need to join multiple DataTables in my .Net classes. I attempt to do as much joining on a Dataserver as possible, but when two tables only exists in memory I must.

You can use a DataRelation object to join my two tables or I can use LINQ to get me my results. In this example, I will show you two simple DataTables and how I join the two together.

Here is my first DataTable:

DataTable dtProducts = new DataTable("Companies");
dtProducts.Columns.AddRange(new DataColumn[] { new DataColumn("CompanyName") });
dtProducts.Rows.Add(new object[] { "Microsoft" });
dtProducts.Rows.Add(new object[] { "Google" });
dtProducts.Rows.Add(new object[] { "Apple" });

Here is my second DataTable

DataTable dtProductMappings = new DataTable("CompanyProducts");
dtProductMappings.Columns.AddRange(new DataColumn[]{new DataColumn("CompanyName"), new DataColumn("ProductName") });
dtProductMappings.Rows.Add(new object[] { "Microsoft", "Windows" });
dtProductMappings.Rows.Add(new object[] { "Microsoft", "Office" });
dtProductMappings.Rows.Add(new object[] { "Google", "Internet Search" });
dtProductMappings.Rows.Add(new object[] { "Google", "Android" });
dtProductMappings.Rows.Add(new object[] { "Apple", "iOS" });
dtProductMappings.Rows.Add(new object[] { "Apple", "iPhone" });
dtProductMappings.Rows.Add(new object[] { "Apple", "iPad" });
dtProductMappings.Rows.Add(new object[] { "Apple", "iPod" });

The first table is a list companies and the second is a list of some of their products.

The two DataTables have a common column, CompanyName. I want to return only the rows in CompanyProducts table. For this example I will create an Generic Int List of the companies I want to see products for using LINQ against my Companies DataTable. In this case, I want to see all but Microsoft's products.

List<int> lintProducts = (from s in dtProducts.AsEnumerable()
                          where s.Field<string>("CompanyName") != "Microsoft"
                          select s.Field<int>("CompanyId")).ToList<int>();

This will result in lintProducts having two integers, 2 (Google) and 3 (Apple).
Now for the query to join the two together:

var companyProducts = from a 
                          in dtProductMappings.AsEnumerable()
                          where productfound(a.Field<int>("CompanyId"), lintProducts)
                          select dtProducts.Select("CompanyId = '" + a.Field<int>("CompanyId") +"'")[0]["CompanyName"]+ " - " + a.Field<string>("ProductName");

Finally, I want to display my results. Its a simple as:

Array.ForEach(companyProducts.ToArray(), z => Console.WriteLine(z + ", "));

Here are the results:

Google - Internet Search, 
Google - Android, 
Apple - iOS, 
Apple - iPhone, 
Apple - iPad, 
Apple - iPod,

Notice that I am using a function called productFound to filter out the products I am interested in.
Here is the code for productFound:

static private bool productfound(int currentProduct, List<int> seekingProduct)
{
        bool lbReturn = false;
        foreach (int s in seekingProduct)
        {
                if (s.Equals(currentProduct))
                {
                    lbReturn = true;
                    break;
                }
        }
        return lbReturn;
}

This simply allows me to search through my DataTable to look for one or more value, in this case 2 (Google) and 3 (Apple)