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.

Contributed by
Rating: 5 stars5 stars5 stars5 stars5 stars / 9
March 06, 2006
Rate this Article:
MEH MEH++


SEARCH ASP FREE
TOOLS YOU CAN USE

advertisement

 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 dtParameterList As DataTable
        If viewstate("dtParameterList") Is Nothing Then
            dtParameterList = New DataTable
            dtParameterList.Columns.Add(New DataColumn("ParamDetail", GetType(String)))
            viewstate("dtParameterList") = dtParameterList
        End If
 
        Dim dr As DataRow = dtParameterList.NewRow
        'dr("ParamDetail") = buildParameter
        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”.

The core database routines to work with stored procedures: “SPgetDataTable” method

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))
                        UsedParameter = CType(ser.Deserialize(New StringReader(drParam(0))), MSSQLProcParameter)
                        ConvertedParameter = UsedParameter.getSQLParameter
                        .Parameters.Add(ConvertedParameter)
                    Next
                End If
 
                Dim privateSQLDataAdapter As New SqlDataAdapter(cmd)
                privateSQLDataAdapter.Fill(privateDataTable)
                .Connection.Close()
                .Dispose()
                privateSQLDataAdapter.Dispose()
            End With
            Return privateDataTable
        Catch ex As Exception
            Try
                If cmd.Connection.State = ConnectionState.Open Then
                    cmd.Connection.Close()
                    cmd.Dispose()
                End If
            Catch e As Exception
           
End Try
            Throw New Exception(ex.Message & ". Stored Procedure: " & ProcName & " -->Error Logged")
        End Try
    End Function



Where does the processing start?

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)
        For Each drRowField As DataRow In dtRowField.Rows
            Dim drReport As DataRow = dtReport.NewRow
            drReport("details") = drRowField(0)
            For Each drColumn As DataRow In dtColumns.Rows
                drReport(drcolumn(0)) = getRowValue("select " & [Enum].GetName(GetType(OperationType), SmryType) & "(" & SummaryFieldName & ") from " & tblname & " where " & RowFieldName & "='" & drRowField(0) & "' and " & ColFieldName & "='" & drcolumn(0) & "'")
            Next
            dtReport.Rows.Add(drReport)
        Next
        Return dtReport
    End Function



The next section gives you the complete source code for the cross-tab engine (dealing with stored procedures).

Cross-tab processing with the information from the stored procedure

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)
 
        'getting distinct values for columns
        Dim dtColumns As New DataTable
        dtColumns.Columns.Add(ColFieldName)
        Dim dvColumns As New DataView(dtSPOutput)
        dvColumns.Sort = ColFieldName
        Dim prevColValue As String = "0"
        For Each drColumn As DataRowView In dvColumns
            If prevColValue <> drcolumn(ColFieldName) Then
                dtColumns.Rows.Add(New Object() {drColumn(ColFieldName)})
                prevColValue = drColumn(ColFieldName)
            End If
        Next
 
        'adding columns to the report data table
        dtReport.Columns.Add("Details")
        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
 
        'getting distinct values for Rows
        Dim dtRows As New DataTable
        dtRows.Columns.Add(RowFieldName)
        Dim dvRows As New DataView(dtSPOutput)
        dvRows.Sort = RowFieldName
        Dim prevRowValue As String = "0"
        For Each drRow As DataRowView In dvRows
            If prevColValue <> drRow(RowFieldName) Then
                dtRows.Rows.Add(New Object() {drRow(RowFieldName)})
                prevColValue = drRow(RowFieldName)
            End If
        Next
 
        'adding rows to the report data table
        For Each drRowField As DataRow In dtRows.Rows
            Dim drReport As DataRow = dtReport.NewRow
            drReport("details") = drRowField(RowFieldName)
            For Each drColumn As DataRow In dtColumns.Rows
                Dim dvSmryCalc As New DataView(dtSPOutput)
                dvSmryCalc.RowFilter = RowFieldName & "='" & drRowField(RowFieldName) & "' and " & ColFieldName & "='" & drcolumn(ColFieldName) & "'"
                Dim sum As Double = 0
                For Each drSmryCalc As DataRowView In dvSmryCalc
                    sum += drSmryCalc(SummaryFieldName)
                Next
                drReport(drColumn(ColFieldName)) = sum
            Next
            dtReport.Rows.Add(drReport)
        Next
 
        Return dtReport
    End Function



Summary

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

blog comments powered by Disqus
ASP.NET ARTICLES

- Implementing ASP.NET 4.0 Page.MetaDescriptio...
- ASP.Net Development Tips
- Intro to Sessions in ASP.Net
- Google Maps API Introduction in ASP.NET usin...
- Creating an ASP.NET 3.5 Gridview Image Galle...
- Encrypt QueryString in ASP.NET 3.5 using VB....
- ASP.NET 3.5 Drop Down List Controls
- Connect to Access Database with ASP.Net
- Secure Audio Streaming with ASP.Net and Flash
- Dynamic Sitemap and Navigation in ASP.Net
- Implement Gzip and Deflate Compression in AS...
- Run ASP.Net in Ubuntu with Apache
- ASP.Net Mono Website Contact Forms
- ASP.Net URL Rewriting Methods
- Murach`s ASP.NET 4 Web Programming with C# 2...

ASP Web Hosting ASP.Net Web Hosting Windows Web Hosting
 
 
 

ASP Free Forums 
 RSS  Tutorials RSS
 RSS  Forums RSS
 RSS  All Feeds
Site Map 
Request Media Kit
Write For Us Get Paid 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Privacy Policy 
Support 


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 7 - Follow our Sitemap
Most Popular Topics
All ASP.Net Tutorials