CODEWITHSUNDEEP
c#sqlitexamarin.android
unknown apk
unknown apk

Reputation: 141

How to get single row from Sqlite table from xamarin

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.

Upvotes: 0

Views: 5952

Answers (3)

Kapil chawala
Kapil chawala

Reputation: 41

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.

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

Upvotes: 3

FreakyAli
FreakyAli

Reputation: 16479

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.

Upvotes: 0

hugo
hugo

Reputation: 1849

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;
    }
}

Upvotes: 0

Related Questions