KingDumbNo
asked on
What is the best approach to run two querries on one Connection object (ODBC)?
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
while (dr.Read())
{
if (prevSSN != dr["BRACCT"].ToString())
//do something with this new SSN
//continue writing loan records for the same SSN
}