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;
}
}
Unity与Sqlite
最新推荐文章于 2024-02-13 17:55:13 发布