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

		private void AddContactsData(String contactFilter)
		{

			// Figure out where the SQLite database will be.
			bool showAll = contactFilter != "Securecom users";
			var conn = new SQLite.SQLiteConnection(AppDelegate._dbPath);
			List<PushContact> pc = conn.Query<PushContact>("select * from PushContact");
			conn.Close();
			List<String> registeredContacts = new List<String>();
			List<String> groups = new List<String>();
			foreach (PushContact c in pc)
				registeredContacts.Add(c.Number);
			var phoneUtil = PhoneNumberUtil.GetInstance();
			if (!showAll) {
				Dictionary<String, List<String>> map = new Dictionary<String, List<String>>();
				foreach (PushContact c in pc) {
					String n = c.Name ?? c.Number;
					if (!map.ContainsKey(n))
						map[n] = new List<String>();
					map[n].Add(c.Number);
				}
				foreach (KeyValuePair<String, List<String>> entry in map.OrderBy(c => c.Key)) {
					String group = entry.Key.Substring(0, 1).ToUpper();
					bool newGroup = !groups.Contains(group);
					foreach (CustomCellGroup ccg in cellGroups) {
						if (ccg.Name.Equals(group)) {
							newGroup = false;
							tableCellGroup = ccg;
						}
					}
					ContactListCell cell = ContactListCell.Create();
					cell.SetName(entry.Key);
					foreach (String number in entry.Value) {
						if (number.Contains("@"))
							cell.SetEmail(number);
						else
							cell.SetPhone(number);
					}
					if (newGroup) {
						tableCellGroup = new CustomCellGroup { Name = group };
						cellGroups.Add(tableCellGroup);
					}
					tableCellGroup.Cells.Add(cell);
				}
				return;
			}

			AddressBook book = new AddressBook();
			book.RequestPermission().ContinueWith(t => {
				if (!t.Result) {
					Console.WriteLine("Permission denied by user or manifest");
					return;
				}
			}, TaskScheduler.FromCurrentSynchronizationContext());

			foreach (Contact contact in book.OrderBy(c => c.DisplayName)) {
				if (!showAll && registeredContacts.Count == 0)
					break;
				if (String.IsNullOrEmpty(contact.DisplayName))
					continue;
				String group = contact.DisplayName.Substring(0, 1).ToUpper();
				bool newGroup = !groups.Contains(group);
				foreach (CustomCellGroup ccg in cellGroups) {
					if (ccg.Name.Equals(group)) {
						newGroup = false;
						tableCellGroup = ccg;
					}
				}

				ContactListCell cell = ContactListCell.Create();
				cell.SetName(contact.DisplayName);
				cell.SetEmail(null);
				cell.SetPhone(null);

				if (contact.Phones.Any()) {
					foreach (Phone p in contact.Phones) {
						if (showAll) {
							cell.SetPhone(p.Number);
							cell.registeredState = ContactListCell.STATE_PENDING;
							break;
						}
						if (p.Number.Contains("*") || p.Number.Contains("#"))
							continue;
						String number;
						try {
							number = phoneUtil.Format(phoneUtil.Parse(p.Number, AppDelegate.GetCountryCode()), PhoneNumberFormat.E164);
						} catch (Exception e) {
							continue;
						}
						if (!registeredContacts.Contains(number))
							continue;
						registeredContacts.Remove(number);
						cell.SetPhone(p.Number);
						cell.registeredState = ContactListCell.STATE_REGISTERED;
						//conn.Execute("UPDATE PushContact Set Name = ? WHERE Number = ?", contact.DisplayName, number);
						break;
					}
				}
				if (contact.Emails.Any()) {
					foreach (Email e in contact.Emails) {
//.........这里部分代码省略.........

		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);
			}
		}

		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 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();
		}

		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 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 PopulateTable()
		{
			table.ReloadData();
			UIImage thumbnail = null;
			List<CustomCellGroup> cellGroups = new List<CustomCellGroup>();
			tableCellGroup = new CustomCellGroup();
			cellGroups.Add(tableCellGroup);
			PhoneNumberUtil phoneUtil = PhoneNumberUtil.GetInstance();
			AddressBook book = new AddressBook();
			var conn = new SQLite.SQLiteConnection(AppDelegate._dbPath);
			pct = conn.Query<PushChatThread>("SELECT * FROM PushChatThread ORDER BY TimeStamp DESC");
			conn.Close();
			int count = 0;

			foreach (PushChatThread thread in pct) {
				String display_name = thread.DisplayName;

				ChatCell chatCell = ChatCell.Create();
				chatCell.SetHeader(display_name + " (" + thread.Number + ")");
				chatCell.SetSubheading(thread.Snippet);
				chatCell.SetThreadID(thread.ID);
				chatCell.SetNumber(thread.Number);
				chatCell.SetAvatar(thumbnail);
				DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc).AddSeconds(thread.TimeStamp / 1000).ToLocalTime();
				chatCell.SetLabelTime("" + epoch.ToString("HH:mm"));
				chatCell.Accessory = UITableViewCellAccessory.DisclosureIndicator;
					
				if (thread.Read != 0)
					chatCell.BackgroundColor = UIColor.Green;
				tableCellGroup.Cells.Insert(count, chatCell);
				count++;

			}
			source = new CustomCellTableSource(cellGroups);
			source.RowSelectedAction = RowSelected;
			source.DeleteAction = DeleteSelected;
			source.DeleteTitle = "Delete";
			table.Source = source;

			ShowEditButton();

		}

		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);
			}
		}