C# Typed DataSets: OleDBDataAdapter use the column names from the Typed DataSet and not ExcelSheet

Question

I'm using VS 2010 with C# for a Windows Form application.

I need to load my data from the Excel sheet onto a DataSet. I created the DataSet using the DataSet designer and I manually added the tables and columns (FirstTable, Column1, Column2). Since I access the columns a lot, the code would be a lot cleaner to have a typed dataset instead of using an untyped one.

When I used the OleDBDataAdapter and populated the DataTable using Fill on FirstTable, Column1 and Column2 were empty and there were two additional columns that were from the Excel sheet (ExcelCol1, ExcelCol2). So unless, I gave the same excel columns names to FirstTable (instead of Column1 and Column2 if I called it ExcelCol1 and ExcelCol2), it would not populate the DataColumns that I created via the designer.

Is there any way to tell the DataSet to ignore the columns coming from Excel and just populate the already defined DataColumns?

IF that is not possible can I somehow connect the Excel sheet via a DataConnection to create the layout of the DataTables? The only thing I'm not sure about is that the excel file is user defined, so the user browses to the excel file to populate the DataSets. BUT the columns for all these excel files will always be the same. Hence I want to preset the layout using a Typed DataSet.

nbz nbz · Accepted Answer · 2011-07-11T13:40:58

Ok, I think I figured out how to do that. I'm not sure if this is the cleanest method of doing it since I still have to hand-code it to assign the columns names i.e. there is no easy way to ignore the Excel column names but at least I am able to use typed datasets.

This link describes Table and Column mappings which is basically when you would like to use your own column names instead of the ones from Excel.

I would like to point out a few things, so I have given the following code.

Let's assume I have created a DataSet via the DataSet designer called MyDataSet.xsd. It has a table called FirstTable with columns Column1 and Column2. Then the following code can be used for the table mappings

MyDataSet myDS = new MyDataset();
//Some query commands using OleDB to get data from Excel
OleDbDataAdapter myAdapter = new OleDbDataAdapter(<OleDbCommand>);
DataTableMapping tableMap = myAdapter.TableMappings.Add("Table", myDS.FirstTable.TableName);
tableMap.ColumnMappings.Add("ExcelCol1", myDS.FirstTable.Column1.ColumnName);
tableMap.ColumnMappings.Add("ExcelCol2", myDS.FirstTable.Column2.ColumnName);
myAdapter.Fill(myDS);

I realised that when you read data from an Excel file, then in the DataTableMapping statement, the source name is always "Table" even though my sheetname/tablename is different on the Excel file.
To take advantage of the Typed Dataset, you can use commands like myDS.FirstTable.TableName and the same for the columns names instead of,

DataTableMapping tableMap = myAdapter.TableMappings.Add("Table", "FirstTable");

So if you change the table or column names in the DataSet you can use the refactoring utilities of VS.

I hope this helps. If there is a cleaner solution, I would appreciate any help but for now this fixed my problem.

C# Typed DataSets: OleDBDataAdapter use the column names from the Typed DataSet and not ExcelSheet

1 Answers