Solved

auto select database and tables on the fly

Posted on 2009-04-06
4
520 Views
Last Modified: 2013-12-17
I have a working page that I have hard coded database and tables to be selected and display the data. I would like to be able to select a database and then a corresponding table on the fly from the following path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

<%@ Page Language="C#"%>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">

    public DataView Source;

    public DataSet ds;

    public bool getSchema, getData;

    string selectString = "";

        string[][] tables = new string[][]

            {

               new string[] {"authors", "discounts", "employee", "jobs", "publishers", "sales", "stores", "titleauthor", "titles", "categories", "AspNet_SqlCacheTablesForChangeNotification", "Products", "ShoppingCarts", "Orders", "OrderItems"},

               new string[] {"Categories", "Customers", "Employees", "Orders", "Products", "Region", "Shippers", "Suppliers", "Territories"},

               new string[] {"AspNet_CacheTableForChangeNotification", "Categories", "Products", "ShoppingCarts", "Orders", "OrderItems"}, 

            };

        public void Page_Load(object sender, EventArgs e)

        {

            if ( !IsPostBack )

            {

                database_SelectedIndexChanged(database, null);

            }

            Init();

        }

        public void Init()

        {

            selectString = "select * from " + table.SelectedItem.Text;

            query.Text = selectString;

            db.Text = database.SelectedItem.Text;

            String otherString = db.Text;

            String anotherString = table.SelectedItem.Text;

            string valueForTesting = "";

            string valueForTesting1 = "";

            string valueForTesting2 = "";

            string valueForTesting3 = "";

            if ("Pubs" == otherString && "authors" == anotherString)

                {

                    valueForTesting = "defaultselect1";

                    Session["Testing"] = valueForTesting;

                    valueForTesting1 = "defaultselect2";

                    Session["Testing1"] = valueForTesting1;

                    valueForTesting2 = "defaultselect3";

                    Session["Testing2"] = valueForTesting2;

                    valueForTesting3 = "defaultselect4";

                    Session["Testing3"] = valueForTesting3;

                }

        }

        public void database_SelectedIndexChanged(Object sender, EventArgs e)

        {

            table.DataSource = tables[database.SelectedIndex];

            table.DataBind();

            Init();

        }

        public void table_SelectedIndexChanged(Object sender, EventArgs e)

        {

            Init();

        }

        public void Submit_Click(Object sender, EventArgs evt) {

            if (IsPostBack)

            {

                if (table.SelectedIndex > -1 && database.SelectedIndex > -1)

                {

                    /*      

                    

                    */

                    string MyString = System.Net.Dns.GetHostName();

                    string otherString = ('#' + MyString + '#');

                    if ('#' + "fuertemon" + '#' == otherString)

                    {

                        if (database.SelectedIndex == 0)

                        {

                            SqlConnection myConnection = new SqlConnection("server=FUERTEMON;database=pubs;User ID=webusertest;Password=h6web97");

                            SqlDataAdapter myCommand = new SqlDataAdapter(selectString, myConnection);

                            ds = new DataSet();

                            myCommand.Fill(ds, "Table");

                            Source = new DataView(ds.Tables[0]);

                            MyDataGrid.DataSource = Source;

                            MyDataGrid.DataBind();

                        }

                        if (database.SelectedIndex == 1)

                        {

                            SqlConnection myConnection = new SqlConnection("server=FUERTEMON;database=Northwind;User ID=webusertest;Password=h6web97");

                            SqlDataAdapter myCommand = new SqlDataAdapter(selectString, myConnection);

                            ds = new DataSet();

                            myCommand.Fill(ds, "Table");

                            Source = new DataView(ds.Tables[0]);

                            MyDataGrid.DataSource = Source;

                            MyDataGrid.DataBind();

                        }

                        if (database.SelectedIndex == 2)

                        {

                            SqlConnection myConnection = new SqlConnection("server=FUERTEMON;database=StoreDB;User ID=webusertest;Password=h6web97");

                            SqlDataAdapter myCommand = new SqlDataAdapter(selectString, myConnection);

                            ds = new DataSet();

                            myCommand.Fill(ds, "Table");

                            Source = new DataView(ds.Tables[0]);

                            MyDataGrid.DataSource = Source;

                            MyDataGrid.DataBind();

                        }

                    }

                }       

            }

         }

    </script>

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>SchemaCS</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

   <table border="0" cellpadding="5" style="font:10pt verdana">

        <tr>

          <td>

            <b>Table Properties</b><br />

            Database:<br/>

            Table:

          </td>

          <td style="width: 94px">

            <br />

            <asp:DropDownList

             runat="server"

             id="database"

             AutoPostBack="true"

             OnSelectedIndexChanged="database_SelectedIndexChanged">

              <asp:ListItem>Pubs</asp:ListItem>

              <asp:ListItem>Northwind</asp:ListItem> 

              <asp:ListItem>StoreDB</asp:ListItem> 

            </asp:DropDownList><br/>

            <asp:DropDownList

             runat="server"

             id="table"

             AutoPostBack="true"

             OnSelectedIndexChanged="table_SelectedIndexChanged" />

          </td>

        </tr>

        <tr>

          <td colspan="2">

            <b>Query:</b>

            <asp:Label runat="server" id="query" />

            <asp:Label runat="server" id="db" />

            <% string valueOfTesting = (Session["Testing"] != null ? Session["Testing"].ToString() : ""); %>

            <a href="./dataGrid/<% =(Session["Testing"]) %>.aspx">Add</a>

            <a href="./dataGrid/<% =(Session["Testing1"])%>.aspx">Edit</a>

            <a href="./dataGrid/<% =(Session["Testing2"])%>.aspx">Delete</a>

            <a href="./dataGrid/<% =(Session["Testing3"])%>.aspx">Sort</a>

          </td>

          </tr>

        <tr>

          <td valign="top">

            <input id="Submit1" type="submit" runat="server"  onserverclick = "Submit_Click"/>

          </td>

        </tr>

        <tr>

          <td colspan="2">

              <%  if (Page.IsPostBack && ds != null) {  %>

                  <b>Data:</b><br/>

              <%  }  %>

              <ASP:DataGrid id="MyDataGrid"

                BackColor="#EDBE7B"

                BorderColor="black"

                ShowFooter="false"

                CellPadding="3"

                cellspacing="0"

                Font-Names="Verdana"

                Font-Size="8pt"

                HeaderStyle-BackColor="#DC6035"

                EnableViewState="false"

                runat="server"/>

          </td>

        </tr>

        </table>	

    </div>

    </form>

