Extending an ASP.NET Datagrid to Support Simple Cross Tab Reporting: The Source Code
(Page 1 of 4 )
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 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”.
Next: The core database routines to work with stored procedures: “SPgetDataTable” method >>
More ASP.NET Articles
More By Jagadish Chaterjee