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); }