public static Transaction Get(string guid)
{
string sql = "SELECT Guid, ExePath,Application,ProcessId, StartedAt, EndedAt, Enabled FROM Transactions WHERE Guid = {0} AND Enabled =" + SqliteDbHelper.SQLBoolean(true);
sql = string.Format(sql, SqliteDbHelper.SQLString(guid));
System.Data.DataSet transData = SqliteDbHelper.GetData(sql);
if (transData != null)
{
if (transData.Tables.Count > 0)
{
if (transData.Tables[0].Rows.Count == 1)
{
System.Data.DataRow dr = transData.Tables[0].Rows[0];
Transaction transaction = new Transaction(SqliteDbHelper.ToString(dr["Guid"]));
transaction.ProcessId = SqliteDbHelper.ToInt(dr["ProcessId"]);
transaction.Application = SqliteDbHelper.ToString(dr["Application"]);
transaction.ExeName = SqliteDbHelper.ToString(dr["ExePath"]);
transaction.StartedAt = SqliteDbHelper.ToNullableDateTime(dr["StartedAt"]);
transaction.EndedAt = SqliteDbHelper.ToNullableDateTime(dr["EndedAt"]);
transaction.Enabled = SqliteDbHelper.ToBoolean(dr["Enabled"]);
return(transaction);
}
}
}
return(null);
}
public static List <Transaction> GetAllRunning(string exePath)
{
List <Transaction> list = new List <Transaction>();
string sql = "SELECT Guid, ExePath,ProcessId, StartedAt, Application, EndedAt, Enabled FROM Transactions WHERE Enabled =" + SqliteDbHelper.SQLBoolean(true);
if (!string.IsNullOrEmpty(exePath))
{
sql += " AND ExePath =" + SqliteDbHelper.SQLString(exePath);
}
System.Data.DataSet transData = SqliteDbHelper.GetData(sql);
if (transData != null)
{
if (transData.Tables.Count > 0)
{
if (transData.Tables[0].Rows.Count > 0)
{
foreach (System.Data.DataRow dr in transData.Tables[0].Rows)
{
Transaction transaction = new Transaction(SqliteDbHelper.ToString(dr["Guid"]));
transaction.ProcessId = SqliteDbHelper.ToInt(dr["ProcessId"]);
transaction.Application = SqliteDbHelper.ToString(dr["Application"]);
transaction.ExeName = SqliteDbHelper.ToString(dr["ExePath"]);
transaction.StartedAt = SqliteDbHelper.ToNullableDateTime(dr["StartedAt"]);
transaction.EndedAt = SqliteDbHelper.ToNullableDateTime(dr["EndedAt"]);
transaction.Enabled = SqliteDbHelper.ToBoolean(dr["Enabled"]);
list.Add(transaction);
}
}
}
}
return(list);
}
/// <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="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);
}
private static string GetExePathByExeName(string exeName)
{
string sql = "SELECT ExePath FROM " + TABLE_NAME + " WHERE ExePath LIKE '%" + exeName + "%' AND Enabled = '1'";
System.Data.DataSet transData = SqliteDbHelper.GetData(sql);
if (transData != null)
{
if (transData.Tables.Count > 0)
{
if (transData.Tables[0].Rows.Count > 0)
{
foreach (System.Data.DataRow dr in transData.Tables[0].Rows)
{
string exePath = SqliteDbHelper.ToString(dr["ExePath"]);
if (!string.IsNullOrEmpty(exePath))
{
if (System.IO.Path.GetFileName(exePath).Equals(exeName))
{
return(exePath);
}
}
throw new Exception("Error while getting exe Name.");
}
}
}
}
return(string.Empty);
}
public void ConfirmButton(Button button)
{
//send score to DB
/* totalScore();
* Debug.Log("Total score is " + total + " and Button is " + button.name);
* Debug.Log(button.name + " is pressed.");
* Debug.Log("total = " + total); */
addTotalScore();
string sid = PlayerPrefs.GetString("sid", "001");
string totalScore = total.ToString();
SqliteDbHelper db = new SqliteDbHelper("Data Source=./sqlite.db");
if (!db.CheckTable("test0"))
{
db.CreateTable("test0",
new string[] { "recordID", "userId", "totalScore", "date" },
new string[] { "integer primary key autoincrement", "int not null", " text not null", " timestamp default (date('now'))" });
}
db.InsertIntoSpecific("test0",
new string[] { "userId", "totalScore" },
new string[] { sid, "'" + totalScore + "'" });
db.CloseSqlConnection();
Debug.Log("OK!");
GameObject.Find("Canvas").transform.Find("ResultPanels").transform.Find("Result").gameObject.SetActive(false);
//for development's sake, lets user know what the total score is
//can be deleted when not needed
GameObject.Find("Canvas").transform.Find("ResultPanels").transform.Find("Result2").gameObject.SetActive(true);
ResultText2.text = "Your total score is: " + total;
}
/// <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>
/// Get licence info of a licences by id
/// </summary>
/// <param name="id">Id</param>
/// <returns></returns>
public static Licence GetInfo(string exePath)
{
string sql = "SELECT ExePath, Type, TimeInterval,MaxTime, ConcurrentLicences ,LastRunStart,LastRunEnd , Enabled FROM " + TABLE_NAME + " WHERE ExePath = {0} ";
sql = string.Format(sql, SqliteDbHelper.SQLString(exePath));
System.Data.DataSet transData = SqliteDbHelper.GetData(sql);
if (transData != null)
{
if (transData.Tables.Count > 0)
{
if (transData.Tables[0].Rows.Count > 0)
{
System.Data.DataRow dr = transData.Tables[0].Rows[0];
Licence Licence = new Licence(SqliteDbHelper.ToString(dr["ExePath"]));
Licence.Type = (LicenceType)Enum.Parse(typeof(LicenceType), SqliteDbHelper.ToString(dr["Type"]));
Licence.TimeInterval = SqliteDbHelper.ToInt(dr["TimeInterval"]);
Licence.MaxTime = SqliteDbHelper.ToInt(dr["MaxTime"]);
Licence.ConcurrentLicences = SqliteDbHelper.ToInt(dr["ConcurrentLicences"]);
Licence.LastRunStart = SqliteDbHelper.ToNullableDateTime(dr["LastRunStart"]);
Licence.LastRunEnd = SqliteDbHelper.ToNullableDateTime(dr["LastRunEnd"]);
Licence.Enabled = SqliteDbHelper.ToBoolean(dr["Enabled"]);
return(Licence);
}
}
}
return(null);
}
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
//Initializing Sqlite db helper
SqliteDbHelper.Initialize();
Application.Run(new frmManageLicences());
}
public SqliteDbHelper GetCacheDb()
{
if (cacheDb == null)
{
cacheDb = LinkDb("Test2.db");
}
return(cacheDb);
}
public SqliteDbHelper GetServerDb()
{
if (serverDb == null)
{
serverDb = LinkDb("Test2.db");
}
return(serverDb);
}
public SqliteDbHelper GetConfigDb()
{
if (configDb == null)
{
configDb = LinkDb("Test.db");
}
return(configDb);
}
/// <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);
}
/// <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>
/// 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);
}
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>
/// 元件模型删除
/// </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
{
}
}
void Start()
{
Debug.LogError(Application.streamingAssetsPath);
db = new SqliteDbHelper("Data Source=" + Application.streamingAssetsPath + "/tttt.db");
Debug.Log(db.ToString());
/*
* SqliteDbAccess db = new SqliteDbAccess("data source=mydb1.db");
* db.CreateTable("momo",new string[]{"name","qq","email","blog"},
* new string[]{"text","text","text","text"});
* db.CloseSqlConnection();
*/
}
/// <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 CloseSqlConnection(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 1);
SqliteDbHelper obj = (SqliteDbHelper)ToLua.CheckObject(L, 1, typeof(SqliteDbHelper));
obj.CloseSqlConnection();
return(0);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
static int GetCacheDb(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 1);
StaticModules.SqliteDbManage obj = (StaticModules.SqliteDbManage)ToLua.CheckObject(L, 1, typeof(StaticModules.SqliteDbManage));
SqliteDbHelper o = obj.GetCacheDb();
ToLua.PushObject(L, o);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
static int OpenDB(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 2);
SqliteDbHelper obj = (SqliteDbHelper)ToLua.CheckObject(L, 1, typeof(SqliteDbHelper));
string arg0 = ToLua.CheckString(L, 2);
obj.OpenDB(arg0);
return(0);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
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));
}
}
static int InsertInto(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 3);
SqliteDbHelper obj = (SqliteDbHelper)ToLua.CheckObject(L, 1, typeof(SqliteDbHelper));
string arg0 = ToLua.CheckString(L, 2);
string[] arg1 = ToLua.CheckStringArray(L, 3);
Mono.Data.Sqlite.SqliteDataReader o = obj.InsertInto(arg0, arg1);
ToLua.PushObject(L, o);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
public void LoadScene(int level)
{
string sid = PlayerPrefs.GetString("sid", "001");
string score = GameObject.Find("Result/Score").GetComponent <Text>().text;
string result = GameObject.Find("Result/Text").GetComponent <Text>().text;
SqliteDbHelper db = new SqliteDbHelper("Data Source=./sqlite.db");
if (!db.CheckTable("test" + currentLevel.ToString()))
{
db.CreateTable("test" + currentLevel.ToString(), new string[] { "recordID", "userId", "score", "result", "date" }, new string[] { "integer primary key autoincrement", "int not null", " text not null", "text not null", " timestamp default (date('now'))" });
}
db.InsertIntoSpecific("test" + currentLevel.ToString(), new string[] { "userId", "score", "result" }, new string[] { sid, "'" + score + "'", "'" + result + "'" });
db.CloseSqlConnection();
Debug.Log("OK!");
SceneManager.LoadScene(level);
}
/// <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>
/// 修改分类名称
/// </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
{
}
}
public void register()
{
string firstname = GameObject.Find("FirstName/InputField/Text").GetComponent <Text>().text;
string lastname = GameObject.Find("LastName/InputField/Text").GetComponent <Text>().text;
string grade = GameObject.Find("Grade/InputField/Text").GetComponent <Text>().text;
string sid = GameObject.Find("SID/InputField/Text").GetComponent <Text>().text;
SqliteDbHelper db = new SqliteDbHelper("Data Source=./sqlite.db");
if (!db.CheckTable("user"))
{
db.CreateTable("user", new string[] { "userid", "firstname", "lastname", "grade" }, new string[] { "integer primary key", "text not null", "text not null", "int not null" });
}
db.InsertInto("user", new string[] { sid, "'" + firstname + "'", "'" + lastname + "'", grade });
db.CloseSqlConnection();
Debug.Log("OK!");
PlayerPrefs.SetString("sid", sid);
}
/// <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>
/// 模型元件查找
/// 根据分类父物体查找子分类列表
/// </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>
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_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>
/// 元件模型删除
/// </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>
/// <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();
}
