Well I finally got the new system launched and after that I have upgraded SQL Server to 2014 so I now have had the chance to look into this.
I have looked into the various options and have decided to use the NeedDataSource approach. I now have a few questions.
1) I assume that the need data source approach and the client data source approach are mutually exclusive owing to what they do?
2) I have had an issue with the filtering, one column is a checkbox column and the generated filter has True and False as the values, as it's sql server I am using a bit column so need 1 and 0 instead. At the moment with only 1 column on my test page I cheat and use a replace function to switch them over. Is there a better way to do this? Worst case I suppose I can write a function to process all checkbox columns replacing when necessary.
3) I have the code below, could you give it a once over to see if you can suggest any improvements to it?
Private Sub uxRadGrid_NeedDataSource(sender As Object, e As GridNeedDataSourceEventArgs) Handles uxRadGrid.NeedDataSource
Dim _connectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim _sqlConnection As SqlConnection = New SqlConnection(_connectionString)
Dim _sqlDataAdapter As SqlDataAdapter = New SqlDataAdapter
_sqlDataAdapter.SelectCommand = New SqlCommand("ausp_COM_Companies_SelectWithPaging", _sqlConnection)
_sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
_sqlDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@PageNo", SqlDbType.Int))
_sqlDataAdapter.SelectCommand.Parameters("@PageNo").Value = uxRadGrid.CurrentPageIndex + 1
_sqlDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@RecordsPerPage", SqlDbType.Int))
_sqlDataAdapter.SelectCommand.Parameters("@RecordsPerPage").Value = uxRadGrid.PageSize
' Setup the filter expression if there is one needed
If uxRadGrid.MasterTableView.FilterExpression <> "" Then
_sqlDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@FilterText", SqlDbType.NVarChar))
' TODO: This needs some work the checkbox column needs a manual fix to change the True to 1 and False to 0
_sqlDataAdapter.SelectCommand.Parameters("@FilterText").Value = uxRadGrid.MasterTableView.FilterExpression.Replace("[Enabled] = False", "[Enabled] = 0").Replace("[Enabled] = True", "[Enabled] = 1")
End If
' If there are any sort expressions then add them to the sort order string
If uxRadGrid.MasterTableView.SortExpressions.Count > 0 Then
Dim _sortOrderString As String = ""
For Each _gridSortExpression As GridSortExpression In uxRadGrid.MasterTableView.SortExpressions
_sortOrderString += _gridSortExpression.FieldName
If _gridSortExpression.SortOrder = GridSortOrder.Descending Then _sortOrderString += " DESC"
_sortOrderString += ","
Next
_sqlDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@SortOrder", SqlDbType.NVarChar))
_sqlDataAdapter.SelectCommand.Parameters("@SortOrder").Value = _sortOrderString.Trim(","c)
End If
' Now fill the datatable from the database
Dim _dataTable As DataTable = New DataTable
_sqlConnection.Open()
Try
_sqlDataAdapter.Fill(_dataTable)
Finally
_sqlConnection.Close()
End Try
Try
' If rows present then grab the total overall number of rows and assign to the grid
If _dataTable.Rows.Count > 0 Then
uxRadGrid.VirtualItemCount = CInt(_dataTable.Rows(0).Item("TotalOverallRows"))
End If
Catch ex As Exception
End Try
uxRadGrid.DataSource = _dataTable
End Sub