Reading tables from SQL Server Express database

Question

I am trying to read my tables that are contained within my database in SQL Server Express, but it keeps coming up empty. What is it that am doing wrong?

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
    string connectionString = "Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";

    string query = "SELECT * FROM INFORMATION_SCHEMA.TABLES";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(query, connection);
        command.Connection.Open();

        command.ExecuteNonQuery();
    }
}

Updated but still empty:

  private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
    {
        try {

            string connectionString = "Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";
            SqlConnection con2 = new SqlConnection(connectionString);
            con2.Open();
            string query = "SELECT * FROM INFORMATION_SCHEMA.TABLES";
            SqlCommand cmd2 = new SqlCommand(query, con2);

            SqlDataReader dr2 = cmd2.ExecuteReader();
            while (dr2.Read())
            {

                comboBox1.Items.Add((string)dr2[0]);

            }


        }


        catch (Exception ex)
        {

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

Latest Updated code. Think I figured it but still showing empty:

 private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
    {
        try {

            string connectionString = "Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";
            SqlConnection con2 = new SqlConnection(connectionString);
            con2.Open();
            string query = "SELECT * FROM INFORMATION_SCHEMA.TABLES ";
            SqlCommand cmd2 = new SqlCommand(query, con2);

            SqlDataReader dr2 = cmd2.ExecuteReader();
            while (dr2.Read())
            {

                string Dtables = dr2.GetString(dr2.GetOrdinal("TABLE_NAME"));
                comboBox1.Items.Add(Dtables);

            }

        }

        catch (Exception ex)
        {

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

Full class:

    namespace unique_database{ public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {

    }

    private void button2_Click(object sender, EventArgs e)
    {
        string connectionString = "Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";
        string query = "CREATE TABLE [dbo].[" + textBox1.Text + "](" + "[Code] [varchar] (13) NOT NULL," +
       "[Description] [varchar] (50) NOT NULL," + "[NDC] [varchar] (50) NULL," +
        "[Supplier Code] [varchar] (38) NULL," + "[UOM] [varchar] (8) NULL," + "[Size] [varchar] (8) NULL,)";


        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);
            command.Connection.Open();
            command.ExecuteNonQuery();
        }

        SqlConnection con = new SqlConnection("Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True");
        string filepath = textBox2.Text; //"C:\\Users\\jdavis\\Desktop\\CRF_105402_New Port Maria Rx.csv";
        StreamReader sr = new StreamReader(filepath);
        string line = sr.ReadLine();
        string[] value = line.Split(',');
        DataTable dt = new DataTable();
        DataRow row;
        foreach (string dc in value)
        {
            dt.Columns.Add(new DataColumn(dc));
        }

        while (!sr.EndOfStream)
        {
            value = sr.ReadLine().Split(',');
            if (value.Length == dt.Columns.Count)
            {
                row = dt.NewRow();
                row.ItemArray = value;
                dt.Rows.Add(row);
            }
        }
        SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
        bc.DestinationTableName = textBox1.Text;
        bc.BatchSize = dt.Rows.Count;
        con.Open();
        bc.WriteToServer(dt);
        bc.Close();
        con.Close();

    }

    private void button3_Click(object sender, EventArgs e)
    {
        OpenFileDialog openFileDialog = new OpenFileDialog();
        openFileDialog.Filter = "CSV files (*.csv)|*.csv|XML files (*.xml)|*.xml";


        if (openFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
        {
            this.textBox2.Text = openFileDialog.FileName;
        }
    }

    private void FillCombo()
    {
        try
        {

            string connectionString = "Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";
            using (SqlConnection con2 = new SqlConnection(connectionString))
            {
                con2.Open();
                string query = "SELECT * FROM INFORMATION_SCHEMA.TABLES ";
                SqlCommand cmd2 = new SqlCommand(query, con2);

                SqlDataReader dr2 = cmd2.ExecuteReader();
                while (dr2.Read())
                {
                    int col = dr2.GetOrdinal("TABLE_NAME");
                    comboBox1.Items.Add(dr2[col].ToString());
                }

            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }

You need to read into something like a DataReader. ExecuteNonQuery is for things like INSERT that don't return values/data. — DonBoitnott, Nov 16 '16 at 17:49
How hard could it be ?? http://stackoverflow.com/questions/14113931/c-sharp-populate-a-combobox-from-table-in-database — Fourat, Nov 16 '16 at 18:05
@Fourat said table, not table values. I know how to do that already — Javy26, Nov 16 '16 at 19:04
@Jevon in case you didn't notice `INFORMATION_SCHEMA.TABLES` is a table that contains data about tables — Fourat, Nov 17 '16 at 07:41

McNets · Accepted Answer · 2016-11-16T20:06:00.157

1

Ok, first, don't use comboBox1_SelectedIndexChanged to fill comboBox1. use YourForm.OnLoad event, the form constructor, or click a button, but don't use SelectedIndexChanged to fill itself, because you're executing this procedure every time you select one item of comboBox1.

To get column index simply use: dr2.GetOrdinal("TABLE_NAME");

private void FillCombo()
{
    try 
    {
        string connectionString = "Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";
        using (SqlConnection con2 = new SqlConnection(connectionString))
        {
            con2.Open();
            string query = "SELECT * FROM INFORMATION_SCHEMA.TABLES";
            SqlCommand cmd2 = new SqlCommand(query, con2);

            SqlDataReader dr2 = cmd2.ExecuteReader();
            while (dr2.Read())
            {
                int col = dr2.GetOrdinal("TABLE_NAME");
                comboBox1.Items.Add(dr2[col].ToString());
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

edited Nov 16 '16 at 20:06

answered Nov 16 '16 at 19:48

McNets

10,352
3
32
61

Still empty for some reason – Javy26 Nov 16 '16 at 19:57
have you any table in your database? how many results if you use the select statement in SSMS? have you opened the combobox? – McNets Nov 16 '16 at 20:00
delete the code of actual question and paste the whole class. – McNets Nov 16 '16 at 20:01
Yes I have opened the comboBox and yes I do have tables. 4 of them. I ran the `"SELECT * FROM INFORMATION_SCHEMA.TABLES "` and the TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME and TABLE_TYPE comes up. Narrowing it down to this command" `SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES` displayed the table data alone so yeah tables are present – Javy26 Nov 16 '16 at 20:03
there is an error in this code, can you compile it w/o errors? – McNets Nov 16 '16 at 20:05
There are no errors or warnings. Just the comboBox values not working – Javy26 Nov 16 '16 at 20:06
@Jevon, there was an error, I correct it. Please, put a breakpoint in while (dr2.Read()) for example, and try to debug it. How many time while loop is executed? – McNets Nov 16 '16 at 20:08
No I saw that error that was just a misplaced bracket and semi colon. That's why am saying there wasn't an error. I'll see what comes up with breakpoint – Javy26 Nov 16 '16 at 20:10
where are you calling FillCombo(); ? Add it to Button1_Click event for example – McNets Nov 16 '16 at 20:10

score 0 · Answer 2 · answered Nov 16 '16 at 17:53

0

Use ExecuteReader() instead of ExecuteNonQuery().then Use SqlDataReader() for get result.

answered Nov 16 '16 at 17:53

ramin Namiranian

1
1

@Jevon this answer give you an idea of where to begin. Look for help on MSDN about ExecuteReader(), I'm sure you'll find some sample there. – McNets Nov 16 '16 at 18:28
Yeah I did all of that but still empty on my latest updated code @mcNets – Javy26 Nov 16 '16 at 19:25

Reading tables from SQL Server Express database

2 Answers2