Accessing Oracle Database


This source code shows you how to connect to an oracle database and do operations such as select, insert, update and delete.

Source Code: 

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
namespace Employee
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class FrmEmp : System.Windows.Forms.Form
{
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.Label label4;
private System.Windows.Forms.TextBox TxtEmpNo;
private System.Windows.Forms.TextBox TxtEName;
private System.Windows.Forms.TextBox TxtESal;
private System.Windows.Forms.TextBox TxtDeptNo;
private System.Windows.Forms.Button BtnAddNew;
private System.Windows.Forms.Button BtnSave;
private System.Windows.Forms.Button BtnDelete;
private System.Windows.Forms.Button BtnExit;
private System.Data.OleDb.OleDbConnection Conn;
private System.Data.OleDb.OleDbCommand Comm;
private System.Data.OleDb.OleDbDataAdapter DataAdpt;
private System.Data.OleDb.OleDbCommandBuilder CBuild;
private System.Data.OleDb.OleDbDataReader DtRead;
private System.Data.DataSet DataSet1;
private System.Data.DataRow DataRow1;
private System.Data.DataTable DataTable1;
private string ConnStr;
private bool blnEdit;
private System.Windows.Forms.Button BtnEdit;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public FrmEmp()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
ConnStr = "Provider=MSDAORA;DSN=oracle;User ID=system;Password=manager"; Conn = new OleDbConnection(ConnStr);
Conn.Open();
DataAdpt = new OleDbDataAdapter("Select * from Emp",Conn);
CBuild = new OleDbCommandBuilder(DataAdpt);
DataSet1 = new DataSet("Emp");
DataTable1 = new DataTable("Emp");
DataAdpt.Fill(DataSet1,"Emp");
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <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.label4 = new System.Windows.Forms.Label();
this.BtnExit = new System.Windows.Forms.Button();
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.label3 = new System.Windows.Forms.Label();
this.BtnEdit = new System.Windows.Forms.Button();
this.BtnSave = new System.Windows.Forms.Button();
this.BtnDelete = new System.Windows.Forms.Button();
this.TxtESal = new System.Windows.Forms.TextBox();
this.TxtEmpNo = new System.Windows.Forms.TextBox();
this.TxtEName = new System.Windows.Forms.TextBox();
this.BtnAddNew = new System.Windows.Forms.Button();
this.TxtDeptNo = new System.Windows.Forms.TextBox();
this.SuspendLayout();
//
// label4
//
this.label4.AutoSize = true;
this.label4.Location = new System.Drawing.Point(104, 160);
this.label4.Name = "label4";
this.label4.Size = new System.Drawing.Size(59, 14);
this.label4.TabIndex = 3;
this.label4.Text = "Dept No :";
//
// BtnExit
//
this.BtnExit.Location = new System.Drawing.Point(280, 256);
this.BtnExit.Name = "BtnExit";
this.BtnExit.Size = new System.Drawing.Size(90, 32);
this.BtnExit.TabIndex = 12;
this.BtnExit.Text = "E&xit";
this.BtnExit.Click += new System.EventHandler(this.BtnExit_Click);
//
// label1
//
this.label1.AutoSize = true;
this.label1.Location = new System.Drawing.Point(104, 64);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(58, 14);
this.label1.TabIndex = 0;
this.label1.Text = "Emp No :";
//
// label2
//
this.label2.AutoSize = true;
this.label2.Location = new System.Drawing.Point(104, 96);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(75, 14);
this.label2.TabIndex = 1;
this.label2.Text = "Emp Name :";
//
// label3
//
this.label3.AutoSize = true;
this.label3.Location = new System.Drawing.Point(104, 128);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(78, 14);
this.label3.TabIndex = 2;
this.label3.Text = "Emp Salary :";
//
// BtnEdit
//
this.BtnEdit.Location = new System.Drawing.Point(328, 208);
this.BtnEdit.Name = "BtnEdit";
this.BtnEdit.Size = new System.Drawing.Size(90, 32);
this.BtnEdit.TabIndex = 10;
this.BtnEdit.Text = "Edit";
this.BtnEdit.Click += new System.EventHandler(this.BtnEdit_Click);
//
// BtnSave
//
this.BtnSave.Location = new System.Drawing.Point(224, 208);
this.BtnSave.Name = "BtnSave";
this.BtnSave.Size = new System.Drawing.Size(90, 32);
this.BtnSave.TabIndex = 9;
this.BtnSave.Text = "Save";
this.BtnSave.Click += new System.EventHandler(this.BtnSave_Click);
//
// BtnDelete
//
this.BtnDelete.Location = new System.Drawing.Point(168, 256);
this
.BtnDelete.Name = "BtnDelete";
this.BtnDelete.Size = new System.Drawing.Size(90, 32);
this.BtnDelete.TabIndex = 11;
this.BtnDelete.Text = "Delete";
this.BtnDelete.Click += new System.EventHandler(this.BtnDelete_Click);
//
// TxtESal
//
this.TxtESal.Location = new System.Drawing.Point(192, 128);
this.TxtESal.Name = "TxtESal";
this.TxtESal.Size = new System.Drawing.Size(112, 21);
this.TxtESal.TabIndex = 6;
this.TxtESal.Text = "";
//
// TxtEmpNo
//
this.TxtEmpNo.Location = new System.Drawing.Point(192, 64);
this
.TxtEmpNo.Name = "TxtEmpNo";
this.TxtEmpNo.Size = new System.Drawing.Size(112, 21);
this.TxtEmpNo.TabIndex = 4;
this.TxtEmpNo.Text = "";
//
// TxtEName
//
this.TxtEName.Location = new System.Drawing.Point(192, 96);
this
.TxtEName.Name = "TxtEName";
this.TxtEName.Size = new System.Drawing.Size(200, 21);
this.TxtEName.TabIndex = 5;
this.TxtEName.Text = "";
//
// BtnAddNew
//
this.BtnAddNew.Location = new System.Drawing.Point(120, 208);
this
.BtnAddNew.Name = "BtnAddNew";
this.BtnAddNew.Size = new System.Drawing.Size(90, 32);
this
.BtnAddNew.TabIndex = 8;
this.BtnAddNew.Text = "&Add New";
this.BtnAddNew.Click += new System.EventHandler(this.BtnAddNew_Click);
//
// TxtDeptNo
//
this.TxtDeptNo.Location = new System.Drawing.Point(192, 160);
this
.TxtDeptNo.Name = "TxtDeptNo";
this.TxtDeptNo.Size = new System.Drawing.Size(112, 21);
this.TxtDeptNo.TabIndex = 7;
this.TxtDeptNo.Text = "";
//
// FrmEmp
//
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(576, 317);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.BtnExit, this.BtnDelete, this.BtnEdit, this.BtnSave, this.BtnAddNew, this.TxtDeptNo, this.TxtESal, this.TxtEName, this.TxtEmpNo, this.label4, this.label3, this.label2, this.label1});
this.Font = new System.Drawing.Font("Microsoft Sans Serif", 9F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.Name = "FrmEmp";
this.Text = "Employee Data Form";
this.Load += new System.EventHandler(this.FrmEmp_Load);
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new FrmEmp());
}
private void FrmEmp_Load(object sender, System.EventArgs e)
{
}
private void BtnExit_Click(object sender, System.EventArgs e)
{
Conn.Close();
this.Close();
}
private void BtnAddNew_Click(object sender, System.EventArgs e)
{
TxtEmpNo.Text = "";
TxtEName.Text = "";
TxtESal.Text = "";
TxtDeptNo.Text = "";
TxtEmpNo.Focus();
}
private void BtnSave_Click(object sender, System.EventArgs e)
{
try
{
if (blnEdit == false)
{
DataRow1 = DataSet1.Tables["Emp"].NewRow();
DataRow1["EmpNo"] = Int16.Parse(TxtEmpNo.Text);
DataRow1["EName"] = TxtEName.Text;
DataRow1["Sal"] = Double.Parse(TxtESal.Text);
DataRow1["DeptNo"] = Int32.Parse(TxtDeptNo.Text);
DataSet1.Tables["Emp"].Rows.Add(DataRow1);
DataAdpt.Update(DataSet1,"Emp");
MessageBox.Show("record saved!");
}
else
{
string Str1;
Str1 = "Update Emp set EName='" + TxtEName.Text + "',Sal=" + double.Parse(TxtESal.Text) + ",";
Str1 = Str1 + "DeptNo=" + Int16.Parse(TxtDeptNo.Text) + " Where EmpNo=" + Int16.Parse(TxtEmpNo.Text);
Comm = new OleDbCommand(Str1,Conn);
Comm.ExecuteNonQuery();
/*drEdit.BeginEdit();
drEdit["EName"] = TxtEName.Text;
drEdit["Sal"] = double.Parse(TxtESal.Text);
drEdit["DeptNo"] = Int16.Parse(TxtDeptNo.Text);
drEdit.EndEdit();
DataAdpt.Update(DataSet1,"Emp");*/

MessageBox.Show("Record Modified!");
}
}
catch(Exception e1)
{
MessageBox.Show(e1.ToString());
}
}
private void BtnEdit_Click(object sender, System.EventArgs e)
{
string Str1;
try
{
Str1 = "Select * from Emp Where EmpNo=" + Int16.Parse(TxtEmpNo.Text);
Comm = new OleDbCommand(Str1, Conn);
DtRead = Comm.ExecuteReader();
if (DtRead.Read())
{
blnEdit = true;
//drEdit = DataSet1.Tables["Emp"].Rows.Find(Int16.Parse(TxtEmpNo.Text));
TxtEName.Text = DtRead["EName"].ToString();
TxtESal.Text = DtRead["Sal"].ToString();
TxtDeptNo.Text = DtRead["DeptNo"].ToString();
TxtEName.Focus();
}
else
{
TxtEName.Text = "";
TxtESal.Text = "";
TxtDeptNo.Text = "";
MessageBox.Show("Record Not found!");
TxtEmpNo.Focus();
}
DtRead.Close();
}
catch(Exception e1)
{
MessageBox.Show(e1.ToString());
}
}
private void BtnDelete_Click(object sender, System.EventArgs e)
{
try
{
object Str1;
string Str2;
Str1 = MessageBox.Show("Do you want to delete the record","Delete Box", System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Warning);
//==DialogResult.Yes)
if (Str1.ToString() == "Yes")
{
Str2 = "Delete from Emp Where EmpNo=" + Int16.Parse(TxtEmpNo.Text);
Comm = new OleDbCommand(Str2,Conn);
Comm.ExecuteNonQuery();
MessageBox.Show("Record has been deleted!");
TxtEmpNo.Text = "";
TxtEName.Text = "";
TxtESal.Text = "";
TxtDeptNo.Text = "";
TxtEmpNo.Focus();
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}


Similar Articles