c# - 我如何在 Tizen .net (c#) 中使用 sqlite？

Question

我今天尝试了所有方法，尤其是 System.data.SQLite，当我尝试创建新的 SQLiteConnection 时，它会生成 SegmentationFault。我在 shared/res 中有一个本地数据库，我想查询它以检索一些数据。有人可以帮助我吗？

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!");
        }
    }

SQLite数据库

public class SQLiteDatabase

{ 私有(private)只读字符串_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

使用以下 nuget 包:

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

然后:

定义表架构，例如:

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

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

BaseRepository 类 - 它连接到数据库并返回处理程序:

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

存储库类(包含常用查询的包装器):

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

和

class Sample
{
    Repository _mapRepository;

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

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