Sqlite＆Dapper

 

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

• 连接字符

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

• 数据库读

基本查询　

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

　条件查询

  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查询

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

• 　数据库写

  　　　　　　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倍（此处仅表达意思未做未做严格处理，见谅）

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