using datasets vs. datareaders

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using a datareader to cycle through a list of records one at a time. I
frequently get connection timeouts and am wondering what I am doing wrong.

In more detail, I retrieve an excel spreadsheet provided by the user into a
datatable (dtExcel), then go through each record to see if a matching record
exists in our sql database. Basically, if there is a match, I mark the
excel data "Matched", if not, mark it "unmatched". GetDataReader is a
separate data layer function. I assume the problem is the GetDataReader
doesn't close the connection, but if I close the connection in the
datalayer, it doesn't return the data reader. So I close the data reader in
the presentation layer, but am still getting time outs when there is a large
excel spreadsheet. Do I have to use a dataset, because with a dataset I can
close the connection??

Psuedo-code:

Public Sub CheckForMatchingRecords
for i=0 to dt.Rows.Count-1

qry = "Select id from tbl where exclid = '" &
dt.rows(i).Item(3).tostring & "'"
Dim dr as sqldatareader
dr = GetDataReader(connString, qry)
if not drOPTransactions.Read
dt.Rows(i).BeginEdit
dt.Rows(i).Item(icol-3) = "NO MATCH"
dt.Rows(i).endedit
else
dt.Rows(i).BeginEdit
dt.Rows(i).Item(icol-3) = "MATCH"
dt.Rows(i).endedit
end if
dr.close
end sub

Public Function GetDataReader(ByVal connString as String, ByVal qry as
String) as SQLDataReader

Dim cn as SqlConnection = New SqlConnection(connString)
Dim dr as SQLDataReader
Dim cmd As New SqlCommand(qry, cn)
Try
cn.Open()
dr = cmd.ExecuteReader
Return dr
Catch ex As SqlClient.SqlException
....
End Try

End Function
 
In your case, I would consider creating a custom business objects, a custom
business object collection, and then using the IDataReader to populate the
objects/collection.

Then have a read-only property that does what you want.

Your datalayer has to return a (non closed) datareader, but you really want
to use it as fast as you possibly can, and then close it.


Look here
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry
download the code.

and find this code:

CustomerController (class)

private List<BusinessObjects.Customer>
SerializeCustomers(IDataReader dataReader, bool deep)


Note, I use this type of code now instead: (which is not in the demo code)

public class CustomerCollection : List<BusinessObjects.Customer>
{
//that's it
}




CustomerController (class)
private CustomerCollection SerializeCustomers(IDataReader
dataReader, bool deep)


Basically, define the CustomerCollection class, and use it instead of a
bunch of "List<BusinessObjects.Customer> " all over the place.

............

I know you're thinking "This is alot of work". Sometimes you gotta chose.
Do you want the best performance? Or do you want easy?
If you do some time tests.......
Strong DataSet (with constraints)
Strong DataSet (no constraints)
DataReader, but with embedded logic (your approach)
Custom Objects via an IDataReader.

I think you'll be surprised. Find the 1.1 version of the URL I list above
(same blog site, different entry)....and I have some time tests in it.


Good luck.
 
Back
Top