Reader Level:
Articles

Accessing Oracle Database

By Srinivas Kandru on Sep 21, 2001
This source code shows you how to connect to an oracle database and do operations such as select, insert, update and delete.
  • 0
  • 0
  • 24311
Download Files:
 

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);
}
}
}
}

COMMENT USING