Oracle Connectivity from C# Windows Forms

Inserting Values from Front End

System.Data.OleDb namespace contains classes required for interacting with any RDBMS including Oracle.

OleDbConnection class is used for establishing a live session between front end and back end. In this example we are connecting to Oracle with scott as username, tiger as password and hostname is Shalvin.

using System.Data.OleDb;

OleDbConnection cnn;
OleDbCommand cmd;
private void Form1_Load(object sender, EventArgs e)
{
cnn = new OleDbConnection("provider=msdaora.1;user id=scott;password=tiger;Data Source=Cargomar");
cnn.Open();
}
private void btnInsert_Click(object sender, EventArgs e)
{
cmd = new OleDbCommand("insert into Categories values (5, 'Mouse', 'Computer Mouse')", cnn);
cmd.ExecuteNonQuery();
MessageBox.Show("Record Saved");
}

Filling TextBox based on Selection from ComboBox

A common programming task is obtaining the details of a record based on a selection. Here Initially I am filling a combo box with Category Names. Based on a selection from Combo Box the the details of the Category will be displayed on the text boxes.










using System.Data.OleDb;

OleDbConnection cnn;
OleDbDataAdapter da;
DataSet ds = new DataSet();

bool b = false;

OleDbCommand cmd;
OleDbDataReader dr;

private void Form1_Load(object sender, EventArgs e)
{
cnn = new OleDbConnection("Provider=msdaora;Data Source=Cargomar;user id=scott;password=tiger");

cnn.Open();
da = new OleDbDataAdapter("select * from Categories", cnn);
da.Fill(ds, "Cat");
cboCategoryName.DataSource = ds.Tables["Cat"];
cboCategoryName.DisplayMember = "CategoryName";
cboCategoryName.ValueMember = "CategoryId";
b = true;
}

private void cboCategoryName_SelectedIndexChanged(object sender, EventArgs e)
{
if (b)
{
cmd = new OleDbCommand("select * from Categories where categoryId = " + cboCategoryName.SelectedValue, cnn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
txtCategoryId.Text = dr["CategoryId"].ToString();
txtDescription.Text = dr["Description"].ToString();
}
}

Deleting Record
private void btnDelete_Click(object sender, EventArgs e)
{
cmd = new OleDbCommand("delete from Categories where CategoryId = " + cboCategoryName.SelectedValue, cnn);
cmd.ExecuteReader();
MessageBox.Show("Record Deleted");
}

Editing Record

private void btnEdit_Click(object sender, EventArgs e)
{
txtCategoryId.Enabled = false;
cmd = new OleDbCommand("Update Categories set Description = '" + txtDescription.Text + "' where CategoryId = " + cboCategoryName.SelectedValue, cnn);
cmd.ExecuteNonQuery();
MessageBox.Show("Record Edited successfully");
}

VB.Net

Imports System.Data.SqlClient

Dim cnn As SqlConnection
Dim da As SqlDataAdapter
Dim ds As New DataSet

Dim b As Boolean = False
Dim cmd As SqlCommand
Dim dr As SqlDataReader

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cnn = New SqlConnection("Integrated Security=sspi;Initial Catalog=Northwind")
cnn.Open()
da = New SqlDataAdapter("select * from Categories", cnn)
da.Fill(ds, "Cat")
cboCategories.DataSource = ds.Tables("Cat")
cboCategories.DisplayMember = "CategoryName"


cboCategories.ValueMember = "CategoryId"

b = True
End Sub

Private Sub cboCategories_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboCategories.SelectedIndexChanged
If b = True Then

Dim strSql As String
strSql = "select * from Categories where CategoryId = " + cboCategories.SelectedValue.ToString()
cmd = New SqlCommand(strSql, cnn)
dr = cmd.ExecuteReader
While (dr.Read())
txtCategoryId.Text = dr("CategoryId")
txtDescription.Text = dr("description")
End While
dr.Close()
End If

End Sub