CODEWITHSUNDEEP
c#sql-serverwinforms
Javy26
Javy26

Reputation: 385

Is it possible to read from any database file?

I have a database with some tables which were created that are read into my c# application. Is there a way to read the first two columns of any table that is added to the database rather than writing in a query within my code that pulls a specific table? For eg below is my code that reads in the Liguanea_Lane table and populates a autocomplete text field

 private void liguaneaRxToolStripMenuItem_Click(object sender, EventArgs e)
    {
        this.liguanea_LaneTableAdapter1.Fill(this.pharmaciesDataSet1.Liguanea_Lane);
        try
        {

            string connectionString = "Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            string query = "SELECT Code FROM dbo.Liguanea_Lane";
            SqlCommand cmd = new SqlCommand(query, con);

            SqlDataReader dr = cmd.ExecuteReader();
            AutoCompleteStringCollection mycollection = new AutoCompleteStringCollection();
            while (dr.Read())
            {

                mycollection.Add(dr.GetString(0));

            }
            textBox2.AutoCompleteCustomSource = mycollection;
            con.Close();
        }
        catch (Exception ex)
        {

            MessageBox.Show(ex.ToString());
        }

Instead of reading in a specific table and its column, is there a way I could read any table? Meaning let's say I create a table called "Cat_values" How would I read the first or second column of data into my c# code? Rather than having to go into the code each time and execute the query each time a new table is added as demonstrated in the code above

Upvotes: 1

Views: 149

Answers (1)

Bouke
Bouke

Reputation: 1577

You can get all the table names within your database by executing the following query: 

SELECT TABLE_NAME FROM YOURDB.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'

In order to manage which tables are added since your last check, you might create a table which holds all existing table names like this:

CREATE TABLE [dbo].[DatabaseTables](
    [TableName] [nvarchar](64) NOT NULL PRIMARY KEY,
    [DateAdded] [datetime] NOT NULL DEFAULT (getdate())
)

Each time you inspect the added tables, execute the statement below to update the table containing the database tables:

INSERT INTO DatabaseTables(TableName)
SELECT TABLE_NAME FROM YOURDB.INFORMATION_SCHEMA.Tables 
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME NOT IN (SELECT TableName FROM DatabaseTables)

Upvotes: 1

Related Questions