/// <summary>
/// 分类查找
/// 根据元件编号查找子分类列表
/// </summary>
/// <param name="model_num">元件编号</param>
/// <returns></returns>
public static DataCell_model SelectModelInfoByNum(string model_num)
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
string query = "SELECT * " + " From " + SQLInfo.model_table;
query += " WHERE " + "model_num = '" + model_num + "'";
DataCell_model datamode = new DataCell_model();
SqliteDataReader sqlData = db.ExecuteQuery(query);
while (sqlData.Read())
{
datamode.Model_id = sqlData.GetInt32(sqlData.GetOrdinal("model_ID"));
datamode.Model_num = sqlData.GetString(sqlData.GetOrdinal("model_num"));
datamode.Model_name = sqlData.GetString(sqlData.GetOrdinal("model_name"));
datamode.Model_address = sqlData.GetString(sqlData.GetOrdinal("model_address"));
datamode.Modle_ThumbnailAddress = sqlData.GetString(sqlData.GetOrdinal("modle_ThumbnailAddress"));
datamode.Model_Introduction = sqlData.GetString(sqlData.GetOrdinal("model_Introduction"));
datamode.Model_classify_name = sqlData.GetString(sqlData.GetOrdinal("model_classify_name"));
}
sqlData.Close();
db.CloseSqlConnection();
return(datamode);
}
/// <summary>
/// 模糊搜索
/// </summary>
/// <param name="strSearch">搜索内容</param>
/// <returns></returns>
public static List <DataCell_model> FuzzySelectModelListInfo(string strSearch)
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
List <DataCell_model> datamodelyList = new List <DataCell_model>();
string query = "SELECT * " + " From " + SQLInfo.model_table;
query += " WHERE " + "model_name like '%" + strSearch + "%'";
SqliteDataReader sqlData = db.ExecuteQuery(query);
while (sqlData.Read())
{
DataCell_model datamode = new DataCell_model();
datamode.Model_id = sqlData.GetInt32(sqlData.GetOrdinal("model_ID"));
datamode.Model_num = sqlData.GetString(sqlData.GetOrdinal("model_num"));
datamode.Model_name = sqlData.GetString(sqlData.GetOrdinal("model_name"));
datamode.Model_address = sqlData.GetString(sqlData.GetOrdinal("model_address"));
datamode.Modle_ThumbnailAddress = sqlData.GetString(sqlData.GetOrdinal("modle_ThumbnailAddress"));
datamode.Model_Introduction = sqlData.GetString(sqlData.GetOrdinal("model_Introduction"));
datamode.Model_classify_name = sqlData.GetString(sqlData.GetOrdinal("model_classify_name"));
datamode.Model_type = sqlData.GetString(sqlData.GetOrdinal("model_type"));
datamodelyList.Add(datamode);
}
sqlData.Close();
db.CloseSqlConnection();
Debug.Log(datamodelyList.Count);
return(datamodelyList);
}
/// <summary>
/// 分类查找
/// 根据分类父物体查找子分类列表
/// </summary>
/// <param name="parentid">分类父分类id</param>
/// <returns></returns>
public static List <DataCell_classify> SelectClassifyListInfo(int parentid)
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
List <DataCell_classify> dataclassifyList = new List <DataCell_classify>();
// db.UpdateInto(SQLInfo.classify_table, dataclassify.Classify_name, dataclassify.Classify_parentID, dataclassify.Classify_numpos)
string query = "SELECT classify_id,classify_name,classify_parent,classify_pos " + " From " + SQLInfo.classify_table;
query += " WHERE " + "classify_parent = " + parentid;
SqliteDataReader sqlData = db.ExecuteQuery(query);
while (sqlData.Read())
{
DataCell_classify dataclassify = new DataCell_classify();
dataclassify.Classify_id = sqlData.GetInt32(sqlData.GetOrdinal("classify_id"));
dataclassify.Classify_name = sqlData.GetString(sqlData.GetOrdinal("classify_name"));
dataclassify.Classify_numpos = sqlData.GetInt32(sqlData.GetOrdinal("classify_pos"));
dataclassify.Classify_parentID = sqlData.GetInt32(sqlData.GetOrdinal("classify_parent"));
dataclassifyList.Add(dataclassify);
// Debug.Log(" Classify_id:" + dataclassify.Classify_id + " Classify_name:" + dataclassify.Classify_name + " Classify_parentID:" + dataclassify.Classify_parentID + " Classify_numpos:" + dataclassify.Classify_numpos + "\n");
}
sqlData.Close();
db.CloseSqlConnection();
Debug.Log(dataclassifyList.Count);
return(dataclassifyList);
}
/// <summary>
/// Delete Licence having guid
/// </summary>
/// <param name="guid">Unique identity</param>
/// <returns>Return true if saved, else false</returns>
public static bool Delete(string exePath)
{
string sql = "DELETE FROM " + TABLE_NAME + " WHERE ExePath = {0}";
sql = string.Format(sql, SqliteDbHelper.SQLString(exePath));
if (string.IsNullOrEmpty(SqliteDbHelper.ExecuteQuery(sql)))
{
return(true);
}
return(false);
}
public static bool Delete(int processId)
{
string sql = "DELETE FROM Transactions WHERE ProcessId = {0}";
sql = string.Format(sql, SqliteDbHelper.SQLNumber(processId));
if (string.IsNullOrEmpty(SqliteDbHelper.ExecuteQuery(sql)))
{
return(true);
}
return(false);
}
/// <summary>
/// Delete transaction having GUID
/// </summary>
/// <param name="guid">Unique identity</param>
/// <returns>Return true if saved, else false</returns>
public static bool Delete(string guid)
{
string sql = "DELETE FROM Transactions WHERE Guid = {0}";
sql = string.Format(sql, SqliteDbHelper.SQLString(guid));
if (string.IsNullOrEmpty(SqliteDbHelper.ExecuteQuery(sql)))
{
return(true);
}
return(false);
}
/// <summary>
/// Update Last run start time, will keep track of when component start
/// </summary>
/// <param name="exePath"></param>
/// <param name="startTime"></param>
/// <returns></returns>
public static bool UpdateLastRunStartTime(string exePath, DateTime startTime)
{
string sql = string.Format("UPDATE " + TABLE_NAME + " Set LastRunStart = {0} where ExePath = {1} ", SqliteDbHelper.SQLDateTime(startTime), SqliteDbHelper.SQLString(exePath));
SingletonLogger.Instance.Debug(sql);
if (string.IsNullOrEmpty(SqliteDbHelper.ExecuteQuery(sql)))
{
return(true);
}
return(false);
}
/// <summary>
/// 元件模型删除
/// </summary>
/// <param name="mode_num">元件编号</param>
/// <returns></returns>
public static void DeleteModelInfo(string mode_num)
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
string query = "Delete From" + SQLInfo.model_table;
query += " WHERE " + "model_num = '" + mode_num + "'";
db.ExecuteQuery(query);
db.CloseSqlConnection();
}
/// <summary>
/// 元件模型删除
/// </summary>
public static void DeleteModelInfo(DataCell_model dataCell_model)
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
DataCell_model datamodel = dataCell_model;
string query = "Delete From" + SQLInfo.model_table;
query += " WHERE " + "model_id = " + datamodel.Model_id;
db.ExecuteQuery(query);
db.CloseSqlConnection();
}
/// <summary>
/// 分类添加
/// </summary>
public static bool AddClassifyInfo(DataCell_classify dataCell_calssify)
{
try
{
DataCell_classify dataclassify = dataCell_calssify;
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
string querySelect = "SELECT * " + " From " + SQLInfo.classify_table;
querySelect += " WHERE " + "classify_name = '" + dataclassify.Classify_name + "'";
SqliteDataReader sqlData = db.ExecuteQuery(querySelect);
if (sqlData.HasRows)
{
//**存在名称一样的**//
if (sqlData != null)
{
sqlData.Dispose();
}
sqlData = null;
db.CloseSqlConnection();
return(false);
}
if (sqlData != null)
{
sqlData.Dispose();
}
sqlData = null;
db.CloseSqlConnection();
SqliteDbHelper db1;
db1 = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
// string[] aa = new string[] { null, dataclassify.Classify_name, dataclassify.Classify_parentID.ToString(), dataclassify.Classify_numpos.ToString() };
// db.InsertClassifyInto(SQLInfo.classify_table, dataclassify.Classify_name, dataclassify.Classify_parentID, dataclassify.Classify_numpos);
string query = "INSERT INTO " + SQLInfo.classify_table + " VALUES (" + "null" + "," + "'" + dataclassify.Classify_name + "'" + "," + dataclassify.Classify_parentID + "," + dataclassify.Classify_numpos;
query += ")";
db1.ExecuteQuery(query);
db1.CloseSqlConnection();
return(true);
}
catch (Exception e)
{
Debug.Log(e.ToString());
return(false);
}
finally
{
}
}
/// <summary>
/// 分类添加
/// </summary>
public static bool AddClassifyInfo(DataCell_classify dataCell_calssify)
{
try
{
DataCell_classify dataclassify = dataCell_calssify;
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
string querySelect = "SELECT * " + " From " + SQLInfo.classify_table;
querySelect += " WHERE " + "classify_name = '" + dataclassify.Classify_name + "'";
SqliteDataReader sqlData = db.ExecuteQuery(querySelect);
if (sqlData.HasRows)
{
//**存在名称一样的**//
if (sqlData != null)
{
sqlData.Dispose();
}
sqlData = null;
db.CloseSqlConnection();
return false;
}
if (sqlData != null)
{
sqlData.Dispose();
}
sqlData = null;
db.CloseSqlConnection();
SqliteDbHelper db1;
db1 = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
// string[] aa = new string[] { null, dataclassify.Classify_name, dataclassify.Classify_parentID.ToString(), dataclassify.Classify_numpos.ToString() };
// db.InsertClassifyInto(SQLInfo.classify_table, dataclassify.Classify_name, dataclassify.Classify_parentID, dataclassify.Classify_numpos);
string query = "INSERT INTO " + SQLInfo.classify_table + " VALUES (" + "null" + "," + "'" + dataclassify.Classify_name + "'" + "," + dataclassify.Classify_parentID + "," + dataclassify.Classify_numpos;
query += ")";
db1.ExecuteQuery(query);
db1.CloseSqlConnection();
return true;
}
catch (Exception e)
{
Debug.Log(e.ToString());
return false;
}
finally
{
}
}
/// <summary>
/// Update Last run end time, will keep track of when component end
/// </summary>
/// <param name="exePath"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public static bool UpdateLastRunEndTime(string exeName, DateTime endTime)
{
//Get exepath from exeName
exeName = GetExePathByExeName(exeName);
string sql = string.Format("UPDATE " + TABLE_NAME + " Set LastRunEnd = {0} where ExePath = {1} ", SqliteDbHelper.SQLDateTime(endTime), SqliteDbHelper.SQLString(exeName));
SingletonLogger.Instance.Debug(sql);
if (string.IsNullOrEmpty(SqliteDbHelper.ExecuteQuery(sql)))
{
return(true);
}
return(false);
}
static int ExecuteQuery(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 2);
SqliteDbHelper obj = (SqliteDbHelper)ToLua.CheckObject(L, 1, typeof(SqliteDbHelper));
string arg0 = ToLua.CheckString(L, 2);
Mono.Data.Sqlite.SqliteDataReader o = obj.ExecuteQuery(arg0);
ToLua.PushObject(L, o);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
/// <summary>
/// <summary>
/// 修改分类名称
/// </summary>
/// <param name="classify_currentname">当前分类名称</param>
/// <param name="classify_targetname">修改后分类名称</param>
/// <returns></returns>
public static bool UpdateClassifyInfo(string classify_currentname, string classify_targetname)
{
try
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
string querySelect = "SELECT * " + " From " + SQLInfo.classify_table;
querySelect += " WHERE " + "classify_name = " + classify_targetname;
SqliteDataReader sqlData = db.ExecuteQuery(querySelect);
if (sqlData.HasRows)
{
//**该名称已经存在**//
if (sqlData != null)
{
sqlData.Dispose();
}
sqlData = null;
db.CloseSqlConnection();
return(false);
}
if (sqlData != null)
{
sqlData.Dispose();
}
sqlData = null;
db.CloseSqlConnection();
SqliteDbHelper db2;
db2 = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
string query = "UPDATE " + SQLInfo.classify_table + " SET " + "classify_name = '" + classify_targetname + "'";
query += " WHERE " + "classify_name = '" + classify_currentname + "'";
db2.ExecuteQuery(query);
db2.CloseSqlConnection();
return(true);
}
catch (Exception e)
{
Debug.Log(e.ToString());
return(false);
}
finally
{
}
}
/// <summary>
/// 分类修改
/// </summary>
public static bool UpdateClassifyInfo(DataCell_classify dataCell_calssify)
{
try
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
DataCell_classify dataclassify = dataCell_calssify;
string querySelect = "SELECT * " + " From " + SQLInfo.classify_table;
querySelect += " WHERE " + "classify_name = '" + dataclassify.Classify_name + "'";
SqliteDataReader sqlData = db.ExecuteQuery(querySelect);
if (sqlData.HasRows)
{
if (sqlData != null)
{
sqlData.Dispose();
}
sqlData = null;
db.CloseSqlConnection();
return(false);
}
if (sqlData != null)
{
sqlData.Dispose();
}
sqlData = null;
db.CloseSqlConnection();
SqliteDbHelper db2;
db2 = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
// db.UpdateInto(SQLInfo.classify_table, dataclassify.Classify_name, dataclassify.Classify_parentID, dataclassify.Classify_numpos)
string query = "UPDATE " + SQLInfo.classify_table + " SET " + "classify_name = " + "'" + dataclassify.Classify_name + "'" + "," + "classify_parent = " + dataclassify.Classify_parentID + "," + "classify_pos =" + dataclassify.Classify_numpos;
query += " WHERE " + "classify_id = " + dataclassify.Classify_id;
db2.ExecuteQuery(query);
db2.CloseSqlConnection();
return(true);
}
catch (Exception e)
{
Debug.Log(e.ToString());
return(false);
}
finally
{
}
}
/// <summary>
/// Save the transaction,
/// <para>If GUID not exists then insert new </para>
/// <para> Otherwise Update existing</para>
/// </summary>
/// <param name="forceInsert">Insert a new transaction force fully</param>
/// <returns>Return true if saved, else false</returns>
public bool Save(bool forceInsert = false)
{
string sql = string.Empty;
if (forceInsert)
{
sql = string.Format("INSERT INTO Transactions (Guid, ProcessId, Application, ExePath, StartedAt, EndedAt, Enabled) Values ({0}, {1}, {2}, {3}, {4}, {5}, {6})", SqliteDbHelper.SQLString(this.Guid), SqliteDbHelper.SQLNumber(this.ProcessId), SqliteDbHelper.SQLString(this.Application), SqliteDbHelper.SQLString(this.ExeName), SqliteDbHelper.SQLDateTime(this.StartedAt), SqliteDbHelper.SQLDateTime(this.EndedAt), SqliteDbHelper.SQLBoolean(this.Enabled));
}
else
{
Transaction transaction = Transaction.Get(this.Guid);
if (transaction != null) //ByJASPREET(09/jan/2015):it insert row sometime after delete by exe successfully.
{
sql = string.Format("UPDATE Transactions Set ProcessId = {0}, Application = {1},ExePath = {2}, StartedAt = {3}, EndedAt = {4}, Enabled = {5} where Guid = {6} ", SqliteDbHelper.SQLNumber(this.ProcessId), SqliteDbHelper.SQLString(this.Application), SqliteDbHelper.SQLString(this.ExeName), SqliteDbHelper.SQLDateTime(this.StartedAt), SqliteDbHelper.SQLDateTime(this.EndedAt), SqliteDbHelper.SQLBoolean(this.Enabled), SqliteDbHelper.SQLString(this.Guid));
}
}
if (string.IsNullOrEmpty(SqliteDbHelper.ExecuteQuery(sql)))
{
_Saved = true;
_Dirty = false;
}
return(_Saved);
}
public bool InsertAndUpdate()
{
string sql = string.Empty;
Licence transaction = Licence.GetInfo(this.OldExePath);
if (transaction == null)
{
sql = string.Format("INSERT INTO " + TABLE_NAME + " (ExePath, Type, TimeInterval,MaxTime,ConcurrentLicences,LastRunStart,LastRunEnd , Enabled) Values ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7})", SqliteDbHelper.SQLString(this.ExePath), SqliteDbHelper.SQLString(this.Type.ToString()), SqliteDbHelper.SQLNumber(this.TimeInterval), SqliteDbHelper.SQLNumber(this.MaxTime), SqliteDbHelper.SQLNumber(this.ConcurrentLicences), SqliteDbHelper.SQLDateTime(this.LastRunStart), SqliteDbHelper.SQLDateTime(this.LastRunEnd), SqliteDbHelper.SQLBoolean(this.Enabled));
}
else
{
this.LastRunEnd = transaction.LastRunEnd;
this.LastRunStart = transaction.LastRunStart;
sql = string.Format("UPDATE " + TABLE_NAME + " Set Type = {1}, TimeInterval = {2}, MaxTime ={3}, ConcurrentLicences = {4}, LastRunStart={5}, LastRunEnd={6}, Enabled = {7},ExePath = {8} WHERE ExePath = {0} ", SqliteDbHelper.SQLString(this.OldExePath), SqliteDbHelper.SQLString(this.Type.ToString()), SqliteDbHelper.SQLNumber(this.TimeInterval), SqliteDbHelper.SQLNumber(this.MaxTime), SqliteDbHelper.SQLNumber(this.ConcurrentLicences), SqliteDbHelper.SQLDateTime(this.LastRunStart), SqliteDbHelper.SQLDateTime(this.LastRunEnd), SqliteDbHelper.SQLBoolean(this.Enabled), SqliteDbHelper.SQLString(this.ExePath));
}
if (string.IsNullOrEmpty(SqliteDbHelper.ExecuteQuery(sql)))
{
_Saved = true;
_Dirty = false;
}
return(_Saved);
}
/// <summary>
/// 分类修改
/// </summary>
public static bool UpdateClassifyInfo(DataCell_classify dataCell_calssify)
{
try
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
DataCell_classify dataclassify = dataCell_calssify;
string querySelect = "SELECT * " + " From " + SQLInfo.classify_table;
querySelect += " WHERE " + "classify_name = '" + dataclassify.Classify_name + "'";
SqliteDataReader sqlData = db.ExecuteQuery(querySelect);
if (sqlData.HasRows)
{
if (sqlData != null)
{
sqlData.Dispose();
}
sqlData = null;
db.CloseSqlConnection();
return false;
}
if (sqlData != null)
{
sqlData.Dispose();
}
sqlData = null;
db.CloseSqlConnection();
SqliteDbHelper db2;
db2 = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
// db.UpdateInto(SQLInfo.classify_table, dataclassify.Classify_name, dataclassify.Classify_parentID, dataclassify.Classify_numpos)
string query = "UPDATE " + SQLInfo.classify_table + " SET " + "classify_name = " + "'" + dataclassify.Classify_name + "'" + "," + "classify_parent = " + dataclassify.Classify_parentID + "," + "classify_pos =" + dataclassify.Classify_numpos;
query += " WHERE " + "classify_id = " + dataclassify.Classify_id;
db2.ExecuteQuery(query);
db2.CloseSqlConnection();
return true;
}
catch (Exception e)
{
Debug.Log(e.ToString());
return false;
}
finally
{
}
}
/// <summary>
/// 模型元件查找
/// 根据分类父物体查找子分类列表
/// </summary>
/// <param name="model_classify_name">分类名称</param>
/// <returns></returns>
public static List<DataCell_model> SelectModelListInfo(string model_classify_name)
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
List<DataCell_model> datamodelyList = new List<DataCell_model>();
string query = "SELECT * " + " From " + SQLInfo.model_table;
query += " WHERE " + "model_classify_name = '" + model_classify_name + "'";
SqliteDataReader sqlData = db.ExecuteQuery(query);
while (sqlData.Read())
{
DataCell_model datamode = new DataCell_model();
datamode.Model_id = sqlData.GetInt32(sqlData.GetOrdinal("model_ID"));
datamode.Model_num = sqlData.GetString(sqlData.GetOrdinal("model_num"));
datamode.Model_name = sqlData.GetString(sqlData.GetOrdinal("model_name"));
datamode.Model_address = sqlData.GetString(sqlData.GetOrdinal("model_address"));
datamode.Modle_ThumbnailAddress = sqlData.GetString(sqlData.GetOrdinal("modle_ThumbnailAddress"));
datamode.Model_Introduction = sqlData.GetString(sqlData.GetOrdinal("model_Introduction"));
datamode.Model_classify_name = sqlData.GetString(sqlData.GetOrdinal("model_classify_name"));
datamode.Model_type = sqlData.GetString(sqlData.GetOrdinal("model_type"));
datamodelyList.Add(datamode);
}
sqlData.Close();
db.CloseSqlConnection();
Debug.Log(datamodelyList.Count);
return datamodelyList;
}
/// <summary>
/// 分类查找
/// 根据元件编号查找子分类列表
/// </summary>
/// <param name="model_num">元件编号</param>
/// <returns></returns>
public static DataCell_model SelectModelInfoByNum(string model_num)
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
string query = "SELECT * " + " From " + SQLInfo.model_table;
query += " WHERE " + "model_num = '" + model_num + "'";
DataCell_model datamode = new DataCell_model();
SqliteDataReader sqlData = db.ExecuteQuery(query);
while (sqlData.Read())
{
datamode.Model_id = sqlData.GetInt32(sqlData.GetOrdinal("model_ID"));
datamode.Model_num = sqlData.GetString(sqlData.GetOrdinal("model_num"));
datamode.Model_name = sqlData.GetString(sqlData.GetOrdinal("model_name"));
datamode.Model_address = sqlData.GetString(sqlData.GetOrdinal("model_address"));
datamode.Modle_ThumbnailAddress = sqlData.GetString(sqlData.GetOrdinal("modle_ThumbnailAddress"));
datamode.Model_Introduction = sqlData.GetString(sqlData.GetOrdinal("model_Introduction"));
datamode.Model_classify_name = sqlData.GetString(sqlData.GetOrdinal("model_classify_name"));
}
sqlData.Close();
db.CloseSqlConnection();
return datamode;
}
/// <summary>
/// 分类查找
/// 根据分类父物体查找子分类列表
/// </summary>
/// <param name="parentid">分类父分类id</param>
/// <returns></returns>
public static List<DataCell_classify> SelectClassifyListInfo(int parentid)
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
List<DataCell_classify> dataclassifyList = new List<DataCell_classify>();
// db.UpdateInto(SQLInfo.classify_table, dataclassify.Classify_name, dataclassify.Classify_parentID, dataclassify.Classify_numpos)
string query = "SELECT classify_id,classify_name,classify_parent,classify_pos " + " From " + SQLInfo.classify_table;
query += " WHERE " + "classify_parent = " + parentid;
SqliteDataReader sqlData = db.ExecuteQuery(query);
while (sqlData.Read())
{
DataCell_classify dataclassify = new DataCell_classify();
dataclassify.Classify_id = sqlData.GetInt32(sqlData.GetOrdinal("classify_id"));
dataclassify.Classify_name = sqlData.GetString(sqlData.GetOrdinal("classify_name"));
dataclassify.Classify_numpos = sqlData.GetInt32(sqlData.GetOrdinal("classify_pos"));
dataclassify.Classify_parentID = sqlData.GetInt32(sqlData.GetOrdinal("classify_parent"));
dataclassifyList.Add(dataclassify);
// Debug.Log(" Classify_id:" + dataclassify.Classify_id + " Classify_name:" + dataclassify.Classify_name + " Classify_parentID:" + dataclassify.Classify_parentID + " Classify_numpos:" + dataclassify.Classify_numpos + "\n");
}
sqlData.Close();
db.CloseSqlConnection();
Debug.Log(dataclassifyList.Count);
return dataclassifyList;
}
/// <summary>
/// 分类查找
/// 根据ID查找子分类列表
/// </summary>
public static DataCell_classify SelectClassifyInfo(int classify_id)
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
// List<DataCell_classify> dataclassifyList = new List<DataCell_classify>();
// db.UpdateInto(SQLInfo.classify_table, dataclassify.Classify_name, dataclassify.Classify_parentID, dataclassify.Classify_numpos)
string query = "SELECT classify_id,classify_name,classify_parent,classify_pos " + " From " + SQLInfo.classify_table;
query += " WHERE " + "classify_id = " + classify_id;
DataCell_classify dataclassify = new DataCell_classify();
SqliteDataReader sqlData = db.ExecuteQuery(query);
while (sqlData.Read())
{
dataclassify.Classify_id = sqlData.GetInt32(sqlData.GetOrdinal("classify_id"));
dataclassify.Classify_name = sqlData.GetString(sqlData.GetOrdinal("classify_name"));
dataclassify.Classify_numpos = sqlData.GetInt32(sqlData.GetOrdinal("classify_pos"));
dataclassify.Classify_parentID = sqlData.GetInt32(sqlData.GetOrdinal("classify_parent"));
}
sqlData.Close();
db.CloseSqlConnection();
return dataclassify;
}
/// <summary>
/// Create schema for Transaction table if it is not exist
/// </summary>
/// <returns>return true if created, else false</returns>
public static void CreateSchema()
{
string sql = "Create Table IF NOT EXISTS Transactions (Guid VARCHAR(100), ProcessId INT, Application VARCHAR(100),ExePath VARCHAR(100), StartedAt VARCHAR(30), EndedAt VARCHAR(30), Enabled BOOLEAN)";
SqliteDbHelper.ExecuteQuery(sql);
}
/// <summary>
/// 元件模型删除
/// </summary>
/// <param name="mode_num">元件编号</param>
/// <returns></returns>
public static void DeleteModelInfo(string mode_num)
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
string query = "Delete From" + SQLInfo.model_table;
query += " WHERE " + "model_num = '" + mode_num + "'";
db.ExecuteQuery(query);
db.CloseSqlConnection();
}
/// <summary>
/// 元件模型删除
/// </summary>
public static void DeleteModelInfo(DataCell_model dataCell_model)
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
DataCell_model datamodel = dataCell_model;
string query = "Delete From" + SQLInfo.model_table;
query += " WHERE " + "model_id = " + datamodel.Model_id;
db.ExecuteQuery(query);
db.CloseSqlConnection();
}
// CREATE TABLE "modelcell" (
//[model_ID] INT,
//[model_num] VARCHAR(32),
//[model_name] VARCHAR(100),
//[model_address] VARCHAR(300),
//[modle_ThumbnailAddress] VARCHAR(300),
//[model_Introduction] VARCHAR(3000),
//[model_classify_name] VARCHAR(300),
//CONSTRAINT [] PRIMARY KEY ([model_ID]));
/// <summary>
/// 元件模型修改
/// </summary>
public static bool UpdateModelInfo(DataCell_model dataCell_model)
{
try
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
DataCell_model datamodel = dataCell_model;
string querySelect = "SELECT * " + " From " + SQLInfo.model_table;
querySelect += " WHERE " + "model_num = '" + datamodel.Model_num + "'";
SqliteDataReader sqlData = db.ExecuteQuery(querySelect);
if (!sqlData.HasRows)
{
//**存在名称一样的**//
if (sqlData != null)
{
sqlData.Dispose();
}
sqlData = null;
db.CloseSqlConnection();
return(false);
}
if (sqlData != null)
{
sqlData.Dispose();
}
sqlData = null;
db.CloseSqlConnection();
if (datamodel.Model_address != "")
{
Debug.Log("dataCell_model.Model_address:" + dataCell_model.Model_address);
string targetPath = Application.dataPath + SQLInfo.Model_path;
if (datamodel.Model_address != targetPath)
{
string path = datamodel.Model_address;
if (!FileHelper.CopyDir(ref path, targetPath))
{
return(false);
}
datamodel.Model_address = path;
}
}
if (datamodel.Modle_ThumbnailAddress != "")
{
Debug.Log("dataCell_model.Modle_ThumbnailAddress:" + dataCell_model.Modle_ThumbnailAddress + " Path:" + Path.GetFileName(dataCell_model.Modle_ThumbnailAddress));
string targetPath = Application.dataPath + SQLInfo.Texture_path;
if (datamodel.Modle_ThumbnailAddress != targetPath)
{
string path = datamodel.Modle_ThumbnailAddress;
if (!FileHelper.CopyDir(ref path, targetPath))
{
return(false);
}
datamodel.Modle_ThumbnailAddress = path;
}
}
SqliteDbHelper db1;
db1 = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
string query = "UPDATE " + SQLInfo.model_table + " SET " + "model_num = '" + datamodel.Model_num + "'," + "model_name = '" + datamodel.Model_name + "'," + "model_address = '" + datamodel.Model_address;
query += "'," + "modle_ThumbnailAddress = '" + datamodel.Modle_ThumbnailAddress + "'," + "model_Introduction = '" + datamodel.Model_Introduction + "'," + "model_classify_name = '" + datamodel.Model_classify_name + "' + " + " model_type = '" + datamodel.Model_type + "'";
query += " WHERE " + "model_id = " + datamodel.Model_id;
db1.ExecuteQuery(query);
db1.CloseSqlConnection();
return(true);
}
catch (Exception e)
{
Debug.Log(e.ToString());
return(false);
}
finally
{
}
}
/// <summary>
/// <summary>
/// 修改分类名称
/// </summary>
/// <param name="classify_currentname">当前分类名称</param>
/// <param name="classify_targetname">修改后分类名称</param>
/// <returns></returns>
public static bool UpdateClassifyInfo(string classify_currentname, string classify_targetname)
{
try
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
string querySelect = "SELECT * " + " From " + SQLInfo.classify_table;
querySelect += " WHERE " + "classify_name = " + classify_targetname;
SqliteDataReader sqlData = db.ExecuteQuery(querySelect);
if (sqlData.HasRows)
{
//**该名称已经存在**//
if (sqlData != null)
{
sqlData.Dispose();
}
sqlData = null;
db.CloseSqlConnection();
return false;
}
if (sqlData != null)
{
sqlData.Dispose();
}
sqlData = null;
db.CloseSqlConnection();
SqliteDbHelper db2;
db2 = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
string query = "UPDATE " + SQLInfo.classify_table + " SET " + "classify_name = '" + classify_targetname + "'";
query += " WHERE " + "classify_name = '" + classify_currentname + "'";
db2.ExecuteQuery(query);
db2.CloseSqlConnection();
return true;
}
catch (Exception e)
{
Debug.Log(e.ToString());
return false;
}
finally
{
}
}
/// <summary>
/// 添加模型元件
/// </summary>
/// <param name="dataCell_model"> 模型类对象</param>
public static bool AddModelInfo(DataCell_model dataCell_model)
{
try
{
SqliteDbHelper db;
db = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
DataCell_model datamodel = dataCell_model;
string querySelect = "SELECT * " + " From " + SQLInfo.model_table;
querySelect += " WHERE " + "model_num = '" + datamodel.Model_num + "'";
SqliteDataReader sqlData = db.ExecuteQuery(querySelect);
// 有 true 没有 false
if (sqlData.HasRows)
{
//**存在名称一样的**//
Debug.Log("该名称已经存在" + sqlData.HasRows);
if (sqlData != null)
{
sqlData.Dispose();
}
sqlData = null;
db.CloseSqlConnection();
return false;
}
if (sqlData != null)
{
sqlData.Dispose();
}
sqlData = null;
db.CloseSqlConnection();
if (datamodel.Model_address != "")
{
Debug.Log("dataCell_model.Model_address:" + dataCell_model.Model_address);
string targetPath = Application.dataPath + SQLInfo.Model_path;
if (datamodel.Model_address != targetPath)
{
string path = datamodel.Model_address;
if (!FileHelper.CopyDir(ref path, targetPath))
return false;
datamodel.Model_address = path;
}
}
if (datamodel.Modle_ThumbnailAddress != "")
{
Debug.Log("dataCell_model.Modle_ThumbnailAddress:" + dataCell_model.Modle_ThumbnailAddress + " Path:" + Path.GetFileName(dataCell_model.Modle_ThumbnailAddress));
string targetPath = Application.dataPath + SQLInfo.Texture_path;
if (datamodel.Modle_ThumbnailAddress != targetPath)
{
string path = datamodel.Modle_ThumbnailAddress;
if (!FileHelper.CopyDir(ref path, targetPath))
return false;
datamodel.Modle_ThumbnailAddress = path;
}
}
SqliteDbHelper db1;
db1 = new SqliteDbHelper("Data Source=" + Application.dataPath + SQLInfo.SQL_path);
string query = "INSERT INTO " + SQLInfo.model_table + " VALUES (" + "null" + ",'" + datamodel.Model_num + "','" + datamodel.Model_name + "','" + datamodel.Model_address;
query += "','" + datamodel.Modle_ThumbnailAddress + "','" + datamodel.Model_Introduction + "','" + datamodel.Model_classify_name + "','" + datamodel.Model_type + "')";
db1.ExecuteQuery(query);
db1.CloseSqlConnection();
return true;
}
catch (Exception e)
{
Debug.Log(e.ToString());
return false;
}
finally
{
}
}
/// <summary>
/// Create schema
/// </summary>
/// <returns>return true if created, else false</returns>
public static void CreateSchema()
{
string sql = "Create Table IF NOT EXISTS " + TABLE_NAME + " (ExePath VARCHAR(100), Type VARCHAR(20), TimeInterval INT, MaxTime INT, ConcurrentLicences INT,LastRunStart datetime, LastRunEnd datetime, Enabled BOOLEAN)";
SqliteDbHelper.ExecuteQuery(sql);
}
