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

 public void Delete(int sourceId)
 {
     using (var db = new SQLite.SQLiteConnection(Settings.DatabasePath))
     {
         db.Execute("delete from Translation where SourceId = ?", sourceId);
         db.Execute("delete from Source where Id = ?", sourceId);
     }
 }

 public void Delete(IEnumerable<int> itemIds)
 {
     using (var db = new SQLite.SQLiteConnection(Settings.DatabasePath))
     {
         foreach (var id in itemIds)
         {
             db.Execute("delete from Item where Id = ?", id);
             db.Execute("delete from Translation where ItemId = ?", id);
         }
     }
 }

 public void UpdateText(string newText, int id)
 {
     using (var db = new SQLite.SQLiteConnection(Settings.DatabasePath))
     {
         db.Execute("update Item set Text = ? where Id = ?", newText, id);
     }
 }

		public void ClearDatabase()
		{
			if (databaseCreated) 
			{
				using (var db = new SQLite.SQLiteConnection (GetDatabasePath ())) 
				{
					db.Execute ("Delete from Person");
				}
			}
		}

 public void Save(Preference model)
 {
     using (var db = new SQLite.SQLiteConnection(Settings.DatabasePath))
     {
         var existingCount = db.ExecuteScalar<int>("select count(Name) from Preference where Name = ?",
            model.Name);
         if (existingCount == 0)
         {
             db.Insert(model);
         }
         else
         {
             db.Execute("update Preference set Value = ? where Name = ?",
                 model.Value, model.Name);
         }
     }
 }

 public ISQLiteConnection GetSqliteConnection()
 {
     if (isPoolingEnabled)
     {
         return mainPool.GetSqliteConnection();
     }
     else
     {
         ISQLiteConnection conn = new SQLite.SQLiteConnection(DatabasePath);
         conn.Execute("PRAGMA synchronous = OFF");
         // Five second busy timeout
         conn.BusyTimeout = new TimeSpan(0, 0, 5);
         return conn;
     }
 }

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

        public ISQLiteConnection GetSqliteConnection()
        {
            lock (connectionPoolLock)
            {
                if (getConnectionsAllowed)
                {
                    ISQLiteConnection conn = null;
                    if (availableConnections.Count > 0)
                    {
                        // Grab an existing connection
                        conn = availableConnections.Pop();
                    }
                    else if (usedConnections < maxConnections)
                    {
                        // There are no available connections, and we have room for more open connections, so make a new one
                        conn = new SQLite.SQLiteConnection(databasePath);
                        conn.Execute("PRAGMA synchronous = OFF");
                        // Five second busy timeout
                        conn.BusyTimeout = new TimeSpan(0, 0, 5);
                    }

                    if (!ReferenceEquals(conn, null))
                    {
                        // We got a connection, so increment the counter
                        usedConnections++;
                        return conn;
                    }
                }
            }

            // If no connection available, sleep for 50ms and try again
            Thread.Sleep(50);

            // Recurse to try to get another connection
            return GetSqliteConnection();
        }

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

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

        private void UpdateDatbase()
        {
            try {
                using (var conn = new SQLite.SQLiteConnection (pathToDatabase)) {

                    var num = conn.ExecuteScalar<Int32> ("SELECT count(name) FROM sqlite_master WHERE type='table' and name='CNNote'", new object[]{ });
                    int count = Convert.ToInt32 (num);
                    if (count > 0)
                        return;

                    conn.CreateTable<CNNote> ();
                    conn.CreateTable<CNNoteDtls> ();
                    conn.DropTable<AdPara> ();
                    conn.CreateTable<AdPara> ();
                    conn.DropTable<AdNumDate> ();
                    conn.CreateTable<AdNumDate> ();
                    conn.DropTable<CompanyInfo> ();
                    conn.CreateTable<CompanyInfo> ();
                    conn.DropTable<Trader> ();
                    conn.CreateTable<Trader> ();
                    conn.DropTable<AdUser> ();
                    conn.CreateTable<AdUser> ();

                    string sql = @"ALTER TABLE Invoice RENAME TO sqlitestudio_temp_table;
                                    CREATE TABLE Invoice (invno varchar PRIMARY KEY NOT NULL, trxtype varchar, invdate bigint, created bigint, amount float, taxamt float, custcode varchar, description varchar, uploaded bigint, isUploaded integer, isPrinted integer);
                                    INSERT INTO Invoice (invno, trxtype, invdate, created, amount, taxamt, custcode, description, uploaded, isUploaded,isPrinted) SELECT invno, trxtype, invdate, created, amount, taxamt, custcode, description, uploaded, isUploaded,0 FROM sqlitestudio_temp_table;
                                    DROP TABLE sqlitestudio_temp_table";
                    string[] sqls = sql.Split (new char[]{ ';' });
                    foreach (string ssql in sqls) {
                        conn.Execute (ssql, new object[]{ });
                    }
                }
            } catch (Exception ex) {
                AlertShow (ex.Message);
            }
        }

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

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