Extending an ASP.NET Datagrid to Support Simple Cross Tab Reporting: The Structure

This article guides you through developing your own ASP.NET custom control by extending the existing datagrid control (in ASP.NET). It mainly concentrates on Cross Tab Reporting with the ASP.NET data grid.

Contributed by Jagadish Chaterjee
Rating:

/ 14
February 27, 2006

Rate this Article:
MEH MEH++

SEARCH ASP FREE

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 later releases apart from the above. Please drop me a line if you really want it customized with any of the later releases along with some suggestions for features or enhancements you want.

I strongly suggest you customize the custom control to suit your needs.

What is inside my custom control?

I was really expecting some new server controls from Microsoft to support cross tab reporting. Instead, Microsoft offers that option only through SQL Server Reporting Services or Crystal Reports. Apart from those two technologies (and third-party vendors), there exists no control to support a cross-tab reporting system.

But I'm still happy with the flexibility that Microsoft offered with datagrid. It really has enormous depth; we just need to use our skill to mold it according to our requirements. Finally, I started with a simple cross tab control (by extending it from datagrid) to support basic cross tabbing. To my surprise, it really worked as I expected. Even though it may not be very useful for enterprise reporting, I can still use it for small cross-tab reports. And there is always a chance for extensibility.

Coming to my custom control, it has a region called “Core Database Routines,” which is very similar to DAL. It is just a small interpretation of the same. The region contains the following methods within it:

getRowValue
getDataTable
SPgetDataTable
SPaddParameter

Those are the methods I mostly used to work with the custom control. The first two shall work directly with the tables, and the last two work with stored procedures.

Apart from the above, I also developed one more class to handle all parameters (or parameter caches) which are likely to be passed to stored procedures. It is named “MSSQLProcParameter.” Even though it is not necessary, it eased some of my problems.

Not only that, I declared three more enumerations, especially to provide properties. Those are as follows:

OperationType (could be sum, avg, etc.)
ObjectType (could be either table or stored procedure)
MSSQLDataType (my own mapping of data types)

I could not finish without giving several additional properties to the custom control as it is related to the cross tab. The following is the list of properties I managed to declare:

ObjectName
TypeOfObject
RowFieldName
ColFieldName
SummaryFieldName
TypeOfOpeation
AdditionalBlankColumns

And finally, I managed to get entire thing working with a simple method, named “LoadData.”

That gives you an overall idea of the inner workings of the custom control. Now let us delve into the details.

The enumerations declared in the custom control

Let us start with all the enumerations I declared. First look at the following:

Public Enum OperationType

Sum

Avg

Max

Min

Count

End Enum

The above mainly deals with the type of operation you may want to process for the cross tab. This is essential; you can even add few more according to your requirements. Now let us look at the following:

Public Enum ObjectType

Table

StoredProcedure

End Enum

Sometimes, the cross-tab information must be retrieved either from a table or stored procedure. The above code handles that. And finally look at the following:

Public Enum MSSQLDataType

SQLString

SQLChar

SQLInteger

SQLBit

SQLDateTime

SQLDecimal

SQLMoney

SQLImage

SQLFloat

End Enum

Those are the only data types (my own interpretation) I am focusing on at the moment. The mapping of these custom data types to the original data types will be dealt with in the “SPAddParameter” method.

The properties available to customize the custom control

The following is the source code related to every property defined within the custom control. You can observe that all the properties become available under a separate category called “CrossTab Details.” I am also using the “viewstate” object to hold the property values. Let us walk through each of the properties.

<Category("CrossTab details")> _

Public Property ObjectName() As String

Get

Return viewstate("ObjectName")

End Get

Set(ByVal Value As String)

viewstate("ObjectName") = Value

End Set

End Property

The above property is mainly used to provide table name or stored procedure name.

<Category("CrossTab details")> _

Public Property TypeOfObject() As ObjectType

Get

If viewstate("TypeOfObject") Is Nothing Then

viewstate("TypeOfObject") = ObjectType.Table

End If

Return viewstate("TypeOfObject")

End Get

Set(ByVal Value As ObjectType)

viewstate("TypeOfObject") = Value

End Set

End Property

The above property is mainly used to specify whether the data source is a table or stored procedure.

<Category("CrossTab details")> _

Public Property RowFieldName() As String

Get

Return viewstate("RowFieldName")

End Get

Set(ByVal Value As String)

viewstate("RowFieldName") = Value

End Set

End Property

The above property specifies the column name which the cross-tab needs to retrieve for the first left column.

<Category("CrossTab details")> _

Public Property ColFieldName() As String