</body>

</html>

Open in new window

0
Comment
Question by:homeshopper
  • 2
  • 2
4 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24084858
Question not very clear. Do you want the user to be able to select the database and the table they want to see? If so, Merge the db name with the connection string and then replace the tablename with the name supplied by user. For example

dim dbcon as new oledbconnection(connectionstring & txtdb.text)
dbcon.open
dim dbcmd as new oledbcommand("Select * from " & txtTableName.text)
0
 

Author Comment

by:homeshopper
ID: 24085229
sorry about the clarity. I have about 20 sql databases residing at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data and do not really want to hard code all the table info from the dbases.
I would first like to be able to sectect a database.
Once selected, fill a dropdown list of available tables in that database.
The query would then display the table data.
I tried to work out a function using schema, but not sure of the prammeters available.
Is there a simpler way of achieving this?
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 24085261
0
 

Author Comment

by:homeshopper
ID: 24180527
Dear CodeCruiser,
I have converted all my code to vb.net.
The table at the moment are hard coded to tables()
Private tables()() As String = { New String() {"authors", "discounts", "employee", "jobs", "publishers", "sales", "stores", "titleauthor", "titles", "categories", "AspNet_SqlCacheTablesForChangeNotification", "Products", "ShoppingCarts", "Orders", "OrderItems"}, New String() {"Categories", "Customers", "Employees", "Orders", "Products", "Region", "Shippers", "Suppliers", "Territories"}, New String() {"AspNet_CacheTableForChangeNotification", "Categories", "Products", "ShoppingCarts", "Orders", "OrderItems"} }
I wanted to pull out the tables from a given selected database.
My initial code follows at the end of this comment.
To the initial code I made the following alterations:
I do not think I have dimensioned some values correctly and am getting 7 errors:
------------------------------------------------------------------------------------------------
Dim TablesSchema As DataTable = objConn.OpenSchema()
'OpenSchema' is not a member of 'String' C:\Projects\Web\ServerOnLine\dbaseCMS\DefaultPopulateVB.aspx
------------------------------------------------------------------------------------------------
Do While Not TablesSchema.EOF
'EOF' is not a member of 'System.Data.DataTableC:\Projects\Web\ServerOnLine\dbaseCMS\DefaultPopulateVB.aspx
------------------------------------------------------------------------------------------------
If Left(TablesSchema("TABLE_NAME"), 4) = "xml_" Then ready = True
Class 'System.Data.DataTable' cannot be indexed because it has no default property.C:\Projects\Web\ServerOnLine\dbaseCMS\DefaultPopulateVB.aspx
------------------------------------------------------------------------------------------------
 If ready And Left(TablesSchema("TABLE_NAME"), 4) <> "xml_" Then started = True
