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:
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-2007 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.
Eg:
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.
Eg:
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();
rno++;
}
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
