How get sqlite schema?

ServiceStack.OrmLite
1

I use PRAGMA table_info(TableName) in commandline get table schema successly, but next code will throw SQLite error near “table_info”: syntax error

using (var db = dbFactory.Open())
            {
               
                var ret = db.Select<TableSchema>("PRAGMA table_info(TableName)");
                ret.PrintDump();

            }
  class TableSchema
    {
        public int cid { get; set; }
        public string name { get; set; }
        public string type { get; set; }
    }

q1: is there any api get table schema?
q2: is there a example or is it possible use a ExpandoObject instance object to crud due to the object prop( table field) will be known at runtime?
3q!

2

There’s no API to get Sqlite’s table schema but you can fetch the results using OrmLite’s dynamic resultset APIs, e.g:

var sql = $$"PRAGMA table_info({tableName})";

var columns = db.SqlList<Dictionary<string, object>>(sql);
foreach (var column in columns)
{
    object oColumnName;
    if (column.TryGetValue("name", out oColumnName))
    {
       //get more info about column
    } 
}
3 
var xx = db.SqlList<Dictionary<string, object>>("SELECT * from " + tableName);  // method A
//var xx = db.SelectLazy<Dictionary<string, object>>("SELECT  * from " + tableName); // method B
 foreach (var x in xx)
 {
     var colValue = x[colName] ...
     ..do some work
 }

now I used method A can get right result, but my table is too big ,so I want to select it by cursor,
method B always return wrong result (xx is empty),why?

maybe I should use ExecuteReader ,I try it

4 
         var reader = db.ExecuteReader("SELECT * from " + tableName);
                while (reader.Read())
                {
                    var x = reader.ConvertToDictionaryObjects();
               }

the above code worked, how about q2:

5

It’s hard to understand what you mean in your other questions but it looks like you just want to deserialize the results into a POCO model in which case you should use db.SqlList<T> for executing any queries that’s not a SELECT statement, e.g:

var results = db.SqlList<TableSchema>("PRAGMA table_info(TableName)");

You should only use db.Select<T> for SELECT statements.

On a side note after converting to an Object Dictionary you should be able to map it back to a POCO Type with FromObjectDictionary() extension method, e.g:

var x = reader.ConvertToDictionaryObjects();
var poco = x.FromObjectDictionary(typeof(Poco));
6

thank you ! i