C# Dapper 操作Sqlite数据库

首先通过nuget安装Dapper以及Sqlite

创建实体模型

using System; namespace SQLiteDemo.Model { public class Customer { public long Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public DateTime DateOfBirth { get; set; } } } using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace SQLiteDemo.Model { public class Teacher { public long Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public DateTime DateOfBirth { get; set; } } }

创建通用接口

using SQLiteDemo.Model; namespace SQLiteDemo.Data { public interface ICustomerRepository<T> { T Get<T>(long id, string sql); void Save(T customer,string sql); } }

创建数据库连接类

using System; using System.Data.SQLite; namespace SQLiteDemo.Data { public class SqLiteBaseRepository { public static string DbFile { get { return Environment.CurrentDirectory + "\\SimpleDb.sqlite"; } } public static SQLiteConnection SimpleDbConnection() { return new SQLiteConnection("Data Source=" + DbFile); } } }

数据库实体映射crud

using System.IO; using System.Linq; using Dapper; using SQLiteDemo.Model; namespace SQLiteDemo.Data { public class SqLiteCustomerRepository<T> : SqLiteBaseRepository, ICustomerRepository<T> { public T Get<T>(long id,string sql) { if (!File.Exists(DbFile)) return default(T); using (var cnn = SimpleDbConnection()) { cnn.Open(); T result = cnn.Query<T>( sql, new { id }).FirstOrDefault(); return result; } } public void Save(T customer,string sql) { using (var cnn = SimpleDbConnection()) { cnn.Open(); cnn.Query<long>( sql, customer).First(); } } public static void CreateDatabase(string createdb) { using (var cnn = SimpleDbConnection()) { cnn.Open(); cnn.Execute(createdb ); } } } }

demo

using System; using System.Collections.Generic; using System.Linq; using System.Text; using SQLiteDemo.Data; using SQLiteDemo.Model; namespace SQLiteDemo { class Program { static void Main(string[] args) { SqLiteCustomerRepository<Customer>.CreateDatabase(@"CREATE TABLE IF NOT EXISTS Customer ( ID integer primary key AUTOINCREMENT, FirstName varchar(100) not null, LastName varchar(100) not null, DateOfBirth datetime not null )"); SqLiteCustomerRepository<Customer> rep = new SqLiteCustomerRepository<Customer>(); var customer = new Customer { FirstName = "Sergey", LastName = "Maskalik", DateOfBirth = DateTime.Now }; rep.Save(customer, @"INSERT INTO Customer ( FirstName, LastName, DateOfBirth ) VALUES ( @FirstName, @LastName, @DateOfBirth ); select last_insert_rowid()"); Customer retrievedCustomer = rep.Get<Customer>(customer.Id, @"SELECT Id, FirstName, LastName, DateOfBirth FROM Customer WHERE Id = @id"); SqLiteCustomerRepository<Teacher> repp = new SqLiteCustomerRepository<Teacher>(); var Teacher = new Teacher { FirstName = "Sergey", LastName = "Maskalik", DateOfBirth = DateTime.Now }; SqLiteCustomerRepository<Teacher>.CreateDatabase(@"CREATE TABLE IF NOT EXISTS Teacher ( ID integer primary key AUTOINCREMENT, FirstName varchar(100) not null, LastName varchar(100) not null, DateOfBirth datetime not null )"); repp.Save(Teacher, @"INSERT INTO Teacher ( FirstName, LastName, DateOfBirth ) VALUES ( @FirstName, @LastName, @DateOfBirth ); select last_insert_rowid()"); Teacher retrievedTeacher = repp.Get<Teacher>(Teacher.Id, @"SELECT Id, FirstName, LastName, DateOfBirth FROM Teacher WHERE Id = @id"); } } }

https://github.com/xdqt/Dapper-SQLiteDemo.git