locked
Display All Tables from selected SQL Server Database into Combolist RRS feed

  • Question

  • i try to display a list of all tables from one database and display in a combolist using C#. But im getting other tables which was not created previously. The other tables are from which i created few days ago.

    source code:

    prontoconnstring = "Data Source=" + txtProntoDS.Text + "; uid=" + txtProntoUname.Text + "; password=" + txtProntoPwd.Text + "; persist security info=" + checkboxpronto + ";";

                    using (SqlConnection sqlprontosource = new SqlConnection(prontoconnstring))
                    {
                        //string strSQLsource = "Select * FROM " + cbSourceTable.SelectedText.ToString();

                        try
                        {
                            // test connection, if successful then change lblSQLConnSourceStatus to 'Connected' and list all the db tables to cbSourceTable
                            sqlprontosource.Open();
                            lblSQLConnProntoStatus.Text = "Connected";
                           
                            connprontosuccess = "true";

                            string strSQLAllTable3 = "Select DISTINCT(name) FROM sys.Tables";

                            DataSet dsAlltable3 = new DataSet();

                            SqlDataAdapter adapter = new SqlDataAdapter(strSQLAllTable3, sqlprontosource);
                            adapter.Fill(dsAlltable3);

                            for (int i = 0; i < dsAlltable3.Tables[0].Rows.Count; i++)
                            {
                                cbProntoTable.Items.Add(dsAlltable3.Tables[0].Rows[i]["name"].ToString());
                            }

                        }
                        catch (Exception ex)
                        {
                            // while testing connection failed, add exception message to View Log listbox.
                            lstViewLog.Items.Add(ex.Message.ToString());
                            connprontosuccess = "false";

                        }
                        finally
                        {
                            sqlprontosource.Close();
                        }

    Thursday, July 9, 2009 9:26 AM

Answers

All replies


  • Try the following sql query,

    select

     * from sysobjects where xtype = 'U' order by name

    It will show all table of database and sysdiagrams table.


    Dont forget to click “Mark as Answer” on the post that helped you.
    Thursday, July 9, 2009 1:03 PM
  • The best way to do it is to call the GetSchema(string, string[]) method.  But, this is a very unusual method to call.  The parameters more or less form the basis for a query of sorts.  The return object is a datatable.

    http://social.msdn.microsoft.com/forums/en-US/winformsdatacontrols/thread/b169bb10-18de-46db-9c25-b2d499117b5c/

    That is a link to the last remaining thread where I posted a sample of how to use the method.  The threads where I posted that sample program, or even pieces of it, have all been corrupted in some way, shape, fashion, or form.  I have decided to no longer post that code publicly again. 

    Good Luck making it work.  It's all there at the link.  It is just simply garbled up a bit.
    Mark the best replies as answers. "Fooling computers since 1971."
    Thursday, July 9, 2009 1:20 PM

  • The idea to using GetSchema is that you must drill down to the info that you wish.  This means you make an initial call and get a set of results in the form of a datatable.  The results you get will help you to determine the proper parameters for your next call to GetSchema, which will drill down a bit deeper into the database but in a narrower scope.  These results will can be used to drill even deeper if you need to.

    That it for the explanations.  The only way to understand it is to play with it.  One will either "get it" or not.  Like I said, this is one of the most unusual methods that I have seen the entire .NET FCL.  As odd as it may appear, it is actually highly efficient for the amount data that you can return, as well as the degree of control that you impose by using the right combinations of parameters.
     

     
    http://msdn2.microsoft.com/en-us/library/ms254934(VS.80).aspx


    Mark the best replies as answers. "Fooling computers since 1971."
    • Edited by Rudedog2 Thursday, July 9, 2009 3:53 PM
    Thursday, July 9, 2009 1:55 PM
  • DELETED

    Thursday, July 9, 2009 2:18 PM
  • using System;
    using System.Data;
    using System.Data.Sql;
    using System.Data.SqlClient;
    
    using System.Windows.Forms;
    using System.Drawing;
    
    class Sample
    {
     static void Main()
     {
       SqlConnection con = new SqlConnection(@"server=.\SQLExpress;Database=AdventureWorks;Trusted_Connection=yes");
       con.Open();
    
       DataTable t  = con.GetSchema("Tables", new string[] {null,null,null,"BASE TABLE"});
       con.Close();
       Form f = new ShowDataForm(t);
       f.ShowDialog();
     }
    }
    
    
    public class ShowDataForm : Form
    {
      public ShowDataForm(object tbl)
      {
         this.dgv = new System.Windows.Forms.DataGridView();
         this.dgv.Location = new System.Drawing.Point(0, 0);
         this.dgv.Dock = DockStyle.Fill;
         this.dgv.DataSource = tbl;
         this.Controls.Add(this.dgv);
         this.ClientSize = new System.Drawing.Size(1024, 768);
      }
      private System.Windows.Forms.DataGridView dgv;
    }
    Thursday, July 9, 2009 2:27 PM
  • Got it already

    string strSQL = "USE " + txtDS.Text + "; Select * FROM sys.Tables WHERE IS_MS_SHIPPED = 0";

     

     

    Friday, July 10, 2009 3:38 AM
  • Got it already

    string strSQL = "USE " + txtDS.Text + "; Select * FROM sys.Tables WHERE IS_MS_SHIPPED = 0";

     

     


    That is not reliable means to get a COMPLETE accounting of ALL tables. 
    the GetSchema method CetinBasoz demonstrated. 

    Depending upon your database and login scenario, sometimes it takes a little playing around with the parameters to get at the exact stuff you want.
    Mark the best replies as answers. "Fooling computers since 1971."
    Friday, July 10, 2009 12:41 PM
  • Got it already

    string strSQL = "USE " + txtDS.Text + "; Select * FROM sys.Tables WHERE IS_MS_SHIPPED = 0";

     

     


    That is not an answer. It may have served you but for other lurkers it only helps one to get into trouble. If you somehow let someone else to fill that txtDS.Text value at runtime then you should be prepared to end up with an hacked SQL server. Search for "SQL injection attack" to see details.
    Friday, July 10, 2009 2:28 PM
  • use databasename
    SELECT * FROM information_schema.tables

    or

    use databasename

    SELECT * FROM sysobjects WHERE xtype='U'

    You can put either one into a sproc, run the sproc with SQL Command Object and use a datareader to read the result.

    Wednesday, September 11, 2013 10:22 AM
  • After 4 years of the original question what is the point of this answer? Also this is not the answer to the question. Read for the solutions above in previous replies.
    Wednesday, September 11, 2013 11:27 AM