Filter is the key

Friday, September 5, 2003

.NET Scoilnet

"The more I work on databinding, the more impressed I am"
Greg Robinson

I can say the same. As an example I was doing some debugging yesterday on a data search.

Basic stuff, with 2 dropdowns and 2 textbox controls to refine the search.

The usual way I write something like that is to send the request and the parameters to a stored procedure which is for me the best way for everything related to databases.

But here I was stuck on the code and nothing was really working. So back to the white board, scratching my head and finally conclude that my search function needed to works as a funnel.

I start from the whole date, and I needed to filter by the different controls content, e.g. Counties list, School Type, etc... The magic word is filter.

So to come back to what say Greg, it's true that now in .Net you can do a lot more without stored procedures.

The idea there is to use the .RowFilter method with a cascade effect until you obtain the right results.

The last issue I fixed is that obviously asking to select all the records from a table can slow down your server if you have too much users.

Thankfully this is not a crucial part of my application, but in case I create a Cache version of the data before doing anything else.

And I am also a big fan of Data Access Application Block ;-)

You can see the result of this code on Scoilnet

Private Property GetAllschools()
        Get
            Dim Conn As SqlConnection

            Conn = New SqlConnection(ConfigurationSettings.AppSettings("Scoil"))

            Dim ds As DataSet
            If Cache("schools") Is Nothing Then
                ds = SqlHelper.ExecuteDataset(Conn, CommandType.StoredProcedure, "SchoolsAll")

                If ds.Tables(0).Rows.Count > 0 Then
                    Cache.Insert("schools", ds.Tables(0), Nothing, DateTime.Now.AddMinutes(30), TimeSpan.Zero)
                    Conn.Close()
                    Conn.Dispose()
                    Return Cache("schools")
                Else
                    Return Nothing
                End If
            Else
                Return Cache("schools")
            End If
        End Get
        Set(ByVal Value)
        End Set
    End Property

Sub BindSchoolsData()
        Dim dsTable As DataTable = GetAllschools

        If Not (dsTable) Is Nothing Then
            Dim Myfilter As String = ""
            If School_RollNumber.Text.Length > 0 Then
                If Myfilter = "" Then
                    Myfilter = "rollNumber like '%" & School_RollNumber.Text & "%'"
                Else
                    Myfilter += " and rollNumber like '%" & School_RollNumber.Text & "%'"

                End If
            End If
            If TxtSearch.Text.Length > 0 Then
                If Myfilter = "" Then
                    Myfilter = "Name like '%" & TxtSearch.Text & "%'"
                Else
                    Myfilter += " and Name like '%" & TxtSearch.Text & "%'"

                End If
            End If
            If School_Type.SelectedIndex > 0 Then
                If Myfilter = "" Then
                    Myfilter = "SchoolType = '" & School_Type.SelectedItem.Text & "'"
                Else
                    Myfilter += " and SchoolType like '" & School_Type.SelectedItem.Text & "%'"
                End If
            End If
            If County_list.SelectedIndex > 0 Then
                If Myfilter = "" Then
                    Myfilter = "Countyname like '%" & County_list.SelectedItem.Text & "%'"
                Else
                    Myfilter += " and Countyname like '%" & County_list.SelectedItem.Text & "%'"
                End If
            End If
            If Myfilter = "" Then
                Myfilter = "Countyname='nodata'"
            End If

            Dim dv As DataView
            dv = New DataView
            With dv
                .Table = dsTable
                .RowFilter = Myfilter
                .RowStateFilter = DataViewRowState.ModifiedCurrent

            End With
            If dv.Count > 0 Then
                DGPaging.DataSource = dv
                If Search_changed.Text = "changed" Then
                    DGPaging.CurrentPageIndex = 0
                End If
                Schools_search_result.Visible = True
            Else
                DGPaging.DataSource = Nothing
                school_details.Visible = False
                Schools_search_result.Visible = False
                'Panel_noresult.visible = True
            End If

            DGPaging.DataBind()
        Else
            DGPaging.DataSource = Nothing
            school_details.Visible = False
            Schools_search_result.Visible = False
        End If
    End Sub

No Comments