e-motiv e-motiv -4 years ago 119
C# Question

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

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.

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download