csharp-sqlite

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

.net framework

使用 4.7.2 就可以安装 sqlite-net-pcl 了

.net framework 4.0

使用 dapper 1.50.2 支持 .net 4.0
依赖包:
System.Data.SQLite.Core（SQLite 包包含了EF完整依赖，这里不需要使用EF）、Dapper 和 Newton.Json。

dapper

public ObservableCollection<ModelConfig> m_config = new ObservableCollection<ModelConfig>();

var query = conn.Query<ModelConfig>("SELECT * FROM ModelConfig").ToList();
foreach (var item in query)
{
    m_config.Add(item);
}

// 单个对象插入
public static int Insert(Person person)
{
    using (IDbConnection connection = new SqlConnection(connectionString))
    {
        return connection.Execute("insert into Person(Name,Remark) values(@Name,@Remark)", person);
    }
}

// 批量插入
public static int Insert(List<Person> persons)
{
    using (IDbConnection connection = new SqlConnection(connectionString))
    {
        return connection.Execute("insert into Person(Name,Remark) values(@Name,@Remark)", persons);
    }
}

// 删除
public static int Delete(Person person)
{
    using (IDbConnection connection = new SqlConnection(connectionString))
    {
        return connection.Execute("delete from Person where id=@ID", person);
    }
}

public static int Delete(List<Person> persons)
{
    using (IDbConnection connection = new SqlConnection(connectionString))
    {
        return connection.Execute("delete from Person where id=@ID", persons);
    }
}

// 修改
public static int Update(Person person)
{
    using (IDbConnection connection = new SqlConnection(connectionString))
    {
        return connection.Execute("update Person set name=@name where id=@ID", person);
    }
}

public static int Update(List<Person> persons)
{
    using (IDbConnection connection = new SqlConnection(connectionString))
    {
        return connection.Execute("update Person set name=@name where id=@ID", persons);
    }
}

支持 sqlite 先使用 System.Data.SQLite 实现

System.Data.SQLite is about 3x faster than Microsoft.Data.Sqlite

.net core

选择 Manage Nuget Packages 安装 sqlite-net-pcl

添加 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
{
    long masterID = 0;
    var items = App.conn.Query<Model_Measure_item>(sql, masterID);

    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)
{
    MessageBox.Show("获取常量系数数据出现异常." + ex.Message);
    Log.Error(ex, "Application start-up failed");
}

using System;
using System.Data.SQLite;

namespace RetrieveCars
{
    class Program
    {
        static void Main(string[] args)
        {
            string cs = @"URI=file:C:\Users\Jano\Documents\test.db";

            using var con = new SQLiteConnection(cs);
            con.Open();

            string stm = "SELECT * FROM cars LIMIT 5";

            using var cmd = new SQLiteCommand(stm, con);
            using SQLiteDataReader rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                Console.WriteLine($"{rdr.GetInt32(0)} {rdr.GetString(1)} {rdr.GetInt32(2)}");
            }
        }
    }
}

参数化

var param1 = Convert.ToInt32(isUserApp).ToString();
var param2 = $"%{constraint.Trim()}%";
var packages = await conn.QueryAsync<Package>("SELECT * FROM Package WHERE UserApp = ? AND Name LIKE ? ORDER BY Name COLLATE NOCASE ASC;", new string[2] { param1, param2 });
    

排序

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

修改表名称

var sql = $"ALTER TABLE Model_trade_diary RENAME TO Model_trade_diary_bak";
var winAmount = App.conn.Execute(sql);

插入

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

public class Model_master_duplicate_attribute
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    // 标准件特征名称
    public string DuplicateAttrName { get; set; }
    // 标准件特征描述
    public string DuplicateAttrComment { get; set; }
    // 标准件特征名义值
    public string DuplicateAttrNominal { get; set; }
    // 标准件特征单位
    public string DuplicateAttrUnit { get; set; }
    // 标准件特征实际值
    public string DuplicateAttrTrueValue { get; set; }
    // 标准件特征实际偏置
    public string DuplicateAttrTrueOffset { get; set; }
    // Model_master_duplicate 的主键
    [ForeignKey(typeof(Model_master_duplicate))]
    public Int64 masterDupID { get; set; }
    // Model_master 的主键
    [ForeignKey(typeof(Model_master))]
    public Int64 masterID { get; set; }
    // Model_master_attribute 的主键
    [ForeignKey(typeof(Model_master_attribute))]
    public Int64 masterAttrID { get; set; }
}