sqllite (3) - c# .net 使用 dapper 操作 sqllite 資料庫

sqllite (3) - c# .net 使用 dapper 操作 sqllite 資料庫

環境配置：

開發環境：vs2013

運作時版本：framework 4.0 

sqllite 版本：非混合模式的靜态連結庫 framework 4.0 版本（x86、x64）

項目類型：單元測試項目

dapper.net 版本：1.4.2

dapper.net 介紹：

dapper.net 是一個輕量級的 ORM，支援多種資料庫。dapper.net 隻有一個6000行代碼 SqlMapper.cs 檔案。但是功能強大。

dapper.net 下載下傳位址：

​​​ http://code.google.com/p/dapper-dot-net/

​​​ https://github.com/StackExchange/dapper-dot-net/tree/1.42

項目結構：

┌TestSqllite

├-x64

│ └-SQLite.Interop.dll （右鍵屬性，複制到輸出目錄）

├-x86

│ └-SQLite.Interop.dll （右鍵屬性，複制到輸出目錄）

├-dapper

│ └-SqlMapper1.4.2.cs

└-TestSqllite.cs

釋出後的結構：

┌bin\debug\

├-x64

│ └-SQLite.Interop.dll

├-x86

│ └-SQLite.Interop.dll

└-System.Data.SQLite.dll

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Dapper;

namespace TestSqllite
{
    [TestClass]
    public class TestDapper
    {
        private string _dbFile = @"D:\temp\sqlliteDb\test.db";

        private string _conn
        {
            get
            {

                //1.基礎連接配接，FailIfMissing 參數 true=沒有資料檔案将異常;false=沒有資料庫檔案則建立一個
                //Data Source=test.db;Pooling=true;FailIfMissing=false
                //2。使用utf-8 格式
                //Data Source={0};Version=3;UTF8Encoding=True;
                //3.禁用日志
                //Data Source={0};Version=3;UTF8Encoding=True;Journal Mode=Off;
                //4.連接配接池
                //Data Source=c:\mydb.db;Version=3;Pooling=True;Max Pool Size=100;

                return string.Format(
@"Data Source={0};Pooling=true;FailIfMissing=false;Version=3;UTF8Encoding=True;Journal Mode=Off;", _dbFile);
            }
        }

        /// <summary>
        /// TestInsert
        /// </summary>
        [TestMethod]
        public void TestInsert()
        {
            var sql = @"
insert into person(
 name,
 age,
 sex,
 create_time
) values(
 @name,
 @age,
 @sex,
 @create_time
);";

            int n = 0;
            object obj;
            using (var conn = new SQLiteConnection(_conn))
            {
                n = conn.Execute(sql, new
                {
                    name = "admin",
                    age= "111",
                    sex="男",
                    create_time = DateTime.Now.ToString("yyy-MM-dd HH:mm:ss")
                });
                Assert.IsTrue(n == 1);
                //
                n = conn.Execute(sql, new
                {
                    name = "guest",
                    age = "222",
                    sex = "女",
                    create_time = DateTime.Now.ToString("yyy-MM-dd HH:mm:ss")
                });
                Assert.IsTrue(n == 1);

                //2 查表
                sql = "select count(*) from person;";
                n = conn.ExecuteScalar<int>(sql);
             
                conn.Close();
            }

            Assert.IsTrue(n > 0);
        }

        /// <summary>
        /// TestDelete
        /// </summary>
        [TestMethod]
        public void TestDelete()
        {
            var sql = @"delete from person where name='admin';";

            int n = 0;
            object obj;
            using (var conn = new SQLiteConnection(_conn))
            {
                n = conn.Execute(sql);
            }

            Assert.IsTrue(n > 0);
        }
        /// <summary>
        /// TestUpdate
        /// </summary>
        [TestMethod]
        public void TestUpdate()
        {
            var sql = @"update person set age=99 where name=@name;";

            int n = 0;
            object obj;
            using (var conn = new SQLiteConnection(_conn))
            {
                n = conn.Execute(sql, new
                {
                    name = "guest"
                });
            }

            Assert.IsTrue(n > 0);
        }
        /// <summary>
        /// TestSelect
        /// </summary>
        [TestMethod]
        public void TestSelect()
        {
            var sql = @"select * from person where name=@name;";
            //sql = @"select * from person;";
            var list = new List<PsersonModel>();
            using (var conn = new SQLiteConnection(_conn))
            {
                list = conn.Query<PsersonModel>(sql, new
                {
                    name = "admin"
                }) as List<PsersonModel>;
            }

            Assert.IsTrue(list.Count > 0);
        }

        public class PsersonModel
        {
            public string name { set; get; }
            public int age { set; get; }
            public string sex { set; get; }
            public DateTime create_time { set; get; }
        }
    }
}