• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 905
  • Last Modified:

SqlDataReader, but (like ADODB.RecordSet) with more advanced properties and methods

It is probably easy, but I am a bit lost in the mess of VB.Net classes.

With ADODB it is easy to define a recordset with different properties, but the SqlDataReader is very rigid. No RecordCount, no Sort(), no Filter.

I do see a SqlDataAdapter class, but I don't need to adapt any data. I just want something like an ADODB.RecordSet that supports the Filter property.

Sample code I found looked needlessly complicated, involving both a SqlDataAdapter (i don't need to adapt data) and a DataSet (of which I do not see something comparable to a Filter property)
0
sybe
Asked:
sybe
  • 6
  • 6
  • 6
  • +1
1 Solution
 
DhaestCommented:
0
 
Praveen VenuCommented:
You should use DataTable or DataView for that

http://www.dotnetjohn.com/articles.aspx?articleid=31
0
 
adatheladCommented:
Hi,

The SqlDataReader is used for forward-only access to the resultset - it does not know how many records are returned as the results are stored in the network buffer - it's from here that the datareader iterates round the results one record at a time so it has no real concept of the "bigger picture". A connection to the database must kept open until you've finished with the reader.

The SqlDataAdapter is used for what you want - more flexbility with the resultset and doesn't actually mean you want to change/adapt the data. This is a disconnected resultset, so it returns all the data from the query into a DataSet/DataTable in .NET (by calling SqlDataAdapter.Fill(YourDataSet)). Once "filled", you can close the database connection and work the data in a disconnected manner, including sorting/filtering/recordcount.

This is a good reference which provides references to specific uses of each:
http://msdn.microsoft.com/en-us/library/ms254931.aspx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
sybeAuthor Commented:
> A Practical Comparison of ADO and ADO.NET

Thanks, but it does not have anything to say about filtering a recordset.


http://www.dotnetjohn.com/articles.aspx?articleid=31

That is about a dataview. I don't want to display the data, I need a filter on a recordset in order to send emails.

http://msdn.microsoft.com/en-us/library/ms254931.aspx

Nice, but nothing about filtering the dataset. The Dataset object does not seems to have a filter property.


> ADO.NET Tutorial

Thanks, but nothing about Filtering.


Anyway, it seems to be true. The "advance" from classic ASP to dot.net is that in stead of needing one object (ADODB.RecordSet) you need two (SqlDataAdapter, which has a illogical name) and a DataSet.

But I still do not see how to Filter a Dataset. I some that a DataView has a .RowFilter property, but the filter I need is not to be applied to a DataView (in fact the data are not displayed at all, I want to filter the records in order to send emails).

In Classic ASP I would have code like in the snippet below.

<%
Set oRS = Server.CreateObject("ADODB.Recordset")
oRS.Open ("SELECT fields FROM table"), oConnection, 1, 2
For i = 1 To 12
    oRS.Filter = "organization_id = " & i
    Do While oRS.EOF
        ' do something
    oRS.MoveNext
Next
%>

Open in new window

0
 
DhaestCommented:
0
 
adatheladCommented:
Details on how to filter a dataset:
http://msdn.microsoft.com/en-us/library/13wb36xf.aspx

More of a background on Dataviews:
http://msdn.microsoft.com/en-us/library/fdcwwhez.aspx
0
 
adatheladCommented:
Based on the example you gave for filterting from your classic ASP, your much better of doing the filtering in the query, rather than returning a potentially much  larger resultset and then filtering in .NET - SQL is much better at querying/filtering than .NET is.

In your example, I'd strongly recommend for example, creating  a stored procedure that accepts an organisation id as a parameter, where the sproc just does:

SELECT fields FROM table WHERE organization_id = @OrganizationId
0
 
DhaestCommented:
@adathelad: If you do so, he needs to call this select each time when the index changes in his loop
0
 
adatheladCommented:
@Dhaest - ah yes, apologies - oversight on my part. I was focussing on the general "filtering of data" topic and overlooked the exact scenario!
0
 
sybeAuthor Commented:
> http://www.developerfusion.com/article/4703/filtering-and-sorting-in-adonet/2/

That seems to be what I am looking for.
Do I understand it right that in order to use the DataTable.Select() method in this case I would have to use first create a SQLDataAdapter, use that to fill a DataSet, and from there create a DataTable?

I can just say one word: Wow!




0
 
DhaestCommented:
That's correctly.
You need
1. a sqlConnection  - the connection to your database
2. a sqlDataAdapter - the link between your database and your results (you can see this as the query)
3. a DataSet - this will hold the data (in tables), which is returned from the database

When you have this dataset, you can filter a datable by using dataTable.Select, creating a dataview, ...
0
 
adatheladCommented:
Yes you would use an SQLDataAdapter - the Fill method can be used to either populate a DataSet (which is basically just a container for potentially multiple DataTables) - or you can just fill a DataTable directly. You simply pass it a DataSet or DataTable object as appropriate.
0
 
sybeAuthor Commented:
Thank you all.

(and a special thanks to MS for making things so much simpler in dot.Net)
0
 
DhaestCommented:
>> (and a special thanks to MS for making things so much simpler in dot.Net)

Once you are used to the new way of working, it's very easy. The most important change is the performance :)
0
 
sybeAuthor Commented:
> Once you are used to the new way of working, it's very easy.

Well, needing 3 objects in stead of 1 object for the same functionality is not really an improvement.

I have the impression that dot.net is designed for managers, just to show how quick a website can be clicked together - and how pointless it is to have real progammers around. Of course anything that goes beyond the point-and-click interface of VS takes in fact more time than classic ASP.

Also, have you ever looked at the HTML that results from the point-and-click way of working? I have never seen such HTML-garbage in my life before.


> The most important change is the performance

Mostly coming from the code being precompiled. I don't see how using three different object gives a better performance. Of course I could still use ADODB, but then the application requires an additional reference (which probably also does not add to performance either).
0
 
sybeAuthor Commented:
After some effort I came up with the code below.

Any suggestions for improvements?
Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data
 
Public Class SQLRecordSet
    Public Version As String = "0.50"
    Private _filter As String = ""
    Private _sort As String = ""
    Private _currentrow As Long = 0
    Private _recordcount As Long = 0
    Private oRows As DataRow()
    Private oColumns As DataColumnCollection
    Private oDataSet As New DataSet("Return")
 
    Public Sub New(ByVal oConnection As SqlConnection, ByVal sSQL As String)
        Open(oConnection, sSQL)
    End Sub
 
    Protected Overrides Sub Finalize()
        oColumns = Nothing
        oRows = Nothing
        oDataSet = Nothing
    End Sub
 
    Public Sub Open(ByVal oConnection As SqlConnection, ByVal sSQL As String)
        Dim bConnectionOpened As Boolean = False
        If oConnection.State = 0 Then oConnection.Open() : bConnectionOpened = True
        Dim oAdapter As New SqlDataAdapter(sSQL, oConnection)
        oAdapter.TableMappings.Add("Table", "Return")
        oAdapter.Fill(oDataSet)
        oRows = oDataSet.Tables("Return").Select()
        oColumns = oDataSet.Tables("Return").Columns
        _recordcount = UBound(oRows) + 1
        If _recordcount > 0 Then _currentrow = 1
        If bConnectionOpened Then oConnection.Close()
        oAdapter = Nothing
    End Sub
 
    Public Sub Close()
        oDataSet.Reset()
        oRows = Nothing
        oColumns.Clear()
    End Sub
 
    Private Sub ApplyFilterSort()
        oRows = oDataSet.Tables("Return").Select(_filter, _sort)
        _recordcount = UBound(oRows) + 1
        _currentrow = IIf(_recordcount > 0, 1, 0)
    End Sub
 
    Public Sub MoveNext()
        _currentrow += 1
    End Sub
 
    Public Sub MovePrevious()
        _currentrow -= 1
    End Sub
 
    Public Sub MoveFirst()
        _currentrow = 1
    End Sub
 
    Public Sub MoveLast()
        _currentrow = _recordcount
    End Sub
 
    Public Sub MoveTo(ByVal i As Long)
        _currentrow = i
    End Sub
 
    Public Sub Move(ByVal i As Long)
        _currentrow += i
    End Sub
 
 
    Default Property Item(ByVal s As String)
        Get
            Return oRows(_currentrow - 1)(oColumns.Item(s))
        End Get
        Set(ByVal value)
 
        End Set
    End Property
 
    Public ReadOnly Property RecordCount() As Long
        Get
            Return _recordcount
        End Get
    End Property
 
    Public ReadOnly Property EOF() As Boolean
        Get
            Return IIf(_recordcount = 0, True, CBool(_currentrow > _recordcount))
        End Get
    End Property
 
    Public ReadOnly Property BOF() As Boolean
        Get
            Return IIf(_recordcount = 0, True, Not CBool(_currentrow = 0))
        End Get
    End Property
 
    Public Property Filter() As String
        Get
            Return _filter
        End Get
        Set(ByVal s As String)
            _filter = s
            ApplyFilterSort()
        End Set
    End Property
 
    Public Property Sort() As String
        Get
            Return _sort
        End Get
        Set(ByVal s As String)
            _sort = s
            ApplyFilterSort()
        End Set
    End Property
 
End Class

Open in new window

0
 
adatheladCommented:
You'll probably want to raise another question for this....
0
 
sybeAuthor Commented:
Hmm, just wanted to share.

Except when you have tons of ideas for remarks, I don't think it is worth another question. The code works more or less as I want, and in time it will be improved.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 6
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now