/// <summary> /// Wypełnienie zapytania aktualizującego produkt o danym ID /// </summary> /// <param name="statement"></param> /// <param name="key"></param> /// <param name="item"></param> protected override void FillUpdateStatement(ISQLiteStatement statement, long key, Device item) { statement.Bind(1, item.Name); statement.Bind(2, item.Manufacturer); statement.Bind(3, item.Others); statement.Bind(4, key); }
protected override void FillInsertStatement(ISQLiteStatement statement, Project item) { statement.Bind(1, item.CustomerId); statement.Bind(2, item.Name); statement.Bind(3, item.Description); statement.Bind(4, item.DueDate.ToString("yyyy-MM-dd HH:mm:ss")); }
public static void Binding(this ISQLiteStatement statement, string paramName, object value) { if (value is DateTime) { statement.Bind(paramName, ((DateTime)value).ToUniversalTime().Ticks); return; } if (value is DateTime?) { if (value != null) { statement.Bind(paramName, ((DateTime)value).ToUniversalTime().Ticks); return; } } else if (value is bool) { statement.Bind(paramName, Convert.ToInt64((bool)value)); return; } else if (value is Enum) { statement.Bind(paramName, (int)value); return; } statement.Bind(paramName, value); return; }
protected override void FillInsertStatement(ISQLiteStatement statement, Customer item) { // NOTE that named parameters have a leading "@",":" or "$". statement.Bind("@name", item.Name); statement.Bind("@city", item.City); statement.Bind("@contact", item.Contact); }
protected override void FillUpdateStatement(ISQLiteStatement statement, string key, TodoList item) { // NOTE that the first host parameter has an index of 1, not 0. statement.Bind(1, item.Id); statement.Bind(2, item.Title); statement.Bind(3, key); }
protected override void FillUpdateStatement(ISQLiteStatement statement, long key, Customer item) { // NOTE that the first host parameter has an index of 1, not 0. statement.Bind(1, item.Name); statement.Bind(2, item.City); statement.Bind(3, item.Contact); statement.Bind(4, key); }
protected override void BindInsertItemQuery(ISQLiteStatement query, LinkStatus item) { //"INSERT INTO LinkStatus (TargetItemId, SourceItemId, ChangeNumber, AssociationId) VALUES(@targetitemid, @sourceitemid, @changenumber, @associationid)"; query.Bind(1, item.TargetItemId); query.Bind(2, item.SourceItemId); query.Bind(3, item.ChangeNumber); query.Bind(4, item.AssociationId); }
protected override void BindInsertItemQuery(ISQLiteStatement query, SyncHistoryEntry item) { query.Bind(1, SQLite.DateTimeHelper.DateTimeSQLite(item.CreateDate)); query.Bind(2, item.Message); query.Bind(3, item.Result.ToString()); query.Bind(4, item.EntityId); query.Bind(5, item.ContentType); }
protected override void FillInsertItemStatement(ISQLiteStatement statement, ItemRow itemRow) { statement.Bind("@SensorId", itemRow.Field <Int64>("SensorId")); statement.Bind("@TimeUTC", SQLiteDB.Utilities.DateTimeSQLite(itemRow.Field <DateTime>("TimeUTC"))); statement.Bind("@Value", itemRow.Field <double>("Value")); statement.Bind("@IsOnline", SQLiteDB.Utilities.BooleanSQLite(itemRow.Field <bool>("IsOnline"))); statement.Bind("@Bucket", itemRow.Field <byte>("Bucket")); }
protected override void BindUpdateItemQuery(ISQLiteStatement query, LinkStatus item, long key) { //UPDATE LinkStatus SET TargetItemId = ?, SourceItemId = ?, ChangeNumber = ?, AssociationId = ? WHERE Id = ? query.Bind(1, item.TargetItemId); query.Bind(2, item.SourceItemId); query.Bind(3, item.ChangeNumber); query.Bind(4, item.AssociationId); query.Bind(5, key); }
public void AddNote(string title, string content) { ISQLiteStatement statement = dbConn.Prepare(INSERT_SIMPLE_NOTE); statement.Bind(1, title); statement.Bind(2, content); statement.Bind(3, TimeUtil.GetStringTimestamp()); statement.Bind(4, null); statement.Step(); }
private void UpdateNote(long noteId, string title, ObservableCollection <TodoNote.TodoEntry> TodoEntries) { string timeStamp = TimeUtil.GetStringTimestamp(); ISQLiteStatement statement = dbConn.Prepare(UPDATE_TODO_NOTE); statement.Bind(1, title); statement.Bind(2, timeStamp); statement.Bind(3, null); statement.Bind(4, noteId); statement.Step(); TodoNote todoNote = (TodoNote)GetNoteById(noteId); // Delete all todo note contents foreach (TodoNote.TodoEntry entry in ((TodoNote)GetNoteById(noteId)).TodoEntries) { statement.Reset(); statement.ClearBindings(); statement = dbConn.Prepare(DELETE_TODO_NOTE_CONTENT); statement.Bind(1, entry.Id); statement.Step(); } // Add all todo note new contents foreach (TodoNote.TodoEntry entry in TodoEntries) { statement.Reset(); statement.ClearBindings(); statement = dbConn.Prepare(INSERT_TODO_NOTE_CONTENT); statement.Bind(1, entry.Content); statement.Bind(2, ConvertBoolToInt(entry.IsDone)); statement.Bind(3, noteId); statement.Bind(4, timeStamp); statement.Step(); } }
private void UpdateNote(long id, string title, string content, long notificationId) { ISQLiteStatement statement = dbConn.Prepare(UPDATE_SIMPLE_NOTE); statement.Bind(1, title); statement.Bind(2, content); statement.Bind(3, TimeUtil.GetStringTimestamp()); statement.Bind(4, notificationId); statement.Bind(5, id); statement.Step(); }
public void AddNote(string title, string content, string schedulingId, DateTimeOffset dateTime) { long notificationId = NotificationHelper.AddNotification(schedulingId, dateTime); ISQLiteStatement statement = dbConn.Prepare(INSERT_SIMPLE_NOTE); statement.Bind(1, title); statement.Bind(2, content); statement.Bind(3, TimeUtil.GetStringTimestamp()); statement.Bind(4, notificationId); statement.Step(); }
public void AddNote(string title, string content, string photoPath) { ISQLiteStatement statement = dbConn.Prepare(INSERT_PHOTO_NOTE); statement.Bind(1, title); statement.Bind(2, content); statement.Bind(3, photoPath); statement.Bind(4, TimeUtil.GetStringTimestamp()); statement.Bind(5, null); statement.Step(); }
public static void UpdateNotification(long id, string schedulingId, DateTimeOffset dateTime) { ISQLiteStatement statement = dbConn.Prepare(UPDATE_NOTIFICATION); statement.Bind(1, TimeUtil.ConvertDateTimeOffsetToString(dateTime)); Debug.WriteLine("Updating notification: data to string " + TimeUtil.ConvertDateTimeOffsetToString(dateTime)); statement.Bind(2, schedulingId); statement.Bind(3, TimeUtil.GetStringTimestamp()); statement.Bind(4, id); statement.Step(); }
public void UpdateNoteAndDeleteNotification(long id, string title, string content) { DeleteNotificationByNoteId(id); ISQLiteStatement statement = dbConn.Prepare(UPDATE_SIMPLE_NOTE); statement.Bind(1, title); statement.Bind(2, content); statement.Bind(3, TimeUtil.GetStringTimestamp()); statement.Bind(4, null); statement.Bind(5, id); statement.Step(); }
/// <summary> /// Wypełnienie zapytania dodającego jeden komentarz z bazy /// </summary> /// <param name="statement"></param> /// <param name="item"></param> protected override void FillInsertStatement(ISQLiteStatement statement, Comment item) { statement.Bind(1, item.DeviceId); statement.Bind(2, item.Zalety); statement.Bind(3, item.Wady); statement.Bind(4, item.TekstOpinii); statement.Bind(5, item.Gwiazdki); statement.Bind(6, item.Autor); statement.Bind(7, item.Data); statement.Bind(8, item.Polecam); statement.Bind(9, item.Przydatna); statement.Bind(10, item.Pochodzenie); }
private void UpdateRow(SQLiteConnection connection, string newName, string oldName) { string sql = string.Format("update {0} set name = ? where name = ?", _tableName); using (ISQLiteStatement sqliteStatement = connection.Prepare(sql)) { //绑定参数 sqliteStatement.Bind(1, newName); sqliteStatement.Bind(2, oldName); //执行语句 sqliteStatement.Step(); } }
public static long AddNotification(string schedulingId, DateTimeOffset dateTime) { Debug.WriteLine("Adding notificaiton"); ISQLiteStatement statement = dbConn.Prepare(INSERT_SIMPLE_NOTE_NOTIFICATION); statement.Bind(1, TimeUtil.ConvertDateTimeOffsetToString(dateTime)); statement.Bind(2, schedulingId); statement.Bind(3, TimeUtil.GetStringTimestamp()); Debug.WriteLine(statement.Step()); Debug.WriteLine("Last inserted notification id: " + GetLastInsertedNotificationId()); return(GetLastInsertedNotificationId()); }
protected override void BindInsertItemQuery(ISQLiteStatement query, BaseItem item) { //"INSERT INTO Item (AssociationId, ItemId, IsCollection, ChangeKey, ChangeNumber) VALUES(@AssociationId, @itemid, @iscollection, @changekey, @changenumber)"; query.Bind(1, item.Association.Id); query.Bind(2, item.EntityId); query.Bind(3, item.IsCollection ? 1 : 0); query.Bind(4, item.ChangeKey); query.Bind(5, item.ChangeNumber); query.Bind(6, item.SyncPostponed ? 1 : 0); query.Bind(7, item.AdapterType.AssemblyQualifiedName); query.Bind(8, SQLite.DateTimeHelper.DateTimeSQLite(item.LastModified)); query.Bind(9, item.Size); query.Bind(10, item.ContentType); }
protected override void BindUpdateItemQuery(ISQLiteStatement query, BaseItem item, long key) { //"UPDATE Item SET EntityId = ?, IsCollection = ?, ChangeKey = ?, ChangeNumber = ? WHERE Id = ?"; query.Bind(1, item.EntityId); query.Bind(2, item.IsCollection ? 1 : 0); query.Bind(3, item.ChangeKey); query.Bind(4, item.ChangeNumber); query.Bind(5, item.SyncPostponed ? 1: 0); query.Bind(6, item.AdapterType.AssemblyQualifiedName); query.Bind(7, SQLite.DateTimeHelper.DateTimeSQLite(item.LastModified)); query.Bind(8, item.Size); query.Bind(9, item.ContentType); query.Bind(10, key); }
public bool Delete(string table, Dictionary <string, object> contentValues) { if (String.IsNullOrWhiteSpace(table) || contentValues == null || contentValues.Keys.Count == 0) { throw new InvalidOperationException("Must specify a table and provide content to delete"); } string values = String.Join(" = ?, ", contentValues.Keys); if (contentValues.Keys.Count > 0) { values += " = ?"; } string sql = String.Format(DeleteStatement, table, values); using (ISQLiteStatement stmt = _sqlConnection.Prepare(sql)) { int place = 1; foreach (object next in contentValues.Values) { stmt.Bind(place, next); place++; } return(stmt.Step() == SQLiteResult.DONE); } }
public ISQLiteStatement Query(string table, string[] columns, string orderBy, string limit, string whereClause, params string[] args) { if (String.IsNullOrWhiteSpace(table) || columns == null || columns.Length == 0) { throw new InvalidOperationException("Must specify a table and columns to query"); } if (String.IsNullOrWhiteSpace(whereClause)) { whereClause = String.Empty; } else { whereClause = "WHERE " + whereClause; } string sql = String.Format(QueryStatement, String.Join(", ", columns), table, whereClause, orderBy, limit, String.Empty); ISQLiteStatement stmt = _sqlConnection.Prepare(sql); if (args != null) { for (int i = 0; i < args.Length; i++) { stmt.Bind(i + 1, args[i]); } } SQLiteResult result = stmt.Step(); return(stmt); }
public void DeleteEntry(long entryId) { ISQLiteStatement statement = dbConn.Prepare(DELETE_TODO_NOTE_CONTENT); statement.Bind(1, entryId); statement.Step(); }
public static void DeleteNotificationById(long id) { ISQLiteStatement statement = dbConn.Prepare(DELETE_SIMPLE_NOTE_NOTIFICATION); statement.Bind(1, id); statement.Step(); }
private IList <JObject> ExecuteQuery(TableDefinition table, string sql, IDictionary <string, object> parameters) { table = table ?? new TableDefinition(); parameters = parameters ?? new Dictionary <string, object>(); var rows = new List <JObject>(); using (ISQLiteStatement statement = this.connection.Prepare(sql)) { foreach (KeyValuePair <string, object> parameter in parameters) { statement.Bind(parameter.Key, parameter.Value); } SQLiteResult result; while ((result = statement.Step()) == SQLiteResult.ROW) { var row = ReadRow(table, statement); rows.Add(row); } ValidateResult(result); } return(rows); }
public void DeleteNote(long id) { ISQLiteStatement statement = dbConn.Prepare(DELETE_TODO_NOTE_CONTENT); TodoNote todoNote = (TodoNote)GetNoteById(id); foreach (TodoNote.TodoEntry entry in todoNote.TodoEntries) { statement.Bind(1, entry.Id); statement.Step(); statement.Reset(); statement.ClearBindings(); } statement = dbConn.Prepare(DELETE_TODO_NOTE); statement.Bind(1, id); statement.Step(); DeleteNotificationByNoteId(id); }
public void AddNote(string title, ObservableCollection <TodoNote.TodoEntry> entries, string schedulingId, DateTimeOffset dateTime) { long notificationId = NotificationHelper.AddNotification(schedulingId, dateTime); ISQLiteStatement statement = dbConn.Prepare(INSERT_TODO_NOTE); statement.Bind(1, title); statement.Bind(2, TimeUtil.GetStringTimestamp()); statement.Bind(3, notificationId); statement.Step(); foreach (TodoNote.TodoEntry entry in entries) { statement = dbConn.Prepare(INSERT_TODO_NOTE_CONTENT); statement.Bind(1, entry.Content); int done = ConvertBoolToInt(entry.IsDone); statement.Bind(2, done); ISQLiteStatement stat = dbConn.Prepare(SELECT_LAST_NOTE_INSERT_ID); stat.Step(); long noteID = (long)stat[0]; statement.Bind(3, noteID); statement.Bind(4, TimeUtil.GetStringTimestamp()); statement.Step(); statement.Reset(); statement.ClearBindings(); } }
protected override void FillUpdateItemStatement(ISQLiteStatement statement, Int64 key, ItemRow itemRow) { itemRow.SetField <DateTime>("ChangeDate", DateTime.Now.ToUniversalTime()); statement.Bind("@" + PrimaryKeyName, itemRow.Field <Int64>(PrimaryKeyName)); statement.Bind("@EventDateTimeUTC", SQLiteDB.Utilities.DateTimeSQLite(itemRow.Field <DateTime>("EventDateTimeUTC"))); statement.Bind("@EventCode", itemRow.Field <Int32>("EventCode")); statement.Bind("@EventPriority", itemRow.Field <Int32>("EventPriority")); statement.Bind("@Latitude", itemRow.Field <double>("Latitude")); statement.Bind("@Longitude", itemRow.Field <double>("Longitude")); statement.Bind("@PropertyBag", itemRow.Field <string>("PropertyBag")); statement.Bind("@SensorId", itemRow.Field <Int64>("SensorId")); statement.Bind("@Value", itemRow.Field <double>("Value")); }
internal static void BindParameter(ISQLiteStatement stmt, int index, object value) { if (value == null) { stmt.Bind(index, null); } else if (value is Int32) { stmt.Bind(index, (int)value); } else if (value is String) { stmt.Bind(index, (string)value); } else if (value is Byte || value is UInt16 || value is SByte || value is Int16) { stmt.Bind(index, Convert.ToInt32(value, CultureInfo.InvariantCulture)); } else if (value is Boolean) { stmt.Bind(index, (bool)value ? 1 : 0); } else if (value is UInt32 || value is Int64) { stmt.Bind(index, Convert.ToInt64(value, CultureInfo.InvariantCulture)); } else if (value is Single || value is Double || value is Decimal) { stmt.Bind(index, Convert.ToDouble(value, CultureInfo.InvariantCulture)); } else if (value is DateTimeOffset) { stmt.Bind(index, ((DateTimeOffset)value).ToString("yyyy-MM-dd HH:mm:ss")); } else if (value is TimeSpan) { stmt.Bind(index, ((TimeSpan)value).Ticks); } else if (value is DateTime) { stmt.Bind(index, ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss")); #if !NETFX_CORE } else if (value.GetType().IsEnum) { #else } else if (value.GetType().GetTypeInfo().IsEnum) { #endif stmt.Bind(index, Convert.ToInt32(value, CultureInfo.InvariantCulture)); } else if (value is byte[]) { var vByte = (byte[])value; //var iBuffer = vByte.AsBuffer(); stmt.Bind(index, vByte); } else if (value is Guid) { stmt.Bind(index, ((Guid)value).ToString()); } else { throw new NotSupportedException("Cannot store type: " + value.GetType()); } }
private static void BindData(ISQLiteStatement statement, params object[] parameters) { if (parameters != null) { for (int i = 1; i <= parameters.Length; i++) { object o = parameters[i - 1]; var dt = o as DateTime?; if (dt.HasValue) { string ticks = dt.Value.Ticks.ToString(); statement.Bind(i, ticks); } else { statement.Bind(i, o); } } } }