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