How to query between one datatable from excel and one table in SQL 2008?

Question

0.00/5 (No votes)

See more:

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?

Posted 30-Jul-12 22:24pm

daiwuju

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Accepted Answer · 2012-07-30T22:25:00

Solution 1

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.

Posted 30-Jul-12 22:25pm

Christian Graus

Kenneth Haugland · Accepted Answer · 2012-07-30T22:39:00

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

C#

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:

VB

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(,))

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