[游戏开发]Unity与Sqlite

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using Mono.Data.Sqlite;
using System.IO;
using System.Text;
using System.Reflection;
using System;

public class ConnectDB
{
    public SqliteConnection sqliteConnection;
    private SqliteCommand sqliteCommand;//增删改查命令
    public ConnectDB(string path)
    {
        if (!File.Exists(path))
        {
            CreateDB(path);//不存在数据库
        }
        ConnectSqlite(path);//连接数据库
        sqliteCommand = new SqliteCommand(sqliteConnection);
    }
    public bool CreateDB(string path)
    {
        try
        {
            string dirPath = new FileInfo(path).Directory.FullName;
            if (!Directory.Exists(dirPath))//不存在存放数据库的路径
                Directory.CreateDirectory(dirPath);
            SqliteConnection.CreateFile(path);
            return true;
        }
        catch (Exception e)
        {
            string str = string.Format("数据库创建异常:{0}", e.Message);
            Debug.Log(str);
            return false;
        }
    }
    public bool ConnectSqlite(string path)
    {
        try
        {
            sqliteConnection = new SqliteConnection(
        new SqliteConnectionStringBuilder() { DataSource = path }.ToString());
            sqliteConnection.Open();
            return true;
        }
        catch (Exception e)
        {
            string str = string.Format("数据库连接异常:{0}", e.Message);
            Debug.Log(str);
            return false;
        }
    }
    public void Dispose()
    {
        sqliteConnection.Dispose();
    }
    public int CreateTable()
    {
        string sql = "create table Test(id int,name string)";//表名 字段 和 类型
        sqliteCommand.CommandText = sql;
        return sqliteCommand.ExecuteNonQuery();
    }
    public int CreateTable<T>() where T : BaseData//建表
    {
        if (IsAlreadyTable<T>())
        {
            return -1;
        }
        StringBuilder sql = new StringBuilder();

        Type type = typeof(T);
        string tableName = type.Name;
        PropertyInfo[] pars = type.GetProperties();

        //SQL命令
        sql.Append("create table " + tableName + "(");

        for (int i = 0; i < pars.Length; i++)
        {
            object[] itemAttributes = pars[i].GetCustomAttributes(false);
            if ((itemAttributes[0] as ModelHelpAttribute).IsCreated)//该属性是否作为字段
            {
                sql.Append((itemAttributes[0] as ModelHelpAttribute).FieldName +
                    " " + (itemAttributes[0] as ModelHelpAttribute).FieldType);//数据字段 和 属性
                if ((itemAttributes[0] as ModelHelpAttribute).IsPrimaryKey)//该字段是否是主键
                {
                    sql.Append(" primary key ");
                }
                if ((itemAttributes[0] as ModelHelpAttribute).FieldIsNull)
                    sql.Append(" null");
                else
                    sql.Append(" not null");
                sql.Append(",");
            }
        }
        sql.Replace(',', ')', sql.Length - 1, 1);

        sqliteCommand.CommandText = sql.ToString();
        return sqliteCommand.ExecuteNonQuery();
    }
    public int DeleteTable<T>() where T : BaseData//删表
    {
        if (IsAlreadyTable<T>())
        {
            return -1;
        }
        string sql = string.Format("drop table {0}", typeof(T).Name);
        sqliteCommand.CommandText = sql;
        return sqliteCommand.ExecuteNonQuery();
    }
    public int Insert<T>(T t) where T : BaseData//表格插入数据
    {
        if (IsAlreadyTable<T>())
        {
            return -1;
        }
        if (t == default(T))
        {
            Debug.LogError("Insert参数错误");
            return -1;
        }
        Type type = typeof(T);
        StringBuilder sql = new StringBuilder();
        sql.Append("Insert into " + typeof(T).Name + "(");

        //列名
        PropertyInfo[] pars = type.GetProperties();
        for (int i = 0; i < pars.Length; i++)
        {
            object[] itemAttributes = pars[i].GetCustomAttributes(false);
            if ((itemAttributes[0] as ModelHelpAttribute).IsCreated)
            {
                sql.Append((itemAttributes[0] as ModelHelpAttribute).FieldName);
                sql.Append(",");
            }
        }
        sql.Replace(",", ")Values(", sql.Length - 1, 1);
        //对应的数值
        foreach (var item in pars)
        {
            object[] itemAttributes = item.GetCustomAttributes(false);
            if ((itemAttributes[0] as ModelHelpAttribute).IsCreated)
            {
                if ((itemAttributes[0] as ModelHelpAttribute).FieldType == "string")
                    sql.Append($"'{item.GetValue(t)}'");
                else
                    sql.Append(item.GetValue(t));
                sql.Append(",");
            }
        }
        sql.Replace(",", ")", sql.Length - 1, 1);
        sqliteCommand.CommandText = sql.ToString();
        return sqliteCommand.ExecuteNonQuery();
    }
    public int Insert<T>(List<T> t) where T : BaseData//表格插入多个数据
    {
        if (IsAlreadyTable<T>())
        {
            return -1;
        }
        if (t == null || t.Count == 0)
        {
            Debug.LogError("Insert参数错误");
            return -1;
        }
        Type type = typeof(T);
        StringBuilder sql = new StringBuilder();
        sql.Append("Insert into " + typeof(T).Name + "(");
        //添加列名
        PropertyInfo[] pars = type.GetProperties();
        for (int i = 0; i < pars.Length; i++)
        {
            object[] itemAttributes = pars[i].GetCustomAttributes(false);
            if ((itemAttributes[0] as ModelHelpAttribute).IsCreated)
            {
                sql.Append((itemAttributes[0] as ModelHelpAttribute).FieldName);
                sql.Append(",");
            }
        }
        sql.Replace(",", ")Values", sql.Length - 1, 1);
        //添加数据
        foreach (var item in t)
        {
            sql.Append("(");
            foreach (var it in pars)
            {
                object[] itemAttributes = it.GetCustomAttributes(false);
                if ((itemAttributes[0] as ModelHelpAttribute).IsCreated)
                {
                    if ((itemAttributes[0] as ModelHelpAttribute).FieldType == "string")
                        sql.Append($"'{it.GetValue(item)}'");
                    else
                        sql.Append(it.GetValue(item));
                    sql.Append(",");
                }
            }
            sql.Replace(",", "),", sql.Length - 1, 1);
        }
        sql.Remove(sql.Length - 1, 1);
        sqliteCommand.CommandText = sql.ToString();
        return sqliteCommand.ExecuteNonQuery();
    }
    public int DeleteByID<T>(int id)where T:BaseData//删除表中数据 通过Id
    {
        if (IsAlreadyTable<T>())
        {
            return -1;
        }
        string sql = $"Delete from {typeof(T).Name} where Id={id}";
        sqliteCommand.CommandText = sql;
        return sqliteCommand.ExecuteNonQuery();
    }
    public int DelteByID<T>(List<int> list) where T:BaseData//删除表中多个数据
    {
        if (IsAlreadyTable<T>())
        {
            return -1;
        }
        int count = 0;
        foreach (var item in list)
        {
            count += DeleteByID<T>(item);
        }
        return count;
    }
    public int DeleteBySql<T>(string sql)
    {
        sqliteCommand.CommandText = $"Delete from {typeof(T).Name} where {sql}";
        return sqliteCommand.ExecuteNonQuery();
    }
    public int Update<T>(T t) where T : BaseData
    {
        if (IsAlreadyTable<T>())
        {
            return -1;
        }
        if (t == default(T))
        {
            Debug.LogError("Insert参数错误");
            return -1;
        }
        Type type = typeof(T);
        StringBuilder sql = new StringBuilder();
        sql.Append($"Update {type.Name} set ");
        var pars = type.GetProperties();
        foreach (var item in pars)
        {
            if (item.GetCustomAttribute<ModelHelpAttribute>().IsCreated)
            {
                sql.Append($"{item.GetCustomAttribute<ModelHelpAttribute>().FieldName}=");
                if (item.GetCustomAttribute<ModelHelpAttribute>().FieldType == "string")
                {
                    sql.Append($"'{item.GetValue(t)}'");
                }
                else
                    sql.Append(item.GetValue(t));
                sql.Append(",");
            }
        }
        sql.Remove(sql.Length - 1, 1);
        sql.Append($" where Id={t.Id}");
        sqliteCommand.CommandText = sql.ToString();
        return sqliteCommand.ExecuteNonQuery();
    }
    public int Update<T>(List<T> list) where T : BaseData
    {
        if (IsAlreadyTable<T>())
        {
            return -1;
        }
        if (list == null || list.Count == 0)
        {
            Debug.Log("更新数据不对");
            return -1;
        }
        int count = 0;
        foreach (var item in list)
        {
            count += Update<T>(item);
        }
        return count;
    }
    public T SelectById<T>(int id) where T : BaseData//查询
    {
        var type = typeof(T);
        string sql = $"Select * from {type.Name} where Id={id}";
        sqliteCommand.CommandText = sql;
        SqliteDataReader sqliteData = sqliteCommand.ExecuteReader();
        //数据库表中的一行数据装换为实体
        if (sqliteData != null && sqliteData.Read())
        {
            return DBToData<T>(sqliteData);
        }
        return default(T);
    }
    public T DBToData<T>(SqliteDataReader sqliteData) where T : BaseData
    {
        try
        {
            List<string> fieldNames = new List<string>();
            for (int i = 0; i < sqliteData.FieldCount; i++)
            {
                fieldNames.Add(sqliteData.GetName(i));
            }
            var type = typeof(T);
            T data = Activator.CreateInstance<T>();
            var pars = type.GetProperties();//实体类的属性
            foreach (var p in pars)
            {
                if (!p.CanWrite) continue;
                var fieldName = p.GetCustomAttribute<ModelHelpAttribute>().FieldName;
                if (fieldNames.Contains(fieldName) && p.GetCustomAttribute<ModelHelpAttribute>().IsCreated)//数据库中有 该属性字段 该属性字段可以作为字段
                {
                    p.SetValue(data, sqliteData[fieldName]);
                }
            }
            return data;
        }
        catch (Exception e)
        {
            Debug.LogError($"转换出错：{e.Message}");
            return null;
        }
    }
    //public List<T> SelectAll<T>() where T : BaseData//查询所有
    //{
    //    var ret = new List<T>();
    //    var type = typeof(T);
    //    string sql = $"Select * from {type.Name}";
    //    sqliteCommand.CommandText = sql;
    //    SqliteDataReader sqliteData = sqliteCommand.ExecuteReader();
    //    //数据库表中的一行数据装换为实体
    //    if (sqliteData != null)
    //    {
    //        while (sqliteData.Read())
    //            ret.Add(DBToData<T>(sqliteData));
    //    }
    //    return ret;
    //}
    public List<T> SelectBySql<T>(string sqlWhere = "") where T : BaseData
    {
        var ret = new List<T>();
        var type = typeof(T);
        string sql;

        if (string.IsNullOrEmpty(sqlWhere))
        {
            sql = $"Select * from {type.Name}";
        }
        else
        {
            sql = $"Select * from {type.Name} where {sqlWhere}";
        }

        sqliteCommand.CommandText = sql;
        SqliteDataReader sqliteData = sqliteCommand.ExecuteReader();
        //数据库表中的一行数据装换为实体
        if (sqliteData != null)
        {
            while (sqliteData.Read())
                ret.Add(DBToData<T>(sqliteData));
        }
        return ret;
    }
    public bool IsAlreadyTable<T>() where T : BaseData//判断表是否存在
    {
        string sql = $"Select count(*) From sqlite_master Where type='table' and name='{typeof(T).Name}'";
        sqliteCommand.CommandText = sql;
        return Convert.ToInt32(sqliteCommand.ExecuteScalar()) == 1;
    }
}
public class CharacterData : BaseData
{
    private float height;
    private string name;
    private bool sex;
    [ModelHelp(false, "Height", "float", false)]
    public float Height
    {
        get
        {
            return height;
        }

        set
        {
            height = value;
        }
    }
    [ModelHelp(true, "Name", "string", false)]
    public string Name
    {
        get
        {
            return name;
        }

        set
        {
            name = value;
        }
    }
    [ModelHelp(true, "Sex", "bool", false)]
    public bool Sex
    {
        get
        {
            return sex;
        }

        set
        {
            sex = value;
        }
    }
}
public class BaseData
{
    private int id;
    [ModelHelp(true, "Id", "int", true)]
    public int Id
    {
        get
        {
            return id;
        }

        set
        {
            id = value;
        }
    }
}
public class ModelHelpAttribute : Attribute//使用特性判断 属性是否可以作为数据库的 字段
{
    public bool IsCreated { get; set; }
    public string FieldName { get; set; }
    public string FieldType { get; set; }
    public bool IsPrimaryKey { get; set; }
    public bool FieldIsNull { get; set; }
    public ModelHelpAttribute(bool isCreated, string fieldName, string fieldType, bool isPrimaryKey,
        bool filedIsNull = false)
    {
        IsCreated = isCreated;
        FieldName = fieldName;
        FieldType = fieldType;
        IsPrimaryKey = isPrimaryKey;
        FieldIsNull = FieldIsNull;
    }
}