?
Solved

What is the best approach to run two querries on one Connection object (ODBC)?

Posted on 2004-09-07
2
Medium Priority
?
210 Views
Last Modified: 2010-04-15
I have to work with the ODBC objects, running against a Cache 4.?? database.

I have successfully created an OdbcConnection object, OdbcCommand object, and an OdbcDataReader object (named drBorr) and have looped thru the whole result of the query with a "while (drBorr.Read())" loop, writing some of the data to a flat output file.

Now that the above "test" is done, what I want to do is run another query for each drBorr record against another table on the same database.  I could have written a larger query with a Join on the querried tables but I thought this approach was cleaner:
while (drBorr.Read())
{
  //Process this borrower record, writing appropriate values to the output flat file.
 
  ...create another OdbcConnection object?
  ...create another OdbcCommand object?
  ...create the drLoan OdbcDataReader object, but how exactly?
  while (drLoan.Read())
  {
    //Process this loan record, writing appropriate values to the output flat file.
  }
}

Please comment on the lines beginning with ... within the code.  The "borrower" query results in about 200,000 records with around 15 fields.  I presume this is not too large for a DataSet object, but I do not currently know how to create the DataSet object.

If you honestly think the large query is a better approach then I can go that route.  I just wanted to avoid the whole "does this loan belong to the same borrower as the previous loan...blah blah blah" which the separate querries via the while loops inherently do in a much cleaner fashion.

Thanks!
0
Comment
Question by:KingDumbNo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 1

Accepted Solution

by:
bowser17 earned 2000 total points
ID: 12000453
I think rather than doing 200,000 queries you need to do an join, or your performance will be garbage.  If you are read only and forward, then you dont need a dataset.
0
 
LVL 6

Author Comment

by:KingDumbNo
ID: 12020970
I figured one large chunk (with built in buffering and such) was better than 200,000 queries but I wanted to be sure.  C# has made reading a "recordset" much easier in that DataReaderVariable.Read() will just keep returning false at the end.  This made my prevSSN != currSSN logic much easier.  Here's a small code snippet, if anyone cares:

while (dr.Read())
{
    if (prevSSN != dr["BRACCT"].ToString())
        //do something with this new SSN

    //continue writing loan records for the same SSN
}
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question