private static void CreateTables(sqlite3 handle, List <string> tableNames, LiteWriter writer) { foreach (var tableName in tableNames) { var stmt = SQLite3.Prepare2(handle, "pragma table_info(" + tableName + ")"); try { int offset = 0; List <Column> columns = new List <Column>(); while (SQLite3.Step(stmt) == SQLite3.Result.Row) { string name = SQLite3.ColumnString(stmt, 1); string type = SQLite3.ColumnString(stmt, 2); int notNull = SQLite3.ColumnInt(stmt, 3); var ct = GetType(type); var column = new Column(name, ct, offset); columns.Add(column); offset += LiteData.GetLength(ct); } var columnArray = new Column[columns.Count]; columns.CopyTo(columnArray); var table = writer.AddTable(tableName, columnArray, offset); AddRows(handle, table); } finally { SQLite3.Finalize(stmt); } } }
public object GetValue(int columnIndex, Type propertyType) { object value = null; if (propertyType == typeof(String) || propertyType == typeof(string)) { value = SQLite3.ColumnString(statement, columnIndex); } else if (propertyType == typeof(long)) { value = SQLite3.ColumnInt64(statement, columnIndex); } else if (propertyType == typeof(Single) || propertyType == typeof(Double) || propertyType == typeof(Decimal)) { value = SQLite3.ColumnDouble(statement, columnIndex); } else if (propertyType == typeof(int)) { value = SQLite3.ColumnInt(statement, columnIndex); } else if (propertyType == typeof(bool) || propertyType == typeof(Boolean)) { value = (SQLite3.ColumnInt(statement, columnIndex) != 0); } else if (propertyType == typeof(byte[])) { value = SQLite3.ColumnByteArray(statement, columnIndex); } /* else if (column.isSingleRelationship) { * value = (V)(object)SQLite3.ColumnString(statement, columnIndex); * }*/ return(value); }
/// <summary> /// execute a query</summary> /// <returns> /// return a JArray which contains all objects </returns> /// <param name="query"> contains the query to execute</param> /// <param name="param"> contains the parameters needed to execute the query</param> public JArray execute(String query, object[] param) { JArray array = new JArray(); if (query != null && query.Length > 0) { stmt = SQLite3.Prepare2(this.Handle, query); if (stmt != null) { if (param != null && param.Length > 0) { for (int i = 1; i <= param.Length; i++) { bindValue(param.GetValue(i - 1), i); } } while (SQLite3.Step(stmt) == SQLite3.Result.Row) { int count = SQLite3.ColumnCount(stmt); JObject obj = new JObject(); for (int i = 0; i < count; i++) { string name = SQLite3.ColumnName(stmt, i); SQLite3.ColType colType = SQLite3.ColumnType(stmt, i); switch (colType) { case SQLite3.ColType.Blob: byte[] bytes = SQLite3.ColumnByteArray(stmt, i); obj.Add(name, bytes); break; case SQLite3.ColType.Integer: int intValue = SQLite3.ColumnInt(stmt, i); obj.Add(name, intValue); break; case SQLite3.ColType.Float: double doubleValue = SQLite3.ColumnDouble(stmt, i); obj.Add(name, doubleValue); break; case SQLite3.ColType.Text: string text = SQLite3.ColumnString(stmt, i); obj.Add(name, text); break; case SQLite3.ColType.Null: default: obj.Add(name, null); break; } } array.Add(obj); } } SQLite3.Finalize(stmt); } return(array); }
private static void AddRows(sqlite3 handle, LiteTableBuilder table) { var columns = table.Columns; string query = GetTableQuery(table.Name, Array.ConvertAll(columns, c => c.Name)); var stmt = SQLite3.Prepare2(handle, query); try { while (SQLite3.Step(stmt) == SQLite3.Result.Row) { var row = table.AddRow(); for (int i = 0; i < columns.Length; i++) { var column = columns[i]; switch (column.Type) { case ColumnType.Int32: row.Write(i, SQLite3.ColumnInt(stmt, i)); break; case ColumnType.Single: row.Write(i, (float)SQLite3.ColumnDouble(stmt, i)); break; case ColumnType.String: row.Write(i, SQLite3.ColumnString(stmt, i)); break; } } } } finally { SQLite3.Finalize(stmt); } }
public NSMutableDictionary GetConfigSettings() { NSMutableDictionary returnDictionary = new NSMutableDictionary(); IntPtr statement = IntPtr.Zero; statement = SQLite3.Prepare2(_database, SQL_GET_SETTINGS); int detailedLogOn = -100; if (statement == IntPtr.Zero) { return(null); } var result = SQLite3.Step(statement); if (result == SQLite3.Result.Row) { IntPtr serverHostPtr = SQLite3.ColumnText(statement, 0); string serverHost = System.Runtime.InteropServices.Marshal.PtrToStringAnsi(serverHostPtr); int port = SQLite3.ColumnInt(statement, 1); int priority = SQLite3.ColumnInt(statement, 2); IntPtr configPtr = SQLite3.ColumnText(statement, 3); string config = System.Runtime.InteropServices.Marshal.PtrToStringAnsi(configPtr); int copyPasteOn = SQLite3.ColumnInt(statement, 4); detailedLogOn = SQLite3.ColumnInt(statement, 5); IntPtr userEmailPtr = SQLite3.ColumnText(statement, 6); string userEmail = System.Runtime.InteropServices.Marshal.PtrToStringAnsi(userEmailPtr); if (!String.IsNullOrWhiteSpace(serverHost)) { GDAppServer firstServer = new GDAppServer(serverHost, new NSNumber(port), new NSNumber(priority)); returnDictionary.Add(GDiOS.GDAppConfigKeyServers, firstServer); } if (!String.IsNullOrWhiteSpace(config)) { returnDictionary.Add(GDiOS.GDAppConfigKeyConfig, new NSString(config)); } returnDictionary.Add(GDiOS.GDAppConfigKeyCopyPasteOn, new NSNumber(copyPasteOn)); if (detailedLogOn != -100) { returnDictionary.Add(GDiOS.GDAppConfigKeyDetailedLogsOn, new NSNumber(detailedLogOn)); } if (!string.IsNullOrWhiteSpace(userEmail)) { returnDictionary.Add(GDiOS.GDAppConfigKeyUserId, new NSString(userEmail)); } } SQLite3.Finalize(statement); return(returnDictionary); }
private object[] RunSql(string sqlString) { SQLitePCL.sqlite3_stmt stQuery = null; try { stQuery = SQLite3.Prepare2(database.DatabaseNotAsync.Handle, sqlString); var colLenght = SQLite3.ColumnCount(stQuery); while (SQLite3.Step(stQuery) == SQLite3.Result.Row) { var obj = new object[colLenght]; for (int i = 0; i < colLenght; i++) { var colType = SQLite3.ColumnType(stQuery, i); switch (colType) { case SQLite3.ColType.Blob: obj[i] = SQLite3.ColumnBlob(stQuery, i); break; case SQLite3.ColType.Float: obj[i] = SQLite3.ColumnDouble(stQuery, i); break; case SQLite3.ColType.Integer: obj[i] = SQLite3.ColumnInt(stQuery, i); break; case SQLite3.ColType.Null: obj[i] = null; break; case SQLite3.ColType.Text: obj[i] = SQLite3.ColumnString(stQuery, i); break; } } return(obj); } return(null); } catch (Exception) { return(null); } finally { if (stQuery != null) { SQLite3.Finalize(stQuery); } } }
public List <object[]> ExecuteCustomQuery() { if (SQLiteConnection.Trace) { Debug.WriteLine("Executing Query: " + this); } var stmt = Prepare(); try { var colLenght = SQLite3.ColumnCount(stmt); var lstRes = new List <object[]>(); while (SQLite3.Step(stmt) == SQLite3.Result.Row) { var obj = new object[colLenght]; lstRes.Add(obj); for (int i = 0; i < colLenght; i++) { var colType = SQLite3.ColumnType(stmt, i); switch (colType) { case SQLite3.ColType.Blob: obj[i] = SQLite3.ColumnBlob(stmt, i); break; case SQLite3.ColType.Float: obj[i] = SQLite3.ColumnDouble(stmt, i); break; case SQLite3.ColType.Integer: obj[i] = SQLite3.ColumnInt(stmt, i); break; case SQLite3.ColType.Null: obj[i] = null; break; case SQLite3.ColType.Text: obj[i] = SQLite3.ColumnString(stmt, i); break; } } } return(lstRes); } finally { SQLite3.Finalize(stmt); } }
public int Count() { IntPtr stmt = SQLite3.Prepare2(db, "SELECT COUNT (" + fieldlist + ") FROM " + tablename + join + " WHERE " + condition + (limit != null ? (" LIMIT " + limit) : "")); BindConditions(stmt); var res = SQLite3.Step(stmt); if (res != SQLite3.Result.Row) { throw new Exception("no count!"); } return(SQLite3.ColumnInt(stmt, 1)); }
public static async Task <List <Item> > GetAllItems() { InitializeConnection(); return(await _connection.QueryAsyncWithPopulateAction <Item>("SELECT _id,name,type,icon_name FROM items", (row) => { return new Item() { _id = SQLite3.ColumnInt(row, 0), name = SQLite3.ColumnString(row, 1), type = SQLite3.ColumnString(row, 2), icon_name = SQLite3.ColumnString(row, 3), //sub_type = SQLite3.ColumnString(row, 4) }; })); }
public object ReadCol(IntPtr stmt, int index, Type clrType) { var type = SQLite3.ColumnType(stmt, index); if (type == SQLite3.ColType.Null) { return(null); } if (clrType == typeof(Byte) || clrType == typeof(UInt16) || clrType == typeof(SByte) || clrType == typeof(Int16) || clrType == typeof(Int32)) { return(Convert.ChangeType(SQLite3.ColumnInt(stmt, index), clrType)); } if (clrType == typeof(UInt32) || clrType == typeof(Int64)) { return(Convert.ChangeType(SQLite3.ColumnInt64(stmt, index), clrType)); } if (clrType == typeof(Single) || clrType == typeof(Double) || clrType == typeof(Decimal)) { return(Convert.ChangeType(SQLite3.ColumnDouble(stmt, index), clrType)); } if (clrType == typeof(String)) { return(Convert.ChangeType(SQLite3.ColumnString(stmt, index), clrType)); } if (clrType == typeof(byte[])) { return(SQLite3.ColumnByteArray(stmt, index)); } if (clrType == typeof(DateTime)) { return(ToDateTime(SQLite3.ColumnString(stmt, index))); } if (clrType == typeof(Boolean)) { return(ToBoolean(SQLite3.ColumnString(stmt, index))); } throw new NotSupportedException("Don't know how to read " + clrType); }
public bool SettingsAlreadyStored() { IntPtr statement = IntPtr.Zero; int count = 0; statement = SQLite3.Prepare2(_database, SQL_COUNT); if (statement == IntPtr.Zero) { return(false); } var result = SQLite3.Step(statement); if (result == SQLite3.Result.Row) { count = SQLite3.ColumnInt(statement, 0); } SQLite3.Finalize(statement); statement = IntPtr.Zero; return(count > 0); }
private List <object[]> RunSql(string sqlString, bool includeColumnNamesAsFirstRow) { var lstRes = new List <object[]>(); SQLitePCL.sqlite3_stmt stQuery = null; try { stQuery = SQLite3.Prepare2(App.Database.DatabaseNotAsync.Handle, sqlString); var colLenght = SQLite3.ColumnCount(stQuery); if (includeColumnNamesAsFirstRow) { var obj = new object[colLenght]; lstRes.Add(obj); for (int i = 0; i < colLenght; i++) { obj[i] = SQLite3.ColumnName(stQuery, i); } } while (SQLite3.Step(stQuery) == SQLite3.Result.Row) { var obj = new object[colLenght]; lstRes.Add(obj); for (int i = 0; i < colLenght; i++) { var colType = SQLite3.ColumnType(stQuery, i); switch (colType) { case SQLite3.ColType.Blob: obj[i] = SQLite3.ColumnBlob(stQuery, i); break; case SQLite3.ColType.Float: obj[i] = SQLite3.ColumnDouble(stQuery, i); break; case SQLite3.ColType.Integer: obj[i] = SQLite3.ColumnInt(stQuery, i); break; case SQLite3.ColType.Null: obj[i] = null; break; case SQLite3.ColType.Text: obj[i] = SQLite3.ColumnString(stQuery, i); break; } } } return(lstRes); } catch (Exception) { return(null); } finally { if (stQuery != null) { SQLite3.Finalize(stQuery); } } }
public int GetInt32(int i) { return(SQLite3.ColumnInt(_vd, i)); }
private object ReadCol(Sqlite3Statement stmt, int index, SQLite3.ColType type, Type clrType) { if (type == SQLite3.ColType.Null) { return(null); } if (clrType == typeof(string)) { return(SQLite3.ColumnString(stmt, index)); } if (clrType == typeof(int)) { return(SQLite3.ColumnInt(stmt, index)); } if (clrType == typeof(bool)) { return(SQLite3.ColumnInt(stmt, index) == 1); } if (clrType == typeof(double)) { return(SQLite3.ColumnDouble(stmt, index)); } if (clrType == typeof(float)) { return((float)SQLite3.ColumnDouble(stmt, index)); } if (clrType == typeof(TimeSpan)) { return(new TimeSpan(SQLite3.ColumnInt64(stmt, index))); } if (clrType == typeof(DateTime)) { if (_conn.StoreDateTimeAsTicks) { return(new DateTime(SQLite3.ColumnInt64(stmt, index))); } string text = SQLite3.ColumnString(stmt, index); return(DateTime.Parse(text)); } if (clrType == typeof(DateTimeOffset)) { return(new DateTimeOffset(SQLite3.ColumnInt64(stmt, index), TimeSpan.Zero)); } if (clrType.IsEnum) { return(SQLite3.ColumnInt(stmt, index)); } if (clrType == typeof(long)) { return(SQLite3.ColumnInt64(stmt, index)); } if (clrType == typeof(uint)) { return((uint)SQLite3.ColumnInt64(stmt, index)); } if (clrType == typeof(decimal)) { return((decimal)SQLite3.ColumnDouble(stmt, index)); } if (clrType == typeof(byte)) { return((byte)SQLite3.ColumnInt(stmt, index)); } if (clrType == typeof(ushort)) { return((ushort)SQLite3.ColumnInt(stmt, index)); } if (clrType == typeof(short)) { return((short)SQLite3.ColumnInt(stmt, index)); } if (clrType == typeof(sbyte)) { return((sbyte)SQLite3.ColumnInt(stmt, index)); } if (clrType == typeof(byte[])) { return(SQLite3.ColumnByteArray(stmt, index)); } if (clrType == typeof(Guid)) { string text = SQLite3.ColumnString(stmt, index); return(new Guid(text)); } throw new NotSupportedException("Don't know how to read " + clrType); }
private static object ReadCol(SQLiteConnection connection, sqlite3_stmt stmt, int index, SQLite3.ColType type, Type clrType) { if (type == SQLite3.ColType.Null) { return(null); } else { if (clrType == typeof(String)) { return(SQLite3.ColumnString(stmt, index)); } else if (clrType == typeof(Int32)) { return((int)SQLite3.ColumnInt(stmt, index)); } else if (clrType == typeof(Boolean)) { return(SQLite3.ColumnInt(stmt, index) == 1); } else if (clrType == typeof(double)) { return(SQLite3.ColumnDouble(stmt, index)); } else if (clrType == typeof(float)) { return((float)SQLite3.ColumnDouble(stmt, index)); } else if (clrType == typeof(TimeSpan)) { return(new TimeSpan(SQLite3.ColumnInt64(stmt, index))); } else if (clrType == typeof(DateTime)) { if (connection.StoreDateTimeAsTicks) { return(new DateTime(SQLite3.ColumnInt64(stmt, index))); } else { string text = SQLite3.ColumnString(stmt, index); DateTime resultDate; if (!DateTime.TryParseExact(text, DateTimeExactStoreFormat, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out resultDate)) { resultDate = DateTime.Parse(text); } return(resultDate); } } // else if (clrType == typeof(DateTimeOffset)) // { // return new DateTimeOffset(SQLite3.ColumnInt64(stmt, index), TimeSpan.Zero); //#if !USE_NEW_REFLECTION_API // } // else if (clrType.IsEnum) // { //#else // } else if (clrType.GetTypeInfo().IsEnum) { //#endif // if (type == SQLite3.ColType.Text) // { // var value = SQLite3.ColumnString(stmt, index); // return Enum.Parse(clrType, value.ToString(), true); // } // else // return SQLite3.ColumnInt(stmt, index); // } else if (clrType == typeof(Int64)) { return(SQLite3.ColumnInt64(stmt, index)); } else if (clrType == typeof(UInt32)) { return((uint)SQLite3.ColumnInt64(stmt, index)); } else if (clrType == typeof(decimal)) { return((decimal)SQLite3.ColumnDouble(stmt, index)); } else if (clrType == typeof(Byte)) { return((byte)SQLite3.ColumnInt(stmt, index)); } else if (clrType == typeof(UInt16)) { return((ushort)SQLite3.ColumnInt(stmt, index)); } else if (clrType == typeof(Int16)) { return((short)SQLite3.ColumnInt(stmt, index)); } else if (clrType == typeof(sbyte)) { return((sbyte)SQLite3.ColumnInt(stmt, index)); } else if (clrType == typeof(byte[])) { return(SQLite3.ColumnByteArray(stmt, index)); } else if (clrType == typeof(Guid)) { string text = SQLite3.ColumnString(stmt, index); return(new Guid(text)); } else { throw new NotSupportedException("Don't know how to read " + clrType); } } }