Dynamic Datagrid Series - 2.Formatting Columns
Requirement 2
=============
Give the Datagrid a decent User Interface with Appropriate Headers, Footers and Formatted Columns.
Let's create a new ASP.NET Web Application under Visual Basic Projects called DataGridDemo2.
Add the following lines just after your <configuration> tag in the web.config. I will be working with the "pubs" database all the time. Better to have it in the web.config.
<appSettings>
<add key="ConnectionInfo" value="server=(local);database=Pubs;user id=sa;password="/>
</appSettings>
1) Create a new Page called "FormattedDataGrid.aspx"
2) Drag and drop a PlaceHolder control and change the ID to "PlaceHolder". Go to the code behind of this page and delete everything. Now paste the following...
Imports System.Data
Imports System.Data.SqlClient
'
Public Class FormattedDataGrid
Inherits System.Web.UI.Page
'
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Dim dg As New DataGrid()
Dim strConn As String = ConfigurationSettings.AppSettings("ConnectionInfo")
Dim strCommand As String = "select top 10 au_lname as Last_Name," & _
" au_fname as First_Name, phone as Phone_Number, state as State, " & _
"12345678 as Dummy_Number, 1234.56789 as Dummy_Currency, " & _
"GetDate() as Dummy_Date from authors"
Protected WithEvents PlaceHolder As System.Web.UI.WebControls.PlaceHolder
'
Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs _
) Handles MyBase.Load
'Put user code to initialize the page here
Dim tblData As DataTable
tblData = GetData(strCommand, strConn)
dg.DataSource = tblData
dg.AutoGenerateColumns = False
FormatColumns(tblData)
dg.DataBind()
PlaceHolder.Controls.Add(dg)
FormatDataGrid()
End Sub
'
Private Sub FormatDataGrid()
'
dg.CellPadding = 5
dg.BorderColor = Color.Black
'Set Font settings
dg.Font.Name = "Arial"
dg.Font.Size = New FontUnit(10)
'Show Header and Footer
dg.ShowHeader = True 'Default is true
dg.ShowFooter = True 'Default is false
'Set Header Style
dg.HeaderStyle.Font.Bold = True
dg.HeaderStyle.BackColor = Color.DarkGray
dg.HeaderStyle.ForeColor = Color.Black
dg.HeaderStyle.HorizontalAlign = HorizontalAlign.Center
dg.HeaderStyle.VerticalAlign = VerticalAlign.Middle
'Set Footer Style
dg.FooterStyle.Font.Bold = True
dg.FooterStyle.BackColor = Color.LightGray
dg.FooterStyle.ForeColor = Color.White
dg.FooterStyle.BackColor = Color.Black
'Set Item Style
dg.ItemStyle.BackColor = Color.Cyan
dg.ItemStyle.ForeColor = Color.Black
'Set Alternating Item Style
dg.AlternatingItemStyle.BackColor = Color.Beige
dg.AlternatingItemStyle.ForeColor = Color.Black
End Sub
'
Private Sub FormatColumns(ByRef tblData As DataTable)
'
Dim colDataColumn As DataColumn
For Each colDataColumn In tblData.Columns()
dg.Columns.Add(CreateBoundColumns(colDataColumn))
Next
dg.Columns(0).FooterText = tblData.Rows.Count() & " Records"
End Sub
'
Private Function GetData(ByVal strCommand As String, _
ByVal strConn As String _
) As DataTable
'
Dim adpSQLAdapter As New SqlDataAdapter(strCommand, strConn)
Dim tblData As New DataTable()
'
adpSQLAdapter.Fill(tblData)
Return tblData
End Function
'
Private Function CreateBoundColumns( _
ByRef colDataColumn As DataColumn _
) As DataGridColumn
'
Dim bndColumn As New BoundColumn()
'
bndColumn.DataField = colDataColumn.ColumnName
bndColumn.HeaderText = colDataColumn.ColumnName.Replace("_", " ")
bndColumn.DataFormatString = SetFormatString(colDataColumn)
Return bndColumn
End Function
'
Private Function SetFormatString( _
ByRef colDataColumn As DataColumn _
) As String
'
Dim strDataType As String
'
Select Case colDataColumn.DataType.ToString()
Case "System.Int32"
strDataType = "{0:#,###}"
Case "System.Decimal"
strDataType = "{0:c}"
Case "System.DateTime"
strDataType = "{0:dd-mm-yyyy}"
Case "System.String"
strDataType = ""
Case Else
strDataType = ""
End Select
Return strDataType
End Function
End Class
3) Open Solution Explorer, right click on "FormattedDataGrid.aspx" and select "Set as Start Page" in the menu
4) Click on Debug -> Start and you should be able to see a formatted table of Data from the Database.
