我是码客，我是全栈工程师，我为自己代言。

WPF桌面端开发14-Sqlite

发表于 2020-02-07 | 分类于 wpf

sqlite

前言

安装

NuGet中查找System.Data.SQLite进行安装

或者使用命令

1	Install-Package System.Data.SQLite -Version 1.0.113.1

工具类

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Reflection;

namespace SchoolClient.Utils
{
    public class ZSqliteUtil
    {
        private static readonly string Dbfolder = AppDomain.CurrentDomain.BaseDirectory + "db\\";
        private static readonly string Dbpath = Dbfolder + "db.sqlite";

        private static SQLiteConnection _cn;

        private static SQLiteConnection GetConnect()
        {
            if (_cn == null)
            {
                DirectoryInfo di = new DirectoryInfo(Dbfolder);
                if (!di.Exists)
                {
                    Directory.CreateDirectory(Dbfolder);
                }
                _cn = new SQLiteConnection("data source=" + Dbpath);
                _cn.Open();
            }
            return _cn;
        }

        /// <summary>
        ///     删除库
        /// </summary>
        public static void DeleteDb()
        {
            if (_cn != null && _cn.State == ConnectionState.Open)
            {
                _cn.Close();
                if (File.Exists(Dbpath))
                {
                    File.Delete(Dbpath);
                }
            }
        }

        /// <summary>
        ///     创建表
        /// </summary>
        /// <param name="sql"></param>
        public static void CreateTable(string sql)
        {
            SQLiteCommand cmd = new SQLiteCommand { Connection = GetConnect(), CommandText = sql };
            cmd.ExecuteNonQuery();
        }

        /// <summary>
        ///     删除表
        /// </summary>
        /// <param name="tablename"></param>
        public static void DeleteTable(string tablename)
        {
            SQLiteCommand cmd = new SQLiteCommand { Connection = GetConnect(), CommandText = "DROP TABLE IF EXISTS " + tablename };
            cmd.ExecuteNonQuery();
        }

        /// <summary>
        ///     增加/删除/修改数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dic"></param>
        /// <returns></returns>
        public static int UpdateData(string sql, Dictionary<string, object> dic)
        {
            SQLiteCommand cmd = new SQLiteCommand { Connection = GetConnect(), CommandText = sql };
            foreach (KeyValuePair<string, object> kvp in dic)
            {
                if (kvp.Value is int)
                {
                    cmd.Parameters.Add(kvp.Key, DbType.Int32).Value = kvp.Value;
                }
                else if (kvp.Value is long)
                {
                    cmd.Parameters.Add(kvp.Key, DbType.Int64).Value = kvp.Value;
                }
                else if (kvp.Value is string)
                {
                    cmd.Parameters.Add(kvp.Key, DbType.String).Value = kvp.Value;
                }
            }
            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        ///     批量增加/删除/修改数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dicList"></param>
        public static void UpdateDataBatch(string sql, List<Dictionary<string, object>> dicList)
        {
            SQLiteConnection conn = GetConnect();
            SQLiteCommand cmd = new SQLiteCommand { Connection = conn };
            SQLiteTransaction tx = conn.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                foreach (Dictionary<string, object> dic in dicList)
                {
                    cmd.CommandText = sql;
                    foreach (KeyValuePair<string, object> kvp in dic)
                    {
                        if (kvp.Value is int)
                        {
                            cmd.Parameters.Add(kvp.Key, DbType.Int32).Value = kvp.Value;
                        }
                        else if (kvp.Value is long)
                        {
                            cmd.Parameters.Add(kvp.Key, DbType.Int64).Value = kvp.Value;
                        }
                        else if (kvp.Value is string)
                        {
                            cmd.Parameters.Add(kvp.Key, DbType.String).Value = kvp.Value;
                        }
                    }
                    cmd.ExecuteNonQuery();
                }
                tx.Commit();
            }
            catch (SQLiteException e)
            {
                tx.Rollback();
                throw new Exception(e.Message);
            }
        }