Class 'System.Data.DataTable' cannot be indexed because it has no default property.C:\Projects\Web\ServerOnLine\dbaseCMS\DefaultPopulateVB.aspx
------------------------------------------------------------------------------------------------
tables(i) = TablesSchema("TABLE_NAME")
Class 'System.Data.DataTable' cannot be indexed because it has no default property.C:\Projects\Web\ServerOnLine\dbaseCMS\DefaultPopulateVB.aspx
------------------------------------------------------------------------------------------------
TablesSchema.MoveNext()
'MoveNext' is not a member of 'System.Data.DataTable'.C:\Projects\Web\ServerOnLine\dbaseCMS\DefaultPopulateVB.aspx
------------------------------------------------------------------------------------------------
<%Dim valueOfTesting As String = (If(Session("Testing") IsNot Nothing, Session("Testing").ToString(), "")) %>
Expression expected. C:\Projects\Web\ServerOnLine\dbaseCMS\DefaultPopulateVB.aspx
------------------------------------------------------------------------------------------------

<%@ Page Language="VB"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
    Public Source As DataView
      Public ds As DataSet
      Public getSchema, getData As Boolean
      Private selectString As String = ""
    'Private tables()() As String = { New String() {"authors", "discounts", "employee", "jobs", "publishers", "sales", "stores", "titleauthor", "titles", "categories", "AspNet_SqlCacheTablesForChangeNotification", "Products", "ShoppingCarts", "Orders", "OrderItems"}, New String() {"Categories", "Customers", "Employees", "Orders", "Products", "Region", "Shippers", "Suppliers", "Territories"}, New String() {"AspNet_CacheTableForChangeNotification", "Categories", "Products", "ShoppingCarts", "Orders", "OrderItems"} }
   
    Private tables()() As String
    Dim objConn As String
    Dim OpenSchema As DataTable
    Dim ready As Boolean
    Dim started As Boolean
    Dim TablesSchema As DataTable = objConn.OpenSchema()
   
    Public Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        If (Not IsPostBack) Then
            database_SelectedIndexChanged(database, Nothing)
            Dim i As Integer = 0
            Do While Not TablesSchema.EOF
                If Left(TablesSchema("TABLE_NAME"), 4) = "xml_" Then ready = True
                If ready And Left(TablesSchema("TABLE_NAME"), 4) <> "xml_" Then started = True
                If Started Then
                    i = i + 1
                    tables(i) = TablesSchema("TABLE_NAME")
                End If
                TablesSchema.MoveNext()
            Loop
               
                End If
                Init()
    End Sub

