C# SQLiteConnection.Table方法代码示例

        protected override void OnCreate(Bundle bundle)
        {
            base.OnCreate (bundle);
            dbPath = Intent.GetStringExtra ("DataBasePath");
            SQLiteConnection sqLiteConn = new SQLiteConnection (dbPath);

            // Create your application here
            SetContentView (Resource.Layout.SQLiteSyncStocks);
            InitComponent ();

            myStocks = sqLiteConn.Table<Stock> ().ToList ();
            lstStock.Adapter = new StocksAdapters (this, myStocks);

            btnSearch.Click += (object sender, EventArgs e) => {
                if (txtStock.Text != "")
                {
                    myStocks = sqLiteConn.Table<Stock>().Where(x => x.Symbol.Contains(txtStock.Text)).ToList();
                    lstStock.Adapter = new StocksAdapters(this, myStocks);
                }
                else {
                    lstStock.Adapter = new StocksAdapters(this, sqLiteConn.Table<Stock>().ToList());
                }
            };

            btnAddStock.Click += (object sender, EventArgs e) => {
                //StartActivity(typeof(SQLiteSyncNewActivity));
                var next = new Intent(this, typeof(SQLiteSyncNewActivity));
                next.PutExtra("DataBasePath", dbPath);
                StartActivity(next);
            };
        }

		/// <returns>
		/// Output of test query
		/// </returns>
		public static string DoSomeDataAccess ()
		{
			var output = "";
			output += "\nCreating database, if it doesn't already exist";
			string dbPath = Path.Combine (
				Environment.GetFolderPath (Environment.SpecialFolder.Personal), "ormdemo.db3");

			var db = new SQLiteConnection (dbPath);
			db.CreateTable<Stock> ();

			if (db.Table<Stock> ().Count() == 0) {
				// only insert the data if it doesn't already exist
				var newStock = new Stock ();
				newStock.Symbol = "AAPL";
				db.Insert (newStock); 

				newStock = new Stock ();
				newStock.Symbol = "GOOG";
				db.Insert (newStock); 

				newStock = new Stock ();
				newStock.Symbol = "MSFT";
				db.Insert (newStock);
			}

			output += "\nReading data using Orm";
			var table = db.Table<Stock> ();
			foreach (var s in table) {
				output += "\n" + s.Id + " " + s.Symbol;
			}

			return output;
		}

        public async Task<IEnumerable<BucketViewModel>> GetAll()
        {
            return await Task.Factory.StartNew(() =>
            {
                using (var db = new SQLiteConnection(configuration.FilePath))
                {
                    var buckets = db.Table<Bucket>()
                                    .Select(BucketViewModel.Map)
                                    .ToList();
                    foreach (var b in buckets)
                    {
                        var tasks = db.Table<UserTask>()
                                      .Where(t => t.BucketId == b.Id)
                                      .Select(UserTaskViewModel.Map)
                                      .ToList();
                        foreach (var t in tasks)
                        {
                            b.Tasks.Add(t);
                        }
                    }

                    return buckets;
                }
            });

        }

		//constructor
		private BookKeeperManager ()
		{
			string dbPath = System.Environment.GetFolderPath (System.Environment.SpecialFolder.Personal);
			db = new SQLiteConnection ( dbPath + @"\database.db");

			try{
				db.Table<Entry>().Count();
			} catch(SQLiteException e){
				db.CreateTable<Entry> ();
			}

			try{
				db.Table<TaxRate>().Count();
			} catch(SQLiteException e){
				db.CreateTable<TaxRate> ();
				db.Insert (new TaxRate(){ Tax = 6.0});
				db.Insert (new TaxRate(){ Tax = 12.0});
				db.Insert (new TaxRate(){ Tax = 25.0});
			}

			try{
				db.Table<Account>().Count();
			} catch(SQLiteException e){
				db.CreateTable<Account> ();
				db.Insert (new Account(){ Name = "Försäljning inom Sverige", Number = 3000});
				db.Insert (new Account(){ Name = "Fakturerade frakter", Number = 3520});
				db.Insert (new Account(){ Name = "Försäljning av övrigt material", Number = 3619});
				db.Insert (new Account(){ Name = "Lokalhyra", Number = 5010});
				db.Insert (new Account(){ Name = "Programvaror", Number = 5420});
				db.Insert (new Account(){ Name = "Energikostnader", Number = 5300});
				db.Insert (new Account(){ Name = "Kassa", Number = 1910});
				db.Insert (new Account(){ Name = "PlusGiro", Number = 1920});
				db.Insert (new Account(){ Name = "Bankcertifikat", Number = 1950});
			}
		}

 public static SyncParams LoadSavedSyncParams()
 {
     SyncParams param = null;
     SQLiteConnection db = new SQLiteConnection(SyncController.DatabasePath);
     db.CreateTable<SyncParams>();
     if (db.Table<SyncParams>().Count() != 0)
         param = db.Table<SyncParams>().First();
     else
         param = new SyncParams();
     db.Close();
     return param;
 }

        public KspMods(string a_InstallationDirectory, string a_ModPath )
        {
            InstallationDirectory = a_InstallationDirectory;
            ModDirectory = a_ModPath;

            db = new SQLiteConnection("kmm.sqlite");

            // make sure the table exists
            if (db.GetTableInfo("KMMInfo").Count == 0)
            {
                db.CreateTable<KMMInfo>();
            }

            var tables = new Type[] { typeof(InstalledMods), typeof(ModFiles), typeof(InstalledFiles) };

            foreach (var table in tables)
            {
                var info = db.GetTableInfo(table.Name);
                if (info.Count == 0)
                    db.CreateTable(table);
            }

            // oh noez it does not match
            if (db.Table<KMMInfo>().Count() == 0 || db.Table<KMMInfo>().First().Version != GetAssemblyVersion())
            {
                // salvage data
                var installed_mods = db.Table<InstalledMods>().ToList();
                db.DropTable<InstalledMods>();
                db.CreateTable<InstalledMods>();
                db.InsertAll(installed_mods);

                var mod_files = db.Table<ModFiles>().ToList();
                db.DropTable<ModFiles>();
                db.CreateTable<ModFiles>();
                db.InsertAll(mod_files);

                var installed_files = db.Table<InstalledFiles>().ToList();
                db.DropTable<InstalledFiles>();
                db.CreateTable<InstalledFiles>();
                db.InsertAll(installed_files);
            }

            // make sure the table is filled
            if (db.Table<KMMInfo>().Count() == 0)
            {
                var nfo = new KMMInfo()
                {
                    Version = GetAssemblyVersion()
                };
                db.Insert(nfo);
            }
        }

        public static string GenerateRandomEmployeeDocuments(ElasticClient client, string namesDbPath)
        {
            var db = new SQLiteConnection(namesDbPath);
            var female = db.Table<female>().ToList();
            var surname = db.Table<surname>().ToList();

            var rng = new Random();

            const int numEmployeesToCreate = 300;
            var numSkillsGenerated = 0;
            var numCertsGenerated = 0;
            for (var i = 0; i <= numEmployeesToCreate; i++)
            {
                var emp = new EmployeeSkillsDocument()
                {
                    Id = i,
                    FirstName = female[rng.Next(0, female.Count - 1)].name,
                    LastName = surname[rng.Next(0, surname.Count - 1)].name,
                    Skills = new List<Skill>(),
                    Certifications = new List<Certification>()
                };

                for (var j = 0; j <= 10; j++)
                {
                    var candidate = SampleDatasets.SkillsCollection[rng.Next(0, SampleDatasets.SkillsCollection.Count - 1)];
                    if (!emp.Skills.Contains(candidate))
                    {
                        emp.Skills.Add(candidate);
                        numSkillsGenerated++;
                    }
                }

                var numCerts = rng.Next(1, 10);
                for (var k = 0; k <= numCerts; k++)
                {
                    var candidate = SampleDatasets.CertificationCollection[rng.Next(0, SampleDatasets.CertificationCollection.Count - 1)];
                    if (!emp.Certifications.Contains(candidate))
                    {
                        emp.Certifications.Add(candidate);
                        numCertsGenerated++;    
                    }
                }

                client.Index(emp);
            }

            return string.Format("Created {0} employee records with {1} skills and {2} certs in index '{3}'",
                numEmployeesToCreate, numSkillsGenerated, numCertsGenerated, EsIndexName);
        }

        private SQLiteConnection _sqliteConnection; // https://github.com/praeclarum/sqlite-net

        #endregion Fields

        #region Constructors

        public ShoppingService(SQLiteConnection sqliteConnection)
        {
            _sqliteConnection = sqliteConnection;
            _sqliteConnection.CreateTable<Item>();
            _sqliteConnection.CreateTable<BoughtItem>();

            var items = _sqliteConnection.Table<Item>().ToList();
            var boughtItems = _sqliteConnection.Table<BoughtItem>().ToList();

            Items = new ObservableCollection<Item>(items);
            BoughtItems = new ObservableCollection<BoughtItem>(boughtItems);

            Items.CollectionChanged += Items_CollectionChanged;
            BoughtItems.CollectionChanged += BoughtItems_CollectionChanged;
        }

 public List<Account> GetAccounts()
 {
     using (var connection = new SQLiteConnection(_dbPath))
     {
         return connection.Table<Account>().ToList();
     }
 }

		// This method checks to see if the database exists, and if it doesn't, it creates
		// it and inserts some data
		protected void CheckAndCreateDatabase (string dbName)
		{
			// create a connection object. if the database doesn't exist, it will create 
			// a blank database
			using(SQLiteConnection db = new SQLiteConnection (GetDBPath (dbName)))
			{				
				// create the tables
				db.CreateTable<Person> ();
				
				// skip inserting data if it already exists
				if(db.Table<Person>().Count() > 0)
					return;
					
				// declare vars
				List<Person> people = new List<Person> ();
				Person person;
				
				// create a list of people that we're going to insert
				person = new Person () { FirstName = "Peter", LastName = "Gabriel" };
				people.Add (person);
				person = new Person () { FirstName = "Thom", LastName = "Yorke" };
				people.Add (person);
				person = new Person () { FirstName = "J", LastName = "Spaceman" };
				people.Add (person);
				person = new Person () { FirstName = "Benjamin", LastName = "Gibbard" };
				people.Add (person);
				
				// insert our people
				db.InsertAll (people);
				
				// close the connection
				db.Close ();
			}

		}

        public SessionTimeDataSource(SQLiteConnection db)
        {
            items = new List<string>(from i in db.Table<TimeSlot>()
                    orderby i.Time select i.Time.ToString("h:mm"));

            section1CellId = "item_id";
        }

        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 MainPage()
        {
            this.InitializeComponent();

            speech = new SpeechSynthesizer(CLIENT_ID, CLIENT_SECRET);
            speech.AudioFormat = SpeakStreamFormat.MP3;
            speech.AudioQuality = SpeakStreamQuality.MaxQuality;
            speech.AutoDetectLanguage = false;
            speech.AutomaticTranslation = false;

            /* Database transactions */
            var db = new SQLiteConnection(Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "mydb.sqlite"));
            db.CreateTable<Button>();

            /* Get and insert sample buttons from system colors */
            var _Colors = typeof(Colors)
                .GetRuntimeProperties()
                .Select((x, i) => new
                {
                    Color = (Color)x.GetValue(null),
                    Name = x.Name,
                    Index = i,
                    ColSpan = 1,
                    RowSpan = 1
                });

            foreach (var c in _Colors)
            {
                db.Insert(new Button { Text = c.Name, ColSpan = c.ColSpan, RowSpan = c.RowSpan, Order = c.Index, ColorHex = c.Color.ToString() });
            }
            
            /* Set data context to Button table */
            this.DataContext = db.Table<Button>().ToList();
        }

		public List<Task> GetAll()
		{
			using (var database = new SQLiteConnection(_helper.WritableDatabase.Path))
			{
				return database.Table<Task>().ToList();
			}
		}

		protected void Initialize ()
		{
			this.Title = "SQLite .NET";
			
			// performance timing
			System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch ();
			stopwatch.Start ();
			
			string dbName = "db_sqlite-net.db3";
			
			// check the database, if it doesn't exist, create it
			CheckAndCreateDatabase (dbName);
			
			// performance timing
			Console.WriteLine ("database creation: " + stopwatch.ElapsedMilliseconds.ToString ());
			
			// create a connection to the database
			using (SQLiteConnection db = new SQLiteConnection (GetDBPath (dbName))) {
				// query a list of people from the db
				people = new List<Person> (from p in db.Table<Person> () select p);
				
				// performance timing
				Console.WriteLine ("database query: " + stopwatch.ElapsedMilliseconds.ToString ());
			
				// create a new table source from our people collection
				tableSource = new BasicOperations.TableSource (people);
				
				// initialize the table view and set the source
				TableView = new UITableView () {
					Source = tableSource
				};
			}
		}