How to Execute Oracle Stored Procedures Dynamically in C#

The attached source code shows how to execute stored procedures from a UI application using Oracle 10G and C#. 

I have not commented the code. If you find any problems, feel free to post your comments at the end of this article or contact me by clicking on Contact Author link in the author profile.

using System;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.IO;
namespace sp
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.DataGrid dataGrid1;
private System.Windows.Forms.Button button1;
private DataSet ds;
private Hashtable hash;
private string tableName;
private System.Windows.Forms.ComboBox SPcomboBox;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public Form1()
{
InitializeComponent();
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.SPcomboBox = new System.Windows.Forms.ComboBox();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.button1 = new System.Windows.Forms.Button();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.SuspendLayout();
//
// SPcomboBox
//
this.SPcomboBox.Location = new System.Drawing.Point(16, 24);
this.SPcomboBox.Name = "SPcomboBox";
this.SPcomboBox.Size = new System.Drawing.Size(121, 21);
this.SPcomboBox.TabIndex = 0;
this.SPcomboBox.Text = "----Select----";
this.SPcomboBox.SelectedIndexChanged += new System.EventHandler(this.SPcomboBox_SelectedIndexChanged);
//
// dataGrid1
//
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(8, 56);
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.Size = new System.Drawing.Size(688, 88);
this.dataGrid1.TabIndex = 1;
//
// button1
//
this.button1.Location = new System.Drawing.Point(8, 152);
this.button1.Name = "button1";
this.button1.TabIndex = 2;
this.button1.Text = "button1";
this.button1.Click += new System.EventHandler(this.button1_Click);
///
/ Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(712, 406);
this.Controls.Add(this.button1);
this.Controls.Add(this.dataGrid1);
this.Controls.Add(this.SPcomboBox);
this.Name = "Form1";
this.Text = "Form1";
this.Load += new System.EventHandler(this.Form1_Load);
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void Form1_Load(object sender, System.EventArgs e)
{
ds = new DataSet();
if(File.Exists(ConfigurationSettings.AppSettings["XMLPath"]))
{
try
{
ds.ReadXml(ConfigurationSettings.AppSettings["XMLPath"]);
}
catch(Exception exp)
{
MessageBox.Show(exp.ToString());
}
if(ds.Tables.Count > 0)
{
foreach(DataTable spTable in ds.Tables)
SPcomboBox.Items.Add(spTable.TableName);
}
else
{
MessageBox.Show("No Schema Available in xml file");
}
}
else
{
MessageBox.Show("Please put Stored procedure schema file in ConfigurationSettings.AppSettings['XMLPath']");
}
}
private OracleType ReturnType(string val)
{
switch(val.ToLower())
{
case "bfile" : return OracleType.BFile;
case "blob" : return OracleType.Blob;
case "byte" : return OracleType.Byte;
case "char" : return OracleType.Char;
case "datetime" : return OracleType.DateTime;
case "clob" : return OracleType.Clob;
case "float" : return OracleType.Float;
case "cursor" : return OracleType.Cursor;
case "int16" : return OracleType.Int16;
case "int32" : return OracleType.Int32;
case "double" : return OracleType.Double;
case "intervaldaytosecond" : return OracleType.IntervalDayToSecond;
case "nchar" : return OracleType.NChar;
case "nclob" : return OracleType.NClob;
case "nvarchar" : return OracleType.NVarChar;
case "Raw" : return OracleType.Raw;
case "intervalyeartomonth" : return OracleType.IntervalYearToMonth;
case "longraw" : return OracleType.LongRaw;
case "longvarchar" : return OracleType.LongVarChar;
case "number" : return OracleType.Number;
case "rowid" : return OracleType.RowId;
case "sbyte" : return OracleType.SByte;
case "timestamp" : return OracleType.Timestamp;
case "timestamplocal" : return OracleType.TimestampLocal;
case "timestampwithtz" : return OracleType.TimestampWithTZ;
case "uint16" : return OracleType.UInt16;
case "uint32" : return OracleType.UInt32;
case "varchar" : return OracleType.VarChar;
}return OracleType.VarChar;
}
private void button1_Click(object sender, System.EventArgs e)
{
OracleConnection con = new OracleConnection("Data Source=ORCL;User Id=scott;Password=tiger");
OracleCommand cmd = new OracleCommand(tableName, con);
cmd.CommandType = CommandType.StoredProcedure;
if( hash.Count > 0)
{
OracleParameter[] param = (OracleParameter[])hash[tableName];
for(int index = 0; index < param.Length ; index++)
{
param[index].Value = dataGrid1[0, index];
cmd.Parameters.Add(param[index]);
}
}
try
{
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Successfully Done");
}
catch(Exception exp)
{
MessageBox.Show(exp.ToString());
}
finally
{
cmd.Parameters.Clear();
cmd.Dispose();
con.Close();
}
}
private void SPcomboBox_SelectedIndexChanged(object sender, System.EventArgs e)
{
tableName = SPcomboBox.Items[SPcomboBox.SelectedIndex].ToString();
BindParameters();
}
private void BindParameters()
{
hash = new Hashtable();
dataGrid1.DataSource = ds.Tables[tableName];
int columnNumber = ds.Tables[tableName].Columns.Count;
if(columnNumber > 0)
{
if(ds.Tables[tableName].Columns[0].ColumnName != "NoParam")
{
OracleParameter[] param = new OracleParameter[columnNumber];
for(int index = 0; index < columnNumber; index++)
{
param[index] = new OracleParameter();
param[index].OracleType = ReturnType(ds.Tables[tableName].Rows[0][index].ToString());
param[index].ParameterName = ds.Tables[tableName].Columns[index].ColumnName;
}
hash.Add(tableName, param);
}
}
}
}
}
<?xml version="1.0" encoding="utf-8" ?>
<StoredProcedure>
<sp1>
<EName>varchar</EName>
<Job>varchar</Job>
<Sal>numeric</Sal>
<comm>numeric</comm>
</sp1>
<sp2>
<NoParam></NoParam>
</sp2>
<sp3>
<NoParam></NoParam>
</sp3>
</StoredProcedure>