Requirement 2
=============
Give the Datagrid a decent User Interface with Appropriate Headers and Formatted Columns created dynamically.
Let's create a new ASP.NET Web site.
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. Ensure that you don't have multiple <appSettings> tag in your web.config, else the application won't compile at all.
<appSettings>
<add key="ConnectionInfo" value="server=(local);database=Pubs;user id=sa;password=Type_Password_here"/>
</appSettings>
1) Create a new Page called "FormattedDataGrid.aspx"
2) Ensure that Language="Visual Basic" and "Place Code in Sepearte file" checkbox is unchecked.
3) Drag and drop a Placeholder control on the form and paste the following in the "Source View"...
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Drawing" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Dim gvGrid As GridView
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"
Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs _
) Handles MyBase.Load
gvGrid = New GridView()
Dim tblData As DataTable
tblData = GetData(strCommand, strConn)
gvGrid.DataSource = tblData
gvGrid.AutoGenerateColumns = False
FormatColumns(tblData)
gvGrid.DataBind()
PlaceHolder1.Controls.Add(gvGrid)
FormatDataGrid()
End Sub
'
Private Sub FormatDataGrid()
'
gvGrid.CellPadding = 5
gvGrid.BorderColor = Color.Black
'Set Font settings
gvGrid.Font.Name = "Arial"
gvGrid.Font.Size = New FontUnit(10)
'Show Header and Footer
gvGrid.ShowHeader = True 'Default is true
'Set Header Style
gvGrid.HeaderStyle.Font.Bold = True
gvGrid.HeaderStyle.BackColor = Color.DarkGray
gvGrid.HeaderStyle.ForeColor = Color.Black
gvGrid.HeaderStyle.HorizontalAlign = HorizontalAlign.Center
gvGrid.HeaderStyle.VerticalAlign = VerticalAlign.Middle
'Set Item Style
gvGrid.RowStyle.BackColor = Color.Cyan
gvGrid.RowStyle.ForeColor = Color.Black
'Set Alternating Item Style
gvGrid.AlternatingRowStyle.BackColor = Color.Beige
gvGrid.AlternatingRowStyle.ForeColor = Color.Black
End Sub
'
Private Sub FormatColumns(ByRef tblData As DataTable)
'
Dim colDataColumn As DataColumn
For Each colDataColumn In tblData.Columns()
gvGrid.Columns.Add(CreateBoundColumns(colDataColumn))
Next
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 BoundField
'
Dim bndColumn As New BoundField()
bndColumn.DataField = colDataColumn.ColumnName
bndColumn.HtmlEncode = False
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
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Format column on a dynamically added and bound GridView</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:PlaceHolder ID="PlaceHolder1" runat="server"></asp:PlaceHolder>
</div>
</form>
</body>
</html>
4) Open Solution Explorer, right click on "FormattedDataGrid.aspx" and select "Set as Start Page" in the menu
5) Click on Debug -> Start and you should be able to see a table of formatted data rows and columns from the Database.