C# SQLiteConnection.Table方法代码示例

        private void loadButton_Click(object sender, EventArgs e)
        {
            SQLiteConnection db = new SQLiteConnection(new SQLite.Net.Platform.Win32.SQLitePlatformWin32(), @"d:\dnd\dndspells.sqlite");
            spells = db.Table<Spell>().ToList<Spell>();

            populateList();
        }

        public void ByteArrays()
        {
            //Byte Arrays for comparisson
            ByteArrayClass[] byteArrays = new ByteArrayClass[] {
                new ByteArrayClass() { bytes = new byte[] { 1, 2, 3, 4, 250, 252, 253, 254, 255 } }, //Range check
                new ByteArrayClass() { bytes = new byte[] { 0 } }, //null bytes need to be handled correctly
                new ByteArrayClass() { bytes = new byte[] { 0, 0 } },
                new ByteArrayClass() { bytes = new byte[] { 0, 1, 0 } },
                new ByteArrayClass() { bytes = new byte[] { 1, 0, 1 } },
                new ByteArrayClass() { bytes = new byte[] { } }, //Empty byte array should stay empty (and not become null)
                new ByteArrayClass() { bytes = null } //Null should be supported
            };

            SQLiteConnection database = new SQLiteConnection(TestPath.GetTempFileName());
            database.CreateTable<ByteArrayClass>();

            //Insert all of the ByteArrayClass
            foreach (ByteArrayClass b in byteArrays)
                database.Insert(b);

            //Get them back out
            ByteArrayClass[] fetchedByteArrays = database.Table<ByteArrayClass>().OrderBy(x => x.ID).ToArray();

            Assert.AreEqual(fetchedByteArrays.Length, byteArrays.Length);
            //Check they are the same
            for (int i = 0; i < byteArrays.Length; i++)
            {
                byteArrays[i].AssertEquals(fetchedByteArrays[i]);
            }
        }

        public static List<ModelDB> LoadData(SaveInfo save = null)
        {
            if (save != null) {
                SetCurrentSave(save);
            }
            var _conn = new SQLiteConnection(Conf.savePath + currentSave.Filename, SQLiteOpenFlags.ReadWrite);
            var loadedData = new List<ModelDB>();
            var subclasses = GameState.GetGeneratedModelTypes();

            foreach(var c in subclasses) {
                var instance = Activator.CreateInstance(c);
                try {
                    _conn.Table(instance).ToList();
                    var query = _conn.GetType().GetMethod("Table", new Type[] { })
                        .MakeGenericMethod(c)
                        .Invoke(_conn, null) as IEnumerable;
                    foreach (var item in query) {
                        loadedData.Add((ModelDB)item);
                    }
                } catch (SQLiteException) {
                    // it's gonna complain about missing tables. ignore them
                }
            }
            return loadedData;
        }

 public List<BackgroundTrackItem> LoadPlaylist()
 {
     using (var c = new SQLiteConnection(platform, DbPath))
     {
         var list = new List<BackgroundTrackItem>(c.Table<BackgroundTrackItem>().ToList());
         return list;
     }
 }

 public ReadOnlyCollection<JobDto> GetJobsByJourneyId(int journeyId)
 {
     using (var databaseConnection = new SQLiteConnection(new SQLitePlatformWP8(), this.DatabaseFilePath))
     {
         var jobs = databaseConnection.Table<Job>().Where(j => j.JourneyId == journeyId).ToList<Job>();
         return new ReadOnlyCollection<JobDto>(Mapper.Map<ObservableCollection<JobDto>>(jobs));
     }
 }

 private void cbRegion_SelectionChanged(object sender, SelectionChangedEventArgs e)
 {
     if (cbRegion.SelectedIndex == -1) return;
     var conn = new SQLiteConnection(new SQLite.Net.Platform.Win32.SQLitePlatformWin32(), "VKCrawler.db");
     cbCity.ItemsSource = conn.Table<City>()
         .Where(v => v.countryId.Equals(((Country)cbCountry.SelectedItem).id))
         .Where(v => v.regionId.Equals(((Region)cbRegion.SelectedItem).id))
         .OrderBy(v => v.title);
     cbCity.IsEnabled = true;
 }

 private void cbCountry_SelectionChanged(object sender, SelectionChangedEventArgs e)
 {
     if (cbCountry.SelectedIndex == -1) return;
     var conn = new SQLiteConnection(new SQLite.Net.Platform.Win32.SQLitePlatformWin32(), "VKCrawler.db");
     cbRegion.ItemsSource = conn.Table<Region>()
         .Where(v => v.countryId.Equals(((Country)cbCountry.SelectedItem).id))
         .Concat(new[] { new Region { id = 0, countryId = ((Country)cbCountry.SelectedItem).id, title = "Города особого значения" } })
         .OrderBy(v => v.title);
     cbRegion.IsEnabled = true;
 }

 public static ObservableCollection<RemindListDB> ToModel()
 {
     var conn = new SQLiteConnection(new SQLitePlatformWinRT(), App.RemindListDBPath);
     var list = conn.Table<RemindListDB>();
     ObservableCollection<RemindListDB> modelList = new ObservableCollection<RemindListDB>();
     foreach (var item in list)
     {
         modelList.Add(item);
     }
     return modelList;
 }

        /// <summary>
        /// Retrive all the Students in the Database
        /// </summary>
        /// <returns></returns>
        #region Students
        public static List<Student> GetAllStudents()
        {
            List<Student> list;

            // Create a new connection
            using (var db = new SQLiteConnection(new SQLitePlatformWinRT(), DbPath))
            {
                list = (from p in db.Table<Student>()
                        select p).ToList();
            }

            return list;
        }

        public JobDto UpdateJobStatus(int jobId, string status)
        {
            using (var databaseConnection = new SQLiteConnection(new SQLitePlatformWP8(), this.DatabaseFilePath))
            {
                var job = databaseConnection.Table<Job>().FirstOrDefault(j => j.Id == jobId);

                if (job == null)
                {
                    throw new Exception("Unable to find Job in database!");
                }

                job.JobStatus = status;
                databaseConnection.Update(job);

                return Mapper.Map<JobDto>(job);
            }
        }

 /// <summary>
 /// Checks what card images shoud be shown and places them
 /// </summary>
 public void CheckCardImages()
 {
     try
     {
         int card = 0;
         string name;
         using (SQLiteConnection db = new SQLiteConnection(new SQLitePlatformWinRT(), App.path))
         {
             //Number of packs
             int size = MainPage.numberOfPacks;
             //Retrieves all savedcards
             IEnumerable<SavedCards> query = db.Table<SavedCards>().OrderBy(x => x.CardNumber);
             //Used to retrieve all cards in a pack
             IEnumerable<int> packquery;
             for (int pack = 1; pack <= size; pack++)
             {
                 packquery = query.Where(x => x.PackNumber == pack).Select(x => x.Image);
                 for (card = 0; card <= packquery.Count() - 1; card++)// Place correct images to the buttons
                 {
                     Button button = new Button { };
                     name = "SavedCardP" + pack + "C" + card;
                     button = (Button)FindName(name);
                     var brush = new ImageBrush();
                     //Select a image that corresponds with card number
                     int image = packquery.ElementAt(card);
                     brush.ImageSource = new BitmapImage(new Uri(@"ms-appx:///" + FolderPath + "/" + Convert.ToString(image) + ImageEnding));
                     button.Background = brush;
                 }
                 for (int emptyCard = packquery.Count(); emptyCard <= 11; emptyCard++) // Place empty images
                 {
                     Button button = new Button { };
                     name = "SavedCardP" + pack + "C" + emptyCard;
                     button = (Button)FindName(name);
                     var brush = new ImageBrush();
                     brush.ImageSource = new BitmapImage(new Uri(@"ms-appx:///Images/Card2.png"));
                     button.Background = brush;
                 }
             }
         }
     }
     catch (Exception error)
     {
         Debug.WriteLine(error.Message);
     }
 }

        public static List<string> ClearRemindItem()
        {
            var conn = new SQLiteConnection(new SQLitePlatformWinRT(), App.RemindListDBPath);
            var list = conn.Table<RemindListDB>();
            List<string> TagList = new List<string>();
            foreach (var item in list)
            {
                var itemList = item.Id_system.Split(',');
                for (int i = 0; i < itemList.Count(); i++)
                {
                    TagList.Add(itemList[i]);
                }
            }
            //var TagList = from x in list where x.Id_system.Equals("") select x.Id_system;

            conn.DropTable<RemindListDB>();
            conn.CreateTable<RemindListDB>();
            return TagList;
        }

        public ObservableCollection<MSPCrew> GetItems()
        {
            _items = new ObservableCollection<MSPCrew>();

            using (var db = new SQLiteConnection(App.SQLITE_PLATFORM,App.DB_PATH))
            {
                var query = db.Table<MSPCrew>().OrderBy(c => c.Name);
                foreach (var msp in query)
                {
                    var _item = new MSPCrew()
                    {
                        Id = msp.Id,
                        Name = msp.Name,
                        Campus = msp.Campus
                    };
                    _items.Add(_item);
                }
            }
            return _items;
        }

 public ObservableCollection<MediaViewModel> GetItems()
 {
     items = new ObservableCollection<MediaViewModel>();
     using (var db = new SQLiteConnection(App.SQLITE_PLATFORM, App.DB_PATH))
     {
         var query = db.Table<Media>().OrderBy(c => c.Id);
         foreach (var _item in query)
         {
             var item = new MediaViewModel()
             {
                 Id = _item.Id,
                 Name = _item.Name,
                 CreationDate = _item.CreationDate,
                 VidOrPic = _item.VidOrPic
             };
             items.Add(item);
         }
     }
     return items;
 }

		public static SQLiteConnection CreateDbConnection()
		{

			if (SQLitePlatform == null || string.IsNullOrWhiteSpace(DatabasePath))
				throw new Exception("Parameters SQLitePlatform and DatabasePath must be set before using this method.");

			_connection = new SQLiteConnection(SQLitePlatform, DatabasePath);

			_connection.CreateTable<Notebook>();
			_connection.CreateTable<Note>();
			if (_connection.Table<Notebook>().ToList().Count == 0)//check if there are 0 notebooks -> add a default one
			{
				_connection.Insert(new Notebook()
				{
					Id = Guid.NewGuid().ToString(),
					Name = "First Notebook",
					CreatedDate = DateTime.Now,
					ModifiedDate = DateTime.Now,
				});
			}
			return _connection;
		}