Accessing Excel Databases using ADO.NET

Accessing Excel Databases using ADO.NET

This example will show you how to connect with Excel databases. To test this sample, if you don't have an Excel database, you can export data from your Northwind database. As you can see from figure 11-33, You can export the Employees table from Microsoft Access by right-clicking on the table and selecting the Export option or by selecting File > Export. { // Connection string for ODBC Excel Driver string ConnectionString = @"Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\Employees.xls"; OdbcConnection conn = new OdbcConnection(ConnectionString); // Table in Excel can be thought of as sheets and are queried as shown string sql = "Select EmployeeID, FirstName, LastName FROM Employees"; conn.Open(); OdbcDataAdapter da = new OdbcDataAdapter(sql, conn); DataSet ds = new DataSet(); da.Fill(ds, "Employees"); dataGrid1.DataSource = ds.DefaultViewManager; listBox1.DataSource = ds.DefaultViewManager; listBox1.DisplayMember = "Employees.FirstName"; } The output of listing 11-4 looks like figure 11-36. Figure 11-36: Output of listing 11-4