public void InsertItem(SqlCeOfflineEntity entity)
{
InsertItem(entity, false);
}
public bool TagExists(SqlCeOfflineEntity entity)
{
var tag = (Tag) entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = GET_TAG;
command.Parameters.Add("@ID", SqlDbType.Int).Value = tag.ID;
object result = command.ExecuteScalar();
return result != null;
}
}
public void DeleteTagItemMapping(SqlCeOfflineEntity entity)
{
var tagItemMapping = (TagItemMapping)entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = DELETE_TAGITEMMAPPING;
command.Parameters.Add("@ID", SqlDbType.NVarChar).Value = tagItemMapping.ServiceMetadata.Id;
command.ExecuteNonQuery();
}
}
public void InsertTagItemMapping(SqlCeOfflineEntity entity, bool isDirty)
{
var tagItemMapping = (TagItemMapping)entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = INSERT_TAGITEMMAPPING;
command.Parameters.Add("@TagID", SqlDbType.Int).Value = tagItemMapping.TagID;
command.Parameters.Add("@ItemID", SqlDbType.UniqueIdentifier).Value = tagItemMapping.ItemID;
command.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = tagItemMapping.UserID;
command.Parameters.AddWithValue("@IsDirty", isDirty ? 1 : 0);
if (null == tagItemMapping.ServiceMetadata.Id)
{
command.Parameters.Add("@MetadataId", SqlDbType.NVarChar, 250).Value = DBNull.Value;
}
else
{
command.Parameters.Add("@MetadataId", SqlDbType.NVarChar, 250).Value = tagItemMapping.ServiceMetadata.Id;
}
command.ExecuteNonQuery();
}
}
public bool ListExists(SqlCeOfflineEntity entity)
{
var list = (List)entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = GET_LIST;
command.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = list.ID;
object result = command.ExecuteScalar();
return result != null;
}
}
public void InsertList(SqlCeOfflineEntity entity, bool isDirty)
{
var list = (List)entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = INSERT_LIST;
command.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = list.ID;
command.Parameters.AddWithValue("@Name", list.Name);
command.Parameters.AddWithValue("@Description", list.Description ?? "");
command.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = list.UserID;
command.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = list.CreatedDate;
command.Parameters.AddWithValue("@IsDirty", isDirty ? 1 : 0);
if (null == list.ServiceMetadata.Id)
{
command.Parameters.Add("@MetadataId", SqlDbType.NVarChar, 250).Value = DBNull.Value;
}
else
{
command.Parameters.Add("@MetadataId", SqlDbType.NVarChar, 250).Value = list.ServiceMetadata.Id;
}
command.ExecuteNonQuery();
}
}
public void InsertTag(SqlCeOfflineEntity entity)
{
var tag = (Tag)entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = INSERT_TAG;
command.Parameters.Add("@ID", SqlDbType.Int).Value = tag.ID;
command.Parameters.AddWithValue("@Name", tag.Name);
if (null == tag.ServiceMetadata.Id)
{
command.Parameters.Add("@MetadataId", SqlDbType.NVarChar, 250).Value = DBNull.Value;
}
else
{
command.Parameters.Add("@MetadataId", SqlDbType.NVarChar, 250).Value = tag.ServiceMetadata.Id;
}
command.ExecuteNonQuery();
}
}
public void UpdateStatus(SqlCeOfflineEntity entity)
{
var status = (Status) entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = UPDATE_STATUS;
command.Parameters.Add("@ID", SqlDbType.Int).Value = status.ID;
command.Parameters.AddWithValue("@Name", status.Name);
if (null == status.ServiceMetadata.Id)
{
command.Parameters.Add("@MetadataId", SqlDbType.NVarChar, 250).Value = DBNull.Value;
}
else
{
command.Parameters.Add("@MetadataId", SqlDbType.NVarChar, 250).Value = status.ServiceMetadata.Id;
}
command.ExecuteNonQuery();
}
}
/// <summary>
/// Update/Delete an existing item in the local database. This method is called to
/// apply changes received in the upload response.
/// </summary>
/// <param name="entity"></param>
public void ApplyItem(SqlCeOfflineEntity entity)
{
if (entity.GetType() == typeof(List))
{
if (entity.ServiceMetadata.IsTombstone)
{
if (!String.IsNullOrEmpty(entity.ServiceMetadata.Id))
{
// Delete using the metadata id since tombstones do not have the primary keys filled in.
// For deletes that are not the result of an upload/download response, we can use the primary keys.
DeleteListUsingMetadataId(entity);
}
else
{
DeleteList(entity);
}
}
else
{
UpdateList(entity);
}
}
else if (entity.GetType() == typeof(Item))
{
if (entity.ServiceMetadata.IsTombstone)
{
if (!String.IsNullOrEmpty(entity.ServiceMetadata.Id))
{
// Delete using the metadata id since tombstones do not have the primary keys filled in.
DeleteItemUsingMetadataId(entity);
}
else
{
DeleteItem(entity);
}
}
else
{
UpdateItem(entity);
}
}
}
public void UpdateItem(SqlCeOfflineEntity entity)
{
UpdateItem(entity, false);
}
public void UpdateList(SqlCeOfflineEntity entity)
{
UpdateList(entity, false);
}
public void TombstoneTagItemMapping(SqlCeOfflineEntity entity)
{
var tagItemMapping = (TagItemMapping) entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = TOMBSTONE_TAGITEMMAPPING;
command.Parameters.Add("@TagID", SqlDbType.Int).Value = tagItemMapping.TagID;
command.Parameters.Add("@ItemID", SqlDbType.UniqueIdentifier).Value = tagItemMapping.ItemID;
command.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = tagItemMapping.UserID;
command.ExecuteNonQuery();
}
}
public void TombstoneList(SqlCeOfflineEntity entity)
{
var list = (List)entity;
using (var connection = GetSqlCeConnection())
{
var transaction = connection.BeginTransaction();
try
{
using (var command = new SqlCeCommand())
{
command.Connection = connection;
command.CommandText = TOMBSTONE_TAGITEMMAPPING_FOR_LIST;
command.Parameters.Add("@ListID", SqlDbType.UniqueIdentifier).Value = list.ID;
command.ExecuteNonQuery();
}
using (var command = new SqlCeCommand())
{
command.Connection = connection;
command.CommandText = TOMBSTONE_ITEMS_FOR_LIST;
command.Parameters.Add("@ListID", SqlDbType.UniqueIdentifier).Value = list.ID;
command.ExecuteNonQuery();
}
using (var command = new SqlCeCommand())
{
command.Connection = connection;
command.CommandText = TOMBSTONE_LIST;
command.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = list.ID;
command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
public bool TagItemMappingExists(SqlCeOfflineEntity entity)
{
var tagItemMapping = (TagItemMapping)entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = GET_TAGITEMMAPPING;
command.Parameters.Add("@TagID", SqlDbType.Int).Value = tagItemMapping.TagID;
command.Parameters.Add("@ItemID", SqlDbType.UniqueIdentifier).Value = tagItemMapping.ItemID;
command.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = tagItemMapping.UserID;
object result = command.ExecuteScalar();
return result != null;
}
}
public void InsertItem(SqlCeOfflineEntity entity, bool isDirty)
{
var item = (Item) entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = INSERT_ITEM;
command.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = item.ID;
command.Parameters.Add("@ListID", SqlDbType.UniqueIdentifier).Value = item.ListID;
command.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = item.UserID;
command.Parameters.AddWithValue("@Name", item.Name);
command.Parameters.AddWithValue("@Description", item.Description ?? "");
if (null == item.Priority)
{
command.Parameters.AddWithValue("@Priority", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@Priority", item.Priority);
}
if (null == item.Status)
{
command.Parameters.AddWithValue("@Status", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@Status", item.Status);
}
if (null != item.StartDate)
{
command.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = item.StartDate;
}
else
{
command.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = DBNull.Value;
}
if (null != item.EndDate)
{
command.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = item.EndDate;
}
else
{
command.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = DBNull.Value;
}
command.Parameters.AddWithValue("@IsDirty", isDirty ? 1 : 0);
if (null == item.ServiceMetadata.Id)
{
command.Parameters.Add("@MetadataId", SqlDbType.NVarChar, 250).Value = DBNull.Value;
}
else
{
command.Parameters.Add("@MetadataId", SqlDbType.NVarChar, 250).Value = item.ServiceMetadata.Id;
}
command.ExecuteNonQuery();
}
}
public void DeleteItem(SqlCeOfflineEntity entity)
{
var item = (Item) entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = DELETE_ITEM;
command.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = item.ID;
command.ExecuteNonQuery();
}
}
public void InsertList(SqlCeOfflineEntity entity)
{
InsertList(entity, false);
}
public void DeleteList(SqlCeOfflineEntity entity)
{
var list = (List)entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = DELETE_LIST;
command.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = list.ID;
command.ExecuteNonQuery();
}
}
public void InsertPriority(SqlCeOfflineEntity entity)
{
var priority = (Priority)entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = INSERT_PRIORITY;
command.Parameters.Add("@ID", SqlDbType.Int).Value = priority.ID;
command.Parameters.AddWithValue("@Name", priority.Name);
if (null == priority.ServiceMetadata.Id)
{
command.Parameters.Add("@MetadataId", SqlDbType.NVarChar, 250).Value = DBNull.Value;
}
else
{
command.Parameters.Add("@MetadataId", SqlDbType.NVarChar, 250).Value = priority.ServiceMetadata.Id;
}
command.ExecuteNonQuery();
}
}
public void DeleteListUsingMetadataId(SqlCeOfflineEntity entity)
{
var list = (List)entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = DELETE_LIST_USING_METADATAID;
command.Parameters.Add("@ID", SqlDbType.NVarChar).Value = list.ServiceMetadata.Id;
command.ExecuteNonQuery();
}
}
public void InsertTagItemMapping(SqlCeOfflineEntity entity)
{
InsertTagItemMapping(entity, false);
}
public void DeletePriority(SqlCeOfflineEntity entity)
{
var priority = (Priority) entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = DELETE_PRIORITY;
command.Parameters.Add("@ID", SqlDbType.NVarChar).Value = priority.ServiceMetadata.Id;
command.ExecuteNonQuery();
}
}
public void InsertUser(SqlCeOfflineEntity entity)
{
var user = (User)entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = INSERT_USER;
command.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = user.ID;
command.Parameters.AddWithValue("@Name", user.Name);
if (null == user.ServiceMetadata.Id)
{
command.Parameters.Add("@MetadataId", SqlDbType.NVarChar, 250).Value = DBNull.Value;
}
else
{
command.Parameters.Add("@MetadataId", SqlDbType.NVarChar, 250).Value = user.ServiceMetadata.Id;
}
command.ExecuteNonQuery();
}
}
public void DeleteStatus(SqlCeOfflineEntity entity)
{
var status = (Status) entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = DELETE_STATUS;
command.Parameters.Add("@ID", SqlDbType.NVarChar).Value = status.ServiceMetadata.Id;
command.ExecuteNonQuery();
}
}
public bool PriorityExists(SqlCeOfflineEntity entity)
{
var priority = (Priority) entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = GET_PRIORITY;
command.Parameters.Add("@ID", SqlDbType.Int).Value = priority.ID;
object result = command.ExecuteScalar();
return result != null;
}
}
public bool StatusExists(SqlCeOfflineEntity entity)
{
var status = (Status) entity;
using (var command = new SqlCeCommand())
{
command.Connection = GetSqlCeConnection();
command.CommandText = GET_STATUS;
command.Parameters.Add("@ID", SqlDbType.Int).Value = status.ID;
object result = command.ExecuteScalar();
return result != null;
}
}
