SQLite - storing the column names of a table in a list of strings

Question

I don't know how to store the column names from a SQLite table into a list of strings. The following code fills a dataGridView with the column names (amongst other things):

string sDatabasePath = DBPath();
SQLiteConnectionStringBuilder datasource = new SQLiteConnectionStringBuilder();
datasource.Add("Data Source", sDatabasePath);
datasource.Add("Version", "3");
datasource.Add("New", "False");
datasource.Add("Compress", "True");             
using (SQLiteConnection connection = new SQLiteConnection(datasource.ConnectionString))
{
    connection.Open(); //opens connection
    SQLiteCommand getColumnNames = new SQLiteCommand("PRAGMA table_info('myTable');", connection);
    SQLiteDataAdapter myAdapter = new SQLiteDataAdapter(getColumnNames);
    DataSet myDataSet = new DataSet();
    //myAdapter.Fill(myDataSet, "name");
    this.dataGridView1.DataSource = myDataSet;
    this.dataGridView1.DataMember = "name";
    connection.Close();
}

In the datagrid I get the column names correctly (first column in the datagrid is called name and on each row is the name of a column in my table. Second column in datagrid is called cid, third type, fourth not_null, etc). — Nick_F
– Nick_F, Commented Jan 20, 2014 at 11:11

James · Accepted Answer · 2014-01-20 11:44:45Z

1

If you are looking to bind your query to a list and not a DataGridView, then you should use a data reader and not a data set e.g.

using (SQLiteConnection connection = new SQLiteConnection(datasource.ConnectionString))
using (SQLiteCommand cmd = new SQLiteCommand("PRAGMA table_info('myTable');"))
{
    connection.Open(); //opens connection
    var tableNames = new List<string>();
    using (SQLiteDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            tableNames.Add(reader.GetString(0)); // read 'name' column
        }
    }
    return tableNames;
}

edited Jan 20, 2014 at 11:44

answered Jan 20, 2014 at 11:08

James

82.4k19 gold badges172 silver badges245 bronze badges

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

2 Comments

Nick_F Over a year ago

Thank you James. You are right about using the data reader. I tried using it, but I didn't know the syntax. I just started using C#. There is a minor change I had to do - tableNames.Add(Convert.ToString(reader["name"]))

James Over a year ago

@Nick_F ah yeah forgot it returns object and not string, alternatively you can call reader.GetString(0) where 0 is the column number or reader["name"].ToString() however the latter would mean it would fail if the name was null.

Dietrich Prg · Accepted Answer · 2014-01-20 11:16:59Z

1

            DataTable dtb = new DataTable();
            myAdapter.Fill(dtb);

            string[] names = new string[dtb.Rows.Count];
            for (int i = 0; i < dtb.Rows.Count; i++)
            {
                DataRow row = dtb.Rows[i];
                names[i] = row[0].ToString();
            }

answered Jan 20, 2014 at 11:16

Dietrich Prg

841 silver badge6 bronze badges

6 Comments

Nick_F Over a year ago

Thank you Dietrich for help. I went with the solution proposed by James, since he posted it first and it worked for me.

Dietrich Prg Over a year ago

No problem ^-^ we are here to try help.

James Over a year ago

@DietrichPrg you could do this in less code, var names = dtb.Rows.Select(x => x.Item["name"]);. I went for the SQLiteDataReader because I was under the assumption the OP didn't need a DataSet. However, if the OP is already using a DataSet this would be the more efficient option.

Dietrich Prg Over a year ago

@James i know this, but I had some experiences with DataReader, and for my opinion, this componet have a problem, need keep the connection with the server, to me, its a problem, because with you work with server far from you and you dont have a nice internet link, what its hard to have in Brasil, the component spent more time than bring everything to memory in DataTable and work with it.

James Over a year ago

@DietrichPrg it's not so much a problem but an implementation detail. Both serve a purpose and you shouldn't blindly say one is better than the other, it really depends on the situation. There is a nice analogy of the difference between the 2 in this answer.

|

Collectives™ on Stack Overflow

SQLite - storing the column names of a table in a list of strings

2 Answers 2

2 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related