How to get single row from SQLite database in Windows Store apps?

Question

If I want single primitive value like int, string, float etc I can do like this.

using (var db = new SQLiteConnection(DbPath))
{
    double i = db.CreateCommand("select salary from PersonMaster where personId = ?", 9).ExecuteScalar<double>();
}

If I try to return whole object of person master i.e. single row the below code returns null

using (var db = new SQLiteConnection(DbPath))
{
    PersonMaster objPersonMaster = db.CreateCommand("select * from PersonMaster where personId = ?", 9).ExecuteScalar<PersonMaster>();
}

I compulsory have to use this

using (var db = new SQLiteConnection(DbPath))
{
    List<PersonMaster> lstPersonMaster = db.Query<PersonMaster>("select * from PersonMaster where personId = ?", 9);
    PersonMaster objPersonMaster = lstPersonMaster.First();
}

Is there any way to get single row as object, rather than dealing with List<T>

chue x · Accepted Answer · 2013-06-11 13:25:53Z

2

I am assuming you are using SQLite-Net. If that is the case you can use the Find method. Find gets an object using its primary key.

The personId field needs the PrimaryKey attribute as follows:

public class PersonMaster
{
    [PrimaryKey]
    public int personId { get; set; }

    public string name { get; set; }
    public decimal salary { get; set; }
}

Then you can use Find like so:

// get person 9
PersonMaster person9 = db.Find<PersonMaster>(9);

edited Jun 11, 2013 at 13:25

answered Jun 11, 2013 at 13:00

chue x

18.9k8 gold badges60 silver badges71 bronze badges

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

3 Comments

Farhan Ghumra Over a year ago

What to do if I have no primary key in the table ? If my whe, then how can I do that ?

chue x Over a year ago

@Xyroid - If you don't have a key, then you have to use First

BlueRaja - Danny Pflughoeft Over a year ago

@Xyroid: Find() can also take a predicate: db.Find<PersonMaster>(person => person.name == "Xyroid")

Perry · Accepted Answer · 2013-06-11 12:10:31Z

1

You can either use "TOP(1) yourquery" or "yourquery LIMIT 1"

answered Jun 11, 2013 at 12:10

Perry

987 bronze badges

Comments

Devolus · Accepted Answer · 2013-06-11 12:08:52Z

0

if you just want to get rid of the list step, you can do it directly:

PersonMaster objPersonMaster = db.Query<PersonMaster>("select * from PersonMaster where personId = ?", 9).First();

Of course you have to deal with a possible exception in case nothing is returned, but you should do this also with the list approach anyway.

answered Jun 11, 2013 at 12:08

Devolus

22.2k15 gold badges70 silver badges122 bronze badges

1 Comment

Farhan Ghumra Over a year ago

That I know, but I don't want to go in List<T> way.

Collectives™ on Stack Overflow

How to get single row from SQLite database in Windows Store apps?

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related