Having moved to SQL Server and .NET from another database one area that's always bugged me is finding out information in table definitions on source and production versions.
Starting off I thought I would just see if I could display the table names and then display the contents of the fields. This code is displayed below and I hope it's of some use to someone.
Obviously there are security issues and I would not suggest you leave the files on server except when you are using them unless you build in your own login security.
The next step is to run two browser windows local and remote. Copy data from one to the other and analyse the differences.
See what you think and see where you can take it.
PS
Don't forget to change the
ConnectionString="<%$ ConnectionStrings:ConnectionString %>" to your own connectionstring text.
The FileDef.aspx is below
Code:
<%@PageLanguage="VB"AutoEventWireup="false"CodeFile="FileDef.aspx.vb"Inherits="FileDef" %>
<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<htmlxmlns="http://www.w3.org/1999/xhtml">
<headrunat="server">
<title>File Definitions</title>
<linkhref="SS.css"rel="stylesheet"type="text/css"/>
</head>
<body>
<formid="form1"runat="server">
<div>
<asp:SqlDataSourceID="SchemaDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables ORDER BY TABLE_NAME">
</asp:SqlDataSource>
<br/>
Table Name
<asp:DropDownList
ID="DropDownList1"runat="server"AppendDataBoundItems="True"
DataSourceID="SchemaDataSource1"
DataTextField="Table_Name"DataValueField="Table_Name"AutoPostBack="True">
<asp:ListItemSelected="True"></asp:ListItem></asp:DropDownList>
Show Full Details <asp:DropDownListID="DropDownList2"runat="server"AutoPostBack="True">
<asp:ListItem>No</asp:ListItem>
<asp:ListItem>Yes</asp:ListItem>
</asp:DropDownList>
<br/>
<br/>
<asp:SqlDataSource
ID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"SelectCommand='SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.Columns WHERE ([Table_Name] = @Table_Name) ORDER BY ORDINAL_POSITION ASC '>
<SelectParameters>
<asp:QueryStringParameterName="Table_Name"QueryStringField="TN"Type="String"/>
</SelectParameters>
</asp:SqlDataSource>
<asp:GridViewID="GridView1"runat="server"CellPadding="4"DataSourceID="SqlDataSource2"
ForeColor="#333333"GridLines="None"AllowSorting="True"Visible="False">
<RowStyleBackColor="#E3EAEB"/>
<FooterStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>
<PagerStyleBackColor="#666666"ForeColor="White"HorizontalAlign="Center"/>
<SelectedRowStyleBackColor="#C5BBAF"Font-Bold="True"ForeColor="#333333"/>
<HeaderStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>
<EditRowStyleBackColor="#7C6F57"/>
<AlternatingRowStyleBackColor="White"/>
</asp:GridView>
<br/>
<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"SelectCommand='SELECT * FROM INFORMATION_SCHEMA.Columns WHERE ([Table_Name] = @Table_Name)'>
<SelectParameters>
<asp:QueryStringParameterName="Table_Name"QueryStringField="TN"Type="String"/>
</SelectParameters>
</asp:SqlDataSource><asp:GridView
ID="GridView2"runat="server"DataSourceID="SqlDataSource1"AllowSorting="True"CellPadding="4"ForeColor="#333333"GridLines="None"Visible="False">
<RowStyleBackColor="#F7F6F3"ForeColor="#333333"/>
<FooterStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/>
<PagerStyleBackColor="#284775"ForeColor="White"HorizontalAlign="Center"/>
<SelectedRowStyleBackColor="#E2DED6"Font-Bold="True"ForeColor="#333333"/>
<HeaderStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/>
<EditRowStyleBackColor="#999999"/>
<AlternatingRowStyleBackColor="White"ForeColor="#284775"/>
</asp:GridView>
<br/>
</div>
</form>
</body>
</html>
The code behind is below
Code:
PartialClass FileDef
Inherits System.Web.UI.Page
ProtectedSub DropDownList1_SelectedIndexChanged(ByVal sender AsObject, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
Response.Redirect("FileDef.aspx?TN=" & DropDownList1.SelectedValue.ToString & "&FV=" & DropDownList2.SelectedValue)
EndSub
ProtectedSub DropDownList2_SelectedIndexChanged(ByVal sender AsObject, ByVal e As System.EventArgs) Handles DropDownList2.SelectedIndexChanged
Response.Redirect("FileDef.aspx?TN=" & DropDownList1.SelectedValue.ToString & "&FV=" & DropDownList2.SelectedValue)
EndSub
ProtectedSub Page_Load(ByVal sender AsObject, ByVal e As System.EventArgs) HandlesMe.Load
If Request.QueryString("FV") = "Yes"Then
GridView2.Visible = True
GridView1.Visible = False
Else
GridView2.Visible = False
GridView1.Visible = True
EndIf
If IsPostBack = FalseThen
If Request.QueryString("TN") <> ""Then
DropDownList1.SelectedValue = Request.QueryString("TN")
EndIf
If Request.QueryString("FV") <> ""Then
DropDownList2.SelectedValue = Request.QueryString("FV")
Else
DropDownList2.SelectedValue = "No"
EndIf
EndIf
EndSub
EndClass
FYI: Table information
