/// <summary>
/// 初始化所有的表并创建没有的表
/// </summary>
/// <returns></returns>
internal bool InitAllTables()
{
Open();
try
{
Type[] ts = this.GetType().Assembly.GetTypes();
List <Type> types = ts.Where(t => t.BaseType != null && t.BaseType.Namespace.Equals(assembly) && t.BaseType.Name.Equals(typeof(SqliteDataTableBase).Name) && t.IsClass)
.Select(tt => tt).ToList();
foreach (var t in types)
{
string tablename = SqliteTabelCmdStrTool.GetTableName(t);
if (!CheckTable(tablename))
{
string cmdStr = SqliteTabelCmdStrTool.GetCreateTableCmdStr(t, true, true);
OperateRecords(cmdStr);
}
}
return(true);
}
catch (SqliteException e)
{
LogOperator.AddWarnningRecord("检查所有的表并创建没有的表时异常", e.Message);
return(false);
}
}
/// <summary>
/// 更新记录
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ts"></param>
/// <param name="args">存的是一个实例中的字段名与值，key为字段名字，value的List是实例的字段名字、类型和值</param>
/// <returns></returns>
internal bool Update <T>(List <T> ts, Dictionary <string, List <object> > args = null) where T : SqliteDataTableBase
{
Open();
bool hasIdPk = HasIdPK <T>();
Dictionary <string, string> argsStr = args != null?args.Select(arg => new { key = arg.Key, value = SqliteTabelCmdStrTool.GetFieldValue(arg.Value[0].ToString(), arg.Value[1] as Type, arg.Value[2]) }).ToDictionary(p => p.key, p => p.value) : null;
string cmdStr = SqliteTabelCmdStrTool.GetUpdateStr(ts, hasIdPk, argsStr);
SqliteTransaction st = connection.BeginTransaction();
try
{
int res = 0;
lock (_dbLocker)
{
res = OperateRecords(cmdStr);
if (res > 0)
{
st.Commit();
}
}
return(res > 0);
}
catch (SqliteException e)
{
st.Rollback();
LogOperator.AddWarnningRecord("更新记录时异常", e.Message);
return(false);
}
finally
{
Close();
}
}
/// <summary>
/// 删除记录
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ts"></param>
/// <returns></returns>
internal bool Delete <T>(List <T> ts) where T : SqliteDataTableBase
{
Open();
bool hasIdPk = HasIdPK <T>();
string cmdStr = SqliteTabelCmdStrTool.GetDeleteStr(ts, hasIdPk);
SqliteTransaction st = connection.BeginTransaction();
try
{
int res = 0;
lock (_dbLocker)
{
res = OperateRecords(cmdStr);
if (res > 0)
{
st.Commit();
}
}
return(res > 0);
}
catch (SqliteException e)
{
st.Rollback();
LogOperator.AddWarnningRecord("删除记录时异常", e.Message);
return(false);
}
finally
{
Close();
}
}
/// <summary>
/// 插入数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ts"></param>
/// <returns></returns>
internal bool Insert <T>(List <T> ts) where T : SqliteDataTableBase
{
Open();
bool hasIdIncre = HasAutoIncrementID <T>();
string cmdStr = SqliteTabelCmdStrTool.GetInsertStr(ts, hasIdIncre);
Debug.Log(cmdStr);
SqliteTransaction st = connection.BeginTransaction();
try
{
int res = 0;
lock (_dbLocker)
{
res = OperateRecords(cmdStr);
if (res > 0)
{
st.Commit();
}
}
return(res > 0);
}
catch (SqliteException e)
{
st.Rollback();
LogOperator.AddWarnningRecord("插入数据时异常", e.Message);
return(false);
}
finally
{
Close();
}
}
/// <summary>
/// 创建数据库
/// </summary>
/// <param name="dbPath"></param>
internal void CreateDB(string dbPath)
{
connection = new SqliteConnection(scsb.ToString());
lock (_dbLocker)
{
connection.Open();
// 设置自动删除空白页
OperateRecords(SqliteTabelCmdStrTool.GetAutoVacuum());
// 创建表
InitAllTables();
connection.Close();
}
}
/// <summary>
/// 结构是否存在自增长字段id
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
internal bool HasAutoIncrementID(Type t)
{
try
{
string cmdStr = SqliteTabelCmdStrTool.GetAutoIncrement(t);
DataTable dt = OperateQuery(cmdStr);
return(dt.Rows[0][0].ToString().Contains("INTEGER PRIMARY KEY AUTOINCREMENT"));
}
catch (SqliteException e)
{
LogOperator.AddWarnningRecord("执行查询结构是否存在自增长字段id时异常", e.Message);
return(false);
}
}
/// <summary>
/// 检查表是否存在
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tablename"></param>
/// <returns></returns>
internal bool CheckTable(string tablename)
{
try
{
int res = 0;
lock (_dbLocker)
{
string cmdStr = SqliteTabelCmdStrTool.GetCheckNameExist(tablename);
DataTable data = OperateQuery(cmdStr);
res = int.Parse(data.Rows[0][0].ToString());
}
return(res == 1);
}
catch (SqliteException e)
{
LogOperator.AddWarnningRecord("检查表存在出现异常", e.Message);
return(false);
}
}
/// <summary>
/// 是否有主键id
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
internal bool HasIdPK(Type t)
{
try
{
string cmdStr = SqliteTabelCmdStrTool.GetAutoIncrement(t);
DataTable dt = OperateQuery(cmdStr);
if (dt.Rows.Count > 0 && dt.Columns.Count > 0)
{
return(dt.Rows[0][0].ToString().ToUpper().Contains("ID INTEGER PRIMARY KEY"));
}
{
return(false);
}
}
catch (SqliteException e)
{
LogOperator.AddWarnningRecord("执行查询结构是否存在自增长字段id时异常", e.Message);
return(false);
}
}
/// <summary>
/// 检查表是否存在
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="type"></param>
/// <returns></returns>
internal bool CheckTable(Type type)
{
return(CheckTable(SqliteTabelCmdStrTool.GetTableName(type)));
}
/// <summary>
/// 条件查询，默认查询所有
/// 查询条件结构解释：key为字段名字，value的List是实例的字段名字、类型和值
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="args">存的是一个实例中的字段名与值，key为字段名字，value的List是实例的字段名字、类型和值</param>
/// <returns></returns>
internal List <T> Select <T>(Dictionary <string, List <object> > args = null) where T : SqliteDataTableBase, new()
{
Open();
Dictionary <string, string> argsStr = (args == null || args.Count == 0) ?
null : args.Select(arg => new { key = arg.Key, value = SqliteTabelCmdStrTool.GetFieldValue(arg.Value[0].ToString(), arg.Value[1] as Type, arg.Value[2]) })
.ToDictionary(p => p.key, p => p.value);
string cmdStr = SqliteTabelCmdStrTool.GetSelectStr <T>(argsStr);
SqliteTransaction st = connection.BeginTransaction();
try
{
List <T> ts = new List <T>();
lock (_dbLocker)
{
DataTable dt = OperateQuery(cmdStr);
if (dt.Rows.Count > 0)
{
Dictionary <string, FieldInfo> fNameType = new Dictionary <string, FieldInfo>();
FieldInfo[] fis = typeof(T).GetFields();
foreach (var fi in fis)
{
fNameType.Add(fi.Name, fi);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
T t = new T();
for (int j = 0; j < dt.Columns.Count; j++)
{
string cName = dt.Columns[j].ColumnName;
Type type = dt.Columns[j].DataType;
if (type == typeof(Int64) || type == typeof(Int32))
{
var v = dt.Rows[i][j];
fNameType[cName].SetValue(t, Convert.ToInt32(v));
}
else if (type == typeof(String))
{
var value = dt.Rows[i][j];
fNameType[cName].SetValue(t, value == null ? null : value.ToString());
}
else if (type == typeof(Single))
{
fNameType[cName].SetValue(t, Convert.ToSingle(dt.Rows[i][j]));
}
else if (type == typeof(Boolean))
{
fNameType[cName].SetValue(t, Convert.ToBoolean(dt.Rows[i][j]));
}
else if (type == typeof(Double))
{
fNameType[cName].SetValue(t, Convert.ToDouble(dt.Rows[i][j]));
}
else
{
// 找不到的就直接赋值
fNameType[cName].SetValue(t, dt.Rows[i][j]);
}
}
ts.Add(t);
}
return(ts);
}
else
{
return(null);
}
}
}
catch (SqliteException e)
{
st.Rollback();
LogOperator.AddWarnningRecord("查询记录时异常", e.Message);
return(null);
}
finally
{
Close();
}
}