Mega Code Archive
Using OracleParameter
<![CDATA[
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OracleClient;
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button btnConnect;
private System.Windows.Forms.Button btnGetIDs;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.ComboBox cbEmpIds;
private System.Windows.Forms.Label lblFirstName;
private System.Windows.Forms.Label lblLastName;
private System.Windows.Forms.Label label4;
private System.Windows.Forms.Label label5;
private System.Windows.Forms.Button btnLookup1;
private System.Windows.Forms.Button btnLookup2;
private System.Windows.Forms.Label lblEmail;
private System.Windows.Forms.Label lblPhone;
private System.Windows.Forms.Label lblEmailText;
private System.Windows.Forms.Label lblPhoneText;
private System.Windows.Forms.Button btnReset;
private System.Windows.Forms.Button btnNoBinds;
private System.ComponentModel.Container components = null;
public Form1()
{
this.btnConnect = new System.Windows.Forms.Button();
this.btnGetIDs = new System.Windows.Forms.Button();
this.cbEmpIds = new System.Windows.Forms.ComboBox();
this.label1 = new System.Windows.Forms.Label();
this.lblFirstName = new System.Windows.Forms.Label();
this.lblLastName = new System.Windows.Forms.Label();
this.label4 = new System.Windows.Forms.Label();
this.label5 = new System.Windows.Forms.Label();
this.btnLookup1 = new System.Windows.Forms.Button();
this.btnLookup2 = new System.Windows.Forms.Button();
this.lblEmail = new System.Windows.Forms.Label();
this.lblPhone = new System.Windows.Forms.Label();
this.lblEmailText = new System.Windows.Forms.Label();
this.lblPhoneText = new System.Windows.Forms.Label();
this.btnReset = new System.Windows.Forms.Button();
this.btnNoBinds = new System.Windows.Forms.Button();
this.SuspendLayout();
this.btnConnect.Location = new System.Drawing.Point(32, 44);
this.btnConnect.Text = "C&onnect";
this.btnConnect.Click += new System.EventHandler(this.btnConnect_Click);
this.btnGetIDs.Location = new System.Drawing.Point(32, 80);
this.btnGetIDs.Text = "&Get IDs";
this.btnGetIDs.Click += new System.EventHandler(this.btnGetIDs_Click);
this.cbEmpIds.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
this.cbEmpIds.Location = new System.Drawing.Point(144, 44);
this.cbEmpIds.Size = new System.Drawing.Size(68, 21);
this.label1.Location = new System.Drawing.Point(144, 24);
this.label1.Size = new System.Drawing.Size(72, 16);
this.label1.Text = "&Employee ID:";
this.lblFirstName.Location = new System.Drawing.Point(228, 48);
this.lblFirstName.Size = new System.Drawing.Size(116, 16);
this.lblLastName.Location = new System.Drawing.Point(364, 48);
this.lblLastName.Size = new System.Drawing.Size(128, 16);
this.label4.Location = new System.Drawing.Point(228, 24);
this.label4.Size = new System.Drawing.Size(100, 16);
this.label4.Text = "&First Name:";
this.label5.Location = new System.Drawing.Point(364, 24);
this.label5.Size = new System.Drawing.Size(100, 16);
this.label5.Text = "&Last Name:";
this.btnLookup1.Location = new System.Drawing.Point(32, 116);
this.btnLookup1.Text = "Lookup &1";
this.btnLookup1.Click += new System.EventHandler(this.btnLookup1_Click);
this.btnLookup2.Location = new System.Drawing.Point(32, 152);
this.btnLookup2.Text = "Lookup &2";
this.btnLookup2.Click += new System.EventHandler(this.btnLookup2_Click);
this.lblEmail.Location = new System.Drawing.Point(228, 88);
this.lblEmail.Size = new System.Drawing.Size(100, 16);
this.lblEmail.Text = "E&mail:";
this.lblPhone.Location = new System.Drawing.Point(364, 88);
this.lblPhone.Size = new System.Drawing.Size(100, 16);
this.lblPhone.Text = "Phone &Number:";
this.lblEmailText.Location = new System.Drawing.Point(228, 112);
this.lblEmailText.Size = new System.Drawing.Size(116, 16);
this.lblPhoneText.Location = new System.Drawing.Point(364, 112);
this.lblPhoneText.Size = new System.Drawing.Size(128, 16);
this.btnReset.Location = new System.Drawing.Point(32, 224);
this.btnReset.Text = "&Reset";
this.btnReset.Click += new System.EventHandler(this.btnReset_Click);
this.btnNoBinds.Location = new System.Drawing.Point(32, 188);
this.btnNoBinds.Text = "No &Binds";
this.btnNoBinds.Click += new System.EventHandler(this.btnNoBinds_Click);
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(534, 264);
this.Controls.Add(this.btnNoBinds);
this.Controls.Add(this.btnReset);
this.Controls.Add(this.lblPhoneText);
this.Controls.Add(this.lblEmailText);
this.Controls.Add(this.lblPhone);
this.Controls.Add(this.lblEmail);
this.Controls.Add(this.btnLookup2);
this.Controls.Add(this.btnLookup1);
this.Controls.Add(this.label5);
this.Controls.Add(this.label4);
this.Controls.Add(this.lblLastName);
this.Controls.Add(this.lblFirstName);
this.Controls.Add(this.label1);
this.Controls.Add(this.cbEmpIds);
this.Controls.Add(this.btnGetIDs);
this.Controls.Add(this.btnConnect);
this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Fixed3D;
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Text = "Oracle Parameter Sample";
this.Load += new System.EventHandler(this.Form1_Load);
this.ResumeLayout(false);
}
static void Main()
{
Application.Run(new Form1());
}
private OracleConnection oraConn;
private void btnConnect_Click(object sender, System.EventArgs e)
{
string connString = "User Id=hr; Password=demo; Data Source=oranet";
if (oraConn.State != ConnectionState.Open)
{
try
{
oraConn = new OracleConnection(connString);
oraConn.Open();
MessageBox.Show(oraConn.ConnectionString, "Successful Connection");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"Exception Caught");
}
}
}
private void btnGetIDs_Click(object sender, System.EventArgs e)
{
OracleCommand cmdEmpId = new OracleCommand();
cmdEmpId.CommandText = "select employee_id from employees order by employee_id";
cmdEmpId.Connection = oraConn;
try
{
OracleDataReader dataReader = cmdEmpId.ExecuteReader();
while (dataReader.Read())
{
cbEmpIds.Items.Add(dataReader.GetDecimal(0));
}
dataReader.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"Exception Caught");
}
finally
{
cmdEmpId.Dispose();
}
}
private void Form1_Load(object sender, System.EventArgs e)
{
oraConn = new OracleConnection();
}
private void btnLookup1_Click(object sender, System.EventArgs e)
{
object selectedItem = cbEmpIds.SelectedItem;
if (selectedItem != null)
{
OracleCommand cmdEmpName = new OracleCommand();
cmdEmpName.CommandText = "select first_name, last_name from employees where employee_id = :p_id";
cmdEmpName.Connection = oraConn;
OracleParameter p_id = new OracleParameter();
p_id.DbType = DbType.Decimal;
p_id.Value = Convert.ToDecimal(selectedItem.ToString());
p_id.ParameterName = "p_id";
cmdEmpName.Parameters.Add(p_id);
OracleDataReader dataReader = cmdEmpName.ExecuteReader();
if (dataReader.Read())
{
lblFirstName.Text = dataReader.GetString(0);
lblLastName.Text = dataReader.GetString(1);
}
dataReader.Close();
dataReader.Dispose();
cmdEmpName.Dispose();
}
}
private void btnLookup2_Click(object sender, System.EventArgs e)
{
OracleCommand cmdEmpInfo = new OracleCommand();
cmdEmpInfo.CommandText = "select email, phone_number from employees where first_name = :p_first and last_name = :p_last";
cmdEmpInfo.Connection = oraConn;
OracleParameter p1 = new OracleParameter();
OracleParameter p2 = new OracleParameter();
p1.ParameterName = "p_first";
p2.ParameterName = "p_last";
p1.Value = lblFirstName.Text;
p2.Value = lblLastName.Text;
cmdEmpInfo.Parameters.Add(p2);
cmdEmpInfo.Parameters.Add(p1);
OracleDataReader dataReader = cmdEmpInfo.ExecuteReader();
if (dataReader.Read())
{
lblEmailText.Text = dataReader.GetString(0);
lblPhoneText.Text = dataReader.GetString(1);
}
dataReader.Close();
dataReader.Dispose();
cmdEmpInfo.Dispose();
}
private void btnReset_Click(object sender, System.EventArgs e)
{
cbEmpIds.SelectedIndex = -1;
lblFirstName.Text = "";
lblLastName.Text = "";
lblEmailText.Text = "";
lblPhoneText.Text = "";
}
private void btnNoBinds_Click(object sender, System.EventArgs e)
{
object selectedItem = cbEmpIds.SelectedItem;
if (selectedItem != null)
{
OracleCommand cmdNoBinds = new OracleCommand();
cmdNoBinds.Connection = oraConn;
OracleDataReader dataReader;
cmdNoBinds.CommandText = "select first_name, last_name from employees where employee_id = " + selectedItem.ToString();
dataReader = cmdNoBinds.ExecuteReader();
if (dataReader.Read())
{
lblFirstName.Text = dataReader.GetString(0);
lblLastName.Text = dataReader.GetString(1);
}
dataReader.Close();
cmdNoBinds.CommandText = "select email, phone_number from employees where first_name = '" + lblFirstName.Text + "' and last_name = '" + lblLastName.Text +"'";
dataReader = cmdNoBinds.ExecuteReader();
if (dataReader.Read())
{
lblEmailText.Text = dataReader.GetString(0);
lblPhoneText.Text = dataReader.GetString(1);
}
dataReader.Close();
dataReader.Dispose();
cmdNoBinds.Dispose();
}
}
}
]]>