Blue Theme Orange Theme Green Theme Red Theme
 
Home | Forums | Videos | Advertise | Certifications | Downloads | Blogs | Interviews | Jobs | Beginners | Training
 | Consulting  
Submit an Article Submit a Blog 
 Jump to
Skip Navigation Links
Search :       Advanced Search �
Home

Author Rank :
Page Views :
Downloads : 0
Rating :
 Rate it
Level :
Become a Sponsor
Tags


This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".

The next 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.

Figure-11.33.gif

Figure 11-33: Exporting the Employees table as an Excel spreadsheet

When you export, make sure you have selected the Microsoft Excel 97-2000 (*.xls) option in the Save as type drop-down list (see Figure 11-34).

Figure-11.34.gif

Figure 11-34: Saving the Employees table as an Excel spreadsheet

Now if you open Employees.xls, it looks like figure 11-35.

Figure-11.35.gif

Figure 11-35: Employees.xls data view

Again you can access the Excel database either using an ODBC DSN or by passing the database name directly in the connection string. In this sample, you're passing the database name directly:

            string ConnnectionString = @"Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\Employees.xls";

After that, the code should be familiar to you. It's the same steps as creating a data adapter, selecting some field of the table, filling a dataset from data adapter, and binding data with the data-bound controls. Listing 11-4 shows the full source code.

Listing 11-4: Accessing Employees.xls using the ODBC data provider

        
private void Form1_Load(object sender, System.EventArgs e)
        {

            // 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.gif

Figure 11-36: Output of listing 11-4

Conclusion

Hope this article would have helped you in understanding Accessing Excel Databases using ADO.NET. See my other articles on the website on ADO.NET.

adobook.jpg
This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.

 [Top] Rate this article
 
 About the author
 
Author
 Post a Feedback, Comment, or Question about this article
Subject:
Comment:
 Comments

 � 2020  contents copyright of their authors. Rest everything copyright Mindcracker. All rights reserved.