The original initial working code is as attached code snippet
<%@ Page Language="VB"%>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">

    Public Source As DataView

	Public ds As DataSet

	Public getSchema, getData As Boolean

	Private selectString As String = ""

	Private tables()() As String = { New String() {"authors", "discounts", "employee", "jobs", "publishers", "sales", "stores", "titleauthor", "titles", "categories", "AspNet_SqlCacheTablesForChangeNotification", "Products", "ShoppingCarts", "Orders", "OrderItems"}, New String() {"Categories", "Customers", "Employees", "Orders", "Products", "Region", "Shippers", "Suppliers", "Territories"}, New String() {"AspNet_CacheTableForChangeNotification", "Categories", "Products", "ShoppingCarts", "Orders", "OrderItems"} }

	

    Public Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

        If (Not IsPostBack) Then

            database_SelectedIndexChanged(database, Nothing)

        End If

        Init()

    End Sub

		

		Public Sub Init()

			selectString = "select * from " & table.SelectedItem.Text

			query.Text = selectString

			db.Text = database.SelectedItem.Text

			Dim otherString As String = db.Text

			Dim anotherString As String = table.SelectedItem.Text

			Dim valueForTesting As String = ""

			Dim valueForTesting1 As String = ""

			Dim valueForTesting2 As String = ""

			Dim valueForTesting3 As String = ""

			

			If "Pubs" = otherString AndAlso "authors" = anotherString Then

					valueForTesting = "defaultselect1"

					Session("Testing") = valueForTesting

					valueForTesting1 = "defaultselect2"

					Session("Testing1") = valueForTesting1

					valueForTesting2 = "defaultselect3"

					Session("Testing2") = valueForTesting2

					valueForTesting3 = "defaultselect4"

					Session("Testing3") = valueForTesting3

			End If

		End Sub

		

		Public Sub database_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)

			table.DataSource = tables(database.SelectedIndex)

			table.DataBind()

			Init()

		End Sub

		

		Public Sub table_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)

			Init()

		End Sub

		

		Public Sub Submit_Click(ByVal sender As Object, ByVal evt As EventArgs)

			If IsPostBack Then

				If table.SelectedIndex > -1 AndAlso database.SelectedIndex > -1 Then

					Dim MyString As String = System.Net.Dns.GetHostName()

                Dim otherString As String = ("#" & MyString & "#")

                If "#" & "fuertemon" & "#" = otherString Then

                    If database.SelectedIndex = 0 Then

                        Dim myConnection As New SqlConnection("server=FUERTEMON;database=pubs;User ID=sa;Password=h6web97")

                        Dim myCommand As New SqlDataAdapter(selectString, myConnection)

                        ds = New DataSet()

                        myCommand.Fill(ds, "Table")

                        Source = New DataView(ds.Tables(0))

                        MyDataGrid.DataSource = Source

                        MyDataGrid.DataBind()

                    End If

                    If database.SelectedIndex = 1 Then

                        Dim myConnection As New SqlConnection("server=FUERTEMON;database=Northwind;User ID=sa;Password=h6web97")

                        Dim myCommand As New SqlDataAdapter(selectString, myConnection)

                        ds = New DataSet()

                        myCommand.Fill(ds, "Table")

                        Source = New DataView(ds.Tables(0))

                        MyDataGrid.DataSource = Source

                        MyDataGrid.DataBind()

                    End If

                    If database.SelectedIndex = 2 Then

                        Dim myConnection As New SqlConnection("server=FUERTEMON;database=StoreDB;User ID=sa;Password=h6web97")

                        Dim myCommand As New SqlDataAdapter(selectString, myConnection)

                        ds = New DataSet()

                        myCommand.Fill(ds, "Table")

                        Source = New DataView(ds.Tables(0))

                        MyDataGrid.DataSource = Source

                        MyDataGrid.DataBind()

                    End If

                End If

				End If

			End If

		End Sub

	</script>

<html xmlns="http://www.w3.org/1999/xhtml" >

<head id="Head1" runat="server">

    <title>SchemaCS</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

   <table border="0" cellpadding="5" style="font:10pt verdana">

        <tr>

          <td>

            <b>Table Properties</b><br />

            Database:<br/>

            Table:

          </td>

          <td style="width: 94px">

            <br />

            <asp:DropDownList

             runat="server"

             id="database"

             AutoPostBack="true"

             OnSelectedIndexChanged="database_SelectedIndexChanged">

              <asp:ListItem>Pubs</asp:ListItem>

              <asp:ListItem>Northwind</asp:ListItem> 

              <asp:ListItem>StoreDB</asp:ListItem> 

            </asp:DropDownList><br/>

            <asp:DropDownList

             runat="server"

             id="table"

             AutoPostBack="true"

             OnSelectedIndexChanged="table_SelectedIndexChanged" />

          </td>

        </tr>

        <tr>

          <td colspan="2">

            <b>Query:</b>

            <asp:Label runat="server" id="query" />

            <asp:Label runat="server" id="db" />

            <%Dim valueOfTesting As String = (If(Session("Testing") IsNot Nothing, Session("Testing").ToString(), "")) %>

            <a href="./<%=(Session("Testing")) %>.asp">Add</a>

            <a href="./<%=(Session("Testing1")) %>.asp">Edit</a>

            <a href="./<%=(Session("Testing2")) %>.asp">Delete</a>

            <a href="./<%=(Session("Testing3")) %>.asp">Sort</a>

          </td>

          </tr>

        <tr>

          <td valign="top">

            <input id="Submit1" type="submit" runat="server"  onserverclick = "Submit_Click"/>

          </td>

        </tr>

        <tr>

          <td colspan="2">

              <%  If (Page.IsPostBack AndAlso ds IsNot Nothing) Then%>

                  <b>Data:</b><br/>

              <%  end if  %>

              <ASP:DataGrid id="MyDataGrid"

                BackColor="#EDBE7B"

                BorderColor="black"

                ShowFooter="false"

                CellPadding="3"

                cellspacing="0"

                Font-Names="Verdana"

                Font-Size="8pt"

                HeaderStyle-BackColor="#DC6035"

                EnableViewState="false"

                runat="server"/>

          </td>

        </tr>

        </table>	

    </div>

    </form>

</body>

</html>

Open in new window

0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now