/** * 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 }