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