Sqlite＆Dapper

 

关于为什么选择dapper访问sqlite这里不作讨论，仅介绍dapper在sqlite中相关操作

• 连接字符

Data Source=物理路径\名称.db;Version=3;UseUTF16Encoding=True;Pooling=False;Max Pool Size=50;Password=数据库密码;

• 数据库读

基本查询　

?

1 2 3 4 5 6

var sql = "select * from table";             using (var con=new SQLiteConnection("连接字符串"))             {                 //TestEntity为table对应实体                 IEnumerable<TestEntity> ret = con.Query<TestEntity>(sql);             }

　条件查询

?

1 2 3 4 5 6 7 8 9 10 11

var sql = "select * from table where name =@Name and sysn=@Sysno";           var param=new TestEntity()           {               Name = "哪咤",               Sysno = 123           };           using (var con=new SQLiteConnection("连接字符串"))           {               //TestEntity为table对应实体               IEnumerable<TestEntity> ret = con.Query<TestEntity>(sql,param);           }

　In查询

?

1 2 3 4 5 6 7 8 9 10

var sql = "select * from table where name in @names";        var param = new List<string>()      {          "金咤","木咤","哪咤"      };        using (var con = new SQLiteConnection("连接字符串"))        {            //TestEntity为table对应实体            IEnumerable<TestEntity> ret = con.Query<TestEntity>(sql, new { names = param });        }

• 　数据库写

?

1 2 3 4 5 6 7

var insert = "INSERT INTO table(col1,col2) values(@col1,@col2)";    // var update = "update table set col1=@col1,col2=@col2 where col=@col3 ";       using (var con = new SQLiteConnection("连接字符串"))     {        var rows = con.Execute(insert, 数据对象);     }

　

---

 

注意事项

1. SQLite只支持库级锁,即同时只能允许一个写操作，也就是说，即事务T1在A表插入一条数据，事务T2在B表中插入一条数据，这两个操作不能同时进行，否则容易锁库。故多线程写入时需要手动加锁
2. 批量写入数据时，尽量不要使用循环写入，开启事务批量写入，其速度是循环的N倍（此处仅表达意思未做未做严格处理，见谅）
   ?

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   var list = new List<TestEntity>()            {                 new TestEntity(){Name = "11",Age = 13},                 new TestEntity(){Name = "22",Age = 14},                 new TestEntity(){Name = "33",Age = 15},                 new TestEntity(){Name = "44",Age = 16},<br>　　             };             var insert = "INSERT INTO table(Name,Age) values(@Name,@Age)";               using (var con = new SQLiteConnection("连接字符串"))             {                 SQLiteTransaction tran = con.BeginTransaction();                 var rows = con.Execute(insert, list, tran);                 tran.Commit();             }

?

1