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

Recordset with Filter and Sort: request for codereview

Weeks ago I changed from classic ASP to ASP.Net. Gradually I find my way, but sometimes I'd like some feedback. Because I am the only developer in my current company, I am turning to you.

One of things that bothered me about ADO.Net is the absence of an easy way to create a recordset can be filtered and sorted. Of course I could turn to ADODB, but I wanted to avoid that.

I came up with a Class as in the code snippet, to make it easier to get a more advanced recordset than the forward-only SqlDataReader.

My request is that you look at this code and make your comments. Any comments are welcome.

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data
 
' ================================================
' Class:    SQLRecordSet
' Purpose:    An SQLRecordSet that has the methods and properties of ADODB.RecordSet
'            Basically it is a oDataSet, because that is what comes the closest.
' Remarks:    Because the default SQLReader is ReadOnly, ForwardOnly
'            and I want it to be sortable and filterable
' ================================================
 
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
sybe
Asked:
sybe
  • 4
  • 3
1 Solution
 
DropZoneCommented:
Sybe,
I understand what you want to do; I was in the same position a few years ago when I moved from ASP to ASP.NET.  There is one important mental leap that you need to take: that ADO.NET is designed to work with disconnected record-sets and offers an object-oriented approach to data access.  It therefore works differently than Classic ADO.

Your code seems fair, and it definitely encapsulates the sorting and filtering of a single record set.  However, it will limit its use to a direct record-set retrieval, like in Classic ASP.  This may be fine for some applications, but ASP.NET and ADO.NET can offer so much more by giving you the ability to work with a disconnected data set, and by modularizing the data access into DataTables, DataSets, DataReaders, and the like, it allows for some very powerful functionality.

I suggest you acquaint yourself with the ADO.NET model.  Like you, I was skeptical at first, but I grew to understand it and discovered all the things I could do that required so much convoluted code to do with Classic ADO and plain record-sets, if they were possible at all.

For example, you could perform a query and assign the results to a DataTable.  This DataTable could form part of a DataSet (with other DataTables).  You can disconnect from the database, release the connection and command objects, and keep the DataTable; use it as the data source for some page control, filter it with a DataView, manipulate it, and then send it back with another Command object to the database.

All the time treating the data not as a record-set, but as fully-featured data object which allows you to interact with other objects.

To get you started, here are some tutorials on the basic concepts of ADO.NET:
    http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspx
    http://www.devx.com/vb2themax/Article/19887/1954?pf=true
    http://www.4guysfromrolla.com/webtech/chapters/ASPNET/ch06.2.shtml
    http://aspnet.4guysfromrolla.com/articles/050405-1.aspx

Here's an article explaining about filtering and sorting using a DataTable and a DataView:
    http://www.developerfusion.com/article/4703/filtering-and-sorting-in-adonet/

     -dZ.
0
 
sybeAuthor Commented:
Thanks for your comment.

I still have to form an opinion about ASP.Net. So far I haven't seen anything splendid, although some things are nice, like the master pages..I am avoiding the Visual Studio controls more and more. At first they seemed nice to me, but they turn out to be little flexible and create ugly HTML-code. I find the "runat='server" stuff only complicate things.

The basic idea of ADO.Net is nice, with disconnected datasets. But it takes too many lines of code to create something as simple as a sortable and filterable dataset. It should not take more than one line of code (after creating the connection). ADO.Net requires at least 4 lines of code.

I know I'll find my way. First I need to be able to do the things that I can do with classic ASP.
0
 
Bob LearnedCommented:
>>First I need to be able to do the things that I can do with classic ASP.
That kind of statement just makes me believe that you are having a problem with the complete and utter paradigm shift that is ASP.NET.   I believe that you need to let go of your preconceived notions, because I am extremely happier not to be working with ASP classic.  I have to admit that it took me a little bit to get over the same learning curve, that I imagine that you are on, so I understand your feelings.  I am also curious why you would "avoid the Visual Studio controls"?
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:
>>>First I need to be able to do the things that I can do with classic ASP.
> That kind of statement just makes me believe that you are having a problem with the complete and utter paradigm shift that is ASP.NET

I am sorry, but did you really read what you wrote? Classic ASP is good for webpages which are connected to a database. ASP.Net is for the same purpose. There is no paradigm shift. ASP.Net works a bit differently. You can talk about disconnected data - but that is also possible with classic ASP. You can talk about object-oriented but classes are also part of classic ASP. Object inheritance is new. In practice it is however not of much use. How much inheritance do you need to build a webpage?

The paradigm shift is maybe that webpages are being build as if they are a desktop application. The idea behind that is however very wrong. A single webpage is a stand-alone application. A "web application" is nothing more then multiple pages ( stand-alone applications) which can share some code because they have a similar functionality.

