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

        public void dropHistory()
        {
            using (var db = new SQLite.SQLiteConnection(app.DBPath))
            {
                db.DropTable<History>();
                var data = db.Table<History>();
                if (db.Delete(data) > 0)
                {

                }
            }
        }

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

        private void ResetData()
        {
            using (var db = new SQLite.SQLiteConnection(this.DBPath))
            {
                // Empty the Customer and Project tables 
                //db.DeleteAll<Collection>();
                //db.DeleteAll<Album>();
                db.DropTable<Collection>();
                db.DropTable<Album>();
                db.CreateTable<Collection>();
                db.CreateTable<Album>();

                // Add seed customers and projects
                db.Insert(new Collection()
                {
                    Id = 1,
                    Title = "Relaxing Music",
                    DateCreated = DateTime.Now.AddDays(-3),
                    Image = "Assets/DarkGray.png",
                    Void = false
                });
                db.Insert(new Collection()
                {
                    Id = 2,
                    Title = "Hardcore Metal",
                    DateCreated = DateTime.Now.AddDays(-2),
                    Image = "Assets/MediumGray.png",
                    Void = false
                });
                db.Insert(new Collection()
                {
                    Id = 3,
                    Title = "Best 90s Music",
                    DateCreated = DateTime.Now.AddDays(-1),
                    Image = "Assets/LightGray.png",
                    Void = false
                });
                db.Insert(new Album()
                {
                    Id = 1,
                    Title = "Believe",
                    Artist = "Cher",
                    CollectionId = 1,
                    LastFmId = "2026126",
                    MusicBrainzId = "61bf0388-b8a9-48f4-81d1-7eb02706dfb0",
                    DateAdded = DateTime.Now.AddDays(-4),
                    Void = false
                });
                db.Insert(new Album()
                {
                    Id = 2,
                    Title = "Believe 2",
                    Artist = "Cher",
                    CollectionId = 1,
                    LastFmId = "2026126",
                    MusicBrainzId = "61bf0388-b8a9-48f4-81d1-7eb02706dfb0",
                    DateAdded = DateTime.Now.AddDays(-3),
                    Void = false
                });
                db.Insert(new Album()
                {
                    Id = 3,
                    Title = "Believe 3",
                    Artist = "Cher",
                    CollectionId = 2,
                    LastFmId = "2026126",
                    MusicBrainzId = "61bf0388-b8a9-48f4-81d1-7eb02706dfb0",
                    DateAdded = DateTime.Now.AddDays(-2),
                    Void = false
                });
                db.Insert(new Album()
                {
                    Id = 4,
                    Title = "Believe 4",
                    Artist = "Cher",
                    CollectionId = 3,
                    LastFmId = "2026126",
                    MusicBrainzId = "61bf0388-b8a9-48f4-81d1-7eb02706dfb0",
                    DateAdded = DateTime.Now.AddDays(-1),
                    Void = false
                });

                //2026126","mbid":"61bf0388-b8a9-48f4-81d1-7eb02706dfb0"
                
            }
        }

		public override void ViewDidLoad ()
		{
			base.ViewDidLoad ();

			#region observadores del teclado
			// Keyboard popup
			NSNotificationCenter.DefaultCenter.AddObserver
			(UIKeyboard.DidShowNotification,KeyBoardUpNotification);

			// Keyboard Down
			NSNotificationCenter.DefaultCenter.AddObserver
			(UIKeyboard.WillHideNotification,KeyBoardDownNotification);
			#endregion

			if (UIDevice.CurrentDevice.UserInterfaceIdiom == UIUserInterfaceIdiom.Phone) {
				this.cmpContraseñaIphone.SecureTextEntry = true;
			} else {
				this.cmpContraseña.SecureTextEntry = true;
			}
				
			// Figure out where the SQLite database will be.
			var documents = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
			_pathToDatabase = Path.Combine(documents, "db_sqlite-net.db");

			this.btnEntrar.TouchUpInside += (sender, e) => {
				try{
					if (UIDevice.CurrentDevice.UserInterfaceIdiom == UIUserInterfaceIdiom.Phone) {
						contraseña = cmpContraseñaIphone;
					} else{
						contraseña = cmpContraseña;
					}
					if(cmpEmail.Text == "" || contraseña.Text == ""){
						UIAlertView alert = new UIAlertView () { 
							Title = "Espera!", Message = "Debes ingresar tu email y tu contraseña primero"
						};
						alert.AddButton ("Aceptar");
						alert.Show ();
					} else{
						//Creamos la base de datos y la tabla de persona
						using (var conn= new SQLite.SQLiteConnection(_pathToDatabase))
						{
							conn.DropTable<Person>();
							conn.CreateTable<Person>();
						}
						if(UIDevice.CurrentDevice.UserInterfaceIdiom == UIUserInterfaceIdiom.Phone){
							loginService.setUserData(cmpEmail.Text.Trim(), cmpContraseñaIphone.Text);
						}else{
							loginService.setUserData(cmpEmail.Text.Trim(), cmpContraseña.Text);
						}

						LoginService userData = loginService.Find();

						if(userData.Id.Equals("Invalido")){
							UIAlertView alert = new UIAlertView () { 
								Title = "Lo sentimos", Message = "Tus datos fueron invalidos, intentalo de nuevo"
							};
							alert.AddButton ("Aceptar");
							alert.Show ();
						}else{
							var person = new Person {ID = int.Parse(userData.Id),  Name = userData.nombre, LastName = userData.paterno, SecondLastName = userData.materno};
							using (var db = new SQLite.SQLiteConnection(_pathToDatabase ))
							{
								db.Insert(person);
							}
							UIAlertView alert = new UIAlertView () { 
								Title = "Bienvenido", Message = "Bienvenido a Fixbuy " + userData.nombre
							};
							alert.AddButton ("Aceptar");
							alert.Show ();
							Console.WriteLine("Este el es ID de usuario: " + userData.Id);
							this.NavigationController.PopViewController(true);
						}
					}
				}catch(System.Net.WebException){
					UIAlertView alerta = new UIAlertView () { 
						Title = "Ups =S", Message = "Algo salio mal, verifica tu conexión a internet e intentalo de nuevo"
					};
					alerta.AddButton ("Aceptar");
					alerta.Show ();
				}catch(Exception){
					UIAlertView alerta = new UIAlertView () { 
						Title = "Ups =S", Message = "Algo salio mal, por favor intentalo de nuevo"
					};
					alerta.AddButton ("Aceptar");
					alerta.Show ();
				}
			};

			this.btnRegistro.TouchUpInside += (sender, e) => {
				RegistryView registry = new RegistryView();
				this.NavigationController.PushViewController(registry, true);
			};
		}

