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