How to get single row from Sqlite table from xamarin

Question

I am developing an Android app in Xamarin.Android. I created a Sqlite database where I can add data and get all data. But now I want to retrieve a single row value from a table, respective to the column key.

Here is my code of AlbumTable:

namespace Myapp
{
  class AlbumTable
  {
    public long SR_ID { get; set; }   

    public string a{ get; set; }

    public string FileName { get; set; }

    public string OrderId { get; set; }

    public string Mobile { get; set; }

    public string Date { get; set; }

    public string CreatedOn { get; set; }

    public string UpdatedOn { get; set; }
  }
}

That is all column names of table from database, which I store in database.

Here is the DatabaseHelper file code:

namespace Myapp
{
  class DatabaseHelper
  {
    Java.IO.File dir = new Java.IO.File(Android.OS.Environment.ExternalStorageDirectory.AbsolutePath + "/Android/data/com.abhijit.testing.app/databases");

    public bool createDataBase()
    {
        try
        {
            using (var connection = new SQLiteConnection(Path.Combine(dir.AbsolutePath, "album.db")))
            {
                connection.CreateTable<AlbumTable>();
                return true;
            }
        }
        catch (SQLiteException e)
        {
            return false;
        }           
    }

    public bool InsertIntoTable(AlbumTable album)
    {
        try
        {
            System.Console.Write("Data Saved Successfully");

            using (var connection = new SQLiteConnection(Path.Combine(dir.AbsolutePath, "album.db")))
            {
                connection.Insert(album);
                return true;
            }
        }
        catch (SQLiteException e)
        {
            return false;
        }
    }

    public List<AlbumTable> getalldata()
    {
        try
        {
            using (var connection = new SQLiteConnection(Path.Combine(dir.AbsolutePath, "album.db")))
            {
                return connection.Table<AlbumTable>().ToList();
            }
        }
        catch (SQLiteException ex)
        {
            return null;
        }
    }
  }
}

Now I want to get one record from the table according to the orderid field. I want to retrieve one row which match the orderid.

I am new to Xamarin.Android so I do not have any idea on how to do it.

Kapil chawala · Accepted Answer · 2017-09-08 12:15:32Z

3

1.You can use FirstOrDefault() Linq method directly for this :-

Add using System.Linq; namespace.

public AlbumTable GetRow(string orderId) {

try
{
    using (var connection = new 
   SQLiteConnection(Path.Combine(dir.AbsolutePath, "album.db")))
    {

        return connection.Table<AlbumTable>().FirstOrDefault(x => x.OrderId 
        == orderId);
    }
}
catch (SQLiteException ex)
{
    return null;
}

}

It will directly return the single record.

If you make order id as primary key, then you can use following code also:- _connection.Find (id);

edited Sep 8, 2017 at 12:15

answered Sep 8, 2017 at 12:07

Kapil chawala

414 bronze badges

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

1 Comment

Kapil chawala Over a year ago

If you make order id as primary key, then you can use following code also:- _connection.Find<AlbumTable> (id);

hugo · Accepted Answer · 2017-09-08 07:45:04Z

0

You just need to use the same code of your getalldata and change the .ToList() with a Where(condition) clause and a SingleOrDefault() to retrieve 1 row or null.

public AlbumTable getRow(string orderId)
{

    try
    {
        using (var connection = new SQLiteConnection(Path.Combine(dir.AbsolutePath, "album.db")))
        {

            return connection.Table<AlbumTable>().Where(x => x.OrderId == orderId).SingleOrDefault();
        }

    }
    catch (SQLiteException ex)
    {
        return null;
    }
}

answered Sep 8, 2017 at 7:45

hugo

1,8592 gold badges19 silver badges47 bronze badges

3 Comments

unknown apk Over a year ago

Does this return all album fields

hugo Over a year ago

No, just one row of the table --> SingleOrDefault() means 1 unique row or null

hugo Over a year ago

@unknownapk don't forget to give feedback :)

FreakyAli · Accepted Answer · 2017-09-08 09:50:06Z

0

if i'm not wrong you are looking for something like this :

var getColumn=new DatabaseHelper().SelectTableDatafromQuery<AlbumTable>("SELECT YourColumnName FROM AlbumTable");

this code will get all the data of a particular column in the above variable good luck.

and to get the column data singularly you can use a foreach loop.

edited Sep 8, 2017 at 9:50

answered Sep 8, 2017 at 9:42

FreakyAli

17k3 gold badges39 silver badges84 bronze badges

Collectives™ on Stack Overflow

How to get single row from Sqlite table from xamarin

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related