csharp-use sqlite

把 sqlite 数据库拷贝到项目目录中，选择 show all files 找到 数据库文件 选择 include in project
然后在属性里面设置如下图：
这样在 build 项目的时候，会自动 copy 到 bin 路径里面

添加 connection string

右键单击 Dependencies or Reference 我这里用的 .net core 所以是 Dependencies
选择 Manage Nuget Packages 安装 sqlite-net-pcl

sqlite-net

SQLite-Net Extensions is a very simple ORM that provides cascade operations, one-to-one, one-to-many, many-to-one, many-to-many, inverse and text-blobbed relationships on top of the sqlite-net library.
SQLiteNetExtensions

[PrimaryKey] – This attribute can be applied to an integer property to force it to be the underlying table’s primary key. Composite primary keys are not supported.
[AutoIncrement] – This attribute will cause an integer property’s value to be auto-increment for each new object inserted into the database
[Column(name)] – The name parameter sets the underlying database column’s name.
[Table(name)] – Marks the class as being able to be stored in an underlying SQLite table with the name specified.
[Ignore] – Causes SQLite.NET to ignore this property. This is particularly useful for properties that have a type that cannot be stored in the database, or properties that model collections that cannot be resolved automatically by SQLite.
[Unique] – Ensures that the values in the underlying database column are unique.

https://docs.microsoft.com/en-us/xamarin/android/data-cloud/data-access/using-sqlite-orm

using SQLite;

var coeff = new model_coeff("k100","k100 test","123.4567");
// 访问数据库
using (SQLiteConnection conn = new SQLiteConnection(App.db_path))
{
    conn.Insert(coeff);
}

查询

class model_coeff
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    public string name { get; set; }
    public string comment { get; set; }
    public string value { get; set; }
    [OneToMany(CascadeOperations = CascadeOperation.CascadeDelete)]
    public List<Model_master_attribute> attributes { get; set; }
    [OneToMany(CascadeOperations = CascadeOperation.CascadeDelete)]
    public List<Model_master_preelaboration> preelaborations { get; set; }
}

try
{
    m_coeffs.Clear();
    var ret  = App.conn.GetTableInfo("model_coeff");
    if (0 == ret.Count)
    {
        App.conn.CreateTable<model_coeff>();
    }
    // 访问数据库
    var query = App.conn.Table<model_coeff>();
    foreach (var item in query)
    {
        m_coeffs.Add(item);
    }
    return m_coeffs;
}
catch (Exception ex)
{
    //TODO log here
    MessageBox.Show("获取常量系数数据出现异常." + ex.Message);
    Log.Error(ex, "Application start-up failed");
}

排序

try
{
    m_preelaboration.Clear();
    var ret = App.conn.GetTableInfo("Model_preelaboration");
    if (0 == ret.Count)
    {
        App.conn.CreateTable<Model_preelaboration>();
    }
    // 访问数据库
    var query = App.conn.Table<Model_preelaboration>().OrderBy(x => x.MeasureStepId);
    foreach (var item in query)
    {
        m_preelaboration.Add(item);
    }
    return m_preelaboration;
}
catch (Exception ex)
{
    //TODO log here
    MessageBox.Show("获取预运算数据出现异常." + ex.Message);
    Log.Error(ex, "List_preelaboration failed");
}

// 访问数据库
var query = App.conn.Table<Model_log>().OrderByDescending(x => x.Time);

last row id

public Int64 Last_row_id()
{
    var result = App.conn.QueryScalars<Int64>("SELECT last_insert_rowid()");
    if (result.Count > 0)
    {
        return result[0];
    }
    else
    {
        return 0;
    }
    
}

查看表是否存在

var ret  = App.conn.GetTableInfo("model_coeff");
if (0 == ret.Count)
{
    App.conn.CreateTable<model_coeff>();
}

插入

public int insert_coeff(model_coeff coeff)
{
    try
    {
        // 访问数据库
        var modified = App.conn.Insert(coeff);
        return modified;
    }
    catch (Exception ex)
    {
        MessageBox.Show("保存系数数据出现异常." + ex.Message);
        Log.Error(ex, "insert_coeff failed");
    }
    return 0;
}

// use transaction
var db = new SQLiteConnection(path);
db.RunInTransaction(() => {
    // database calls inside the transaction
    db.Insert(stock);
    db.Insert(valuation);
});

更新

public int update_coeff(model_coeff coeff)
{
    try
    {
        // 访问数据库
        var modified = App.conn.Update(coeff);
        return modified;
    }
    catch (Exception ex)
    {
        MessageBox.Show("获取常量系数数据出现异常." + ex.Message);
        Log.Error(ex, "update_coeff failed");
    }
    return 0;
}

删除

public int delete_coeff(int id)
{
    try
    {
        // 访问数据库
        var modified = App.conn.Delete<model_coeff>(id);
        return modified;
    }
    catch (Exception ex)
    {
        MessageBox.Show("删除常量系数数据出现异常." + ex.Message);
        Log.Error(ex, "delete_coeff failed");
    }
    return 0;
}

// use extension cascade  delete 
// Take into account that SQLite-Net Extensions won't go into database to search for relationships, they have to be already loaded in memory.

WriteOperations.Delete(App.conn, item, true);

public int Delete_All_model<T>()
{
    try
    {
        // 访问数据库
        var modified = App.conn.DeleteAll<T>();
        return modified;
    }
    catch (Exception ex)
    {
        MessageBox.Show("清空数据出现异常." + ex.Message);
        Log.Error(ex, "delete all model failed");
    }
    return 0;
}

数据模型忽略字段

[Table("my_tab")]
public class MyObj
{
    [PrimaryKey, Column("column1")]
    public string myObjField1 { get; set; }

    [Column("column2")]
    public string myObjField2 { get; set; }

    [Ignore]
    public string myObjField3 { get; set; }
}

关系模型，1对多，多对多

SQLiteNetExtensions
bitbucket