Select certain rows into dataset

[tailz]

Hi All,

This may belong in the database section, not sure. Anyway, I am selecting approx 1000 rows in a storedprocedure into a dataset using a dataadapter (sql Server 2k)

I know I can say "SELECT TOP 100 from etc" but what I wanna do is select records 100 to 200.

Is there a way to do this with sql/dataadapters? or are you gonna tell me to select the top 200 then advance 100?

I just didnt wanna waste me memory... unless anyone thinks selecting 1000 records will make hardly any difference...

thanks for any help

GaZ

[Lord_Rat]

To do it with SQL Server:

SELECT TOP 100 * FROM ( SELECT TOP 100 * FROM ( SELECT TOP 200 * FROM [TableName] ORDER BY IDENTITYCOL ASC ) ORDER BY IDENTITYCOL DESC ) ORDER BY IDENTITYCOL ASC

The best way, though, is to assign the results to a datatable, assign the datatable to a dataview and then use a filter on the dataview to see just the records you are looking for.

[Lord_Rat]

As for wasting memory:

If you are not using fields of the types ntext, text, image or binary, then don't worry about it. That's why I suggested the datatable/dataview combo. Using that set up, you don't have to requery if you decide to view a different subset of records.

[tailz]

Hi, thanks for replying!

The best way, though, is to assign the results to a datatable, assign the datatable to a dataview and then use a filter on the dataview to see just the records you are looking for.

Could you explain that a bit more please? No rush, I'm knackered and calling it a day now hehe

Point behind this, is that I don't wanna select 1000 records when only 100 are needed... seems a waste :] I'm not using any of the uber-big field types tho in this select so I might just stick with it.

Again, thanks for replying... didn't expect anything till 2morrow

GaZ

[Lord_Rat]

Consider this function that I use with my applications:

        Public Shared Function ReturnMultipleResults(ByVal sSQLText As String, Optional ByRef objTrace As Object = Nothing) As DataTable
            Dim objConn As New SqlClient.SqlConnection(g_ConnString)
            Dim objCmd As New SqlClient.SqlCommand(sSQLText, objConn)
            Dim objRS As SqlClient.SqlDataReader
            objConn.Open()
            Dim objDT As New DataTable("success")
            Try
                objRS = objCmd.ExecuteReader
            Catch ee As Exception
                objConn.Close()
                objDT.TableName = "nothing"
                If Not objTrace Is Nothing Then
                    objTrace.Warn("SQL Failed: " & ee.Message)
                    objTrace.warn(objCmd.CommandText)
                End If
                Return objDT
            End Try

            If Not objRS.Read() Then
                objDT.TableName = "nothing"
                If Not objTrace Is Nothing Then
                    objTrace.warn("Empty Result Set: " & objCmd.CommandText)
                End If
                objRS.Close()
                objConn.Close()
                Return objDT
            End If

            Dim iX As Integer
            For iX = 0 To objRS.FieldCount - 1
                objDT.Columns.Add(objRS.GetName(iX))
            Next

            Do
                Dim objRow As DataRow
                objRow = objDT.NewRow
                For iX = 0 To objRS.FieldCount - 1
                    objRow.Item(iX) = objRS.Item(iX)
                Next
                objDT.Rows.Add(objRow)
            Loop While objRS.Read()
            objRS.Close()
            objConn.Close()
            Return objDT
        End Function 

It returns a datatable from whatever sql string I supply.

Once I get that datatable, I create a dataview object and set the first table to that datatable.

Finally, I can filter the dataview for any 100 records I want.

Futher, if I have to manipulate the data at all anywhere down the line, I can refilter without a hit back to the server which, in turn, will increase the performace of this application.

[pvb]

Why not use a DataAdapter to do the dirty work:

Public Shared Function ReturnMultipleResultsEasy(ByVal cmdText As String) As DataTable
    Dim cn As New SqlConnection(g_ConnString)
    Dim cmd As New SqlCommand(cmdText, cn)
    Dim da As New SqlDataAdapter(cmd)
    Dim dt As New DataTable
    da.Fill(dt)
    Return dt
End Function

...less work, easier to read, although the names of these functions are missleading, neither can really return multiple results as only one datatable is ever returned. The dataview approach to paging is nice and easy but could lead to stale data. There are TONS of articles on the web for paging resultsets, stored proc techniques and others that are pretty cool, which one to use just depends on the situation and how much work you're willing to do, just hit google for paging resultsets.

[tailz]

thanks lord_rat and pvb

GaZ