//.........这里部分代码省略.........

			this.btnEstado.TouchUpInside += (sender, e) => {
				try{
					statesService = new StatesService();
					List<StatesService> estados = statesService.All();
					pickerDataModel.Items = estados;
					pickerStates.Model = pickerDataModel;
					pickerStates.Hidden = false;
					btnAceptar.Hidden = false;
					//actionSheetPicker.Picker.Source = pickerDataModel;
					//actionSheetPicker.Show();
				}catch (System.Net.WebException){
					UIAlertView alert = new UIAlertView () { 
						Title = "Ups =S", Message = "No se puede mostrar la lista de estados, verifica tu conexión a internet e intentalo de nuevo."
					};
					alert.AddButton ("Aceptar");
					alert.Show ();
				}catch(Exception excep){
					Console.WriteLine("ESTE ES EL ERROR: " + excep.ToString());
					UIAlertView alert = new UIAlertView () { 
						Title = "Ups =S", Message = excep.ToString()
					};
					alert.AddButton("Aceptar");
					alert.Show ();
				}
			};

			btnAceptar.TouchUpInside += (sender, e) => {
				this.pickerStates.Hidden = true;
				this.btnAceptar.Hidden = true;
			};

			pickerDataModel.ValueChanged += (sender, e) => {
				this.btnEstado.SetTitle(pickerDataModel.SelectedItem.ToString(), UIControlState.Normal);
				this.stateId = pickerDataModel.SelectedItem.id;
				this.state = pickerDataModel.SelectedItem.ToString();
			};

			this.btnLocalidad.TouchUpInside += (sender, e) => {
				try{
					if(stateId != ""){
						localityService = new LocalityService();
						localityService.setState(stateId);
						List<LocalityService> localidades = localityService.All();
						pickerDataModelLocality.Items = localidades;
						pickerStates.Model = pickerDataModelLocality;
						pickerStates.Hidden = false;
						btnAceptar.Hidden = false;
						//actionSheetPicker.Picker.Source = pickerDataModelLocality;
						//actionSheetPicker.Show();
					}
				}catch(System.Net.WebException){
					UIAlertView alert = new UIAlertView () { 
						Title = "Ups =S", Message = "No se puede mostrar la lista de localidades, verifica tu conexión a internet e intentalo de nuevo."
					};
					alert.AddButton ("Aceptar");
					alert.Show ();
				}catch(Exception){
					UIAlertView alert = new UIAlertView () { 
						Title = "Ups =S", Message = "Algo salio mal, por favor intentalo de nuevo."
					};
					alert.AddButton("Aceptar");
					alert.Show ();
				}
			};
				
			pickerDataModelLocality.ValueChanged += (sender, e) => {
				this.btnLocalidad.SetTitle(pickerDataModelLocality.SelectedItem.ToString(),UIControlState.Normal);
				this.localityId = pickerDataModelLocality.SelectedItem.id;
				this.locality = pickerDataModelLocality.SelectedItem.ToString();
			};

			btnGuardar.TouchUpInside += (sender, e) => {
				try{
					if(this.stateId != "" && this.localityId != ""){
						var state = new State {stateId = int.Parse( this.stateId), state = this.locality, localityId = int.Parse(this.localityId), locality = this.locality};
						using (var db = new SQLite.SQLiteConnection(_pathToDatabase ))
						{
							db.DropTable<State>();
							db.CreateTable<State>();
							db.Insert(state);
						}

						MainView.localityId = state.localityId;
						UIAlertView alert = new UIAlertView () { 
							Title = "Bien! =D", Message = "Gracias por definir tu estado y localidad ahora puedes empezar a buscar productos con FixBuy =D"
						};
						alert.AddButton ("Aceptar");
						alert.Show ();
						this.NavigationController.PopViewController(true);
					}
				}catch(Exception){
					UIAlertView alert = new UIAlertView () { 
						Title = "Ups =S", Message = "Algo salio mal, por favor intentalo de nuevo."
					};
					alert.AddButton("Aceptar");
					alert.Show ();
				}
			};
		}

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

		public override void ViewDidLoad ()
		{
			base.ViewDidLoad ();

			this.Title = "Menú";


			//Ocultamos el boton de tiendas registradas temporalmente
			//btnTiendas.Hidden = true;
			//btnInfo2.Hidden = true;

			var documents = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
			_pathToDatabase = Path.Combine(documents, "db_sqlite-net.db");

			//Creamos la base de datos y la tabla de persona, en caso de que ya exista no hace nada.
			using (var conn= new SQLite.SQLiteConnection(_pathToDatabase))
			{
				conn.CreateTable<Person>();
			}

			//Hacemos la conexion a la bd para buscar si hay un usuario registrado
			using (var db = new SQLite.SQLiteConnection(_pathToDatabase ))
			{
				people = new List<Person> (from p in db.Table<Person> () select p);
			}

			//Establecemos las imagenes de los botones
			setButtonImages();

			//Eventos para los botones de informacion
			this.btnInfo1.TouchUpInside += (sender, e) => {
				ToastView view = new ToastView("Busca productos por código de barras o nombre", 3000);
				view.SetGravity(ToastGravity.Center,0,0);
				view.Show();
			};

			this.btnInfo2.TouchUpInside += (sender, e) => {
				ToastView view = new ToastView("Localiza todas las tiendas registradas en FIXBUY", 3000);
				view.SetGravity(ToastGravity.Center,0,0);
				view.Show();
			};

			this.btnInfo3.TouchUpInside += (sender, e) => {
				ToastView view = new ToastView("Administra tus listas y los productos en ellas", 3000);
				view.SetGravity(ToastGravity.Center,0,0);
				view.Show();
			};

			this.btnInfo4.TouchUpInside += (sender, e) => {
				ToastView view = new ToastView("Inicia Sesion en FIXBUY para poder acceder a tus listas y más!", 3000);
				view.SetGravity(ToastGravity.Center,0,0);
				view.Show();
			};

			this.btnInfo5.TouchUpInside += (sender, e) => {
				ToastView view = new ToastView("Establece tu ubicación para una busqueda mas eficaz!", 3000);
				view.SetGravity(ToastGravity.Center,0,0);
				view.Show();
			};

			this.btnInfo6.TouchUpInside += (sender, e) => {
				ToastView view = new ToastView("Consulta los servicios que ofrece FIXBUY", 3000);
				view.SetGravity(ToastGravity.Center,0,0);
				view.Show();
			};

			this.btnCerrarSesion.TouchUpInside += (sender, e) => {
				UIAlertView alert = new UIAlertView () { 
					Title = "Te vas? =(", Message = "Estas seguro que quieres cerrar la sesión?"
				};
				alert.AddButton("Aceptar");
				alert.AddButton("Cancelar");
				alert.Clicked += (s, o) => {
					if(o.ButtonIndex == 0){
						using (var conn= new SQLite.SQLiteConnection(_pathToDatabase))
						{
							conn.DropTable<Person>();
							conn.CreateTable<Person>();
						}
						this.NavigationController.PopViewController(true);
					}
				};
				alert.Show ();
			};

			//Boton de buscar productos
			this.btnScan.TouchUpInside += (sender, e) => {
				// Configurar el escaner de codigo de barras.
				picker = new ScanditSDKRotatingBarcodePicker (MainView.appKey);
				picker.OverlayController.Delegate = new overlayControllerDelegate(picker, this);
				picker.OverlayController.ShowToolBar(true);
				picker.OverlayController.ShowSearchBar(true);
				picker.OverlayController.SetToolBarButtonCaption("Cancelar");
				picker.OverlayController.SetSearchBarKeyboardType(UIKeyboardType.Default);
				picker.OverlayController.SetSearchBarPlaceholderText("Búsqueda por nombre de producto");
				picker.OverlayController.SetCameraSwitchVisibility(SICameraSwitchVisibility.OnTablet);
				picker.OverlayController.SetTextForInitializingCamera("Iniciando la camara");
				this.PresentViewController (picker, true, null);

				picker.StartScanning ();
//.........这里部分代码省略.........

        private void ResetData()
        {
            using (var db = new SQLite.SQLiteConnection(this.DBPath))
            {
                // Empty the Customer and Project tables 
                //db.DeleteAll<Collection>();
                //db.DeleteAll<Album>();
                db.DropTable<Collection>();
                db.DropTable<Album>();
                db.CreateTable<Collection>();
                db.CreateTable<Album>();

                // Add seed customers and projects
                db.Insert(new Collection()
                {
                    Id = 1,
                    Title = "Relaxing Music",
                    DateCreated = DateTime.Now.AddDays(-3),
                    Void = false
                });
                db.Insert(new Collection()
                {
                    Id = 2,
                    Title = "Hardcore Metal",
                    DateCreated = DateTime.Now.AddDays(-2),
                    Void = false
                });
                db.Insert(new Collection()
                {
                    Id = 3,
                    Title = "Best 90s Music",
                    DateCreated = DateTime.Now.AddDays(-1),
                    Void = false
                });
                db.Insert(new Album()
                {
                    Id = 1,
                    Title = "Believe",
                    Artist = "Cher",
                    CollectionId = 1,
                    LastFmId = "2026126",
                    MusicBrainzId = "86b5434d-9479-35e3-98ca-8fbcfcf4e357",
                    DateAdded = DateTime.Now.AddDays(-4),
                    Void = false
                });
                db.Insert(new Album()
                {
                    Id = 2,
                    Title = "Believe 2",
                    Artist = "Cher",
                    CollectionId = 1,
                    LastFmId = "2026126",
                    MusicBrainzId = "86b5434d-9479-35e3-98ca-8fbcfcf4e357",
                    DateAdded = DateTime.Now.AddDays(-3),
                    Void = false
                });
                db.Insert(new Album()
                {
                    Id = 3,
                    Title = "Believe 3",
                    Artist = "Cher",
                    CollectionId = 2,
                    LastFmId = "2026126",
                    MusicBrainzId = "86b5434d-9479-35e3-98ca-8fbcfcf4e357",
                    DateAdded = DateTime.Now.AddDays(-2),
                    Void = false
                });
                db.Insert(new Album()
                {
                    Id = 4,
                    Title = "Believe 4",
                    Artist = "Cher",
                    CollectionId = 3,
                    LastFmId = "2026126",
                    MusicBrainzId = "86b5434d-9479-35e3-98ca-8fbcfcf4e357",
                    DateAdded = DateTime.Now.AddDays(-1),
                    Void = false
                });

                //2026126","mbid":"86b5434d-9479-35e3-98ca-8fbcfcf4e357"
                
            }
        }

        public void deleteAllAppliances()
        {
            using (var db = new SQLite.SQLiteConnection(app.DBPath))
            {
                db.DropTable<Appliance>();
                    var data = db.Table<Appliance>();
                    if (db.Delete(data) > 0)
                    {

                    }
                    else { 
                        
                    }   
            
            }
        }

 public void dropAppliancesTable()
 {
     using (var db = new SQLite.SQLiteConnection(app.DBPath))
     {
         db.DropTable<Appliance>();
     }
 }