Solved

auto select database and tables on the fly

Posted on 2009-04-06
4
529 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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