For demonstration purposes, I am here using a sample table called Customers, I then created a sequence and two before insert/update triggers to populate Customer_Id, Create_Date and Last Date fields. I tend to use triggers to upsert the common attributes of the table, such as create_date or last_update column. Some people say using triggers maybe not as efficient as using SQL statement, this is probably true. But if the table doesn’t contain huge amount of data, it shouldn’t make much difference, I may post a performance comparison later. The gain of using triggers is also quite obvious, it simplifies the update/insert SQL and keeps your code clean in procedures.
create table customers
(
customer_id integer,
customer_name varchar2(30),
address varchar2(100),
create_date date,
last_update date,
constraint customer_pk primary key (customer_id)
)
/
create sequence seq_cust_id
start with 1
increment by 1
nomaxvalue
nocycle
cache 24
/
create trigger bi_customers
before insert on customers
for each row
begin
select seq_cust_id.nextval into :new.customer_id from dual;
:new.create_date := sysdate;
:new.last_update := sysdate;
end;
/
create trigger bu_customers
before update on customers
for each row
begin
:new.last_update := sysdate;
end;
/
insert into customers (customer_name, address) values ('abc', 'London');
insert into customers (customer_name, address) values ('bcd', 'Birmingham');
insert into customers (customer_name, address) values ('cde', 'Manchester');
insert into customers (customer_name, address) values ('def', 'Leeds');
insert into customers (customer_name, address) values ('efg', 'Cambridge');
insert into customers (customer_name, address) values ('fgh', 'Oxford');
The next step is to create package for CRUD operations. (Create, Retrieve, Update and Delete).
create or replace package crud_customers as
type t_cursor is ref cursor;
-- Create a new customer
procedure create_customer(p_cust_name varchar2, p_address varchar2);
-- Retrieve data
procedure retrieve_customers(p_cust_name in varchar2,
cur_customer out t_cursor);
-- Update operation
procedure update_customer(p_cust_id number,
p_cust_name varchar2,
p_address varchar2);
-- Delete operation
procedure delete_customer(p_cust_id in number);
end crud_customers;
/
create or replace package body crud_customers as
procedure create_customer(p_cust_name varchar2, p_address varchar2) as
begin
insert into customers
(customer_name, address)
values
(p_cust_name, p_address);
commit;
exception
when others then
rollback;
raise;
end create_customer;
procedure retrieve_customers(p_cust_name in varchar2,
cur_customer out t_cursor) as
begin
open cur_customer for
select customer_id, customer_name, address, create_date, last_update
from customers
where '-1' = nvl(p_cust_name, '-1')
or customer_name like '%' || p_cust_name || '%';
end retrieve_customers;
procedure update_customer(p_cust_id number,
p_cust_name varchar2,
p_address varchar2) as
begin
update customers
set customer_name = p_cust_name, address = p_address
where customers.customer_id = p_cust_id;
commit;
exception
when others then
rollback;
raise;
end update_customer;
procedure delete_customer(p_cust_id in number) as
begin
delete from customers where customer_id = p_cust_id;
commit;
end delete_customer;
end crud_customers;
/
Test the ref cursor output in the package:
Now we have completed all works in Oracle, it’s time to move to visual studio. Inside visual studio I created a project and named it as AccessOracleDemo.
Step 1: The first thing we need to do is to add OracleClient reference to references list. Note I have also added System.Configuration reference into the list, in this demo it used to store the database connection string.
Step 2: Set up the configuration file: app.config. Right click on the project name and select the properties. In setting tab, create a new connection string.
Once you saved the change, visual studio generates an app.config file for us. The content would be something like:
connectionString="Data Source=demo;Persist Security Info=True;User ID=scott;Password=xxxxxx;Unicode=True"
providerName="System.Data.OracleClient" />
There are various ways you can and you should encrypt your connection string here. But in this demo, I’ll leave it as it is.
Step 3: Create a class which read the connection string from app.config file and return it.
using System.Data.OracleClient;
using System.Configuration;
namespace AccessOracleDemo
{
///
/// Return the database connection string
///
class DBCONN
{
public static OracleConnection GetConnection()
{
// Get the current configuration file.
string connectionString =
ConfigurationManager.ConnectionStrings[1].ConnectionString.ToString();
return new OracleConnection(connectionString);
}
}
}
Step 4: Create a data access class. In this class, I created 4 command objects for CRUD operation.
using System;
using System.Data;
using System.Data.OracleClient;
namespace AccessOracleDemo
{
class DALCustomers
{
private OracleConnection conn;
private OracleCommand selectCommand = null;
private OracleCommand updateCommand = null;
private OracleCommand insertCommand = null;
private OracleCommand deleteCommand = null;
private OracleDataAdapter da;
public DataTable select_Customers(DataSet dsdata, string strCustomerName)
{
conn = DBCONN.GetConnection();
da = new OracleDataAdapter();
selectCommand = new OracleCommand();
selectCommand.CommandText = "crud_customers.retrieve_customers";
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Connection = conn;
selectCommand.Parameters.Add("p_cust_name", OracleType.VarChar, 30);
selectCommand.Parameters.Add("cur_customer", OracleType.Cursor).Direction = ParameterDirection.Output;
selectCommand.Parameters["p_cust_name"].Value = strCustomerName;
da = new OracleDataAdapter(selectCommand);
try
{
conn.Open();
if (!dsdata.Tables.Contains("customers"))
dsdata.Tables.Add("customers");
da.Fill(dsdata, "customers");
}
catch (OracleException oex)
{
throw new Exception(oex.Message);
}
finally
{
conn.Close();
}
return dsdata.Tables["customers"];
}
public int update_Customer(DataSet dsdata)
{
int rowAffected = 0;
da = new OracleDataAdapter();
conn = DBCONN.GetConnection();
updateCommand = new OracleCommand();
updateCommand.CommandText = "crud_customers.update_customer";
updateCommand.CommandType = CommandType.StoredProcedure;
updateCommand.Connection = conn;
updateCommand.Parameters.Add("p_cust_id", OracleType.Number, 4, "customer_id");
updateCommand.Parameters.Add("p_cust_name", OracleType.VarChar, 10, "customer_name");
updateCommand.Parameters.Add("p_address", OracleType.VarChar, 30, "address");
da.UpdateCommand = updateCommand;
try
{
conn.Open();
rowAffected = da.Update(dsdata.Tables["customers"].Select(null, null, DataViewRowState.ModifiedCurrent));
}
catch (Exception oex)
{
throw new Exception(oex.Message);
}
finally
{
conn.Close();
}
return rowAffected;
}
public int insert_Customer(DataSet dsdata)
{
int rowAffected = 0;
conn = DBCONN.GetConnection();
da = new OracleDataAdapter();
insertCommand = new OracleCommand();
insertCommand.CommandText = "crud_customers.create_customer";
insertCommand.CommandType = CommandType.StoredProcedure;
insertCommand.Connection = conn;
insertCommand.Parameters.Add("p_cust_name", OracleType.VarChar, 10, "customer_name");
insertCommand.Parameters.Add("p_address", OracleType.VarChar, 30, "address");
da.InsertCommand = insertCommand;
try
{
conn.Open();
rowAffected = da.Update(dsdata.Tables["customers"].Select(null, null, DataViewRowState.Added));
}
catch (Exception oex)
{
throw new Exception(oex.Message);
}
finally
{
conn.Close();
}
return rowAffected;
}
public void delete_Customer(int intCustomerID)
{
conn = DBCONN.GetConnection();
deleteCommand = new OracleCommand();
deleteCommand.CommandText = "crud_customers.delete_customer";
deleteCommand.CommandType = CommandType.StoredProcedure;
deleteCommand.Connection = conn;
deleteCommand.Parameters.Add("p_cust_id", OracleType.Number, 4);
deleteCommand.Parameters["p_cust_id"].Value = intCustomerID;
try
{
conn.Open();
deleteCommand.ExecuteNonQuery();
}
catch (OracleException oex)
{
throw new Exception(oex.Message);
}
finally
{
conn.Close();
}
}
}
}
Step 5: Drag one text box, three buttons and one DataGridView onto Windows form designer and give them a meaningful name.
using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
namespace AccessOracleDemo
{
public partial class frmDemo : Form
{
public frmDemo()
{
InitializeComponent();
}
DALCustomers DBCustomers;
DataSet dsCustomer;
private void btnSearch_Click(object sender, EventArgs e)
{
PopulateDGV();
btnSave.Enabled = true;
btnDelete.Enabled = true;
}
private void btnSave_Click(object sender, EventArgs e)
{
ListrowAffected = new List ();
rowAffected = UpsertCustomers(dsCustomer);
if (rowAffected[0] == 0 && rowAffected[1] == 0)
MessageBox.Show("No change made!");
else
{
MessageBox.Show("You have successfully updated \n" + rowAffected[0].ToString() + " records; " +
"Inserted " + rowAffected[1].ToString() + " records;");
}
PopulateDGV();
}
private void btnDelete_Click(object sender, EventArgs e)
{
DeleteCustomer(dgvCustomers);
PopulateDGV();
}
private DataSet PopulateCustomers()
{
DBCustomers = new DALCustomers();
dsCustomer = new DataSet();
DBCustomers.select_Customers(dsCustomer, textSearch.Text.ToString());
return dsCustomer;
}
private void PopulateDGV()
{
dgvCustomers.DataSource = PopulateCustomers();
dgvCustomers.DataMember = "customers";
}
private ListUpsertCustomers(DataSet ds)
{
ListrowAffected = new List ();
try
{
DBCustomers = new DALCustomers();
// Update table customers
if (ds.HasChanges())
rowAffected.Add(DBCustomers.update_Customer(ds));
else
rowAffected.Add(0);
// Insert table customers
if (ds.HasChanges())
rowAffected.Add(DBCustomers.insert_Customer(ds));
else
rowAffected.Add(0);
}
catch (Exception ex)
{
throw ex;
}
return rowAffected;
}
private void DeleteCustomer(DataGridView dgv)
{
try
{
DialogResult delResult;
delResult = MessageBox.Show("Do you really want to delete the item?", "Delete Confirmation",
MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (delResult == DialogResult.OK)
{
DBCustomers = new DALCustomers();
DBCustomers.delete_Customer(int.Parse(dgv.CurrentRow.Cells["customer_id"].Value.ToString()));
}
}
catch (Exception ex)
{
throw ex;
}
}
}
}
Step 6: We can now run and test the application, some screen shots as below:
Load data from Oracle:
Update and insert records into the table:
Delete a record:
Displaying data in tabular format is one of the common requirements in Windows-based applications. The DataGridView control, new in Windows Forms 2.0, is so versatile and powerful that beginners can easily be overwhelmed by the options. We can easily lock/unlock the rows/columns/cells, change the front/background color, re-format the columns, add some calculations…… without writing a single line of code. We can also change the column display type as dropdown or checkbox or datepicker or even cascading dropdown with only few lines of code. I’ll demonstrate some of them in my next posts. Feel free to let me know should you have any questions, problems or comments.
