WPF/SQL - how to get tables from a selected database into a ListView

Question

Good evening,

i'm at the beginning of an IT apprenticeship and i've started my own little project. I've got following problem:

I've created in WPF a standard Login Window for getting SQL Connection Datas. This Window is working fine and i only can switch to the next window when the entered Login-Datas are correct. My problem is the second window. In the second one i have one ListBox for listing every Database from the connected Server. Next to this ListBox i've a ListView. In this ListView i want to see the tables from the selected Database.

Code for listing Databases:

SqlConnection GetConnection = new SqlConnection("Server=admin-pc;user Id=sa;Password=123;");
try
{
   this.libDatabase.Items.Clear();
   DataTable databases = new DataTable("Databases");

   using (IDbConnection connection = GetConnection)
   {
      IDbCommand command = connection.CreateCommand();
      command.CommandText = "SELECT * FROM sys.Databases";
      connection.Open();
      databases.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
   }
   this.libDatabase.Items.Clear();

   foreach (DataRow row in databases.Rows)
        this.libDatabase.Items.Add(row[0].ToString());
   }
   catch (SqlException)
   {
      this.libDatabase.Items.Clear();
      this.libDatabase.Items.Add("Connection error");
   }
   catch (Exception ex)
   {
      MessageBox.Show("Error while loading available databases");
   }
   finally
   {
      GetConnection.Close();
   }

libDatabase is the name of the ListBox.

This Code is working good and every Database is shown in my ListBox. But no i've a totally Blackout. How do i get the Tables from the selected Database into the ListView? I've tried the same way like i did with the Databases but with a different "SELECT"-Statement ==> "select * from {0}", listbox.SelectedItem Here are different Codes i've tried but i think that i do something completely wrong.

First Version:

            List<ListViewItem> gettables = new List<ListViewItem>();
        if (libDatabase.SelectedItem != null)
        {
            SqlConnection con = new SqlConnection("Server=admin-pc;user Id=sa;Password=123;");
            string strSQL = string.Format("select * from {0}", libDatabase.SelectedItem);

            SqlCommand cmd = new SqlCommand(strSQL, con);

            con.Open();

            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {

            }


            reader.Close();
            con.Close();
        }

Second Version:

SqlConnection GetConnection = new SqlConnection("Server=admin-pc;user Id=sa;Password=123;");
        try
        {
            this.libTables.Items.Clear();
            DataTable tables = new DataTable("Tables");
            using (IDbConnection connection = GetConnection)
            {
                IDbCommand command = connection.CreateCommand();
                if (libDatabase.SelectedItem != null)
                {
                    command.CommandText = string.Format("SELECT * FROM {0}", libDatabase.SelectedItem);
                    connection.Open();
                    tables.Load(command.ExecuteReader());
                }
            }
            this.libTables.Items.Clear();
            foreach (DataRow row in tables.Rows)
                this.libTables.Items.Add(row[0].ToString());
        }
        catch (SqlException)
        {
            this.libTables.Items.Clear();
            this.libTables.Items.Add("Connection error. Check server");
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error while loading available tables" + ex);
        }
        finally
        {
            GetConnection.Close();
        }

libTables is the name of the ListView.

Maybe i did it completely wrong or there's just one code block false.

Thank you for supporting.

Dave S.

SWalters · Accepted Answer · 2013-11-12 16:14:12Z

1

Try this select statement instead:

               command.CommandText = string.Format("SELECT * FROM {0}.sys.tables", libDatabase.SelectedItem);

As the database names are to be found in sys.databases within master, so too the tables are to be found within sys.tables for each database.

It pays to step through code of this nature with a debugger, and/or put a breakpoint at each meaningful location. Verify each sql statement built in the code is exactly what you intended, and execute each statement within Sql Management studio using the same permissions under which it is being executed within your code, to ensure the statement will produce the results you want.

Edit for triggering the table load:

You must also ensure the table-loading process is triggered by the appropriate action. In this case, it would most likely need to be triggered by the user selecting a database within the database listbox. You'll need to add an event handler for the SelectionChanged event to the listbox. (Note that I'm assuming you're not using the MVVM pattern based on the code samples you included in your question.)

Add the SelectionChanged attribute to your ListBox definition:

<ListBox Name="libDatabase" SelectionChanged="libDatabase_SelectionChanged">

And in the codebehind, define the event handler:

private void libDatabase_SelectionChanged(object sender, System.Windows.Controls.SelectionChangedEventArgs e)
{
    if(libDatabase.SelectedItem != null)
    {
        // open a database connection and select your tables here.
    }
}

edited Nov 12, 2013 at 16:14

answered Nov 12, 2013 at 14:52

SWalters

3,7145 gold badges32 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Dave Stockinger Over a year ago

Sorry but this isn't working either! I think there's a problem with libDatabase.SelectedItem?? Or should i put this code block in the same method as the database code block is?? It's the window_loaded code block. But i don't want to see the tables when the window is loaded. Only when a database is selected.

SWalters Over a year ago

It's not clear where you have the second chunk of code (the table loading process). From what you have stated, it sounds like you would want the tables loading in response to the database listbox's SelectionChanged event. Is this where the table-loading code is?

Dave Stockinger Over a year ago

in private void Window_Loaded i have the database list loaded because when the window opens the databases should be in the listbox. In private void livTables_SelectionChanged i want to load the table list from the selected database.

SWalters Over a year ago

I've modified my answer to include a section on the libDatabase_SelectionChanged event you'll need. You should load the tables in reaction to a database selection change occurring, not a table selection occurring. It sounds like you've got it hooked up to the SelectionChanged event for the wrong control.

Dave Stockinger Over a year ago

Oh ok now i know! I have the SelectionChanged="lbTodoList_SelectionChanged not in the ListBox tag but in the ListView tag. Maybe that's my problem.

|

Collectives™ on Stack Overflow

WPF/SQL - how to get tables from a selected database into a ListView

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related