sqllite (2) - c# .net 使用 sqllite 增删改查操作数据库

sqllite (2) - c# .net 使用 sqllite 增删改查操作数据库


环境配置：
开发环境：vs2013
运行时版本：framework 4.0 
sqllite 版本：非混合模式的静态链接库 framework 4.0 版本（x86、x64）
项目类型：单元测试项目


项目结构：
┌TestSqllite
├-x64
│ └-SQLite.Interop.dll （右键属性，复制到输出目录）
├-x86
│ └-SQLite.Interop.dll （右键属性，复制到输出目录）
└-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;

namespace TestSqllite
{
    [TestClass]
    public class TestSqllite
    {
        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>
        /// 连接数据
        /// </summary>
        [TestMethod]
        public void TestConnection()
        {
            using (var conn = new SQLiteConnection(_conn))
            {
                conn.Open();
                conn.Close();
            }
            Assert.IsTrue(true);
        }
        /// <summary>
        /// 创建表
        /// </summary>
        [TestMethod]
        public void TestCreateTable()
        {
            var sql = @"
CREATE TABLE IF NOT EXISTS person (
 id INTEGER PRIMARY KEY AUTOINCREMENT, 
 name TEXT,
 age REAL,
 sex TEXT,
 create_time TEXT);";

            int n = 0;
            object obj;
            using (var conn = new SQLiteConnection(_conn))
            {
                conn.Open();
                //1 建表
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                n = cmd.ExecuteNonQuery();
                //2 查表
                sql = "select count(*) from person;";
                cmd.CommandText = sql;
                obj = cmd.ExecuteScalar();
                conn.Close();
            }

            Assert.IsTrue(obj!=null);
        }

        /// <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))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                //
                cmd.Parameters.Add(new SQLiteParameter("name", "admin"));
                cmd.Parameters.Add(new SQLiteParameter("age", "111"));
                cmd.Parameters.Add(new SQLiteParameter("sex", "男"));
                cmd.Parameters.Add(new SQLiteParameter("create_time", DateTime.Now.ToString("yyy-MM-dd HH:mm:ss")));
                n = cmd.ExecuteNonQuery();
                //
                cmd.Parameters["name"].Value = "guest";
                cmd.Parameters["age"].Value = "222";
                cmd.Parameters["sex"].Value = "女";
                cmd.Parameters["create_time"].Value = DateTime.Now.ToString("yyy-MM-dd HH:mm:ss");
                n = cmd.ExecuteNonQuery();

                //2 查表
                sql = "select count(*) from person;";
                cmd.CommandText = sql;
                obj = cmd.ExecuteScalar();
                int.TryParse(obj.ToString(), out n);
                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))
            {
                conn.Open();
                //1 建表
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                n = cmd.ExecuteNonQuery();
                conn.Close();
            }

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

            int n = 0;
            object obj;
            using (var conn = new SQLiteConnection(_conn))
            {
                conn.Open();
                //1 建表
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                n = cmd.ExecuteNonQuery();
                conn.Close();
            }

            Assert.IsTrue(n > 0);
        }
        /// <summary>
        /// TestSelect
        /// </summary>
        [TestMethod]
        public void TestSelect()
        {
            var sql = @"select * from person;";

            var list = new List<PsersonModel>();
            using (var conn = new SQLiteConnection(_conn))
            {
                conn.Open();
                //1 建表
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                var dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    list.Add(new PsersonModel()
                    {
                        name = dr["name"].ToString(),
                        age = int.Parse(dr["age"].ToString()),
                        sex = dr["sex"].ToString(),
                        create_time = DateTime.Parse(dr["create_time"].ToString())
                    });
                }
                conn.Close();
            }

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

测试代码：