Image via CrunchBaseAfter so many days of searching and experimenting on how to use
stored procedures in my applications, and specifically, to use Oracleclient, I was getting tired and frustrated. None of my searches in the
web is returning the exact article that I need. Until about today.
It was a search, some kind of a last recourse, a button click with my dying enthusiasm... the last blast.
And it somehow paid off!
When I saw the article in
this page, I knew that the
connection string is defined differently.
I kept on doing search and review and testing.
I should mention that I saw some useful articles in
MSDN, where I also learned that when handling result sets that are multiple rows and/or multiple columns, the ordinary
query or
stored procedure will not work. The use of REF CURSOR is now a mandate.
One such useful reference can be found
here. This is where I patterned my stored procedure, made up of a stored procedure in a package.
Then finally, today, I could say that as a last recourse, I typed on "Data Reader on
Oracle stored procedure in C#", "using Oracle client to fetch multiple rows", and to the likes of that search strings, and I found one coming from
The Code Project, where I am subscribed to.
I found one, which, I am not able to understand immediately. But the connection string is once again stressed out to be different.
I tried a few more times, and then finally, with one last try before I would ditch my attempts forever, it worked!
That page from The Code Project team is
here.
I needed the Oracleclient dll file (
.NET Managed Provider for Oracle), and as directed by the Code Project article, i downloaded the installer from a Microsoft Download page. Well, find that page
here also.
So why did I pursue this item?
Speed and efficiency. Not to mention maintainability.
Stored procedures are many times more efficient that inline queries. The comparison I did was to run an inline query and the stored procedure. The timing I put in front and behind is to measure the time the small code would take to complete.
One query. Short one. Simple one.
The inline query took about 1 min and 30 secs.
The stored procedure took about 11 secs to complete.
That is approximately a speed of 6x faster or slower!
And if you need to change anything in the query, you can do it outside without ever disturbing your application - if you are using stored procedures.
And as always said, stored procedures already
IN THE Database system are already optimized by the machine, rather that when it is first submitted and run by the machine.
And with all that has been done and tested, I am happy to present my simple code, for my own reference, and for everybody else who would find this useful.
Till then!
-----
Points to note:
- Inline query is the same as the stored procedure, modified accordingly.
- The parameters (input, output) should have the same names as declared in the stored procedure/package.
-----
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Text;
using System.Windows.Forms;
namespace Get_Data_by_OracleClient
{
public partial class Form1 : Form
{
public Form1()
{ InitializeComponent(); }
private void Form1_Load(object sender, EventArgs e)
{
TBox1.Clear();
TBox2.Clear();
TBox3.Clear();
TBox4.Clear();
}
private void Btn_Click(object sender, EventArgs e)
{
this.Get_Data_by_InlineQ(TBox1.Text, TBox4);
this.Get_Data_by_StoredP(TBox1.Text, TBox4);
}
private void Get_Data_by_InlineQ(String thisITMSDevice, TextBox thisTBox)
{
thisTBox.Text = "";
//
StringBuilder myQuery = new StringBuilder();
string dbProvider = "Provider=MSDAORA.1;";
string dbUser = "User ID=myUserName;";
string dbPwd = "Password=MyPassword;";
string dbName = "myOracleDBName";
string dbODS = dbProvider + dbUser + dbPwd + "Data Source=" + dbName;
OleDbConnection dbConn = new OleDbConnection(dbODS);
StringBuilder myResult = new StringBuilder();
myResult.Append(DateTime.Now.ToLongTimeString()).AppendLine();
//
myQuery.Append(" type in your inline query here ");
//
OleDbCommand dbCmd = new OleDbCommand(myQuery.ToString(), dbConn);
//
try
{
dbConn.Open();
OleDbDataReader dr = dbCmd.ExecuteReader();
myResult.Append(dr.FieldCount.ToString()).AppendLine();
while (dr.Read())
{ myResult.Append(dr[0].ToString()).AppendLine(); }
dr.Close();
dbConn.Close();
thisTBox.Text += myResult.ToString();
thisTBox.Text += DateTime.Now.ToLongTimeString() + "\n";
}
catch (Exception ex)
{ MessageBox.Show(ex.ToString()); }
}
private void Get_Data_by_StoredP(String thisITMSDevice, TextBox thisTBox)
{
thisTBox.Text = "";
//
string dbUser = "User ID=myUserName;";
string dbPwd = "Password=MyPassword;";
string dbName = "Data Source=myOracleDBName;";
string dbODS = dbName + "Persist Security Info=True;" + dbUser + dbPwd + "Unicode=True;";
StringBuilder myResult = new StringBuilder();
myResult.Append(DateTime.Now.ToLongTimeString()).AppendLine();
//
OracleConnection dbConn = new OracleConnection(dbODS);
OracleCommand dbCmd = new OracleCommand();
dbCmd.Connection = dbConn;
dbCmd.CommandText = "GetData_by_Ora.GetData_by_StoredP";
dbCmd.CommandType = CommandType.StoredProcedure;
//
OracleParameter myITMSDev = dbCmd.Parameters.Add("InputVar", OracleType.VarChar, 32);
myITMSDev.Direction = ParameterDirection.Input;
OracleParameter myPrcdName = dbCmd.Parameters.Add("ResVar1", OracleType.Cursor);
myPrcdName.Direction = ParameterDirection.Output;
//
myITMSDev.Value = thisITMSDevice.ToString().Trim().ToUpper();
//
dbConn.Open();
OracleDataReader rdr = dbCmd.ExecuteReader();
myResult.Append(rdr.FieldCount.ToString()).AppendLine();
while (rdr.Read())
{ myResult.Append(rdr[0].ToString()).AppendLine(); }
rdr.Close();
dbConn.Close();
thisTBox.Text += myResult.ToString();
thisTBox.Text += DateTime.Now.ToLongTimeString() + "\n";
}
}
}