Web pages are request-based. Yes. I have seen people getting lost in ASP.Net complicating matters so much that they have lost track of HTML and javascript (which is what web pages are made of). People using ASP.Net having trouble to create a simple link to an iframe, people getting lost in AJAX-controls, because they think they need AJAX to make something as basic as a hyperlink work as they want.

Examples? Here you go:
http://www.experts-exchange.com/Q_24141087.html
Somebody is using a gridview to display data from a database. One of the fields is a date field and the record corresponding with "today" needs to get to get another color. In stead of comparing the dates while writing the display, the solution seems to be to first write the gridview (where dates are converted to strings), then loop through the values in the gridview, convert the strings back to dates, compare them with the current date and then if they are equal, change the color.

http://www.experts-exchange.com/Q_24137222.html
Somebody is trying to get a link or button which, when clicked, changes the url of an iframe. For a link that is totally basic HTML functionality, for a button it needs some very simple javascript. But this 'paradigm shift' you are talking about is so confusing that the person has big trouble doing just that. He thinks that it is needed to use AJAX.

Just some examples of the 'amazing' paradigm shift of ASP.Net. People have lost sight of what they are doing in terms of HTML and Javascript.

>  I am also curious why you would "avoid the Visual Studio controls"?
As I said, they are too rigid.
Also the VS2005 designer does not work with nested master pages. I'm am not sure if nested master pages are a good idea, but I have experimented with it.
They produce horrible code, both in the ASP.Net source and even worse in the resulting HTML. It comes up with fantastic id's for "labels" (spans) as "ctl00_ContentPlaceHolder1_Repeater1_ctl00_Vacaturelisting1_Repeater1_ctl00_hlFunctie" and then for each element.Yes, I see how such id's are generated, but in real life such id's are either hopeless to work with or totally unneeded.

So the 'paradigm shift' seems to be that people don't care about the actual output anymore, lose sight of the basics of HTML and javascript and get lost in the VS wizards.

ASP.Net might have some 'powerful' features, but the truth is that for 90% of the pages those powerful features are utterly useless. The page just needs to display some data from a database. For most of the interaction a simple HTML link is sufficient or some basic javascript. In fact ASP.Net makes it harder to produce nicely looking and understandable HTML.People lose themselves too easily in complicated AJAX controls of which they do not control the source and which are impossible to change or debug.

It is nice to have a calendar control, but it is not so nice when you need it in another language as English. And when finally the calendar itself is in the right language, then still the "today" at the bottom is in English. Yes, that can be changed too, but that needs another adaptation of the code. Totally silly. Oh, and look how many external javascripts automatically get included when using such a control. Dozens, each of them having a totally random url like below, only making it very hard to find and fix sources of errors.

/ScriptResource.axd?d=JdwR0Czl67-nEG_Xk49DxRW6S_GtwIZYyKDKrM3alHEJFGxLHUhYKD0Sp29gysW0fWD-MeHUoCelqInpuW7qHmUQWBCjO7wGFv5Iv1cZVmI1&t=633052361146686982

So no, thank you.




0
 
DropZoneCommented:
You asked for experts' comments, and you received some.  Your attitude towards ASP.NET reflects a complete misunderstanding of the framework and its intention, perhaps due to your lack of experience with it.  Both TheLearnedOne and I have tried to express empathy, because we also remember how completely alien the environment felt when we started with it.  Indeed, it took some time for me to warm up to the idea, but once I did I was able to do things which were mostly impossible in Classic ASP, or required complex and bloated pre-compiled COM components.  I expect that in time you may come to appreciate the framework too.

Other than guidance and assistance in getting things done in ASP.NET, I have nothing else to offer, sorry.  I sincerely wish you luck.

     Cheers!
     -dZ.
0
 
sybeAuthor Commented:
> You asked for experts' comments, and you received some

All I got was remarks about how I misunderstand ASP.Net and ADO.Net. Also from you.  I was asking for code review, not for attitude review.
0
 
DropZoneCommented:
I reviewed the code and offered my opinion, suggestions, and links to references:

>> "Your code seems fair, and it definitely encapsulates the sorting and filtering of a single record set.  However, it will limit its use to a direct record-set retrieval, like in Classic ASP.", etc.

Take that as you will; I'm only trying to help.  I'm sorry you do not feel satisfied with my response and that I have nothing more to offer.  You can leave the question open and perhaps other experts will chime in with more satisfactory comments, or you could have the question closed and keep your points.  In any case, I wish you good luck in your project.

     Cheers,
     -dZ.
0
 
sybeAuthor Commented:
Well. Thanks anyway.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now