C＃键入的数据集：OleDBDataAdapter使用Typed DataSet而不是ExcelSheet中的列名 [英] C# Typed DataSets: OleDBDataAdapter use the column names from the Typed DataSet and not ExcelSheet

问题描述

我需要将Excel中的数据从Excel表加载到DataSet上。我使用DataSet设计器创建了DataSet，并手动添加了表和列（FirstTable，Column1，Column2）。由于我访问了很多列，所以使用类型化数据集而不是使用无类型数据集的代码会更加清晰。

当我使用OleDBDataAdapter并填充DataTable使用填充 FirstTable ， Column1 和 Column2 为空并且还有Excel表格（ExcelCol1，ExcelCol2）中的另外两列。所以除非我给了相同的excel列名称到 FirstTable （而不是 Column1 和 Column2 如果我调用它 ExcelCol1 和 ExcelCol2 ），它不会填充DataColumns我通过设计器创建。

有没有办法告诉DataSet忽略来自Excel的列，只是填充已经定义的DataColumn？

如果是不是可能我可以通过DataConnection连接Excel表以创建DataTables的布局？我唯一不确定的是Excel文件是用户定义的，所以用户浏览到excel文件来填充DataSet。但是所有这些excel文件的列将始终是相同的。因此，我想使用类型化数据集预设布局。

解决方案

好的，我想我知道如何做到这一点。我不知道这是否是最干净的方法，因为我仍然需要手动编码来分配列名称，即没有简单的方法忽略Excel列名称，但至少我可以使用类型化的数据集。

此链接描述了表和列映射，这基本上是当你想使用自己的列名，而不是Excel中的列名。

我想指出一个很少有东西，所以我给了以下代码。

我们假设我有一个通过DataSet设计器创建了一个名为 MyDataSet.xsd 的DataSet。它有一个名为 FirstTable 的列，列为 Column1 和 Column2 。然后可以使用以下代码进行表映射

  MyDataSet myDS = new MyDataset（）; 
 //使用OleDB从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）; 
  

1. 我意识到当您从Excel文件中读取数据时， DataTableMapping语句，即使Excel文件中的sheetname / tablename不同，源名始终为Table。



2. 要利用类型化数据集，可以使用像 myDS.FirstTable.TableName 这样的命令，列名称相同，而不是

   
   
   

   DataTableMapping tableMap = myAdapter.TableMappings.Add（Table，FirstTable）;

因此，如果您更改DataSet中的表或列名称，则可以使用VS的重构实用程序。

希望这有助于您。如果有一个更清洁的解决方案，我会感谢任何帮助，但现在这解决了我的问题。

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.

解决方案

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);

1. 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.

2. 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＃键入的数据集：OleDBDataAdapter使用Typed DataSet而不是ExcelSheet中的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！