VS2015 Winform使用SQLite

SQLite.Net-PCL

新建Winform项目，NuGet查找SQLite.Net-PCL，选择sqlite-net-pcl，注意，查找结果有好几个非常接近的，我们选择的程序包项目URL是https://github.com/praeclarum/sqlite-net。

using SQLite;

public class Book
    {
        [PrimaryKey, AutoIncrement]
        public int id { get; set; }

        public string Name { get; set; }//书名

        public DateTime PublishDate { get; set; }//出版日期

        public string Author { get; set; }//作者

        public float Price { get; set; }//价格
    }

 定义数据库，在数据库的构造函数中创建数据表：

public class MyBookDB : SQLiteConnection
    {
        //定义属性，便于外部访问数据表
        public TableQuery<Book> Books { get { return this.Table<Book>(); } }

        public MyBookDB(string dbPath) : base(dbPath)
        {
            //创建数据表
            CreateTable<Book>();
        }
    }

Winform窗口设计非常简单，演示对数据库的增删改查，操作完成后，点击更新按钮，显示数据表全部记录，确认操作结果。表格控件用DataGridView。 

//数据库文件路径
        private string dbPath;
        private void Form1_Load(object sender, EventArgs e)
        {
            //数据库文件路径就在运行目录下
            dbPath = $"{Environment.CurrentDirectory}\\mybooks.db";
        }

增加

private void btnAdd_Click(object sender, EventArgs e)
        {
            List<Book> books = new List<Book>()
            {
                new Book() { Name = "射雕英雄传", PublishDate = new DateTime(1960, 1, 1), Author = "金庸", Price = 10.5f },
                new Book() { Name = "神雕侠侣", PublishDate = new DateTime(1960, 2, 2), Author = "金庸", Price = 12.5f },
                new Book() { Name = "倚天屠龙记", PublishDate = new DateTime(1960, 3, 3), Author = "金庸", Price = 16.5f },
                new Book() { Name = "小李飞刀", PublishDate = new DateTime(1965, 5, 5), Author = "古龙", Price = 13.5f },
                new Book() { Name = "绝代双骄", PublishDate = new DateTime(1965, 6, 6), Author = "古龙", Price = 15.5f },
            };

            using (var db = new MyBookDB(dbPath))
            {
                int count = db.InsertAll(books);
                this.Text = $"{DateTime.Now}, 插入{count}条记录";
            }
        }

修改

private void btnModify_Click(object sender, EventArgs e)
        {
            using (var db = new MyBookDB(dbPath))
            {
                var book = db.Books.FirstOrDefault(x => x.Name == "绝代双骄");
                if (book != null)
                {
                    book.Price += 1;
                    int count = db.Update(book);
                    this.Text = $"{DateTime.Now}, 修改{count}条记录";
                }
            }
        }

删除

private void btnDel_Click(object sender, EventArgs e)
        {
            using (var db = new MyBookDB(dbPath))
            {
                int count = db.Books.Delete(x => x.Name == "绝代双骄");
                this.Text = $"{DateTime.Now}, 删除{count}条记录";
            }
        }

查询

private void btnQuery_Click(object sender, EventArgs e)
        {
            using (var db = new MyBookDB(dbPath))
            {
                var books = db.Books.Where(x => x.Author == "金庸").OrderByDescending(x => x.PublishDate).ToList();
                this.Text = $"{DateTime.Now}, 查到{books.Count}条记录";
                this.dataGridView1.DataSource = books;
            }
        }

刷新

private void btnRefresh_Click(object sender, EventArgs e)
        {
            using (var db = new MyBookDB(dbPath))
            {
                var books = db.Books.ToList();
                this.dataGridView1.DataSource = books;
            }
        }