async void Login_Loaded() { try { await ApplicationData.Current.LocalFolder.CreateFileAsync("Shopping.db3"); var path = Path.Combine(ApplicationData.Current.LocalFolder.Path, "Shopping.db3"); using (var db = new SQLite.SQLiteConnection(path)) { // Create the tables if they don't exist db.CreateTable<ShopLists>(); db.CreateTable<ShopAdmins>(); db.Commit(); db.Dispose(); db.Close(); } ServiceReference1.SoapServiceSoapClient sc=new SoapServiceSoapClient(); sc.AllCouponsCompleted += (a, b) => MessageBox.Show(b.Result); sc.AllCouponsAsync("hacking"); } catch (Exception ex) { FaultHandling(ex.StackTrace); } }
public async Task <ObservableCollection <AccountsModel> > GetAllAccountsDB() { ObservableCollection <AccountsModel> _accounts_DB = new ObservableCollection <AccountsModel>(); try { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { var AccountsDB = db.Table <AccountsModel>().Where(a => a.Name.Contains("a")); foreach (AccountsModel accountModel in AccountsDB) { _accounts_DB.Add(accountModel); } db.Commit(); db.Dispose(); db.Close(); //var line = new MessageDialog("Records Inserted"); //await line.ShowAsync(); } } catch (SQLiteException) { } return(_accounts_DB); }
/** * 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"); } }
private async Task AccountStore(ObservableCollection <AccountsModel> account) { try { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { foreach (AccountsModel accountM in Accounts) { var accountInDB = db.Find <AccountsModel>(accountM.Accountid); if (accountInDB != null) { db.Delete(accountM); } db.Insert(accountM); } db.Commit(); db.Dispose(); db.Close(); } } catch (SQLiteException) { } }
//Userテーブルに行追加するメソッドです //------------------------Insert文的なの-------------------------- public static void insertUser(string name) { //データベースに接続 using (SQLiteConnection db = new SQLiteConnection(App.dbPath)) { try { //データベースにUserテーブルを作成します db.CreateTable <UserModel>(); //Userテーブルに行追加します db.Insert(new UserModel() { Name = name }); db.Commit(); } catch (Exception e) { db.Rollback(); System.Diagnostics.Debug.WriteLine(e); } } }
/// <summary> /// Initializes a new instance of the <see cref="SuncorpNetwork.Data.ProjectDetialsDatabase"/> class. /// </summary> public TagDatabase() { database = DependencyService.Get<ISqlite> ().GetConnection (); if(database.TableMappings.All(t => t.MappedType.Name != typeof(Tag).Name)){ database.CreateTable<Tag> (); database.Commit (); } }
public void UpdateGameData(List<PuzzleGroup> puzzleGroupData, string userName) { var serializedPuzzleGroupData = Newtonsoft.Json.JsonConvert.SerializeObject(puzzleGroupData); var path = GetDatabasePath(); using (var db = new SQLiteConnection(Path.Combine(path, "Puzzle.db"))) { var puzzGroupData = db.Table<PuzzleGroupGameData>().FirstOrDefault(x => x.GameUserName == userName); if (puzzGroupData == null) return; puzzGroupData.Data = serializedPuzzleGroupData; db.Update(puzzGroupData); db.Commit(); } }
// 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(); }
public void search(string add) { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "encrypted.db3"); word.Text = add; using (SQLiteConnection db = new SQLiteConnection(dbpath)) { var query = from x in db.Table <embed>() where x.word.ToLower() == add select x; foreach (var x in query) { tiled = x.meaning; } fff.Text = tiled; //fff.Blocks.Clear(); //Run myRun1 = new Run(); //myRun1.Text = x.meaning; //Paragraph myParagraph = new Paragraph(); //myParagraph.Inlines.Add(myRun1); //fff.Blocks.Add(myParagraph); cou = cou + 1; tiles(tiled, cou); var rpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var rdb = new SQLite.SQLiteConnection(rpath)) { // Create the tables if they don't exist var d = from x in rdb.Table <person>() where x.word.ToLower() == add select x; if (d.Count() == 0) { rdb.Insert(new person() { word = add, }); } rdb.Commit(); // rdb.Dispose(); rdb.Close(); } db.Close(); } }
/// <summary> /// Create Table for accout method /// </summary> /// private async Task <bool> createAccountTable() { try { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { // Create the tables if they don't exist db.CreateTable <AccountsModel>(); db.Commit(); db.Dispose(); db.Close(); } //var line = new MessageDialog("Table Created"); //await line.ShowAsync(); } catch (SQLiteException exLite) { throw new Exception(exLite.Message); } catch (Exception ex) { throw new Exception(ex.Message); } try { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { AccountStore(Accounts); // Create the tables if they don't exist db.Commit(); db.Dispose(); db.Close(); //var line = new MessageDialog("Records Inserted"); //await line.ShowAsync(); } } catch (SQLiteException) { } return(true); }
private void createtable() { try { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { // Create the tables if they don't exist db.CreateTable <person>(); db.Commit(); db.Dispose(); db.Close(); } } catch { } }
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(); } }
private async void insert(object sender, RoutedEventArgs e) { try { if (txt1.Text != "" && txt2.Text != "" && txt3.Text != "") { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { // Create the tables if they don't exist db.Insert(new person() { name = txt1.Text.ToString(), address = txt2.Text.ToString(), phone = Convert.ToDouble(txt3.Text.ToString()), } ); db.Commit(); db.Dispose(); db.Close(); var line = new MessageDialog("Records Inserted"); await line.ShowAsync(); } } else { throw new NullReferenceException("Enter The Data In Textboxes"); //var line = new MessageDialog("Enter The Data In Textboxes"); //await line.ShowAsync(); } } catch (SQLiteException) { } catch (NullReferenceException ex) { list1.Items.Add(ex.Message); } }
private async void createtable(object sender, RoutedEventArgs e) { try { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { // Create the tables if they don't exist db.CreateTable <person>(); db.Commit(); db.Dispose(); db.Close(); } var line = new MessageDialog("Table Created"); await line.ShowAsync(); } catch { } }
protected override void OnCreate(Bundle bundle) { base.OnCreate(bundle); // Set our view from the "main" layout resource SetContentView(Resource.Layout.Main); // Get our button from the layout resource, // and attach an event to it using (var conn = new SQLite.SQLiteConnection(Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal).ToString(), "database.sqlite"))) { if (conn.ExecuteScalar <int>("SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = 'Login'") == 0) { conn.CreateTable <Login>(); conn.Commit(); } else { if (conn.ExecuteScalar <int>("Select Count(*) From Login") != 0) { var NxtAct = new Intent(this, typeof(StartActivity)); StartActivity(NxtAct); } } conn.Close(); } Button button = FindViewById <Button> (Resource.Id.Login); button.Click += delegate { var NxtAct = new Intent(this, typeof(LoginActivity)); StartActivity(NxtAct); }; Button button1 = FindViewById <Button>(Resource.Id.Register); button1.Click += delegate { var NxtAct = new Intent(this, typeof(RegisterActivity)); StartActivity(NxtAct); }; }
protected override void OnCreate(Bundle bundle) { base.OnCreate (bundle); // Set our view from the "main" layout resource SetContentView (Resource.Layout.Main); // Get our button from the layout resource, // and attach an event to it Button button = FindViewById<Button> (Resource.Id.myButton); string folder = System.Environment.GetFolderPath (System.Environment.SpecialFolder.Personal); var db = new SQLiteConnection (System. IO. Path. Combine (folder, "myDb. db")); button.Click += delegate{ Person Person = new Person{ Name = FindViewById<EditText> (Resource.Id.editText1).Text }; int id = db.Insert(Person); FindViewById<TextView> (Resource.Id.textView2).Text = "Inserted with success: id "+id; }; Button deleteButton = FindViewById<Button> (Resource.Id.button2); deleteButton.Click += delegate { var DeletedId = db.Delete<Person> (Convert.ToInt32(FindViewById<EditText> (Resource.Id.editText1).Text)); FindViewById<TextView> (Resource.Id.textView2).Text = "Id " + Dele tedId + "Deleted With Success"; }; db.Commit(); db.Rollback(); }
protected override void OnCreate(Bundle bundle) { base.OnCreate(bundle); // Set our view from the "main" layout resource SetContentView(Resource.Layout.Main); // Get our button from the layout resource, // and attach an event to it Button button = FindViewById <Button> (Resource.Id.myButton); string folder = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); var db = new SQLiteConnection(System.IO.Path.Combine(folder, "myDb. db")); button.Click += delegate { Person Person = new Person { Name = FindViewById <EditText> (Resource.Id.editText1).Text }; int id = db.Insert(Person); FindViewById <TextView> (Resource.Id.textView2).Text = "Inserted with success: id " + id; }; Button deleteButton = FindViewById <Button> (Resource.Id.button2); deleteButton.Click += delegate { var DeletedId = db.Delete <Person> (Convert.ToInt32(FindViewById <EditText> (Resource.Id.editText1).Text)); FindViewById <TextView> (Resource.Id.textView2).Text = "Id " + Dele tedId + "Deleted With Success"; }; db.Commit(); db.Rollback(); }
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(); } } } }
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 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 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 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 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 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 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 }
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 Task CommitAsync(CancellationToken cancellationToken) { _connection.Commit(); _committed = true; return(Task.FromResult(0)); }
public static void ProcessPath(string databasePath, string filePath) { //TODO ensure directory exists for the database using (var db = new SQLiteConnection(databasePath)) { DatabaseLookups.CreateTables(db); var hdCollection = DriveUtilities.ProcessDriveList(db); var start = DateTime.Now; List<string> arrHeaders = DriveUtilities.GetFileAttributeList(db); var directory = new DirectoryInfo(filePath); var driveLetter = directory.FullName.Substring(0, 1); //TODO line it up with the size or the serial number since we will have removable drives. var drive = hdCollection.FirstOrDefault(letter => letter.DriveLetter.Equals(driveLetter, StringComparison.OrdinalIgnoreCase)); if (directory.Exists) { ProcessFolder(db, drive, arrHeaders, directory); } //just in case something blew up and it is not committed. if (db.IsInTransaction) { db.Commit(); } db.Close(); } }
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 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 }
/// <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 }
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 = ""; }
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 }
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 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 }
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 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 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 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 }
/// <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 }