Objective:
To develop a windows application using c#.net to insert, search and update
records in M.S.Excel-2007 file using OleDb Connection.
Design:
![oledb.gif]()
Design the form as above with a DataGridView, 3 Labels, 3 TextBoxes and 9
buttons.
Introduction:
As we want to use OleDb Connection include the namespace:
'using System.Data.OleDb
'
For accesing records from M.S.Excel-2003 file we use 'Jet' driver.
But, for accesing records from M.S.Excel-2007 file we use 'Ace' driver.
In this application, we will search a record by taking input from the InputBox.
For this, we have to add reference to Microsoft.VisualBasic.
Adding Reference to Microsoft.VisualBasic:
Goto Project Menu ->Add Reference -> select 'Microsoft.VisualBasic' from .NET
tab.
Inorder to use this we have to include the namespace:
'using Microsoft.VisualBasic'
Creating a primary key in the dataTable:
In this app. we use Find() method to search a record, which requires details of
primarykey column. For database tables this is provided using statement:
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
But as we don't have any primarykey column in M.S.Excel sheet, we have to create
a primary key column in datatable.
Example:
ds.Tables[0].Constraints.Add("pk_sno", ds.Tables[0].Columns[0], true);
Pointing to current record in dataTable:
After searching a record, we have to get index of that record so that we can
navigate the next and previous records in correct order.
Example:
rno= ds.Tables[0].Rows.IndexOf(drow);
-------------
Code:
using
System;
using
System.Data;
using
System.Data.OleDb;
using
System.Windows.Forms;
using
Microsoft.VisualBasic;
namespace
xloledb07
{
public partial
class Form1 :
Form
{
public Form1()
{
InitializeComponent();
}
OleDbConnection con;
OleDbCommand cmd;
OleDbDataAdapter da;
DataSet ds;
int rno = 0;
private void
Form1_Load(object sender,
EventArgs e)
{
con = new
OleDbConnection(@"provider=microsoft.ace.oledb.12.0;data
source=e:\prash\xldb.xlsx;extended properties=""excel 12.0;hdr=yes;"" ");
//xldb.xlsx ->MS.Excel-2007 file, hdr=yes->
1st row is treated as header
loaddata();
showdata();
}
void loaddata()
{
da = new
OleDbDataAdapter("select * from [student$]",
con);
//student-> sheet name in xldb.xlsx file,
which should be specified as [student$]
ds = new
DataSet();
da.Fill(ds, "student");
//creating primary key in Tables["student"]
of dataset(for using Find() method)
ds.Tables[0].Constraints.Add("pk_sno",
ds.Tables[0].Columns[0], true);
dataGridView1.DataSource = ds.Tables[0];
}
void showdata()
{
textBox1.Text = ds.Tables[0].Rows[rno][0].ToString();
textBox2.Text = ds.Tables[0].Rows[rno][1].ToString();
textBox3.Text = ds.Tables[0].Rows[rno][2].ToString();
}
private void
btnInsert_Click(object sender,
EventArgs e)
{
cmd = new
OleDbCommand("insert
into [student$] values(" + textBox1.Text +
",' " + textBox2.Text + " ',' " +
textBox3.Text + " ')", con);
con.Open();
int n = cmd.ExecuteNonQuery();
con.Close();
if (n > 0)
{
MessageBox.Show("record
inserted");
loaddata();
}
else
MessageBox.Show("insertion
failed");
}
private void
btnSearch_Click(object sender,
EventArgs e)
{
int n =
Convert.ToInt32(Interaction.InputBox("Enter
sno:", "Search",
"20", 200, 200));
DataRow drow = ds.Tables[0].Rows.Find(n);
if (drow !=
null)
{
textBox1.Text = drow[0].ToString();
textBox2.Text = drow[1].ToString();
textBox3.Text = drow[2].ToString();
}
else
MessageBox.Show("Record
not found");
}
private void
btnUpdate_Click(object sender,
EventArgs e)
{
cmd = new
OleDbCommand("
update [student$] set sname='" + textBox2.Text +
"',course='" + textBox3.Text +
"' where sno="+ textBox1.Text , con);
con.Open();
int n = cmd.ExecuteNonQuery();
con.Close();
if (n > 0)
{
MessageBox.Show("Record
Updated");
loaddata();
}
else
MessageBox.Show("Update
failed");
}
private void
btnFirst_Click(object sender,
EventArgs e)
{
if (ds.Tables[0].Rows.Count > 0)
{
rno = 0;
showdata();
MessageBox.Show("First
Record");
}
else
MessageBox.Show("no
records");
}
private void
btnPrevious_Click(object sender,
EventArgs e)
{
if (ds.Tables[0].Rows.Count > 0)
{
if (rno > 0)
{
rno--;
showdata();
}
else
MessageBox.Show("First
Record");
}
else
MessageBox.Show("no
records");
}
private void
btnNext_Click(object sender,
EventArgs e)
{
if (ds.Tables[0].Rows.Count > 0)
{
if (rno <
ds.Tables[0].Rows.Count - 1)
{
rno++;
showdata();
}
else
MessageBox.Show("Last
Record");
}
else
MessageBox.Show("no
records");
}
private void
btnLast_Click(object sender,
EventArgs e)
{
if (ds.Tables[0].Rows.Count > 0)
{
rno = ds.Tables[0].Rows.Count - 1;
showdata();
MessageBox.Show("Last
Record");
}
else
MessageBox.Show("no
records");
}
private void
btnClear_Click(object sender,
EventArgs e)
{
textBox1.Text = textBox2.Text = textBox3.Text =
"";
}
private void
btnExit_Click(object sender,
EventArgs e)
{
this.Close();
}
}
}
Note:
'xldb.xlsx' file is provided in xloledb07.zip file along with the source code.