How to get column names from a table in sqlite via PRAGMA (.net / c#)?

Question

I have been struggling to get the right c# code for getting the values after a PRAGMA table_info query.

_{Since my edit with extra code was rejected in this post, I made this question for other people that would otherwise waste hours for a fast solution.}

Solution 1

Assuming you want a DataTable with the list of field of your table:

 using (var con = new SQLiteConnection(preparedConnectionString))
    {
       using (var cmd = new SQLiteCommand("PRAGMA table_info(" + tableName + ");"))
        {
            var table = new DataTable();

            cmd.Connection = con;
            cmd.Connection.Open();

             SQLiteDataAdapter adp = null;
                try
                {
                    adp = new SQLiteDataAdapter(cmd);
                    adp.Fill(table);
                    con.Close();
                    return table;
                }
              catch (Exception ex)
              { }
         }
     }

Return result is:

• cid: id of the column
• name: the name of the column
• type: the type of the column
• notnull: 0 or 1 if the column can contains null values
• dflt_value: the default value
• pk: 0 or 1 if the column partecipate to the primary key

If you want only the column names into a List you can use (you have to include System.Data.DataSetExtension):

 return table.AsEnumerable().Select(r=>r["name"].ToString()).ToList();

EDIT: Or you can avoid the DataSetExtension reference using this code:

using (var con = new SQLiteConnection(preparedConnectionString))
      {
          using (var cmd = new SQLiteCommand("PRAGMA table_info(" + tableName + ");"))
          {
              var table = new DataTable();
              cmd.Connection = con;
              cmd.Connection.Open();

              SQLiteDataAdapter adp = null;
              try
              {
                  adp = new SQLiteDataAdapter(cmd);
                  adp.Fill(table);
                  con.Close();
                  var res = new List<string>();
                  for(int i = 0;i<table.Rows.Count;i++)
                      res.Add(table.Rows[i]["name"].ToString());
                  return res;
              }
              catch (Exception ex){ }
          }
      }
      return new List<string>();

There are a lot of PRAGMA statements that you can use in SQLite, have a look at the link.

About the using statement: it's very simple, it is used to be sure that disposable objects will be disposed whatever can happen in your code: see this link or this reference

Solution 2

Code:

DB = new SQLiteConnection(@"Data Source="+DBFileName);
DB.Open();
SQLiteCommand command = new SQLiteCommand("PRAGMA table_info('tracks')", DB);
DataTable dataTable = new DataTable();
SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(command);
dataAdapter.Fill(dataTable);
DB.Close();
foreach (DataRow row in dataTable.Rows) { 
    DBColumnNames.Add((string)row[dataTable.Columns[1]]); }  
            //Out(String.Join(",", 
    DBColumnNames.ToArray()));//debug

All elements in the resulted rows:
int cid, string name, string type,int notnull, string dflt_value, int pk

More info on PRAGMA