此orm需要用到sqlite3的运行库,从这里下载:https://www.sqlite.org/download.html
安装source: https://github.com/praeclarum/sqlite-net/blob/master/src/SQLite.cs
应用:
class DBConfig { public SQLiteConnection connection { get; set; } public DBConfig() { var databasePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "lpm.db"); connection = new SQLiteConnection(databasePath); } ~DBConfig() { connection.Close(); } } ## 1、建立/连接s
建立数据库、表
private DBConfig dBConfig; private SQLiteConnection conn; public PLMDB() { this.dBConfig = new DBConfig(); conn = dBConfig.connection; conn.CreateTable<T96PDLogTable>(); conn.CreateTable<Keyvalue>(); }
统计查询
public long UnSubmitCount() { return conn.ExecuteScalar<long>("select count(*) from T96PDLogTable where uploadTime is null"); }
查询记录
public List<T96PDLogTable> getUnunLoad(int limitNum) { IEnumerable <T96PDLogTable> r = conn.Query<T96PDLogTable>("select * from T96PDLogTable where uploadTime is null order by RecSEQ limit " + limitNum); return r.ToList(); }
参数化
public int removeOldData(int keepDays) { DateTime utc = Utils.toUTC(DateTime.Now.AddDays(-keepDays)); long tm = Utils.CurrentMillis(utc); return conn.Execute("delete from T96PDLogTable where uploadTime is not null and f96_create_dt<?", tm); }
条件查询Where
public bool markToUploaded(String recseq)
{
var query = conn.Table().Where(v => v.RecSEQ.Equals(recseq));
…
}
批量增加
public bool addT96(List<T96PDLogTable> t96List) { try { return conn.InsertAll(t96List, true) == t96List.Count; } catch (Exception e) { if (!e.Message.Contains("UNIQUE constraint")) { throw e; } return true; } }
批量修改
public bool markToUploaded(List<T96PDLogTable> t96List) { t96List.ForEach(c => c.uploadTime = DateTime.Now.ToString()); return conn.UpdateAll(t96List, true) == t96List.Count; }