Ms Excel is a file system which stores data in the form of rows and columns same as a database table. An Excel document is referred as Work Book that contains Work Sheets in it, work books are considered as databases and work sheets are considered as tables.First row of work sheet can store column names.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;
namespace WinForms7
{
public partial class Form6 : Form
{
public Form6()
{
InitializeComponent();
}
OdbcConnection con; OdbcCommand cmd; OdbcDataReader dr; string Sqlstr;
private void Form6_Load(object sender, EventArgs e)
{
con = new OdbcConnection("Dsn=ExcelDsn; ReadOnly=0");
cmd = new OdbcCommand();
cmd.Connection = con;
con.Open();
LoadData();
label1.Text = dr.GetName(0); label2.Text = dr.GetName(1);
label3.Text = dr.GetName(2); label4.Text = dr.GetName(3);
}
private void LoadData()
{
cmd.CommandText = "Select * from[Student$]";
cmd.ExecuteReader();
ShowData();
}
private void ShowData()
{
if (dr.Read())
{
textBox1.Text = dr[0].ToString();
textBox2.Text = dr[1].ToString();
textBox3.Text = dr[2].ToString();
textBox4.Text = dr[3].ToString();
}
else
MessageBox.Show("No data exists");
}
private void button3_Click(object sender, EventArgs e)
{
ShowData();
}
private void button2_Click(object sender, EventArgs e)
{
textBox1.Text = textBox2.Text = textBox3.Text = textBox4.Text = " ";
textBox1.Focus();
}
private void ExecuteDML()
{
dr.Close();
cmd.CommandText = Sqlstr;
if (cmd.ExecuteNonQuery() > 0)
MessageBox.Show("Insert or Update operation was successful");
else
MessageBox.Show("Insert or Update operation failed");
LoadData();
}
private void button1_Click(object sender, EventArgs e)
{
Sqlstr = String.Format("Insert into [Stident$] values({0},'{1}' {2},{3})", textBox1.Text, textBox2.Text, textBox3.Text, textBox4.Text);
dr.Close();
ExecuteDML();
}
private void button5_Click(object sender, EventArgs e)
{
Sqlstr = String.Format("Update [Stident$] Set Sname='{0},Class={1}, Fees= {2} where Sno={3}", textBox2.Text, textBox2.Text, textBox3.Text, textBox4.Text);
dr.Close();
ExecuteDML();
}
}
}
