How do I Select from all Tables a certain column to a string array in C#

Question

I need to get from each individual table but from the same column name the value to a string array. Although I don't what I table names I've got. I only know they all have a column name 'email'.

        string connectionString = "SERVER=********;PORT=****;DATABASE=********;UID=********;PASSWORD=******;";
        MySqlConnection conn = new MySqlConnection(connectionString);
        MySqlCommand command = conn.CreateCommand();


        try
        {
        conn.Open();
        command.CommandText = "SHOW FULL TABLES FROM ExampleDataBase";
        }   


        catch 
        { 
            MessageBox.Show("error"); 
        }
        MySqlDataReader reader;
        reader = command.ExecuteReader();

        while (reader.Read())
        {

            string usermail = reader.GetString("email");

            string[] mail = new String[] { usermail};
            foreach (string s in mail)
            {
                listboxMails.Items.Add("email: " + s)
            }

        }

score 0 · Answer 1 · edited May 23 '17 at 12:24

Not knowing the table names is not a good start when you work with DB...

First get a list of all table name who have the email field

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'email'
        AND TABLE_SCHEMA='ExampleDataBase';

Second generate a UNION request with all your table name

like

(SELECT email FROM %myTable1%)
UNION
(SELECT email FROM %myTable2%)
-- ...

score 0 · Answer 2 · answered Jan 08 '17 at 03:51

Lacking a name, it might be easiest to use Linq's mapping. Make sure to add the reference System.Linq.Data... Here's the code:

    using System.Linq;
    using System.Data;
    using System.Data.Linq;
    using System.Data.Linq.Mapping;

    namespace ...
    {
            class Program
            {
                static void Main(string[] args)
                {                    
                    DataContext dataContext = new DataContext(connectionString);
                    Table<Email> Emails = dataContext.GetTable<Email>();
                    IQueryable<string> query = from e in Emails
                                               select $"email: {e.email}";


                }
            }

            [Table]
            public class Email
            {
                [Column(IsPrimaryKey = true)]
                public string email;
            }
     }

This should be the easiest way to enumerate. I hope this helps.

you probably do not need the (IsPrimaryKey = true) as I doubt a DB would set a Primary Key column to a user's email address as this may be duplicated for multiple accounts. — David Cardinale, Jan 08 '17 at 04:00

How do I Select from all Tables a certain column to a string array in C#

2 Answers2