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

约定不等于承诺〃 2024-02-17 17:58 49阅读 0赞

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

测试代码：