如何在Excel中的一个数据表和SQL 2008中的一个表之间进行查询? [英] How to query between one datatable from excel and one table in SQL 2008?

问题描述

我在SQL Server 2008中有一个名为Person的表，如下所示:
ID名称城市
01汉娜·穆斯·曼海姆
02FrédériqueCiteaux斯特拉斯堡
03马丁·索默·马德里
04劳伦斯·勒比汉·马赛
05伊丽莎白·林肯(Elizabeth Lincoln Tsawassen)
06 Victoria Ashworth伦敦
07 Patricio Simpson布宜诺斯艾利斯
08 Francisco Chang墨西哥D.F.
09杨望伯恩
10佩德罗·阿方索·圣保罗

我有一个来自Excel的数据表，名为dataTable_excel，如下所示:
ID名称城市
01帕特里西奥·辛普森(Patricio Simpson Torino)
02 Francisco Chang里斯本
03杨王考斯
04佩德罗·阿方索·勃兰登堡
05伊丽莎白·布朗伦敦
06斯文·奥特利布·亚琛
07 Janine Labrune Nantes
08安德文·伦敦

我想从dataTable_Excel中获取一个dataTable，其中包含表Person中具有相同ID和名称的记录，例如:
ID名称城市
01帕特里西奥·辛普森(Patricio Simpson Torino)
02 Francisco Chang里斯本
03杨王考斯
04佩德罗·阿方索·勃兰登堡

我还想从dataTable_Excel获取一个dataTable，该数据表不包含表Person中的记录，例如:
ID名称城市
01伊丽莎白·布朗伦敦
02 Sven Ottlieb Aachen
03 Janine Labrune Nantes
04安德文·伦敦

我曾在VS2008中尝试过如下代码:"for(int i = 0; i< dataTable_Excel.Rows.Count; i ++){string sql = string.Format("exec spCheckCustomer``{0}''，''' '{1}''，"，dataTable_Excel.Rows [i] [" ID]); ......}''.但是，如果记录很大，则代码运行速度会很慢.

I have a table, named Person, in SQL Server 2008 which looks like this:
ID Name City
01 Hanna Moos Mannheim
02 Frédérique Citeaux Strasbourg
03 Martín Sommer Madrid
04 Laurence Lebihan Marseille
05 Elizabeth Lincoln Tsawassen
06 Victoria Ashworth London
07 Patricio Simpson Buenos Aires
08 Francisco Chang México D.F.
09 Yang Wang Bern
10 Pedro Afonso Sao Paulo

I have a datatable, named dataTable_excel, from an excel which looks like this:
ID Name City
01 Patricio Simpson Torino
02 Francisco Chang Lisboa
03 Yang Wang Cowes
04 Pedro Afonso Brandenburg
05 Elizabeth Brown London
06 Sven Ottlieb Aachen
07 Janine Labrune Nantes
08 Ann Devon London

I want to get one dataTable from dataTable_Excel, which contains the records with both the same ID and name in table Person, for example:
ID Name City
01 Patricio Simpson Torino
02 Francisco Chang Lisboa
03 Yang Wang Cowes
04 Pedro Afonso Brandenburg

I also want to get one dataTable from dataTable_Excel, which does not contains the records in table Person, for example:
ID Name City
01 Elizabeth Brown London
02 Sven Ottlieb Aachen
03 Janine Labrune Nantes
04 Ann Devon London

I have tried in VS2008 the code that looks like ''for(int i = 0; i < dataTable_Excel.Rows.Count; i++){string sql = string.Format("exec spCheckCustomer ''{0}'',''{1}''", "", dataTable_Excel.Rows[i]["ID"]);......}''. But if the records is huge, the code runs very slow. Could there was any fast code in C# or LINQ?

推荐答案

LINQ不能神奇地在两个不同的数据存储之间建立桥梁.它不会使您的代码更快.如果您无法将Excel数据移入SQL Server，那么您所做的就和获得的一样好.如果您可以将数据移到SQL Server中，它将更快地运行很多.

LINQ does not magically create a bridge between two different data stores. It won''t make your code faster. If you can''t move your Excel data in to SQL Server, what you''re doing is about as good as it gets. If you can move the data in to SQL Server, it will run a LOT faster.

您在此处提供的代码是在excel中读取单元格的较慢方法:

The code you are giving here is the slowes possible way of reading cells in excel:

for(int i = 0; i < dataTable_Excel.Rows.Count; i++)
{
string sql = string.Format("exec spCheckCustomer '{0}','{1}'", "", dataTable_Excel.Rows[i]["ID"]);
......
}

您应该执行以下操作:

You should do something like:

Dim w As Excel.Workbook = excel.Workbooks.Open(filename)
  Dim sheet As Excel.Worksheet = w.Sheets(1)
  Dim usedRange As Excel.Range = sheet.UsedRange

  Dim darray(,) As Object
  darray = CType(usedRange.Value, Object(,))

是的，我知道它在VB中，但是无论如何您都应该使用它.UsedRange并将其存储在数组中.

Yes, I know it is in VB, but you should in any rate use sheet.UsedRange and store it in a array.

这篇关于如何在Excel中的一个数据表和SQL 2008中的一个表之间进行查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！