Connect to Excel using ODBC in C#
Working with ODBC connections:
1)ODBC(open database connectivity) is a very old technology
2)ODBC concept was introduced in 1980s
3)OLEDB concept was introduced in 1995s
4)TDS concept was introduced in 2000s
5)ODBC connections are recommended especially with very old databases like foxpro, dbase, lotus 123, ms excel
6)ODBC supports to work with all types of databases
7)ODBC works with jelp of ODBC drivers , ODBC drivers are developed DLL files which are com components
8)for these DLL we need to create aliasname which is called as DSN(data source name)
9)in excel every sheet works like a table
Example on ODBC connection with EXCEL
Note:all the ODBC drivers(DLLs) are located at
Start->control panel->performance and maintainance->administrative tools->data sources(ODBC)
(or)
Start ->run->ODBCAD32
Step1:creat an excel sheet
Open excel software type as follows in sheet1, then save it with name c:\mydata.xlsx (2007) (or) c:\mydata.xls(2003)
A B c
Eno ename sal
101 raj 5000
102 varun 10000
103 teja 15000
Step2:creat a DSN
Open datasources(start->run->ODBCAD32)
Click on add button
Select ‘driver domicrosoft excel’->finish
Provide data source name=abc(need to be remembered)
Then click on “select workbook” button then choose c:\mydata.xls (or) c:\mydata.xlsx->ok->ok
Step3:
Write a program to get the data from excel
Open windows forms app project
Start->programs->Microsoft visual studio 2010->Microsoft Visual studio 2010->file menu->new->
project->select visual c# from installed templates->select windows forms application project
place a button and datagridview controls
using System.Data.Odbc;
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 WindowsFormsApplication27
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
OdbcConnection cn = new OdbcConnection("dsn=abc");
OdbcDataAdapter da = new OdbcDataAdapter("select * from [Sheet1$]", cn);
//[] and $ is required only with excel
DataSet ds = new DataSet();
da.Fill(ds, "d");
dataGridView1.DataSource = ds.Tables["d"];
}
}
}
