public List <T> ReadTableData <T>(string tablename) where T : AbstractSqliteDataClass, new()
{
List <T> lst = new List <T>();
DataBaseTable dt = GetTable(tablename);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
lst.Add(JsonMapper.ToObject <T>(JsonMapper.ToJson(row)));
}
return(lst);
}
public void UpdateData(string tablename, int index)
{
if (string.IsNullOrEmpty(tablename))
{
Debug.LogError("tablename is error");
return;
}
//"_rowid_"
string sql = string.Format("SELECT 1 FROM " + tablename);
DataBaseTable data = m_sqlDB.ExecuteQuery(sql);
//Debug.LogError(data.Rows.Count);
//INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO")
}
/// <summary>
/// Executes a query that requires a response (SELECT, etc).
/// </summary>
/// <returns>
/// Dictionary with the response data
/// </returns>
/// <param name='query'>
/// Query.
/// </param>
/// <exception cref='SqliteException'>
/// Is thrown when the sqlite exception.
/// </exception>
public DataBaseTable ExecuteQuery(string query)
{
if (!CanExQuery)
{
Debug.Log("ERROR: Can't execute the query, verify DB origin file");
return(null);
}
this.Open();
if (!IsConnectionOpen)
{
throw new SqliteException("SQLite database is not open.");
}
IntPtr stmHandle = Prepare(query);
int columnCount = sqlite3_column_count(stmHandle);
var dataTable = new DataBaseTable();
for (int i = 0; i < columnCount; i++)
{
string columnName = Marshal.PtrToStringAnsi(sqlite3_column_name(stmHandle, i));
dataTable.Columns.Add(columnName);
}
//populate datatable
while (sqlite3_step(stmHandle) == SQLITE_ROW)
{
object[] row = new object[columnCount];
for (int i = 0; i < columnCount; i++)
{
switch (sqlite3_column_type(stmHandle, i))
{
case SQLITE_INTEGER:
row[i] = sqlite3_column_int(stmHandle, i);
break;
case SQLITE_TEXT:
IntPtr text = sqlite3_column_text(stmHandle, i);
row[i] = Marshal.PtrToStringAnsi(text);
break;
case SQLITE_FLOAT:
row[i] = sqlite3_column_double(stmHandle, i);
break;
case SQLITE_BLOB:
IntPtr blob = sqlite3_column_blob(stmHandle, i);
int size = sqlite3_column_bytes(stmHandle, i);
byte[] data = new byte[size];
Marshal.Copy(blob, data, 0, size);
row[i] = data;
break;
case SQLITE_NULL:
row[i] = null;
break;
}
}
dataTable.AddRow(row);
}
Finalize(stmHandle);
this.Close();
return(dataTable);
}
private DataBaseTable GetTable(string tablename)
{
DataBaseTable dt = m_sqlDB.ExecuteQuery("SELECT * FROM " + tablename);
return(dt);
}
