Marina said:
I don't find datareader development to be any more difficult then using a
dataset. I don't spend more time fixing problems with a datareader, then I
do with anything else. How do you figure that it takes more work to maintain
a datareader? And how exactly are datatables easier? As far as I am
concerned, they require equal amounts of work.
For some applications they do.
If you can make good use of the DataTables features, it can save you tons of
time.
Additionally, we are talking about the time spent on database access. If
you measure filling a datatable with 5000 rows, vs looping through a
datareader with 5000 rows, you will notice a big difference.
No you won't. Filling a datatable with 5000 rows is almost exactly the same
as reading them from a datareader. The DataAdapter uses a datareader to
fill the datatable, and the only difference is the memory you need to
allocate to hold the results. Fetching rows across the network is _very_
slow, and DotNet allocates memory _very_ quickly. Much more quickly than
previous languages, so the difference in elapsed time is minimal. (run the
test program at the end to prove it to yourself).
The performance differences are only that the datatable occupies memory, and
that your fetching is seperate from your application processing. After you
load the DataTable, you still have to go back and sort, filter iterate the
rows. Whereas with a datareader you can do it in one shot.
David
Module Module1
Sub Main()
Dim cn As New SqlClient.SqlConnection("Network Library=DBMSSOCN;Data
Source=ressptdb;Initial Catalog=northwind;User ID=usr;Password=pass")
cn.Open()
Dim i As Integer
Dim t As DateTime = Now
Console.WriteLine(String.Format("Begin {0:d}", t))
For i = 0 To 99
ReadDataReader(cn)
Next
Console.WriteLine(String.Format("DataReader elapsed {0:i}",
Now.Subtract(t)))
t = Now
For i = 0 To 99
LoadDataTable(cn)
Next
Console.WriteLine(String.Format("DataTable elapsed {0:i}",
Now.Subtract(t)))
Console.WriteLine(String.Format("End {0:d}", t))
End Sub
Public Sub LoadDataTable(ByVal cn As SqlClient.SqlConnection)
Dim cmd As New SqlClient.SqlCommand("select * from [order details]", cn)
Dim ds As New DataSet()
Dim da As New SqlClient.SqlDataAdapter(cmd)
da.Fill(ds)
cmd.Dispose()
Console.WriteLine(String.Format("DataAdapter filled {0} rows",
ds.Tables(0).Rows.Count))
End Sub
Public Sub ReadDataReader(ByVal cn As SqlClient.SqlConnection)
Dim cmd As New SqlClient.SqlCommand("select * from [order details]", cn)
Dim dr As SqlClient.SqlDataReader = cmd.ExecuteReader
Dim OrderId As Integer
Dim ProductID As Integer
Dim UnitPrice As Decimal
Dim Quantity As Integer
Dim Discount As Double
Dim i As Integer
Do While dr.Read
OrderId = dr("OrderId")
ProductID = dr("ProductID")
UnitPrice = dr("UnitPrice")
Quantity = dr("Quantity")
Discount = dr("Discount")
i += 1
Loop
dr.Close()
cmd.Dispose()
Console.WriteLine(String.Format("DataReader read {0} rows", i))
End Sub
End Module
