/**
* Inserts a golf course in the database from an xml string describing the golf course
* xmlGolfCourse : the xml string describing the golf course
*/
private void InsertGolfCourseBdd(String xmlGolfCourse)
{
GolfCourse gc;
try
{
gc = GolfXMLReader.getSingleGolfCourseFromText(xmlGolfCourse);
SQLite.SQLiteConnection connection = DependencyService.Get <ISQLiteDb>().GetConnection();
try
{
connection.CreateTable <Hole>();
connection.CreateTable <MyPosition>();
connection.CreateTable <GolfCourse>();
connection.BeginTransaction();
SQLiteNetExtensions.Extensions.WriteOperations.InsertWithChildren(connection, gc, true);
connection.Commit();
this.DisplayAlert("Succès", "Le " + this.pins.Count + " trous : " + golfNameEntry.Text + " a été créé avec succès", "Continuer");
this.ManageAllPinsDelete();
}
catch (SQLiteException bddException)
{
this.DisplayAlert("Erreur avec la base de donnée", bddException.Source + " : Ce nom de golf existe déjà ou une autre erreur inattendu s'est produite", "Ok");
connection.Rollback();
}
}
catch (Exception xmlConversionException)
{
this.DisplayAlert("Erreur lors de la conversion XML -> GolfCourse", xmlConversionException.StackTrace, "Ok");
}
}
static async void FetchRSSAsync(Block completionBlock)
{
using (var wc = new WebClient()) {
string url = "http://forums.bignerdranch.com/smartfeed.php?limit=7_DAY&count_limit=25&sort_by=standard&feed_type=RSS2.0&feed_style=COMPACT"; // count_limit=10&
try {
string xmlData = await wc.DownloadStringTaskAsync(new Uri(url));
XDocument doc = XDocument.Parse(xmlData);
channel.parseXML(doc);
var allItems = doc.Descendants("item");
string dbPath = GetDBPath();
SQLiteConnection db;
db = new SQLiteConnection(dbPath);
db.BeginTransaction();
foreach (XElement current in allItems) {
RSSItem item = new RSSItem();
item.parseXML(current);
item.type = "post";
bool inItems = false;
foreach(RSSItem i in items) {
if (i.link == item.link)
inItems = true;
}
int index = 0;
if (!inItems) {
items.Insert(index++, item);
db.Insert(item);
if (db.Table<RSSItem>().Count() > 100) {
db.Query<RSSItem>("DELETE FROM RSSItems WHERE rowid IN (SELECT rowid FROM RSSItems ORDER BY rowid ASC LIMIT 1)");
}
Console.WriteLine("Items in table: {0} ItemID: {1}", db.Table<RSSItem>().Count(), item.ID);
}
}
db.Commit();
db.Close();
db = null;
completionBlock("success");
}
catch (WebException ex) {
Console.WriteLine("Exception: {0}", ex.Message);
completionBlock(ex.Message);
}
}
}
public List<Beacon> GetListOfBeacons()
{
lock (dbLock)
{
using (var sqlCon = new SQLiteConnection(DBPath))
{
sqlCon.Execute(Constants.DBClauseSyncOff);
sqlCon.BeginTransaction();
var data = sqlCon.Query<Beacon>("SELECT * FROM Beacon");
return data;
}
}
}
// public static bool saveChanges()
// {
// // Save to db
// string dbPath = GetDBPath();
// var db = new SQLiteConnection(dbPath);
// db.DeleteAll<BNRItem>();
// db.BeginTransaction();
// db.InsertAll(allItems);
// db.Commit();
// db.Close();
// return true;
// Archive method of saving
// // returns success or failure // For archiving method of saving
// string path = itemArchivePath(); // For archiving method of saving
// NSMutableArray newArray = new NSMutableArray(); // For archiving method of saving
// foreach (BNRItem item in allItems) { // For archiving method of saving
// newArray.Add(item); // For archiving method of saving
// } // For archiving method of saving
// return NSKeyedArchiver.ArchiveRootObjectToFile(newArray, path); // For archiving method of saving
// }
public static void addAssetType(string assetType)
{
string dbPath = GetDBPath();
SQLiteConnection db;
if (File.Exists(dbPath)) {
db = new SQLiteConnection(dbPath);
db.BeginTransaction();
var at = new BNRAssetType();
at.assetType = assetType;
allAssetTypes.Add(at);
db.Insert(at);
db.Commit();
db.Close();
}
}
public static void CreateTables(SQLiteConnection db)
{
db.BeginTransaction();
db.CreateTable<DriveInformation>();
db.CreateTable<DirectoryInformation>();
db.CreateTable<FileInformation>();
db.CreateTable<FileAttributeInformation>();
db.CreateTable<FileAttribute>();
db.Execute("CREATE INDEX if not exists \"main\".\"ix_DirectoryInformation_driveid_path\" ON \"DirectoryInformation\" (\"DriveId\" ASC, \"Path\" ASC)");
db.Execute("CREATE INDEX if not exists \"main\".\"ix_FileInformation_driveid_directoryid\" ON \"FileInformation\" (\"DirectoryId\" ASC, \"DriveId\" ASC)");
db.Commit();
}
private void Cache(IEnumerable<Scrobble> scrobbles)
{
using (var db = new SQLiteConnection(DatabasePath, SQLiteOpenFlags.ReadWrite))
{
var tableInfo = db.GetTableInfo(typeof (Scrobble).Name);
if (!tableInfo.Any())
{
db.CreateTable<Scrobble>();
}
db.BeginTransaction();
foreach (var scrobble in scrobbles)
{
db.Insert(scrobble);
}
db.Commit();
db.Close();
}
}
public void CleanUpDB()
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(Constants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
sqlCon.Execute("DELETE FROM Beacon");
sqlCon.Commit();
sqlCon.Execute(Constants.DBClauseVacuum);
}
catch (Exception ex)
{
#if(DEBUG)
System.Diagnostics.Debug.WriteLine("Error in CleanUpDB! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}
}
}
}
private static void ProcessFolder(SQLiteConnection db, DriveInformation drive, List<string> arrHeaders, DirectoryInfo directory)
{
try {
if (!directory.Exists)
return;
if (IgnoreFolder(directory)) {
return;
}
//go get the cached items for the folder.
var directoryId = DatabaseLookups.GetDirectoryId(db, drive, directory);
var cmd = db.CreateCommand("Select * from " + typeof(FileInformation).Name + " Where DriveId = ? AND DirectoryId = ?", drive.DriveId, directoryId);
var databaseFiles = cmd.ExecuteQuery<FileInformation>();
//obtain the file metadata for all of the files in the directory so we can determine if we care about this folder.
var processList = GetFilesToProcess(databaseFiles, arrHeaders, directory);
if (processList.Count > 0) {
db.BeginTransaction();
Shell32.Shell shell = new Shell32.Shell();
Shell32.Folder folder = shell.NameSpace(directory.FullName);
foreach (var item in processList) {
try {
var fi = item.FileInfo;
var headerList = new List<FileAttributeInformation>();
for (int i = 0; i < arrHeaders.Count; i++) {
var header = arrHeaders[i];
if (!IgnoreHeader(header)) {
var value = folder.GetDetailsOf(item.FolderItem, i);
if (!string.IsNullOrWhiteSpace(value)) {
headerList.Add(new FileAttributeInformation() {
AttributeId = DatabaseLookups.GetAttributeId(db, header),
Value = value
});
}
}
}
//this should have been already checked but we want to be safe.
if (fi.Exists) {
var fileInfo = databaseFiles.FirstOrDefault(info => info.FileName.Equals(fi.Name, StringComparison.OrdinalIgnoreCase));
if (fileInfo == null) {
fileInfo = new FileInformation() {
DriveId = drive.DriveId,
DirectoryId = directoryId,
FileName = fi.Name
};
SetFileInformation(fi, fileInfo);
db.Insert(fileInfo);
Console.WriteLine("Inserted:" + fi.FullName);
}
else {
SetFileInformation(fi, fileInfo);
db.Update(fileInfo);
var deleteCount = db.Execute("Delete from " + typeof(FileAttributeInformation).Name + " WHERE FileId = ?", fileInfo.FileId);
Console.WriteLine("Changed:" + fi.FullName);
}
//save the headers
headerList.ForEach(hl => hl.FileId = fileInfo.FileId);
db.InsertAll(headerList);
}
}
catch (Exception ex) {
Console.WriteLine(ex.ToString());
}
}
db.Commit();
}
//see if we have any additional folders. If we get access denied it will throw an error
try {
foreach (var subDirectory in directory.GetDirectories()) {
ProcessFolder(db, drive, arrHeaders, subDirectory);
}
}
catch (Exception ex) {
Console.WriteLine(ex.ToString());
}
}
catch (UnauthorizedAccessException) {
}
catch (Exception ex) {
Console.WriteLine(ex.ToString());
}
}
public void DeleteMessages(List<MessageDB> messages)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
foreach (MessageDB eachMessageDB in messages)
{
eachMessageDB.MessageStepDBList.ForEach(step => {
sqlCon.Execute("DELETE FROM MessageStepDB WHERE StepID=?", step.StepGuid);
});
eachMessageDB.MessageRecipientDBList.ForEach(s => {
sqlCon.Execute("DELETE FROM MessageRecipientDB WHERE AccountGuid=? AND MessageGuid=?", s.AccountGuid, s.MessageGuid);
});
sqlCon.Execute("DELETE FROM MessageDB WHERE MessageGuid=?", eachMessageDB.MessageGuid);
}//end foreach
sqlCon.Commit();
} catch (Exception ex)
{
#if(DEBUG)
Console.WriteLine("Error in DeleteMessages! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end using lock
}
public void DeleteContentInfoIfExists(ContentInfo contentInfo)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
ContentInfo contentInfoToDelete =
sqlCon.Query<ContentInfo>("SELECT * FROM ContentInfo WHERE ID=?", contentInfo.ID)
.FirstOrDefault();
if (null != contentInfoToDelete)
{
// Take care of the message first.
MessageDB messageDB = sqlCon.Query<MessageDB>("SELECT * FROM MessageDB WHERE ID=?", contentInfo.MessageDBID)
.FirstOrDefault();
if (messageDB.MessageID == default(Guid))
{
// Delete it, because the message has already been inserted from message create.
sqlCon.Execute("DELETE FROM MessageDB WHERE ID=?", messageDB.ID);
}//end if
sqlCon.Execute("DELETE FROM VoiceCache WHERE ContentInfoID=?", contentInfo.ID);
sqlCon.Execute("DELETE FROM PollingStepDBCache WHERE ContentInfoID=?", contentInfo.ID);
sqlCon.Execute("DELETE FROM MessageRecipientDBCache WHERE MessageDBID=?", messageDB.ID);
sqlCon.Execute("DELETE FROM MessageStepDBCache WHERE MessageDBID=?", messageDB.ID);
sqlCon.Execute("DELETE FROM ContentState WHERE ContentInfoID=?", contentInfo.ID);
sqlCon.Execute("DELETE FROM ContentInfo WHERE ID=?", contentInfo.ID);
}//end if
sqlCon.Commit();
} catch (Exception ex)
{
#if(DEBUG)
Console.WriteLine("Error deleting content info! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}
public void UpdateContentPackItemUsageDate(int contentPackItemID, DateTime dateTime)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
sqlCon.Execute("UPDATE ContentPackItemDB SET LastDateTimeUsed=? WHERE ContentPackItemID=?", dateTime, contentPackItemID);
sqlCon.Commit();
} catch (Exception ex)
{
sqlCon.Rollback();
#if(DEBUG)
Console.WriteLine("Error updating content pack item date! {0}--{1}", ex.Message, ex.StackTrace);
#endif
}//end try catch
}//end using sqlCon
}//end lock
}
public void DeleteContacts(List<Guid> contactIDs)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
foreach (Guid eachContactID in contactIDs)
{
string eachContactGuid = eachContactID.ToString();
// Delete ContactOAuthDB items
sqlCon.Execute("DELETE FROM ContactOAuthDB WHERE ContactGuid=?", eachContactGuid);
// Delete contact's user object
sqlCon.Execute("DELETE FROM UserDB WHERE AccountGuid IN (SELECT ContactAccountGuid FROM ContactDB WHERE ContactGuid=?)", eachContactGuid);
// Delete Contact object
sqlCon.Execute("DELETE FROM ContactDB WHERE ContactGuid=?", eachContactGuid);
}//end foreach
sqlCon.Commit();
} catch (Exception ex)
{
#if(DEBUG)
Console.WriteLine("Error in DeleteContacts! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}
public void MarkMessageSent(string messageGuid)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
sqlCon.Execute("UPDATE MessageDB SET MessageConfirmed=? WHERE MessageGuid=?", true, messageGuid);
sqlCon.Commit();
} catch (Exception ex)
{
#if(DEBUG)
Console.WriteLine("Error in MarkMessageSent! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}
public void InsertOrUpdateAnimation(AnimationInfo animationInfo)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.AnimationDBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
Type animationInfoType = typeof(AnimationInfo);
Type frameInfoType = typeof(FrameInfo);
Type layerInfoType = typeof(LayerInfo);
Type drInfoType = typeof(DrawingInfo);
Type trInfoType = typeof(TransitionInfo);
Type trSettingsType = typeof(TransitionEffectSettings);
Type brushItemType = typeof(BrushItem);
Type audioItemType = typeof(AnimationAudioInfo);
sqlCon.BeginTransaction();
try
{
//
// AnimationInfo
//
if (sqlCon.Execute("UPDATE AnimationInfo SET " +
"MessageGuid=?, " +
"StepNumber=?, " +
"CreatedOn=?, " +
"OriginalCanvasSizeWidth=?, " +
"OriginalCanvasSizeHeight=?, " +
"IsEditing=?, " +
"IsSent=? " +
"WHERE DBID=?",
animationInfo.MessageGuid,
animationInfo.StepNumber,
animationInfo.CreatedOn,
animationInfo.OriginalCanvasSizeWidth,
animationInfo.OriginalCanvasSizeHeight,
animationInfo.IsEditing,
animationInfo.IsSent,
animationInfo.DBID) == 0)
{
sqlCon.Insert(animationInfo, animationInfoType);
}//end if
foreach (FrameInfo eachFrameInfo in animationInfo.FrameItems.Values)
{
eachFrameInfo.AnimationDBID = animationInfo.DBID;
if (sqlCon.Execute("UPDATE FrameInfo SET " +
"ID=?, " +
"AnimationDBID=? " +
"WHERE DBID=?",
eachFrameInfo.ID,
eachFrameInfo.AnimationDBID,
eachFrameInfo.DBID) == 0)
{
sqlCon.Insert(eachFrameInfo, frameInfoType);
}//end if
foreach (LayerInfo eachLayerInfo in eachFrameInfo.Layers.Values)
{
eachLayerInfo.FrameDBID = eachFrameInfo.DBID;
if (sqlCon.Execute("UPDATE LayerInfo SET " +
"ID=?, " +
"FrameDBID=?, " +
"CanvasSizeWidth=?, " +
"CanvasSizeHeight=?, " +
"IsCanvasActive=? " +
"WHERE DBID=?",
eachLayerInfo.ID,
eachLayerInfo.FrameDBID,
eachLayerInfo.CanvasSizeWidth,
eachLayerInfo.CanvasSizeHeight,
eachLayerInfo.IsCanvasActive,
eachLayerInfo.DBID) == 0)
{
sqlCon.Insert(eachLayerInfo, layerInfoType);
}//end if
foreach (DrawingInfo eachDrawingInfo in eachLayerInfo.DrawingItems.Values)
{
eachDrawingInfo.LayerDBID = eachLayerInfo.DBID;
if (null != eachDrawingInfo.Brush)
{
if (sqlCon.Execute("UPDATE BrushItem SET " +
"Thickness=?, " +
"BrushType=?, " +
"BrushImageBuffer=?, " +
"BrushColorBuffer=?, " +
"InactiveBrushColorBuffer=?, " +
"IsSprayBrushActive=? " +
"WHERE DBID=?",
eachDrawingInfo.Brush.Thickness,
eachDrawingInfo.Brush.BrushType,
eachDrawingInfo.Brush.BrushImageBuffer,
eachDrawingInfo.Brush.BrushColorBuffer,
eachDrawingInfo.Brush.InactiveBrushColorBuffer,
eachDrawingInfo.Brush.IsSprayBrushActive,
eachDrawingInfo.Brush.DBID) == 0)
{
sqlCon.Insert(eachDrawingInfo.Brush, brushItemType);
}//end sqlCon
eachDrawingInfo.BrushItemDBID = eachDrawingInfo.Brush.DBID;
}//end if
if (sqlCon.Execute("UPDATE DrawingInfo SET " +
"DrawingID=?, " +
"LayerDBID=?, " +
"BrushItemDBID=?, " +
"LineColorBuffer=?, " +
"DrawingType=?, " +
"ImageBuffer=?, " +
"ImageFrameX=?, " +
"ImageFrameY=?, " +
"ImageFrameWidth=?, " +
"ImageFrameHeight=?, " +
"RotationAngle=?, " +
"RotatedImageBoxX=?, " +
"RotatedImageBoxY=?, " +
"RotatedImageBoxWidth=?, " +
"RotatedImageBoxHeight=?, " +
"ContentPackItemID=?, " +
"CalloutText=?, " +
"CalloutTextRectX=?, " +
"CalloutTextRectY=?, " +
"CalloutTextRectWidth=?, " +
"CalloutTextRectHeight=? " +
"WHERE DBID=?",
eachDrawingInfo.DrawingID,
eachDrawingInfo.LayerDBID,
eachDrawingInfo.BrushItemDBID,
eachDrawingInfo.LineColorBuffer,
eachDrawingInfo.DrawingType,
eachDrawingInfo.ImageBuffer,
eachDrawingInfo.ImageFrameX,
eachDrawingInfo.ImageFrameY,
eachDrawingInfo.ImageFrameWidth,
eachDrawingInfo.ImageFrameHeight,
eachDrawingInfo.RotationAngle,
eachDrawingInfo.RotatedImageBoxX,
eachDrawingInfo.RotatedImageBoxY,
eachDrawingInfo.RotatedImageBoxWidth,
eachDrawingInfo.RotatedImageBoxHeight,
eachDrawingInfo.ContentPackItemID,
eachDrawingInfo.CalloutText,
eachDrawingInfo.CalloutTextRectX,
eachDrawingInfo.CalloutTextRectY,
eachDrawingInfo.CalloutTextRectWidth,
eachDrawingInfo.CalloutTextRectHeight,
eachDrawingInfo.DBID) == 0)
{
sqlCon.Insert(eachDrawingInfo, drInfoType);
}//end if
if (eachDrawingInfo.DrawingType == DrawingLayerType.Drawing)
{
List<PathPointDB> drPoints = eachDrawingInfo.GetPathPointsForDB();
// DELETE path points for this drawing item. No need to try to update each one individually.
sqlCon.Execute("DELETE FROM PathPointDB WHERE DrawingInfoDBID=?", eachDrawingInfo.DBID);
// And INSERT
sqlCon.InsertAll(drPoints);
}//end if
}//end foreach
foreach (TransitionInfo eachTrInfo in eachLayerInfo.Transitions.Values)
{
eachTrInfo.LayerDBID = eachLayerInfo.DBID;
if (sqlCon.Execute("UPDATE TransitionInfo SET " +
"ID=?, " +
"LayerDBID=?, " +
"FadeOpacity=?, " +
"RotationAngle=?, " +
"EndSizeWidth=?, " +
"EndSizeHeight=?, " +
"EndSizeFixedPointX=?, " +
"EndSizeFixedPointY=?, " +
"EndLocationX=?, " +
"EndLocationY=? " +
"WHERE DBID=?",
eachTrInfo.ID,
eachTrInfo.LayerDBID,
eachTrInfo.FadeOpacity,
eachTrInfo.RotationAngle,
eachTrInfo.EndSizeWidth,
eachTrInfo.EndSizeHeight,
eachTrInfo.EndSizeFixedPointX,
eachTrInfo.EndSizeFixedPointY,
eachTrInfo.EndLocationX,
eachTrInfo.EndLocationY,
eachTrInfo.DBID) == 0)
{
sqlCon.Insert(eachTrInfo, trInfoType);
}//end if
foreach (TransitionEffectSettings eachTrSetting in eachTrInfo.Settings.Values)
{
eachTrSetting.TransitionInfoDBID = eachTrInfo.DBID;
if (sqlCon.Execute("UPDATE TransitionEffectSettings SET " +
"TransitionID=?, " +
"TransitionInfoDBID=?, " +
"FrameID=?, " +
"LayerID=?, " +
"EffectType=?, " +
"Duration=?, " +
"RotationCount=?, " +
"Delay=? " +
"WHERE DBID=?",
eachTrSetting.TransitionID,
eachTrSetting.TransitionInfoDBID,
eachTrSetting.FrameID,
eachTrSetting.LayerID,
eachTrSetting.EffectType,
eachTrSetting.Duration,
eachTrSetting.RotationCount,
eachTrSetting.Delay,
eachTrSetting.DBID) == 0)
{
sqlCon.Insert(eachTrSetting, trSettingsType);
}//end if
}//end foreach
}//end foreach
}//end foreach
}//end foreach
foreach (AnimationAudioInfo eachAudioItem in animationInfo.AudioItems.Values)
{
FrameInfo frameItem = null;
if (animationInfo.FrameItems.TryGetValue(eachAudioItem.FrameID, out frameItem))
{
eachAudioItem.FrameDBID = frameItem.DBID;
}//end if
if (sqlCon.Execute("UPDATE AnimationAudioInfo SET " +
"ID=?, " +
"FrameID=?, " +
"FrameDBID=?, " +
"AudioBuffer=?, " +
"Duration=? " +
"WHERE DBID=?",
eachAudioItem.ID,
eachAudioItem.FrameID,
eachAudioItem.FrameDBID,
eachAudioItem.AudioBuffer,
eachAudioItem.Duration,
eachAudioItem.DBID) == 0)
{
sqlCon.Insert(eachAudioItem, audioItemType);
}//end if
}//end foreach
sqlCon.Commit();
} catch (Exception ex)
{
sqlCon.Rollback();
throw ex;
}//end try catch
}//end using sqlCon
}//end lock
}
public void CleanUpDB()
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
sqlCon.Execute("DELETE FROM UserDB");
sqlCon.Execute("DELETE FROM ContactDB");
sqlCon.Execute("DELETE FROM ContactOAuthDB");
sqlCon.Execute("DELETE FROM ContentPackDB");
sqlCon.Execute("DELETE FROM ContentPackItemDB");
sqlCon.Execute("DELETE FROM MessageDB");
sqlCon.Execute("DELETE FROM MessageStepDB");
sqlCon.Commit();
sqlCon.Execute(WZConstants.DBClauseVacuum);
} catch (Exception ex)
{
#if(DEBUG)
Console.WriteLine("Error in CleanUpDB! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}
public void MarkMessageRead(string messageGuid, string ownerAccountID)
{
#if(DEBUG)
Console.WriteLine("Will mark message read! {0}", messageGuid);
#endif
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
if (sqlCon.Execute("UPDATE MessageRecipientDB SET IsRead=? WHERE MessageGuid=? AND AccountGuid=?", true, messageGuid, ownerAccountID) == 0)
{
MessageRecipientDB msgRcp = new MessageRecipientDB()
{
AccountGuid = ownerAccountID,
MessageGuid = messageGuid,
IsRead = true
};
#if(DEBUG)
Console.WriteLine("Created a new message recipient! {0}", msgRcp);
#endif
sqlCon.Insert(msgRcp, typeof(MessageRecipientDB));
}//end if
sqlCon.Commit();
} catch (Exception ex)
{
#if(DEBUG)
Console.WriteLine("Error in MarkMessageRead! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}
public void InsertOrUpdateContentInfoItems(List<ContentInfo> contentInfoItems)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
Type messageDBType = typeof(MessageDB);
Type contentInfoType = typeof(ContentInfo);
Type voiceCacheType = typeof(VoiceCache);
Type pollingStepDBCacheType = typeof(PollingStepDBCache);
Type messageStepDBCacheType = typeof(MessageStepDBCache);
Type messageRecipientDBCacheType = typeof(MessageRecipientDBCache);
Type contentStateType = typeof(ContentState);
foreach (ContentInfo eachContentInfo in contentInfoItems)
{
if (sqlCon.Execute("UPDATE MessageDB SET " +
"MessageGuid=?, " +
"FromAccountGuid=?, " +
"IsOutgoing=?, " +
"MessageSent=? " +
"WHERE ID=?",
eachContentInfo.Message.MessageID.ToString(),
eachContentInfo.Message.FromAccountID.ToString(),
true,
eachContentInfo.Message.MessageSent,
eachContentInfo.MessageDBID) == 0)
{
MessageDB msgDB = MessageDB.ConvertFromMessage(eachContentInfo.Message);
sqlCon.Insert(msgDB, messageDBType);
eachContentInfo.MessageDBID = msgDB.ID;
}//end if
if (sqlCon.Execute("UPDATE ContentInfo SET " +
"MessageDBID=?, " +
"IsFailed=?, " +
"IsMessageCreateFailed=? " +
"WHERE ID=?",
eachContentInfo.MessageDBID,
eachContentInfo.IsFailed,
eachContentInfo.IsMessageCreateFailed,
eachContentInfo.ID) == 0)
{
sqlCon.Insert(eachContentInfo, contentInfoType);
}//end if
foreach (KeyValuePair<int, byte[]> eachVoiceCache in eachContentInfo.VoiceRecordings)
{
bool isSent = eachContentInfo.ContentState != null ?
!eachContentInfo.ContentState.VoiceIDQ.Contains(eachVoiceCache.Key) :
false;
if (sqlCon.Execute("UPDATE VoiceCache SET " +
"ContentInfoID=?, " +
"StepNumber=?, " +
"VoiceData=?, " +
"IsSent=? " +
"WHERE ContentInfoID=? AND StepNumber=?",
eachContentInfo.ID,
eachVoiceCache.Key,
eachVoiceCache.Value,
isSent,
eachContentInfo.ID,
eachVoiceCache.Key) == 0)
{
VoiceCache voiceCache = new VoiceCache();
voiceCache.ContentInfoID = eachContentInfo.ID;
voiceCache.StepNumber = eachVoiceCache.Key;
voiceCache.VoiceData = eachVoiceCache.Value;
voiceCache.IsSent = isSent;
sqlCon.Insert(voiceCache, voiceCacheType);
}//end if
}//end foreach
foreach (KeyValuePair<int, PollingStep> eachPollingStep in eachContentInfo.PollingSteps)
{
bool isSent = eachContentInfo.ContentState != null ?
!eachContentInfo.ContentState.PollingIDQ.Contains(eachPollingStep.Key) :
false;
if (sqlCon.Execute("UPDATE PollingStepDBCache SET " +
"ContentInfoID=?, " +
"MessageGuid=?, " +
"PollingData1=?, " +
"PollingData2=?, " +
"PollingData3=?, " +
"PollingData4=?, " +
"HasResponded=?, " +
"PollingAnswer1=?, " +
"PollingAnswer2=?, " +
"PollingAnswer3=?, " +
"PollingAnswer4=?, " +
"PollingQuestion=?, " +
"StepNumber=?, " +
"IsSent=? " +
"WHERE ContentInfoID=? AND StepNumber=?",
eachContentInfo.ID,
eachContentInfo.Message.MessageID.ToString(),
eachPollingStep.Value.PollingData1,
eachPollingStep.Value.PollingData2,
eachPollingStep.Value.PollingData3,
eachPollingStep.Value.PollingData4,
eachPollingStep.Value.HasResponded,
eachPollingStep.Value.PollingAnswer1,
eachPollingStep.Value.PollingAnswer2,
eachPollingStep.Value.PollingAnswer3,
eachPollingStep.Value.PollingAnswer4,
eachPollingStep.Value.PollingQuestion,
eachPollingStep.Key,
isSent,
eachContentInfo.ID, eachPollingStep.Key) == 0)
{
PollingStepDBCache pollStepCache = new PollingStepDBCache();
pollStepCache.ContentInfoID = eachContentInfo.ID;
pollStepCache.HasResponded = eachPollingStep.Value.HasResponded;
pollStepCache.IsSent = isSent;
pollStepCache.MessageID = eachContentInfo.Message.MessageID;
pollStepCache.PollingAnswer1 = eachPollingStep.Value.PollingAnswer1;
pollStepCache.PollingAnswer2 = eachPollingStep.Value.PollingAnswer2;
pollStepCache.PollingAnswer3 = eachPollingStep.Value.PollingAnswer3;
pollStepCache.PollingAnswer4 = eachPollingStep.Value.PollingAnswer4;
pollStepCache.PollingData1 = eachPollingStep.Value.PollingData1;
pollStepCache.PollingData2 = eachPollingStep.Value.PollingData2;
pollStepCache.PollingData3 = eachPollingStep.Value.PollingData3;
pollStepCache.PollingData4 = eachPollingStep.Value.PollingData4;
pollStepCache.PollingQuestion = eachPollingStep.Value.PollingQuestion;
pollStepCache.StepNumber = eachPollingStep.Key;
sqlCon.Insert(pollStepCache, pollingStepDBCacheType);
}//end if
}//end foreach
foreach (MessageStep eachMessageStep in eachContentInfo.Message.MessageSteps)
{
if (sqlCon.Execute("UPDATE MessageStepDBCache SET " +
"MessageDBID=?, " +
"MessageGuid=?, " +
"StepGuid=?, " +
"ContentPackItemID=?, " +
"MessageText=?, " +
"StepNumber=?, " +
"StepType=? " +
"WHERE MessageDBID=? AND StepNumber=?",
eachContentInfo.MessageDBID,
eachContentInfo.Message.MessageID.ToString(),
eachMessageStep.StepID.ToString(),
eachMessageStep.ContentPackItemID,
eachMessageStep.MessageText,
eachMessageStep.StepNumber,
eachMessageStep.StepType,
eachContentInfo.MessageDBID,
eachMessageStep.StepNumber) == 0)
{
MessageStepDBCache msgStepCache = new MessageStepDBCache();
msgStepCache.ContentPackItemID = eachMessageStep.ContentPackItemID;
msgStepCache.MessageDBID = eachContentInfo.MessageDBID;
msgStepCache.MessageID = eachContentInfo.Message.MessageID;
msgStepCache.MessageText = eachMessageStep.MessageText;
msgStepCache.StepID = eachMessageStep.StepID;
msgStepCache.StepNumber = eachMessageStep.StepNumber;
msgStepCache.StepType = eachMessageStep.StepType;
sqlCon.Insert(msgStepCache, messageStepDBCacheType);
}//end if
}//end foreach
foreach (Guid eachRcpID in eachContentInfo.Recipients)
{
if (sqlCon.Execute("UPDATE MessageRecipientDBCache SET " +
"MessageDBID=?, " +
"AccountGuid=?, " +
"MessageGuid=?, " +
"IsRead=? " +
"WHERE MessageDBID=? AND AccountGuid=?",
eachContentInfo.MessageDBID,
eachRcpID.ToString(),
eachContentInfo.Message.MessageID.ToString(),
false,
eachContentInfo.MessageDBID,
eachRcpID.ToString()) == 0)
{
MessageRecipientDBCache rcpCache = new MessageRecipientDBCache();
rcpCache.AccountGuid = eachRcpID.ToString();
rcpCache.IsRead = false;
rcpCache.MessageDBID = eachContentInfo.MessageDBID;
rcpCache.MessageGuid = eachContentInfo.Message.MessageID.ToString();
sqlCon.Insert(rcpCache, messageRecipientDBCacheType);
}//end if
}//end foreach
if (null != eachContentInfo.ContentState)
{
if (sqlCon.Execute("UPDATE ContentState SET " +
"ContentInfoID=? WHERE ContentInfoID=?",
eachContentInfo.ID, eachContentInfo.ID) == 0)
{
sqlCon.Insert(eachContentInfo.ContentState, contentStateType);
}//end if
}//end if
}//end foreach
sqlCon.Commit();
} catch (Exception ex)
{
#if(DEBUG)
Console.WriteLine("Error saving content info item! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}
/// <summary>
/// Deletes all contacts that belong to the owner and replaces them with the supplied contact list.
/// </summary>
/// <param name='withContacts'>
/// Contacts to replace the deleted ones with.
/// </param>
/// <param name='ownerAccountID'>
/// Owner account GUID. Normally, this should be the currently logged in user.
/// </param>
public void RefreshContactsForOwner(List<Contact> withContacts, string ownerAccountID)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
// Delete all ContactOAuth items for owner
sqlCon.Execute("DELETE FROM ContactOAuthDB WHERE ContactGuid IN " +
"(SELECT ContactGuid FROM ContactDB WHERE OwnerAccountGuid=?)", ownerAccountID);
// Delete all Contacts for owner
sqlCon.Execute("DELETE FROM ContactDB WHERE OwnerAccountGuid=?", ownerAccountID);
// Insert contacts
Type contactDBType = typeof(ContactDB);
Type contactOAuthDBType = typeof(ContactOAuthDB);
Type userDBType = typeof(UserDB);
foreach (Contact eachContact in withContacts)
{
ContactDB contactDB = ContactDB.ConvertFromContact(eachContact);
sqlCon.Insert(contactDB, contactDBType);
// Insert the contact auth db item
contactDB.ContactOAuthItems.ForEach(s => {
sqlCon.Insert(s, contactOAuthDBType);
});
// Insert Or Update user object
UserDB contactUser = UserDB.ConvertFromUser(contactDB.ContactUser);
if (sqlCon.Execute("UPDATE UserDB SET " +
"AccountActive=?, " +
"AccountGuid=?, " +
"DateOfBirth=?, " +
"EmailAddress=?, " +
"FirstName=?, " +
"LastName=?, " +
"Password=?, " +
"Picture=?, " +
"PictureUrl=?, " +
"HasProfileImage=? " +
"WHERE AccountGuid=?",
contactUser.AccountActive,
contactUser.AccountGuid,
contactUser.DateOfBirth,
contactUser.EmailAddress,
contactUser.FirstName,
contactUser.LastName,
contactUser.Password,
contactUser.Picture,
contactUser.PictureURL,
contactUser.HasProfileImage,
contactUser.AccountGuid) == 0)
{
sqlCon.Insert(contactUser, userDBType);
}//end if
}//end foreach
sqlCon.Commit();
} catch (Exception ex)
{
sqlCon.Rollback();
#if(DEBUG)
Console.WriteLine("Error in RefreshContactsForOwner! {0}--{1}", ex.Message, ex.StackTrace);
#endif
}//end try catch
}//end using sqlCon
}//end lock
}
public void InsertOrUpdateContentPackItems(List<ContentPackItemDB> contentPackItems)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
Type contentPackItemType = typeof(ContentPackItemDB);
foreach (ContentPackItemDB eachContentPack in contentPackItems)
{
if (sqlCon.Execute("UPDATE ContentPackItemDB SET " +
"ContentItemTitle=?, " +
"ContentPackDataFile=?, " +
"ContentPackID=?, " +
"ContentPackItemIconFile=?, " +
"ContentPackItemID=?, " +
"LastDateTimeUsed=? " +
"WHERE ContentPackItemID=?",
eachContentPack.ContentItemTitle,
eachContentPack.ContentPackDataFile,
eachContentPack.ContentPackID,
eachContentPack.ContentPackItemIconFile,
eachContentPack.ContentPackItemID,
eachContentPack.LastDateTimeUsed,
eachContentPack.ContentPackItemID) == 0)
{
sqlCon.Insert(eachContentPack, contentPackItemType);
}//end foreach
}//end foreach
sqlCon.Commit();
} catch (Exception ex)
{
#if(DEBUG)
Console.WriteLine("Error in InsertNewContentPackItems! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}
public void SaveAnimationUndoInfo(List<UndoInfo> undoInfo)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.AnimationDBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
sqlCon.Execute("DELETE FROM UndoInfo");
sqlCon.InsertAll(undoInfo);
sqlCon.Commit();
} catch (Exception ex)
{
sqlCon.Rollback();
#if(DEBUG)
Console.WriteLine("Error inserting undo info objects! {0}--{1}", ex.Message, ex.StackTrace);
#endif
}//end try catch
}//end using sqlCon
}//end lock
}
public void InsertOrUpdateContentPacks(List<ContentPackDB> contentPacks)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
Type contentPackDBType = typeof(ContentPackDB);
foreach (ContentPackDB eachContentPack in contentPacks)
{
if (sqlCon.Execute("UPDATE ContentPackDB SET " +
"ContentPackAdFile=?, " +
"ContentPackAvailableDate=?, " +
"ContentPackDescription=?, " +
"ContentPackEndDate=?, " +
"ContentPackIconFile=?, " +
"ContentPackID=?, " +
"ContentPackIsFree=?, " +
"ContentPackPrice=?, " +
"ContentPackSaleEndDate=?, " +
"ContentPackSalePrice=?, " +
"ContentPackTitle=?, " +
"ContentPackTypeID=? " +
"WHERE ContentPackID=?",
eachContentPack.ContentPackAdFile,
eachContentPack.ContentPackAvailableDate,
eachContentPack.ContentPackDescription,
eachContentPack.ContentPackEndDate,
eachContentPack.ContentPackIconFile,
eachContentPack.ContentPackID,
eachContentPack.ContentPackIsFree,
eachContentPack.ContentPackPrice,
eachContentPack.ContentPackSaleEndDate,
eachContentPack.ContentPackSalePrice,
eachContentPack.ContentPackTitle,
eachContentPack.ContentPackTypeID,
eachContentPack.ContentPackID) == 0)
{
sqlCon.Insert(eachContentPack, contentPackDBType);
}//end if
}//end foreach
sqlCon.Commit();
} catch (Exception ex)
{
#if(DEBUG)
Console.WriteLine("Error in InsertNewContentPacks! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}
public void UpdateUserImage(string accountGuid, byte[] imageBuffer)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
sqlCon.Execute("UPDATE UserDB SET Picture=? WHERE AccountGuid=?", imageBuffer, accountGuid);
sqlCon.Commit();
} catch (Exception ex)
{
Console.WriteLine("Error in UpdateUserImage! {0}--{1}", ex.Message, ex.StackTrace);
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}
public void InsertOrUpdateMessages(List<MessageDB> messageList)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
Type messageDBType = typeof(MessageDB);
Type messageStepDBType = typeof(MessageStepDB);
Type messageRecipientDBType = typeof(MessageRecipientDB);
foreach (MessageDB eachMessage in messageList)
{
if (sqlCon.Execute("UPDATE MessageDB SET " +
"MessageGuid=?, " +
"FromAccountGuid=?, " +
"IsOutgoing=?, " +
"MessageSent=? " +
"WHERE MessageGuid=?",
eachMessage.MessageGuid,
eachMessage.FromAccountGuid,
eachMessage.IsOutgoing,
eachMessage.MessageSent,
eachMessage.MessageGuid) == 0)
{
sqlCon.Insert(eachMessage, messageDBType);
}//end if
eachMessage.MessageStepDBList.ForEach(step => {
if (sqlCon.Execute("UPDATE MessageStepDB SET " +
"MessageGuid=?, " +
"StepGuid=?, " +
"ContentPackItemID=?, " +
"MessageText=?, " +
"StepNumber=?, " +
"StepType=? " +
"WHERE MessageGuid=? AND StepNumber=?",
step.MessageGuid,
step.StepGuid,
step.ContentPackItemID,
step.MessageText,
step.StepNumber,
step.StepType,
step.MessageGuid,
step.StepNumber) == 0)
{
sqlCon.Insert(step, messageStepDBType);
}//end if
});
eachMessage.MessageRecipientDBList.ForEach(rcp => {
if (sqlCon.Execute("UPDATE MessageRecipientDB SET " +
"AccountGuid=?, " +
"MessageGuid=?, " +
"IsRead=? " +
"WHERE MessageGuid=? AND AccountGuid=?",
rcp.AccountGuid,
rcp.MessageGuid,
rcp.IsRead,
rcp.MessageGuid,
rcp.AccountGuid) == 0)
{
sqlCon.Insert(rcp, messageRecipientDBType);
}//end if
});
}//end foreach
sqlCon.Commit();
} catch (Exception ex)
{
#if(DEBUG)
Console.WriteLine("Error in InsertOrUpdateMessages! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}
public void DeleteContentPackItems(List<ContentPackItemDB> items)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
foreach (ContentPackItemDB eachPackItem in items)
{
sqlCon.Execute("DELETE FROM ContentPackItemDB WHERE ID=?", eachPackItem.ID);
}//end foreach
sqlCon.Commit();
} catch (Exception ex)
{
sqlCon.Rollback();
#if(DEBUG)
Console.WriteLine("Error deleting content pack item! {0}--{1}", ex.Message, ex.StackTrace);
#endif
}//end try catch
}//end using sqlCon
}//end lock
}
public void InsertOrUpdatePollingSteps(List<PollingStepDB> pollingSteps)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
Type pollingStepDBType = typeof(PollingStepDB);
foreach (PollingStepDB eachItem in pollingSteps)
{
if (sqlCon.Execute("UPDATE PollingStepDB SET " +
"MessageGuid=?, " +
"PollingAnswer1=?, " +
"PollingAnswer2=?, " +
"PollingAnswer3=?, " +
"PollingAnswer4=?, " +
"PollingData1File=?, " +
"PollingData2File=?, " +
"PollingData3File=?, " +
"PollingData4File=?, " +
"PollingQuestion=?, " +
"StepNumber=? " +
"WHERE MessageGuid=? AND StepNumber=?",
eachItem.MessageGuid,
eachItem.PollingAnswer1,
eachItem.PollingAnswer2,
eachItem.PollingAnswer3,
eachItem.PollingAnswer4,
eachItem.PollingData1File,
eachItem.PollingData2File,
eachItem.PollingData3File,
eachItem.PollingData4File,
eachItem.PollingQuestion,
eachItem.StepNumber,
eachItem.MessageGuid,
eachItem.StepNumber) == 0)
{
sqlCon.Insert(eachItem, pollingStepDBType);
}//end if
}//end foreach
sqlCon.Commit();
} catch (Exception ex)
{
#if(DEBUG)
Console.WriteLine("Error in InsertOrUpdatePollingSteps! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}
public void DeletePollingStep(PollingStepDB pollingStep)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
sqlCon.Execute("DELETE FROM PollingStepDB WHERE MessageGuid=? AND StepNumber=?", pollingStep.MessageGuid, pollingStep.StepNumber);
sqlCon.Commit();
} catch (Exception ex)
{
Console.WriteLine("Error in DeletePollingStep! {0}--{1}", ex.Message, ex.StackTrace);
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}
public void InsertOrUpdateUser(User user)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
UserDB userDB = UserDB.ConvertFromUser(user);
if (sqlCon.Execute("UPDATE UserDB SET " +
"AccountActive=?, " +
"AccountGuid=?, " +
"DateOfBirth=?, " +
"EmailAddress=?, " +
"FirstName=?, " +
"LastName=?, " +
"Password=?, " +
"Picture=?, " +
"PictureUrl=?, " +
"HasProfileImage=? " +
"WHERE AccountGuid=?",
userDB.AccountActive,
userDB.AccountGuid,
userDB.DateOfBirth,
userDB.EmailAddress,
userDB.FirstName,
userDB.LastName,
userDB.Password,
userDB.Picture,
userDB.PictureURL,
userDB.HasProfileImage,
userDB.AccountGuid) == 0)
{
sqlCon.Insert(userDB, typeof(UserDB));
}//end if
sqlCon.Commit();
} catch (Exception ex)
{
#if(DEBUG)
Console.WriteLine("Error in InsertOrUpdateContact! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}
private async void Import()
{
var picker = new FileOpenPicker { SuggestedStartLocation = PickerLocationId.Desktop };
picker.FileTypeFilter.Add(".json");
var storageFile = await picker.PickSingleFileAsync();
if (storageFile == null)
{
return;
}
importProgressBar.Visibility = Visibility.Visible;
importProgressText.Text = "Loading...";
var text = await FileIO.ReadTextAsync(storageFile);
JsonObject jsonObject;
if (!JsonObject.TryParse(text, out jsonObject))
{
// TODO: error message
return;
}
var locations = jsonObject["locations"].GetArray();
importProgressBar.Maximum = locations.Count;
importProgressBar.Value = 0;
var app = (App)Application.Current;
using (var db = new SQLiteConnection(app.DatabasePath))
{
var step = 1;
var count = 1000;
for (var i = 0; i < locations.Count; i += step * count)
{
db.BeginTransaction();
var i1 = i;
await Task.Run(() => AddLocationsToDatabase(db, locations, i1, count, step));
db.Commit();
importProgressText.Text = string.Format("Imported {0} of {1}", i, locations.Count);
importProgressBar.Value += step * count;
}
}
importProgressBar.Visibility = Visibility.Collapsed;
importProgressText.Text = "";
}
/// <summary>
/// Deletes the contact that belongs to the owner.
/// </summary>
/// <param name='contact'>
/// The contact to delete.
/// </param>
public void DeleteContactForOwner(ContactDB contact)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
// Delete OAuths for the contact
sqlCon.Execute("DELETE FROM ContactOAuthDB WHERE ContactGuid=? AND " +
"ContactGuid IN (SELECT ContactGuid FROM ContactDB WHERE OwnerAccountGuid=?)",
contact.ContactGuid, contact.OwnerAccountGuid);
// Delete the Contact object
sqlCon.Execute("DELETE FROM ContactDB WHERE ContactGuid=? AND OwnerAccountGuid=?",
contact.ContactGuid, contact.OwnerAccountGuid);
//NOTE: No need to delete the user object for the contact (maybe).
// Delete the User object
// sqlCon.Execute("DELETE FROM UserDB WHERE AccountGuid=?", contact.ContactUser.AccountID.ToString());
sqlCon.Commit();
} catch (Exception ex)
{
sqlCon.Rollback();
#if(DEBUG)
Console.WriteLine("Error in DeleteContactForOwner! {0}--{1}", ex.Message, ex.StackTrace);
#endif
}//end try catch
}//end using sqlCon
}//end lock
}
protected override void OnCreate(Bundle bundle)
{
base.OnCreate(bundle);
// Create your application here
SetContentView(Resource.Layout.Update);
var buttonUpdate = FindViewById<Button>(Resource.Id.buttonUpdate);
var textViewCurrentVersion = FindViewById<TextView>(Resource.Id.textViewCurrentVersion);
var textViewLatestVersion = FindViewById<TextView>(Resource.Id.textViewLatestVersion);
var progressBarUpdate = FindViewById<ProgressBar>(Resource.Id.progressBarUpdate);
var buttonAppUpdate = FindViewById<Button>(Resource.Id.buttonAppUpdate);
var textViewCurrentAppVersion = FindViewById<TextView>(Resource.Id.textViewCurrentAppVersion);
var textViewLatestAppVersion = FindViewById<TextView>(Resource.Id.textViewLatestAppVersion);
progressBarUpdate.Visibility = ViewStates.Invisible;
var preference = GetSharedPreferences("settings", FileCreationMode.WorldWriteable);
var editor = preference.Edit();
if (!preference.Contains("version"))
{
editor.PutString("version", "20150504");
editor.Commit();
}
textViewCurrentVersion.Text = preference.GetString("version", "20150504");
editor.PutString("appVersion", PackageManager.GetPackageInfo(PackageName, 0).VersionName);
editor.Commit();
textViewCurrentAppVersion.Text = preference.GetString("appVersion", "0");
buttonUpdate.Click += async (sender, e) =>
{
try
{
buttonUpdate.Enabled = false;
buttonUpdate.Text = "正在更新";
var requestVersion = WebRequest.Create("http://kidfruit.github.io/WisdriContacts/LatestDataVersion.txt?" + Guid.NewGuid().ToString());
requestVersion.Method = "GET";
using (var response = await requestVersion.GetResponseAsync())
{
using (var responseStream = response.GetResponseStream())
{
using (var sr = new StreamReader(responseStream, Encoding.UTF8))
{
var receive = sr.ReadToEnd();
textViewLatestVersion.Text = receive;
}
}
}
if (!string.IsNullOrEmpty(textViewLatestVersion.Text.Trim()) &&
textViewCurrentVersion.Text != textViewLatestVersion.Text)
{
progressBarUpdate.Visibility = ViewStates.Visible;
var request = WebRequest.Create("http://kidfruit.github.io/WisdriContacts/data.txt?" + Guid.NewGuid().ToString());
request.Method = "GET";
using (var response = await request.GetResponseAsync())
{
using (var responseStream = response.GetResponseStream())
{
using (var sr = new StreamReader(responseStream, Encoding.GetEncoding("UTF-8")))
{
int count = 0;
long length = responseStream.Length;
byte[] receiveStream = new byte[length];
progressBarUpdate.Visibility = ViewStates.Visible;
progressBarUpdate.Indeterminate = false;
progressBarUpdate.Max = (int)length;
progressBarUpdate.Progress = 0;
while (true)
{
int readLength = (int)(length - count > 1000 ? 1000 : length - count);
int num = await responseStream.ReadAsync(receiveStream, count, readLength);
if (num == 0)
break;
count += num;
progressBarUpdate.Progress = count;
}
var receive = Encoding.UTF8.GetString(receiveStream, 0, (int)length);
//var receive = await sr.ReadToEndAsync();
var byteValue = Convert.FromBase64String(receive);
string decodeReceive = Encoding.UTF8.GetString(byteValue, 0, byteValue.Length);
var personList = JsonConvert.DeserializeObject<List<PERSON>>(decodeReceive);
var dbFile = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "contacts.db");
using (SQLiteConnection conn = new SQLiteConnection(dbFile))
{
try
{
var sql = "delete from PERSON";
int oldDataCount = conn.Execute(sql);
new AlertDialog.Builder(this).SetMessage(string.Format("旧数据有{0}条", oldDataCount)).Show();
sql = "DROP TABLE [PERSON];";
conn.Execute(sql);
sql = @"CREATE TABLE [PERSON] (
[ID] nvarchar(2147483647) NOT NULL
, [NAME] nvarchar(2147483647) NULL
, [DEPARTMENT] nvarchar(2147483647) NULL
, [MOBILE_PHONE] nvarchar(2147483647) NULL
, [VIRTUAL_PHONE] nvarchar(2147483647) NULL
, [POSITION] nvarchar(2147483647) NULL
, [REGION] nvarchar(2147483647) NULL
, [OFFICE_PHONE] nvarchar(2147483647) NULL
, [INNER_PHONE] nvarchar(2147483647) NULL
, [PY] nvarchar(2147483647) NULL
, [CAR] nvarchar(2147483647) NULL
, CONSTRAINT [sqlite_autoindex_PERSON_1] PRIMARY KEY ([ID])
);";
conn.Execute(sql);
conn.BeginTransaction();
conn.InsertAll(personList);
textViewCurrentVersion.Text = textViewLatestVersion.Text;
editor.PutString("version", textViewCurrentVersion.Text);
editor.Commit();
sql = "select count(ID) from PERSON";
int newDataCount = conn.ExecuteScalar<int>(sql);
new AlertDialog.Builder(this).SetMessage(string.Format("新数据有{0}条", newDataCount)).Show();
conn.Commit();
new AlertDialog.Builder(this).SetMessage("更新完毕").SetPositiveButton("确定", delegate
{
}).Show();
}
catch (Exception ex)
{
conn.Rollback();
new AlertDialog.Builder(this).SetMessage(ex.Message).Show();
}
}
}
}
}
}
else
{
new AlertDialog.Builder(this).SetMessage("无需更新").Show();
}
}
catch (System.Exception ex)
{
new AlertDialog.Builder(this).SetMessage(ex.Message).Show();
}
finally
{
progressBarUpdate.Visibility = ViewStates.Invisible;
buttonUpdate.Enabled = true;
buttonUpdate.Text = "更新数据";
}
};
buttonAppUpdate.Click += async (sender, e) =>
{
try
{
buttonAppUpdate.Enabled = false;
var requestAppVersion = WebRequest.Create("http://kidfruit.github.io/WisdriContacts/LatestAndroidVersion.txt?" + Guid.NewGuid().ToString());
requestAppVersion.Method = "GET";
using (var response = await requestAppVersion.GetResponseAsync())
{
using (var responseStream = response.GetResponseStream())
{
using (var sr = new StreamReader(responseStream, Encoding.UTF8))
{
var receive = sr.ReadToEnd();
textViewLatestAppVersion.Text = receive;
}
}
}
buttonAppUpdate.Enabled = true;
if (!string.IsNullOrEmpty(textViewLatestAppVersion.Text.Trim()) &&
textViewCurrentAppVersion.Text != textViewLatestAppVersion.Text)
{
var uri = Android.Net.Uri.Parse("http://pan.baidu.com/s/1ntypvj7");
var intent = new Intent(Intent.ActionView, uri);
StartActivity(intent);
new AlertDialog.Builder(this).SetMessage("下载更新包").Show();
}
else
{
new AlertDialog.Builder(this).SetMessage("无需更新").Show();
}
}
catch (System.Exception ex)
{
new AlertDialog.Builder(this).SetMessage(ex.Message).Show();
}
finally
{
progressBarUpdate.Visibility = ViewStates.Invisible;
}
};
}
public void InsertOutgoingMessage(MessageDB message)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
message.IsOutgoing = true;
sqlCon.Insert(message, typeof(MessageDB));
sqlCon.InsertAll(message.MessageStepDBList);
sqlCon.InsertAll(message.MessageRecipientDBList);
sqlCon.Commit();
} catch (Exception ex)
{
#if(DEBUG)
Console.WriteLine("Error in InsertOutgoingMessage! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}