        public static int UpdateData(string sql)
        {
            SQLiteCommand cmd = new SQLiteCommand { Connection = GetConnect(), CommandText = sql };
            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        ///     清空表数据
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public static int DeleteAllData(string tablename)
        {
            SQLiteCommand cmd = new SQLiteCommand { Connection = GetConnect(), CommandText = "delete from " + tablename };
            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        ///     查询数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<T> QueryData<T>(string sql) where T : class, new()
        {
            //Console.WriteLine("sql:"+ sql);
            List<T> list = new List<T>();
            PropertyInfo[] propArr = new T().GetType().GetProperties();
            SQLiteCommand cmd = new SQLiteCommand { Connection = GetConnect(), CommandText = sql };
            SQLiteDataReader sr = cmd.ExecuteReader();
            while (sr.Read())
            {
                T itemModel = new T();
                foreach (PropertyInfo property in propArr)
                {
                    try
                    {
                        object value = sr[property.Name];
                        if (property.PropertyType == typeof(int))
                        {
                            value = int.Parse(value.ToString());
                        }
                        if (property.PropertyType == typeof(float))
                        {
                            value = float.Parse(value.ToString());
                        }
                        property.SetValue(
                            itemModel,
                            value,
                            null
                        );
                    }
                    catch (IndexOutOfRangeException)
                    {
                        Console.WriteLine($@"数据库中不包含:{property.Name}");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($@"Exception:{property.Name} {ex.Message}");
                    }
                }
                list.Add(itemModel);
            }
            sr.Close();
            return list;
        }

        /// <summary>
        ///     查询数据条数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int QueryCount(string sql)
        {
            SQLiteCommand cmd = new SQLiteCommand { Connection = GetConnect(), CommandText = sql };
            SQLiteDataReader sr = cmd.ExecuteReader();
            sr.Read();
            int count = sr.GetInt32(0);
            sr.Close();
            return count;
        }

        /// <summary>
        ///     遍历查询表结构
        /// </summary>
        public static void QueryAllTableInfo()
        {
            SQLiteCommand cmd = new SQLiteCommand { Connection = GetConnect(), CommandText = "SELECT name FROM sqlite_master WHERE TYPE='table' " };
            SQLiteDataReader sr = cmd.ExecuteReader();
            List<string> tables = new List<string>();
            while (sr.Read())
            {
                tables.Add(sr.GetString(0));
            }
            //datareader 必须要先关闭，否则 commandText 不能赋值
            sr.Close();
            foreach (string a in tables)
            {
                cmd.CommandText = $"PRAGMA TABLE_INFO({a})";
                Console.WriteLine($@"-------- 表名：{a} --------");
                sr = cmd.ExecuteReader();
                while (sr.Read())
                {
                    Console.WriteLine($@"{sr[0]} {sr[1]} {sr[2]} {sr[3]}");
                }
                sr.Close();
                Console.WriteLine(@"");
            }
        }
    }
}

注意

不要每次操作都打开关闭数据库连接，容易数据库锁死。

使用

相关实体类

public class ClassData
{
  public long unix { get; set; }

  public string api { get; set; }

  public string json { get; set; }

  public long askid { get; set; }
}

创建库

1	ZSqliteUtil.DeleteTable("TClassData");

创建表

1	ZSqliteUtil.CreateTable("CREATE TABLE IF NOT EXISTS TClassData(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,unix INTERGER,api TEXT,json TEXT,askid INTERGER)");

插入数据

Dictionary<string, object> dic = new Dictionary<string, object>();
dic.Add("unix", data.unix);
dic.Add("api", data.api);
dic.Add("json", data.json);
dic.Add("askid", data.askid);
ZSqliteUtil.UpdateData("INSERT INTO TClassData(unix,api,json,askid) VALUES(@unix,@api,@json,@askid)", dic);

查询数据

1	ZSqliteUtil.QueryData<ClassData>("select * from TClassData");

注意

Sqlite中的integer类型获取的时候都是long类型，如果对象的属性类型是int，会注入失败，要进行判断转换。