Testing DB2 OLEDB Provider using Microsoft Excel
Hi Nancy, Delete Operation is not possible using oledb. We can just insert, update the records in excel. The Code is to read excel file. Private DataTable ReadExcelFile (string sheetName, string path) using (OleDbConnection conn = new OleDbConnection ) DataTable dt = new DataTable ; string ImportFileName = path; string fileExtension = Path.GetExtension (ImportFileName); if (fileExtension '.xls') conn.ConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + ImportFileName +. Creating a new Excel workbook and worksheet with OLE DB, SQL and C-Sharp The following C# example tries to demonstrate how it is possible to create a new Excel workbook and worksheet with OLE DB. Cannot initialize the data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0' for linked server '(null)'. So close the file and try the query again. OLE DB Driver Not Installed The OPENROWSET function uses OLE DB, so it needs a driver for your data source, in this case for Excel. Before start Reading/Writing from/in Excel file, we need to connect to OLEDB using connection string, here OLEDB will act as Bridge between your program and EXCEL. Rows and columns of Excel sheet can be directly imported to data-set using OLEDB, no need to open Excel file using INTROP EXCEL object. Let's start with the code.
It is well known that from DB2 control center, one can test connectivity using configuration assistant for various providers like OLEDB , CLI , JDBC , ADO .
But configuration assistant can’t fetch data using oledb provider from DB2 table unless one has some custom application to fetch data using specified providers.
Microsoft excel provides another way and such readymade application to test and fetch data from DB2 table using OLEDB provider.
For windows DB2 32 bit installation, the dll name for DB2 oledb provider is ibmdadb2.dll. For windows 64 bit DB2 installation, oledb provider comes with both 64 bit ibmdadb264.dll and 32 bit ibmdadb2.dll found in sqllib/bin directory.
Though example is provided in excel 2003, it can be used for other excel version like excel 2007 and excel 2010.
and any database like sample can be used for testing. In this example we used our custom database created for testing.
Following steps gives detailed overview of testing DB2 oledb provider using excel
1a - Launch MS Excel 2003.
1b - Go to Data->Import External Data-> Import Data
1c- Click and open “Connect to new data source.odc”
1d - This will launch Data Connection Wizard. Click and select Other/advanced and click next
2 . It will open datalink properties dialog box. Select and double click the IBM OLEDB provider which corresponds to OLEDB provider Driver copy.
3 - This will display “All” tab properties corresponding to OLEDB provider
4. There are two options to test connectivity to database. One is with DSN defined in
db2dsdriver.cfg and another one is without DSN .
4A - Option A- With DSN defined in db2dsdriver.cfg
Oledb Driver For Excel 2016
Select the data source and give the DSN name as defined in db2dsdriver.cfg
For example data source “TDB2” is used in this example.
It will open “Connect to DB2 database” dialog box.
Provide the User ID and Password for the Database to be connected.
4B- Option B- if there is no DSN defined in Db2dsdriver.cfg .
Go to “All” tab and click on extended properties, provide the information like
Database, Hostname, Protocol and port for the database server.
It will open “Connect to DB2 database” dialog box
Provide the UserID and password for the database and click OK.
5. Select the table from which data need to be imported.
Click Next button to continue.
6. Save the Data connection file so that it can be used next time when importing data from same Data source and table. Click Finish button.
7. Select the start column of the worksheet to import the table data and click OK.
Oledb Excel Write C#
This will put the data from the database table in the selected worksheet column.
Oledb Excel 255
8. After clicking OK button ,Microsoft excel displays the data from the table EMP which is selected in step 5.
In this way not only one can test OLEDB provider but can also verify the data coming from DB2 database table.
Authors
1) Prashant N Kulkarni
C# Oledb Excel
2) Arun Kumar purnaiah