I need readonly data- how about not the whole dataset, just a DataRowCollection or DataTable?

[Guest]

I need to have a call into a database which runs a bit of SQL- you know
"Select * from users where Username like %rick%" that sort of thing. I am
just going to go through the one set of rows that comes back- I don't need
all the attributes of a full DataSet which is, as microsoft puts it, an in
memory snapshot of a database.

Is there any reason NOT to just pass around a DataTable object or a
DataRowCollection object?

Specific example; I'll have a class full of methods, in my DAL class. The
second chunk is how to use the function. How does this look to you all?


Public Shared Function GetDataRowCollection(ByVal strSQL As String) As
DataRowCollection
Dim con As SqlConnection = New SqlConnection(DBAccess.SQL_CONN_STR)
Dim sqlDA As SqlDataAdapter
Dim resultDS As DataSet
Dim retRows As DataRowCollection 'This is what we will be handing
back.
Try
con.Open()
sqlDA = New SqlDataAdapter ' Create a new SQLDataAdapter object
resultDS = New DataSet ' Create a new DataSet object
sqlDA.SelectCommand = New SqlCommand(strSQL, con) ' Add a
SelectCommand object
sqlDA.SelectCommand.CommandType = CommandType.Text 'Specify the
Select Command type; Just some SQL
sqlDA.Fill(resultDS, "tmpTblName") ' Populate the DataSet with
the returned data. Make up a table name.
retRows = resultDS.Tables("tmpTblName").Rows ' Retrieve rows
from the DataSet. This should be a copy
Catch ex As Exception
Throw ex
Finally
sqlDA.Dispose()
con.Close()
End Try

Return retRows

End Function

Use it with this:
Dim oRows As DataRowCollection = DBAccess.GetDataRowCollection("select *
from orders")
Dim i As Integer
For i = 0 To oRows.Count - 1
Dim oFocus As New Focus
oRows(i).Item("FocusID")
oRows(i).Item("FocusDescription").ToString()
someCollection.Add(oRows)
Next

 

[Joe Agster]

I use standalone DataTables mostly cause they are most
similar to recordsets. I even wrote a common
BuildDataTable method in my DAL common library which
takes a IDbCommand (if working with SqlClient and OleDb)
and returns a DataTable.

DataSets are for 2 way communication with the database.
They're only necessary to relate DataTables for the
purpose of making changes and updating changes back.

DataReaders are kinda annoying cause they lock your
connection, I only use those if I'm returning 1 row of
data and I'm loading that row's data into an object.

-----Original Message-----
I need to have a call into a database which runs a bit of SQL- you know
"Select * from users where Username like %rick%" that sort of thing. I am
just going to go through the one set of rows that comes back- I don't need
all the attributes of a full DataSet which is, as microsoft puts it, an in
memory snapshot of a database.

Is there any reason NOT to just pass around a DataTable object or a
DataRowCollection object?

Specific example; I'll have a class full of methods, in my DAL class. The
second chunk is how to use the function. How does this look to you all?


Public Shared Function GetDataRowCollection(ByVal strSQL As String) As
DataRowCollection
Dim con As SqlConnection = New SqlConnection (DBAccess.SQL_CONN_STR)
Dim sqlDA As SqlDataAdapter
Dim resultDS As DataSet
Dim retRows As DataRowCollection 'This is what we will be handing
back.
Try
con.Open()
sqlDA = New SqlDataAdapter ' Create a new SQLDataAdapter object
resultDS = New DataSet ' Create a new DataSet object
sqlDA.SelectCommand = New SqlCommand(strSQL, con) ' Add a
SelectCommand object
sqlDA.SelectCommand.CommandType = CommandType.Text 'Specify the
Select Command type; Just some SQL
sqlDA.Fill(resultDS, "tmpTblName") ' Populate the DataSet with
the returned data. Make up a table name.
retRows = resultDS.Tables

("tmpTblName").Rows ' Retrieve rows

from the DataSet. This should be a copy
Catch ex As Exception
Throw ex
Finally
sqlDA.Dispose()
con.Close()
End Try

Return retRows

End Function

Use it with this:
Dim oRows As DataRowCollection =

DBAccess.GetDataRowCollection("select *