SQLite net PCL - Simple select

Question

I use SQLite from windows app and now I am developing in Xamarin a portable app so I am using the plugin sqlite net pcl and I am having great trouble to understand how it works.

I have a table that is created by te following:

public class Config
    {
        public string IP { get; set; }
        [SQLite.Net.Attributes.Default(true, "Client 2")]
        public string ID { get; set; }
    }

and to create the table:

db.CreateTable<Model.Config>();

Problem: Now I want to select the value in ID column and I do the following:

List<string> hhid = db.Query<string>("select ID from Config",null);

I get this exception: "Object reference not set to an instance of an object"

How can I make a simple select to find this field?

Thanks for any tip

Baird

List<string> hhid = db.Query<string>("select id from Config",null); the field name is ID not id change the Select to match the field name – Amalee

@Amalee I tried that and same result. I updated the question now – Hazel

Is db properly initialized? – Fere

Have you tried the LINQ syntax instead of the .Query ? i.e. github.com/oysteinkrog/SQLite.Net-PCL. conn.Table<Config>(); – Fere

@nachogsiri Did you solve it? – Shumate

@Shumate Yes, there I posted an answer. Tell me if you have problems – Hazel

Answer 1

Hoping this will be usefull to someone in my place...

Between the brackets (<>) goes the table name:

db.Query<TableName>("select * from ....");

Some examples that worked for me:

Simple select:

var list = db.Query<MyTableName>("select * from MyTableName");

Select with restrictions:

var list = db.Query<MyTableName>("select * from MyTableName where lastname=? and firstname=?", lastnameValue, firstNameValue);

Baird

Answer 2

The accepted answer doesn't really help if you have a custom mapping for the tables name. The "Sql" table name can be found at runtime accessing to the type mapping.

Here an extension method

public static class NativeConnectionExtension
{
    public static List<T> SelectAllFrom<T>(this SQLiteConnection cnn) where T : new()
    {
        var mapping = cnn.GetMapping<T>();
        var result = cnn.Query<T>(String.Format("select * from {0};", mapping.TableName));
        return result;
    }
}

Watchband