Friday, November 30, 2007 9:33 AM
rrobbins
Generate A Data Dictionary Using ASP.NET
I hate having to open SQL Server or Microsoft Access just to look up a table or column name. Therefore I usually print out a data dictionary for future reference. Of course, I could just use Server Explorer in Visual Studio but I am not in the habit of using that (possibly because Server Explorer is not descriptive of what it actually does).
I've been referring to my data dictionaries as "database design" but I've checked Wikipedia and I guess database schema would be a better term for your data model and data dictionary is the correct term for documentation. I know phpMyAdmin allows you to generate this kind of information about your database and phpMyAdmin calls it a data dictionary.
Anyway, here is the ASP.NET code I use to generate my data dictionaries. The only interesting thing about the code is that it dynamically generates datagrids for each table.
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="data_dictionary.aspx.vb" Inherits="data_dictionary"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>Data Dictionary</title>
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
<meta name="author" content="Robert S. Robbins">
</HEAD>
<body bgcolor="#FFFFFF">
<h1 style="font-family:Arial;">Data Dictionary</h1>
<hr noshade color="#000000">
<font face="Arial">The purpose of this script is to document the database schema.</font>
<br><br>
<form id="Form1" method="post" runat="server">
<asp:panel id="pnlErrorMessage" Visible="False" Runat="server">
<BR>
<asp:Label id="lblErrorMessage" Runat="server" Visible="False" ForeColor="#cc0000" Font-Name="Arial"></asp:Label>
</asp:panel>
</form>
</body>
</HTML>
Imports System.Data.SqlClient
Imports System.Data
Imports System.Drawing
Public Class data_dictionary
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
Protected WithEvents lblErrorMessage As System.Web.UI.WebControls.Label
Protected WithEvents pnlErrorMessage As System.Web.UI.WebControls.Panel
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
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
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
Try
'Create a connection string
Dim connString As String
connString = ConfigurationSettings.AppSettings("ConnectionString")
'Open a connection
Dim objConnection As New SqlConnection
objConnection.ConnectionString = connString
objConnection.Open()
'Specify the SQL string
Dim strSQL As String = "SELECT table_name FROM information_schema.tables " & _
"WHERE table_catalog = 'YOUR_DATABASE_NAME_HERE' " & _
"AND NOT table_name = 'dtproperties' " & _
"AND NOT table_name = 'sysconstraints' " & _
"AND NOT table_name = 'syssegments' " & _
"ORDER BY table_name;"
Dim objCmd As IDbCommand = objConnection.CreateCommand()
objCmd.CommandText = strSQL
Dim objReader As IDataReader = objCmd.ExecuteReader()
Do While objReader.Read()
CreateGrid(objReader("table_name").ToString())
Loop
objReader.Close()
objConnection.Close()
Catch ex As Exception
pnlErrorMessage.Visible = True
lblErrorMessage.Text = ex.Message & "<br>Error loading data from database."
lblErrorMessage.Visible = True
End Try
End Sub
Public Sub CreateGrid(ByVal table_name As String)
Dim DataGrid1 As New DataGrid
Dim DataGridSpacer As New System.Web.UI.WebControls.Panel
Dim DataGridHeader As New System.Web.UI.WebControls.Label
DataGrid1.ShowHeader = True
DataGrid1.AutoGenerateColumns = False
DataGrid1.CellPadding = 3
DataGrid1.HeaderStyle.Font.Bold = True
DataGrid1.HeaderStyle.Font.Name = "Arial"
DataGrid1.HeaderStyle.Font.Size = FontUnit.Point(10)
DataGrid1.HeaderStyle.BackColor = ColorTranslator.FromHtml("#ECECEC")
DataGrid1.ItemStyle.Font.Name = "Arial"
DataGrid1.ItemStyle.Font.Size = FontUnit.Point(10)
DataGridSpacer.Height = System.Web.UI.WebControls.Unit.Pixel(20)
DataGridHeader.Text = table_name
DataGridHeader.Font.Bold = True
DataGridHeader.Font.Name = "Arial"
DataGridHeader.Font.Size = FontUnit.Point(10)
Dim datagridcol_1 As New BoundColumn
datagridcol_1.HeaderText = "column_name"
datagridcol_1.DataField = "column_name"
DataGrid1.Columns.Add(datagridcol_1)
Dim datagridcol_2 As New BoundColumn
datagridcol_2.HeaderText = "data_type"
datagridcol_2.DataField = "data_type"
DataGrid1.Columns.Add(datagridcol_2)
Dim datagridcol_3 As New BoundColumn
datagridcol_3.HeaderText = "numeric_precision"
datagridcol_3.DataField = "numeric_precision"
DataGrid1.Columns.Add(datagridcol_3)
Dim datagridcol_4 As New BoundColumn
datagridcol_4.HeaderText = "character_maximum_length"
datagridcol_4.DataField = "character_maximum_length"
DataGrid1.Columns.Add(datagridcol_4)
Dim datagridcol_5 As New BoundColumn
datagridcol_5.HeaderText = "is_nullable"
datagridcol_5.DataField = "is_nullable"
DataGrid1.Columns.Add(datagridcol_5)
Dim datagridcol_6 As New BoundColumn
datagridcol_6.HeaderText = "column_default"
datagridcol_6.DataField = "column_default"
DataGrid1.Columns.Add(datagridcol_6)
Try
'Create a connection string
Dim connString As String
connString = ConfigurationSettings.AppSettings("ConnectionString")
'Open a connection
Dim objConnection As New SqlConnection
objConnection.ConnectionString = connString
objConnection.Open()
'Specify the SQL string
Dim strSQL As String = "SELECT column_name, data_type, numeric_precision, character_maximum_length,is_nullable, column_default FROM information_schema.columns WHERE table_name = '" & Trim(table_name) & "';"
Dim objCmd As IDbCommand = objConnection.CreateCommand()
objCmd.CommandText = strSQL
Dim adapter As New SqlDataAdapter(objCmd)
Dim rs As New DataSet
adapter.Fill(rs)
DataGrid1.DataSource = rs
DataGrid1.DataBind()
Page.Controls(1).Controls.Add(DataGridSpacer)
Page.Controls(1).Controls.Add(DataGridHeader)
Page.Controls(1).Controls.Add(DataGrid1)
objConnection.Close()
Catch ex As Exception
pnlErrorMessage.Visible = True
lblErrorMessage.Text = ex.Message & "<br>Error loading data from database."
lblErrorMessage.Visible = True
End Try
End Sub
End Class