HomeASP.NET Extending an ASP.NET Datagrid to Support S...
Extending an ASP.NET Datagrid to Support Simple Cross Tab Reporting: The Source Code
This article, second in the series, guides you through developing your own ASP.NET custom control by extending the existing datagrid control (in ASP.NET). The main concentration will be on Cross Tab Reporting with ASP.NET data grid.
This article gives you only the complete source code for the custom control. You can expect the explanation for the source code in an upcoming article.
A downloadable file for this article is available here.
You can find the first article in this series here.
The entire source code is developed using Visual Studio.NET 2003 Enterprise Architect with SQL Server 2000 enterprise edition on Windows Server 2003 standard edition. I didn’t really test the source code in any of the previous releases or latest releases apart from the above. Please drop me a line if you really want it customized with any of the latest releases, along with some suggestions for features or enhancements you want.
I strongly suggest you customize the custom control to suit your needs.
The core database routines to work with stored procedures: the methods
I already presented the entire structure of the custom control in my previous article. Now, in this article, I will focus on the processing engines for the cross-tab. Before I do that, I need to introduce you to two more methods I used to access stored procedures. Let us have a look at the complete code for the first one:
Public Sub SPaddParameter(ByVal ParameterName As String, Optional ByVal Value As Object = Nothing, Optional ByVal SQLType As MSSQLDataType = Nothing, Optional ByVal Size As Integer = Nothing, Optional ByVal Direction As ParameterDirection = ParameterDirection.Input)
Dim buildDataType As SqlDbType
Dim buildParameter As MSSQLProcParameter = Nothing
Select Case SQLType
Case MSSQLDataType.SQLString
buildDataType = SqlDbType.VarChar
Case MSSQLDataType.SQLChar
buildDataType = SqlDbType.Char
Case MSSQLDataType.SQLInteger
buildDataType = SqlDbType.Int
Case MSSQLDataType.SQLBit
buildDataType = SqlDbType.Bit
Case MSSQLDataType.SQLDateTime
buildDataType = SqlDbType.DateTime
Case MSSQLDataType.SQLDecimal
buildDataType = SqlDbType.Decimal
Case MSSQLDataType.SQLMoney
buildDataType = SqlDbType.Money
Case MSSQLDataType.SQLImage
buildDataType = SqlDbType.Image
Case MSSQLDataType.SQLFloat
buildDataType = SqlDbType.Float
End Select
buildParameter = New MSSQLProcParameter(ParameterName, Value, buildDataType, Size, Direction)
Dim ser As XmlSerializer = New XmlSerializer(GetType(MSSQLProcParameter))
Dim sb As New StringBuilder
Dim writer As New StringWriter(sb)
ser.Serialize(writer, buildParameter)
writer.Close()
dr("ParamDetail") = writer.ToString
dtParameterList.Rows.Add(dr)
End Sub
Even though the method is bit lengthy, it does nothing other than add a new parameter to the parameter cache. The parameter cache stays in “dtParameterList” (offline data table). I use all of the parameters available in “dtParameterList” in “SPgetDataTable” to retrieve information from the stored procedure.
The next section gives you the complete source code for “SPgetDataTable”.
This is one of the core methods I used to retrieve information from a stored procedure using parameters.
All the parameters will be available in the parameter cache “dtParameterList”. This method goes through all the parameters available in the parameter cache and converts them into the parameters of the command type. We shall execute the stored procedure along with all the parameters.
After successful execution of the stored procedure, it gives the information in the form of rows (or a data table), which is what we finally return.
The following is the complete source code for “SPgetDataTable”:
Public Function SPgetDataTable(ByVal ProcName As String) As DataTable
Dim cmd As SqlCommand
Try
cmd = New SqlCommand(ProcName, New SqlConnection(getConnectionString))
Dim Parm As SqlParameter
Dim privateDataTable As New DataTable
With cmd
.CommandType = CommandType.StoredProcedure
Dim UsedParameter As MSSQLProcParameter Dim ConvertedParameter As SqlParameter
Dim drParam As DataRow
If Not viewstate("dtParameterList") Is Nothing Then
For Each drParam In viewstate("dtParameterList").Rows
UsedParameter = Nothing
Dim ser As New XmlSerializer(GetType(MSSQLProcParameter))
The processing starts when you call “LoadData.” The following is the code I wrote in “LoadData:”
Public Sub LoadData()
Dim dtReport As DataTable
Select Case TypeOfObject
Case ObjectType.Table
dtReport = getCrossTab_LevelSingle_ForTable()
Case ObjectType.StoredProcedure
dtReport = getCrossTab_LevelSingle_ForSP()
End Select
Me.DataSource = dtReport
Me.DataBind()
End Sub
It is really very simple. I just made it very precise for proper readability. Based on the data source (for retrieving information) provided by the user, the processing starts either at “getCrossTab_LevelSingle_FoTable” (for table) or “getCrossTab_LevelSingle_FoSP” (for stored procedure).
The following is the complete source code for the cross-tab engine (dealing with the table):
Private Function getCrossTab_LevelSingle_ForTable() As DataTable
Dim tblname As String = ObjectName
Dim SmryType As OperationType = Me.TypeOfOperation
Dim dtReport As New DataTable
'adding columns to the report data table
dtReport.Columns.Add("Details")
Dim dtColumns As DataTable = getDataTable("select distinct " & ColFieldName & " from " & tblname & " order by " & ColFieldName)
For Each drColumn As DataRow In dtColumns.Rows
dtReport.Columns.Add(drColumn(0))
Next
'additional blank columns
For i As Integer = 1 To AdditionalBlankColumns
dtReport.Columns.Add(New DataColumn)
Next
'adding rows to the report data table
Dim dtRowField As DataTable = getDataTable("select distinct " & RowFieldName & " from " & tblname & " order by " & RowFieldName)
The previous section provided the source code for dealing with table data source. Now we shall deal with the stored procedure as the data source. The following is the complete source code for cross-tab engine towards stored procedure.
Private Function getCrossTab_LevelSingle_ForSP() As DataTable
Dim SPName As String = ObjectName
Dim SmryType As OperationType = OperationType.Sum
Dim dtReport As New DataTable
Dim dtSPOutput As DataTable = SPgetDataTable(SPName)
An upcoming article of mine will give you a full explanation for both of the above routines, as it would really cross too many sections. Let us discuss some of the issues of this custom control:
I currently implemented only a few operation types, as it would otherwise go beyond the limits of the article.
I currently didn’t implement any data grid events. It would be very helpful to you if you really want to work with totals, sub-totals, grand totals or column wise totals.
The current control works only with SQL Server at the moment. You need to modify all the core database routines to make it flexible enough to work with any database.
I didn’t implement any styling for the data grid.
It works only for a single level at the top and a single level at the left. You can expect multiple levels in my upcoming articles.
I used this parameter cache method just to ease my calling. You can work with your own approach as well.
If you are familiar with Data Access blocks or Enterprise Application blocks, I request that you replace (or modify) my core database routines with those types of routines in blocks. They are really suitable for enterprise use.
Note that this is a very small trial of mine which I would like to share the same with you all.
I strongly suggest you test the control thoroughly before moving it into production use.
Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com
ASP.NET 
Extending an ASP.NET Datagrid to Support Simple Cross Tab Reporting: The Structu
