How can i use sqlite in Tizen .net (c#)?

Question

I tried everything today, in particular System.data.SQLite, that generates SegmentationFault when i try to create new SQLiteConnection. I have a local db in shared/res, and i want to interrogate it to retrieve some data. Somebody can help me?

App.xaml.cs

protected override void OnStart()
    {
        // Handle when your app starts
        String db_name = "CTM_BUS.db";
        String old_db_path = "/opt/usr/apps/com.feduss.example.BusFinder.Tizen.Wearable/shared/res/";


        if (File.Exists(old_db_path + db_name))
        {

            SQLiteDatabase db = new SQLiteDatabase(new_db_path);
            SQLiteCommand selectBusLines = new SQLiteCommand
            ("SELECT * from Routes");
            DataTable res = db.GetDataTable(selectBusLines);
        }
        else
        {
            Console.WriteLine("File does not exist!");
        }
    }

SQLiteDatabase

public class SQLiteDatabase

{ private readonly string _dbConnection;

    public SQLiteDatabase(string dataSource)
    {
        _dbConnection = string.Format("Data Source={0}", dataSource);
    }

    public DataTable GetDataTable(SQLiteCommand command)
    {
        if (command == null) throw new ArgumentNullException("command");

        using (SQLiteConnection connection = new SQLiteConnection(_dbConnection))
        {
            connection.Open();
            command.Connection = connection;

            using (SQLiteDataReader reader = command.ExecuteReader())
            {
                DataTable result = new DataTable();
                result.Load(reader);
                return result;
            }
        }
    }

    public SQLiteCommand GetCommand(string sql)
    {
        if (string.IsNullOrEmpty(sql))
            throw new ArgumentNullException("sql");

        return new SQLiteCommand
        {
            CommandText = sql,
            CommandType = CommandType.Text
        };
    }

    public int ExecuteNonQuery(SQLiteCommand command)
    {
        if (command == null) throw new ArgumentNullException("command");

        using (SQLiteConnection connection = new SQLiteConnection(_dbConnection))
        {
            connection.Open();
            command.Connection = connection;

            return command.ExecuteNonQuery();
        }
    }
}

Answer 1

Use following nuget packages:

• sqlite-net-base
• SQLitePCLRaw.core
• SQLitePCLRaw.provider.sqlite3.netstandard11

then:

define table schema, for example:

public class TableSchema
{
    [AutoIncrement, PrimaryKey]
    public int Id { get; set; }

    public int Column1 { get; set; } = 0;
    public int Column2 { get; set; } = 0;
}

BaseRepository class - it connects to database and return handler:

public abstract class BaseRepository
{
    private readonly string _databasePath;

    protected BaseRepository(string databasePath)
    {
        _databasePath = databasePath;
    }

    protected SQLite.SQLiteConnection ConnectDatabase()
    {
        SQLitePCL.raw.SetProvider(new SQLitePCL.SQLite3Provider_sqlite3());
        SQLitePCL.raw.FreezeProvider();

        return new SQLite.SQLiteConnection(_databasePath);
    }
}

repository class (contains wrappers for common used queries):

public class Repository : BaseRepository
{
    public Repository(string databasePath) : base(databasePath)
    {
    }

    public List<TableSchema> GetAll()
    {
        using (SQLite.SQLiteConnection connection = ConnectDatabase())
        {
            return connection.Table<TableSchema>().ToList();
        }
    }

    public List<TableSchema> Query(string query)
    {
        using (SQLite.SQLiteConnection sqlConnection = ConnectDatabase())
        {
            try
            {
                SQLite.SQLiteCommand queryCommand = sqlConnection.CreateCommand(query);
                List<TableSchema> results = queryCommand.ExecuteQuery<TableSchema>();

                return results;
            }
            catch (System.Exception e)
            {
                global::Tizen.Log.Error("TEST_TAG", e.Message);

                return new List<TableSchema>();
            }
        }
    }
}

and

class Sample
{
    Repository _mapRepository;

    public Sample()
    {
        _mapRepository = new Repository(App.Current.DirectoryInfo.Data + "db.sqlite3");

         List<Table> allRecords = _mapRepository.GetAll();
    }
}