C# SQLite.SQLiteConnection.Commit方法代码示例

        public override bool Open(Area owner)
        {
            Owner = owner;
            if (!DataFolder.Exists)
            {
                DataFolder.Create();
            }
            ObjectDatabase = new SQLite.SQLiteConnection(DataFile.FullName, SQLite.SQLiteOpenFlags.Create | SQLite.SQLiteOpenFlags.FullMutex | SQLite.SQLiteOpenFlags.ReadWrite);
            BlobDatabase = new SQLite.SQLiteConnection(BlobFile.FullName, SQLite.SQLiteOpenFlags.FullMutex | SQLite.SQLiteOpenFlags.Create | SQLite.SQLiteOpenFlags.ReadWrite);
            InitializeDBTypes();

            var version = ObjectDatabase.Table<StandardObjectStoreMetadata>().FirstOrDefault();
            if (version == null)
            {
                ObjectDatabase.BeginExclusive();
                Printer.PrintMessage("Upgrading object store database...");
                var records = owner.GetAllRecords();
                foreach (var x in records)
                {
                    ImportRecordFromFlatStore(x);
                }
                var meta = new StandardObjectStoreMetadata();
                meta.Version = 2;
                ObjectDatabase.InsertSafe(meta);
                ObjectDatabase.Commit();
            }
            else if (version.Version < 3)
            {
                Printer.PrintMessage("Upgrading object store database...");
                foreach (var x in BlobDatabase.Table<Blobject>())
                {
                    Blobsize bs = new Blobsize() { BlobID = x.Id, Length = x.Data.Length };
                    BlobDatabase.Insert(bs);
                }
                ObjectDatabase.BeginExclusive();
                ObjectDatabase.DropTable<StandardObjectStoreMetadata>();
                ObjectDatabase.CreateTable<StandardObjectStoreMetadata>();
                var meta = new StandardObjectStoreMetadata();
                meta.Version = 3;
                ObjectDatabase.InsertSafe(meta);
                ObjectDatabase.Commit();
            }
            return true;
        }

		private void ShowPendingContactOptions()
		{
			try {
				UIActionSheet actionSheet;
				actionSheet = new UIActionSheet();
				if (!string.IsNullOrEmpty(this.mobile))
					actionSheet.AddButton(this.mobile);
				if (!string.IsNullOrEmpty(this.email))
					actionSheet.AddButton(this.email);
				actionSheet.AddButton("Cancel");
				actionSheet.Clicked += delegate(object a, UIButtonEventArgs b) {
					using (var conn = new SQLite.SQLiteConnection(AppDelegate._dbPath)) {
						string recipient = "";
						//int present_thread_id = 0;

						if (actionSheet.ButtonTitle(b.ButtonIndex) == this.mobile) {
							try {
								var phoneUtil = PhoneNumberUtil.GetInstance();
								recipient = phoneUtil.Format(phoneUtil.Parse(this.mobile, AppDelegate.GetCountryCode()), PhoneNumberFormat.E164);
							} catch (Exception e) {
								recipient = this.mobile;
							}
						} else if (actionSheet.ButtonTitle(b.ButtonIndex) == this.email) {
							recipient = this.email;
						}

						if (actionSheet.ButtonTitle(b.ButtonIndex) != "Cancel") { 
							/*
							List<PushChatThread> pctList = conn.Query<PushChatThread>("select * from PushChatThread");

							foreach (PushChatThread pct in pctList) {
								if (pct.Number.Equals(recipient)) {
									present_thread_id = pct.ID;
									break;
								}
							}
							*/
							PushChatThread thread = conn.FindWithQuery<PushChatThread>("select * from PushChatThread where Number = ?", recipient);
							//if (present_thread_id == 0) {
							if (thread == null) {
								PushContact contact = conn.FindWithQuery<PushContact>("select * from PushContact where Number = ?", recipient);
								thread = new PushChatThread {
									DisplayName = contact.Name,
									Number = recipient,
									Recipient_id = 0,
									TimeStamp = AppDelegate.CurrentTimeMillis(),
									Message_count = 0,
									Snippet = "",
									Read = 0,
									Type = "PushLocal"
								};
								conn.Insert(thread);
								conn.Commit();
								conn.Close();
								//present_thread_id = pct_val.ID;
							}

							//appDelegate.chatView.setThreadID(present_thread_id);
							appDelegate.chatView.setThreadID(thread.ID);
							appDelegate.chatView.setNumber(recipient);
							appDelegate.chatView.setThreadSelected(thread.DisplayName + " (" + recipient + ")");
							appDelegate.GoToView(appDelegate.chatView);
						}
					}
				};
				actionSheet.ShowInView(View);
			} catch (Exception ex) {
				Console.Write(ex.Message);
			}
		}

        private void CouponCodeValidations(object sender, RoutedEventArgs e)
        {
            try
            {

                // MessageBox.Show(GlobalMethods.AdminName());
                var path = Path.Combine(ApplicationData.Current.LocalFolder.Path, "Shopping.db3");
                using (var db = new SQLite.SQLiteConnection(path))
                {
                    var a = from x in db.Table<ShopLists>() where x.CouponId == CopuonCode.Text && x.Condition == 0 && x.AdminName == AdminName select x;
                    //MessageBox.Show(a.Count().ToString());
                    if (a.Count() == 1)
                    {
                        foreach (var VARIABLE in a)
                        {
                            CouponCheck.Text = "Coupon Valid\n" + VARIABLE.Offer + "% Offer,Valid Till " + VARIABLE.ExpiryDateTime + "\n" +
                                               VARIABLE.CouponId + "\n" + VARIABLE.Name + "\n" + VARIABLE.MobileNo;
                            db.Update(new ShopLists()
                            {
                                Condition = 1,
                                ExpiryDateTime = VARIABLE.ExpiryDateTime,
                                AdminName = VARIABLE.AdminName,
                                CouponId = VARIABLE.CouponId,
                                MobileNo = VARIABLE.MobileNo,
                                Name = VARIABLE.Name,
                                Offer = VARIABLE.Offer,
                                AddedDateTime = DateTime.Now

                            });
                            db.Commit();
                        }
                    }
                    else
                    {
                        var b = from x in db.Table<ShopLists>() where x.CouponId == CopuonCode.Text && x.Condition == 1 && x.AdminName == AdminName select x;
                        if (b.Count() == 1)
                        {
                            foreach (var VARIABLE in b)
                            {
                                CouponCheck.Text = "Sorry,Coupon Already Used on " + VARIABLE.AddedDateTime.ToString() +
                                                    ".\n" + VARIABLE.Offer + "% Offer,Valid Till " +
                                                    VARIABLE.ExpiryDateTime + "\n" +
                                                    VARIABLE.CouponId + "\n" + VARIABLE.Name + "\n" + VARIABLE.MobileNo;
                            }
                        }
                        else
                        {
                            CouponCheck.Text = "Invalid Coupon";
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.StackTrace);
            }
        }

        private async void Button_Click_1(object sender, RoutedEventArgs e)
        {
            String Coupon_Id = Id();
            //await ApplicationData.Current.LocalFolder.CreateFileAsync("Shopping.db3");
            var path = Path.Combine(ApplicationData.Current.LocalFolder.Path, "Shopping.db3");
            if (Name.Text != "" && Mobile_No.Text != "")
            {
                using (var db = new SQLite.SQLiteConnection(path))
                {

                    int offer = Coupon_List.SelectedIndex;
                    int coupon = 0;
                    switch (offer)
                    {
                        case 0:
                            coupon = 5;
                            break;
                        case 1:
                            coupon = 10;
                            break;
                        case 2:
                            coupon = 25;
                            break;
                        case 3:
                            coupon = 40;
                            break;
                        case 4:
                            coupon = 50;
                            break;

                    }

                    db.Insert(new ShopLists() { Name = Name.Text, Condition = 0, AdminName = AdminName, ExpiryDateTime = Date_Picker.Value.Value, CouponId = Coupon_Id, MobileNo = Mobile_No.Text, Offer = coupon, AddedDateTime = DateTime.Now });


                    SmsComposeTask smsComposeTask = new SmsComposeTask();
                    smsComposeTask.To = Mobile_No.Text;
                    smsComposeTask.Body = "Coupon Code " + Coupon_Id + ",Offer " + coupon + "%.Valid Till " +
                                          Date_Picker.Value.Value;
                    smsComposeTask.Show();
                    db.Commit();
                    Mobile_No.Text = Name.Text = "";
                }


                //string b="";
                //var a = from x in db.Table<Shop>() select x;
                //foreach (var s in a)
                //{
                //    b = b + s.Condition + s.DateTime + s.Id + s.MobileNo + s.Name + s.Offer;
                //}
                //MessageBox.Show(b);
            }
            else
            {
                MessageBox.Show("Fill all the fields");
            }

        }

		public static void UpdateThreadNames()
		{
			var conn = new SQLite.SQLiteConnection(AppDelegate._dbPath);
			List<PushChatThread> pct = conn.Query<PushChatThread>("SELECT * FROM PushChatThread where DisplayName is null or DisplayName = ''");
			PhoneNumberUtil phoneUtil = PhoneNumberUtil.GetInstance();
			AddressBook book = new AddressBook();
			foreach (PushChatThread thread in pct) {
				String display_name = null;
				foreach (Contact c in book) {
					if (thread.Number.Contains("@")) {
						if (!c.Emails.Any())
							continue;
						foreach (Email e in c.Emails) {
							if (thread.Number.Equals(e.Address)) {
								display_name = c.DisplayName;
								break;
							}
						}
					} else {
						if (!c.Phones.Any())
							continue;
						foreach (Phone p in c.Phones) {
							if (p.Number.Contains("*") || p.Number.Contains("#"))
								continue;
							try {
								String number = phoneUtil.Format(phoneUtil.Parse(p.Number, AppDelegate.GetCountryCode()), PhoneNumberFormat.E164);
								if (thread.Number.Equals(number)) {
									display_name = c.DisplayName;
									break;
								}
							} catch (Exception e) {
								Console.WriteLine("Exception parsing/formatting '" + p.Number + "': " + e.Message);
							}
						}
					}
					if (display_name != null) {
						conn.Execute("UPDATE PushChatThread Set DisplayName = ? WHERE ID = ?", display_name, thread.ID);
						break;
					}
				}
			}
			conn.Commit();
			conn.Close();
		}

		private void updateChatThread(IncomingMessage message, string msg)
		{
			// Figure out where the SQLite database will be.
			var conn = new SQLite.SQLiteConnection(_dbPath);
			String number = message.Sender;
			// Check if there is an existing thread for this sender

			PushChatThread thread = conn.FindWithQuery<PushChatThread>("select * from PushChatThread where Number = ?", number);
			if (thread != null) {
				conn.Execute("UPDATE PushChatThread Set Snippet = ?, TimeStamp = ?, Message_count = ?, Read = ?, Type = ? WHERE ID = ?", msg, message.MessageId, 1, 1, "Push", thread.ID);
				conn.Commit();
			} else {
				PushContact contact = conn.FindWithQuery<PushContact>("select * from PushContact where Number = ?", number);
				thread = new PushChatThread {
					DisplayName = contact.Name,
					Number = number,
					Recipient_id = 0,
					TimeStamp = Convert.ToInt64(message.MessageId),
					Message_count = 1,
					Snippet = msg,
					Read = 1,
					Type = "Push"
				};
				conn.Insert(thread);
				//conn.Execute("UPDATE PushChatThread Set Recipient_id = ? WHERE Number = ?", present_thread_id, sender);
			}

			var pmessage = new PushMessage {
				Thread_id = thread.ID,
				Number = number,
				TimeStamp = CurrentTimeMillis(),
				TimeStamp_Sent = Convert.ToInt64(message.MessageId),
				Read = 1,
				Message = msg,
				Status = true,
				Service = "Push"
			};
			conn.Insert(pmessage);
			conn.Commit();
			conn.Close();
			RefreshChatListView();
		}

		private void AddDataToConversation()
		{
			messages = new List<string>();
			timeStamps = new List<string>();
			isLeft = new List<Boolean>();
			message_ids = new List<long>();
			isdelivered = new List<bool>();
			using (var conn = new SQLite.SQLiteConnection(AppDelegate._dbPath)) {
				// Check if there is an existing thread for this sender
				List<PushMessage> pmList = conn.Query<PushMessage>("SELECT * FROM PushMessage WHERE Thread_id = ?", ThreadID);

				foreach (PushMessage pm in pmList) {
					messages.Add(pm.Message);
					DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc).AddSeconds(pm.TimeStamp / 1000).ToLocalTime();

					timeStamps.Add("" + epoch.ToString("ddd HH:mm tt"));
					message_ids.Add(pm.TimeStamp);
					isdelivered.Add(pm.Status);
					isLeft.Add(pm.Service == "Push");
				}
				conn.Execute("UPDATE PushChatThread Set Read = ? WHERE ID = ?", 0, ThreadID);
				conn.Commit();
				conn.Close();
			}

		}

		public static void RefreshPushDirectory()
		{
			Console.WriteLine("starting contacts sync");
			AddressBook book = new AddressBook();
			PhoneNumberUtil phoneUtil = PhoneNumberUtil.GetInstance();
			book.RequestPermission().ContinueWith(t => {
				if (!t.Result) {
					Console.WriteLine("Permission denied by user or manifest");
					return;
				}
				long now = Utils.CurrentTimeMillis();
				Dictionary<String, String> map = new Dictionary<String, String>();
				int i = 0, j = 0, k = 0;
				foreach (Contact contact in book) {
					if (String.IsNullOrEmpty(contact.DisplayName))
						continue;
					foreach (Phone phone in contact.Phones) {
						j++;
						if (phone.Number.Contains("*") || phone.Number.Contains("#"))
							continue;
						try {
							String number = phoneUtil.Format(phoneUtil.Parse(phone.Number, AppDelegate.GetCountryCode()), PhoneNumberFormat.E164);
							if (!map.ContainsKey(number))
								map.Add(number, contact.DisplayName);
						} catch (Exception e) {
							Console.WriteLine("Exception parsing/formatting '" + phone.Number + "': " + e.Message);
						}
					}
					foreach (Email email in contact.Emails) {
						k++;
						if (!map.ContainsKey(email.Address))
							map.Add(email.Address, contact.DisplayName);
					}
					i++;
				}
				Console.WriteLine(i + " contacts in address book with " + j + " phone numbers and " + k + " email addresses");
				Dictionary<String, String> tokens = hashNumbers(map.Keys.ToList());
				List<String> response = MessageManager.RetrieveDirectory(tokens.Keys.ToList());
				Console.WriteLine("found " + response.Count + " securecom users");
				using (var conn = new SQLite.SQLiteConnection(AppDelegate._dbPath)) {
					conn.BeginTransaction();
					conn.Execute("DELETE FROM PushContact");
					foreach (String key in response) {
						String number = tokens[key];
						if (number == null) // is this needed?
							continue;
						conn.Insert(new PushContact { Number = number, Name = map[number] });
					}
					conn.Commit();

				}
				Console.WriteLine("contacts sync finished, took " + ((Utils.CurrentTimeMillis() - now) / 1000.0) + " seconds");
			}, TaskScheduler.Current);
		}

		private async void SendMessageThread(bool isattachment, string message)
		{
			DateTime now = DateTime.Now;
			const string format = "ddd HH:mm tt";
			bool delivered = false;
			try {
				MessageManager.SendMessage(MessageManager.PrepareOutgoingMessage(message, Number));
				delivered = true;
			} catch (StaleDevicesException e) {
				MessageManager.EndSession(Number);
			} catch (Exception e) {
				
				Console.WriteLine("Exception while sending message...." + e.Message);
			}

			using (var conn = new SQLite.SQLiteConnection(AppDelegate._dbPath)) {
				var pmessage = new PushMessage {
					Thread_id = ThreadID,
					Number = Number,
					TimeStamp = AppDelegate.CurrentTimeMillis(),
					TimeStamp_Sent = Convert.ToInt64(AppDelegate.CurrentTimeMillis()),
					Read = 0,
					Message = message,
					Status = delivered,
					Service = "PushLocal"
				};
				conn.Insert(pmessage);
				conn.Commit();
				conn.Close();
			}

			PointF pf = new PointF(0f, (messages.Count + 3) * 64 - table.Bounds.Size.Height);
			table.SetContentOffset(pf, true);

			ChatBubbleCell cell = new ChatBubbleCell(false, false, delivered);
			cell.Update(message, now.ToString(format));
			TableRowHeight = cell.getHeight();
			if (!delivered)
				cell.SetAsUndelivered();
			if (isattachment)
				cell.setImagePreview(thumbnail);
			LoadTable(cell);

			AddDataToConversation();
		}

		public void DeleteSelected(UITableView tableView, NSIndexPath indexPath)
		{
			ChatBubbleCell selectedCell = (ChatBubbleCell)source.CellGroups[indexPath.Section].Cells[indexPath.Row];
			try {
				lock (this) {
					using (var conn = new SQLite.SQLiteConnection(AppDelegate._dbPath)) {
						conn.Execute("DELETE FROM PushMessage WHERE TimeStamp = ?", selectedCell.getMessageID());
						conn.Commit();
						conn.Close();
						ViewWillAppear(true);
					}
				}
			} catch (Exception e) {
				Console.WriteLine("Error while deleting thread " + e.Message);
			}
		}

		public void DeleteSelected(UITableView tableView, NSIndexPath indexPath)
		{
			ChatCell selectedCell = (ChatCell)source.CellGroups[indexPath.Section].Cells[indexPath.Row];
			try {
				using (var conn = new SQLite.SQLiteConnection(AppDelegate._dbPath)) {
					conn.Execute("DELETE FROM PushChatThread WHERE ID = ?", selectedCell.GetThreadID());
					conn.Execute("DELETE FROM PushMessage WHERE Thread_id = ?", selectedCell.GetThreadID());
					conn.Commit();
					conn.Close();
				}
			} catch (Exception e) {
				Console.WriteLine("Error while deleting thread " + e.Message);
			}
			ShowEditButton();
//			UIAlertView alert = new UIAlertView("Deleted Conversation with", ""+selectedCell.GetNumber(), null, "Ok");
//			alert.Show();
		}

		public void RowSelected(UITableView tableView, NSIndexPath indexPath)
		{

			ChatCell selectedCell = (ChatCell)source.CellGroups[indexPath.Section].Cells[indexPath.Row];
			appDelegate.chatView.setThreadSelected(selectedCell.GetHeader());
			using (var conn = new SQLite.SQLiteConnection(AppDelegate._dbPath)) {
				conn.Execute("UPDATE PushChatThread Set Read = ? WHERE ID = ?", 0, selectedCell.GetThreadID());
				conn.Commit();
				conn.Close();
			}
			appDelegate.chatView.setThreadID(selectedCell.GetThreadID());
			appDelegate.chatView.setNumber(selectedCell.GetNumber());
			appDelegate.GoToView(appDelegate.chatView);
//			UIAlertView alert = new UIAlertView("Chat index selected", ""+indexPath.Row, null, "Ok");
//			alert.Show();

		}

		private void SettingsAction()
		{
			try {
				UIActionSheet actionSheet;
				actionSheet = new UIActionSheet();

				actionSheet.AddButton("Refresh Securecom Contacts");
				actionSheet.AddButton("Re-register");
				actionSheet.AddButton("Cancel");

				actionSheet.Clicked += delegate(object a, UIButtonEventArgs b) {
					if (b.ButtonIndex == (0)) {
						try {
							LoadingOverlay loadingOverlay;

							// Determine the correct size to start the overlay (depending on device orientation)
							var bounds = UIScreen.MainScreen.Bounds; // portrait bounds
							if (UIApplication.SharedApplication.StatusBarOrientation == UIInterfaceOrientation.LandscapeLeft || UIApplication.SharedApplication.StatusBarOrientation == UIInterfaceOrientation.LandscapeRight) {
								bounds.Size = new SizeF(bounds.Size.Height, bounds.Size.Width);
							}
							// show the loading overlay on the UI thread using the correct orientation sizing
							loadingOverlay = new LoadingOverlay(bounds);
							this.View.Add(loadingOverlay);

							Task taskA = Task.Factory.StartNew(StextUtil.RefreshPushDirectory);
							taskA.Wait();
							loadingOverlay.Hide();

						} catch (Exception e) {
							UIAlertView alert = new UIAlertView("Failed!", "Contact Refresh Failed!", null, "Ok");
							alert.Show();
						}

					} else if (b.ButtonIndex == (1)) {
							var alert = new UIAlertView {
								Title = "Re-register?", 
								Message = "This action will delete your preivious conversations!"
							};
							alert.AddButton("Yes");
							alert.AddButton("No");
							// last button added is the 'cancel' button (index of '2')
							alert.Clicked += delegate(object a1, UIButtonEventArgs b1) {
								if (b1.ButtonIndex == (0)) {
									using (var conn = new SQLite.SQLiteConnection(AppDelegate._dbPath)) {
										conn.Execute("DELETE FROM PushContact");
										conn.Execute("DELETE FROM PushChatThread");
										conn.Execute("DELETE FROM PushMessage");
										conn.Commit();
										conn.Close();
									}
									Session.ClearSessions();
									appDelegate.GoToView(appDelegate.registrationView);
								}
							};
							alert.Show();
						} 
				};
				actionSheet.ShowInView(View);
			} catch (Exception ex) {
				Console.Write(ex.Message);
			}
		}