Get

Return viewstate("ColFieldName")

End Get

Set(ByVal Value As String)

viewstate("ColFieldName") = Value

End Set

End Property

The above property specifies the column name which the cross-tab needs to retrieve for the first top row.

<Category("CrossTab details")> _

Public Property SummaryFieldName() As String

Get

Return viewstate("SummaryFieldName")

End Get

Set(ByVal Value As String)

viewstate("SummaryFieldName") = Value

End Set

End Property

The above property specifies the column name which the cross-tab needs to retrieve for summarizing (or processing) data.

<Category("CrossTab details")> _

Public Property TypeOfOperation() As OperationType

Get

If viewstate("TypeOfOperation") Is Nothing Then

viewstate("TypeOfOperation") = OperationType.Sum

End If

Return viewstate("TypeOfOperation")

End Get

Set(ByVal Value As OperationType)

viewstate("TypeOfOperation") = Value

End Set

End Property

The above property specifies the type of summary that the cross-tab needs to process.

<Category("CrossTab details")> _

Public Property AdditionalBlankColumns() As Int16

Get

Return Val(viewstate("AdditionalBlankColumns") & "")

End Get

Set(ByVal Value As Int16)

viewstate("AdditionalBlankColumns") = Value

End Set

End Property

The above property may be necessary if you want a few more columns at the right most level for further calculations at the web form level.

The core database routines

Following are the core database routines I used to work seamlessly with tables or stored procedures. Let us have a look at the first one:

Private Function getRowValue(ByVal SQL As String) As String

Try

Dim cmd As New SqlCommand(SQL, New SqlConnection
(getConnectionString))

cmd.Connection.Open()

Dim v As String = cmd.ExecuteScalar & ""

cmd.Connection.Close()

cmd.Dispose()

Return v

Catch ex As Exception

Throw New Exception(ex.Message & ": " & SQL)

End Try

End Function

The above method simply returns a single value based on the SELECT statement we send to it.

Private Function getDataTable(ByVal SQL As String) As DataTable

Dim da As New SqlDataAdapter(SQL, New SqlConnection
(getConnectionString))

Dim dt As New DataTable

da.Fill(dt)

da.Dispose()

Return dt

End Function

The above method simply returns a set of rows in the form of a data table based on the SELECT statement we send to it.

Private Function getConnectionString() As String

Return System.Configuration.ConfigurationSettings.AppSettings.Get
("ConnectionString")

End Function

I used the above method to simply grab the connection string from the web.config file.

The core database routines for working with stored procedures: the parameter cache class

To work with stored procedures, I developed my own class to hold all the parameters (or parameter cache). The following is the complete source code for the same class. You can even use the class to pass the values between several tiers, as I included the concept of serialization along with the class. You can have a look at it.

<XmlRoot("MSSQLProcParameter")> _

Public Class MSSQLProcParameter

<XmlElement("ParameterName", GetType(String))> _

Public ParameterName As String

<XmlElement("ParameterValue", GetType(Object))> _

Public ParameterValue As Object

<XmlElement("ParameterDataType", GetType(SqlDbType))> _

Public ParameterDataType As SqlDbType

<XmlElement("ParameterSize", GetType(Integer))> _

Public ParameterSize As Integer

<XmlElement("ParameterDirectionUsed", GetType
(ParameterDirection))> _

Public ParameterDirectionUsed As ParameterDirection

Public Sub New()

End Sub

Public Sub New(ByVal passedParameterName As String, _

ByVal passedValue As Object, _

Optional ByVal passedSQLType As SqlDbType = Nothing,
_

Optional ByVal passedSize As Integer = Nothing, _

Optional ByVal passedDirection As ParameterDirection
= ParameterDirection.Input)

ParameterName = passedParameterName

ParameterValue = passedValue

ParameterDataType = passedSQLType

ParameterSize = passedSize

ParameterDirectionUsed = passedDirection

End Sub

Public Function getSQLParameter() As SqlParameter

Dim returnSQLParameter As SqlParameter = New SqlParameter

returnSQLParameter.ParameterName = ParameterName

returnSQLParameter.Value = ParameterValue

returnSQLParameter.SqlDbType = ParameterDataType

returnSQLParameter.Size = ParameterSize

returnSQLParameter.Direction = ParameterDirectionUsed

Return returnSQLParameter

End Function

End Class

The class is mainly used to hold the parameters of stored procedures. We would use this class with both of the methods involved in accessing the stored procedures.

The upcoming article shall extend this article to complete it. Sign up for the newsletter to get notified when the next article gets published. Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com