C# SQLiteConnection.Insert方法代码示例

        public void SeedDatabase(SQLiteConnection conn)
        {
            string TeamSeed = GetSeedString("teams.txt");
            var teamlist = JArray.Parse(TeamSeed);
            {
                foreach (JObject team in teamlist)
                {
                    var _team = JsonConvert.DeserializeObject<Team>(team.ToString());
                    conn.Insert(_team);
                }
            }
            TeamSeed = null;

            string PlayerSeed = GetSeedString("players.txt");
            var PlayerList = JObject.Parse(PlayerSeed);
            foreach (var jsonplayerobject in PlayerList)
            {
                var jsonplayer = jsonplayerobject.Value;
                try
                {
                    var player = JsonConvert.DeserializeObject<Player>(jsonplayer.ToString());
                    player.PlayerId = jsonplayerobject.Key;
                    if (!string.IsNullOrWhiteSpace(player.BirthdateString))
                    {
                        player.Birthdate = DateTime.Parse(player.BirthdateString);
                    }
                    conn.Insert(player);
                }
                catch (JsonSerializationException ex)
                {

                }
            }
            PlayerSeed = null;




            string StatMapSeed;
            string CategorySeed;
            string CountingStatsSeed;
            string FieldPositionSeed;
            string GameTimeSeed;
            string SeasonPhaseSeed;
            string GamePhaseSeed;
            string GameDaySeed;
            string PlayerStatusSeed;



        }

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

 static OptionsRepository()
 {
     // Figure out where the SQLite database will be.
       var documents = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
       dbLocation = Path.Combine(documents, "timer_sqlite-net.db");
       using (SQLiteConnection conn = new SQLiteConnection(dbLocation))
       {
     conn.CreateTable<Options>();
     Options o = null;
     try
     {
       o = conn.Get<Options>(1);
     }
     catch (InvalidOperationException)
     {
       // Doesn't exist...
     }
     if (o == null)
     {
       // Create options... a bit contrived perhaps :)
       o = new Options();
       o.Seconds = 5;
       int rows = conn.Insert(o);
       if (rows == 0)
       {
     throw new InvalidOperationException("Can't create options!");
       }
     }
       }
 }

        public void ByteArrayWhere()
        {
            //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>();

            byte[] criterion = new byte[] { 1, 0, 1 };

            //Insert all of the ByteArrayClass
            int id = 0;
            foreach (ByteArrayClass b in byteArrays)
            {
                database.Insert(b);
                if (b.bytes != null && criterion.SequenceEqual<byte>(b.bytes))
                    id = b.ID;
            }
            Assert.AreNotEqual(0, id, "An ID wasn't set");

            //Get it back out
            ByteArrayClass fetchedByteArray = database.Table<ByteArrayClass>().Where(x => x.bytes == criterion).First();
            Assert.IsNotNull(fetchedByteArray);
            //Check they are the same
            Assert.AreEqual(id, fetchedByteArray.ID);
        }

        private void Proceed_Click(object sender, RoutedEventArgs e)
        {
            Model.Userdata userData = new Model.Userdata();

            userData.addData(name.Text, (int)AgeList.SelectedItem, 0);

            // Create a Database.

            // Add to database.

            try
            {
                var path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "App.db");

                var db = new SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT() ,path);

                db.CreateTable<Model.Userdata>();
                db.CreateTable<Model.Subject>();

                db.Insert(userData);

                this.Frame.Navigate(typeof(World2));

            }
            catch (Exception)
            {
                throw;
            }
            

            // redirecting to 

        }

        public void AutoGuid_HasGuid()
        {
            var db = new SQLiteConnection(TestPath.GetTempFileName());
            db.CreateTable<TestObj>(CreateFlags.AutoIncPK);

            var guid1 = new Guid("36473164-C9E4-4CDF-B266-A0B287C85623");
            var guid2 = new Guid("BC5C4C4A-CA57-4B61-8B53-9FD4673528B6");

            var obj1 = new TestObj() { Id = guid1, Text = "First Guid Object" };
            var obj2 = new TestObj() { Id = guid2, Text = "Second Guid Object" };

            db.Insert(obj1);
            db.Insert(obj2);

            Assert.AreEqual(guid1, obj1.Id);
            Assert.AreEqual(guid2, obj2.Id);

            db.Close();
        }

        public void AutoGuid_EmptyGuid()
        {
            var db = new SQLiteConnection(TestPath.GetTempFileName());
            db.CreateTable<TestObj>(CreateFlags.AutoIncPK);

            var obj1 = new TestObj() { Text = "First Guid Object" };
            var obj2 = new TestObj() { Text = "Second Guid Object" };

            Assert.AreEqual(Guid.Empty, obj1.Id);
            Assert.AreEqual(Guid.Empty, obj2.Id);

            db.Insert(obj1);
            db.Insert(obj2);

            Assert.AreNotEqual(Guid.Empty, obj1.Id);
            Assert.AreNotEqual(Guid.Empty, obj2.Id);
            Assert.AreNotEqual(obj1.Id, obj2.Id);

            db.Close();
        }

        public JobDto AddJob(Job job)
        {
            using (var databaseConnection = new SQLiteConnection(new SQLitePlatformWP8(), this.DatabaseFilePath))
            {
                databaseConnection.RunInTransaction(() =>
                {
                    databaseConnection.Insert(job);
                });
            }

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

        public void Insert(Student student)
        {
            var sqlPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "StudentDB.sqlite");

            using (SQLiteConnection conn = new SQLiteConnection(new SQLitePlatformWinRT(), sqlPath))
            {
                conn.RunInTransaction(() =>
                {
                    conn.Insert(student);
                });
            }
        }

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

        /// <summary>
        /// Generating sample data.
        /// </summary>
        public async Task<bool> SampleData()
        {
            await DropAndCreateDatabase();

            using (var dbConn = new SQLiteConnection(new SQLitePlatformWinRT(), App.DbPath))
            {
                //create user
                var user = GetUser("sutak.j[email protected]", "Jey");
                dbConn.Insert(user);

                //create myOwnWord
                var mow = GetMyOwnWord(user.UserID, "Man", "Muž, Chlap");
                dbConn.Insert(mow);

                //create second myOwnWord
                var mow2 = GetMyOwnWord(user.UserID, "Hello", "Ahoj");
                dbConn.Insert(mow2);
                
                //create 10 photos belongs to mow
                for (int i = 0; i < 10; i++)
                {
                    dbConn.Insert(GetPhoto(mow.MyOwnWordID));
                }

                //create 5 recordings belongs to mow
                for (int i = 0; i < 5; i++)
                {
                    dbConn.Insert(GetRecording(mow.MyOwnWordID));
                }

                //create sentence belongs to mow
                dbConn.Insert(GetSentence(mow.MyOwnWordID, "This man is amazing!"));
                dbConn.Insert(GetSentence(mow2.MyOwnWordID, "Hello my love :-*"));

                MowListItem item = new MowListItem();
                var listItems = dbConn.Query<MowListItem>("select * from vw_mowlist");
            }

            return true;
        }

		//
		// This method is invoked when the application has loaded and is ready to run. In this 
		// method you should instantiate the window, load the UI into it and then make the window
		// visible.
		//
		// You have 17 seconds to return from this method, or iOS will terminate your application.
		//
		public override bool FinishedLaunching (UIApplication app, NSDictionary options)
		{
			// create a new window instance based on the screen size
			window = new UIWindow (UIScreen.MainScreen.Bounds);
			
			// open up a connection to our SQLite database
			string docsDir = Environment.GetFolderPath (Environment.SpecialFolder.Personal);
			string path = Path.Combine (docsDir, "SampleItems.sqlite");
			var sqlitedb = new SQLiteConnection (path);
			
			// create our SQLite table...
			sqlitedb.CreateTable<SampleItem> ();
			
			// prepopulate our table with some data
			if (sqlitedb.Table<SampleItem> ().Count () == 0) {
				SampleItem item;
				
				for (int i = 0; i < 10000; i++) {
					item = new SampleItem ();
					item.Title = "Row " + i;
					item.Details = "This is item #" + i;
					
					sqlitedb.Insert (item);
				}
			}
			
			// create our TableViewController to display the data from our SQLite database table
			viewController = new SampleTableViewController (sqlitedb);
			
			// set our view controller as the root view
			window.RootViewController = viewController;
			
			// make the window visible
			window.MakeKeyAndVisible ();
			
			return true;
		}

        public static void SavePerson(Person person)
        {
            // Create a new connection
            using (var db = new SQLiteConnection(new SQLitePlatformWinRT(), DbPath))
            {
                // Activate Tracing
                db.TraceListener = new DebugTraceListener();

                if (person.Id == 0)
                {
                    // New
                    db.Insert(person);
                }
                else
                {
                    // Update
                    db.Update(person);
                }
            }
        }

        public void LargeByteArray()
        {
            const int byteArraySize = 1024 * 1024;
            byte[] bytes = new byte[byteArraySize];
            for (int i = 0; i < byteArraySize; i++)
                bytes[i] = (byte)(i % 256);

            ByteArrayClass byteArray = new ByteArrayClass() { bytes = bytes };

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

            //Insert the ByteArrayClass
            database.Insert(byteArray);

            //Get it back out
            ByteArrayClass[] fetchedByteArrays = database.Table<ByteArrayClass>().ToArray();

            Assert.AreEqual(fetchedByteArrays.Length, 1);

            //Check they are the same
            byteArray.AssertEquals(fetchedByteArrays[0]);
        }

		static void CreateTestData (SQLiteConnection db)
		{
			var js = new MeasurementSubjectModel () { Name = "JS"};
			if (db.Table<MeasurementSubjectModel> ().Count (c => c.Name == "JS") == 0) {
				db.Insert (js);
			} else {
				js = db.Table<MeasurementSubjectModel> ().Where (w => w.Name == "JS").FirstOrDefault ();
			}

			var jsProfile = new ProfileModel () { MeasurementSubjectId = js.Id};

			var jp = db.Query<ProfileModel> ("SELECT P.* FROM ProfileModel as P JOIN MeasurementSubjectModel AS s ON p.MeasurementSubjectId == s.Id WHERE s.Name == ?", "JS");
			if(jp.Count == 0) {
				db.Insert (jsProfile);

				var v = new ProfileMeasurementDefinitionModel () {
					MeasurementFrequencyId = 3,
					MeasurementDefinitionId = 1,
					ProfileId = jsProfile.Id,
					MeasureTypeId = 2
				};

				db.Insert (v);

				db.Insert(new ProfileMeasurementDefinitionModel () {
					MeasurementFrequencyId = 4 ,
					MeasurementDefinitionId = 2 ,
					ProfileId = jsProfile.Id,
					MeasureTypeId = 7
				});
				db.Insert (new ProfileMeasurementDefinitionModel () {
					MeasurementFrequencyId = 3 ,
					MeasurementDefinitionId = 3 ,
					ProfileId = jsProfile.Id,
					MeasureTypeId = 2

				});


			} else {
				jsProfile = jp.FirstOrDefault ();
				db.GetChildren (jsProfile, true);
			}

			var stella = new MeasurementSubjectModel () { Name = "Stella" };
			if (db.Table<MeasurementSubjectModel> ().Count (c => c.Name == "Stella") == 0) {
				db.Insert (stella);
			}else {
				stella = db.Table<MeasurementSubjectModel> ().Where (w => w.Name == "Stella").FirstOrDefault ();
			}

			var stellaProfile = new ProfileModel () { MeasurementSubjectId = stella.Id };

			var sp = db.Query<ProfileModel> ("SELECT P.* FROM ProfileModel as P JOIN MeasurementSubjectModel AS s ON p.MeasurementSubjectId == s.Id WHERE s.Name == ?", "Stella");
			if(sp.Count == 0) {
				db.Insert (stellaProfile);

				var v = new ProfileMeasurementDefinitionModel () {
					MeasurementFrequencyId = 3,
					MeasurementDefinitionId = 1,
					ProfileId = stellaProfile.Id,
					MeasureTypeId = 2
				};

				var c = db.Insert (v);

				db.Insert(new ProfileMeasurementDefinitionModel () {
					MeasurementFrequencyId = 4 ,
					MeasurementDefinitionId = 2 ,
					ProfileId = stellaProfile.Id,
					MeasureTypeId = 7
				});
				db.Insert (new ProfileMeasurementDefinitionModel () {
					MeasurementFrequencyId = 3 ,
					MeasurementDefinitionId = 3 ,
					ProfileId = stellaProfile.Id,
					MeasureTypeId = 2

				});

			} else {
				stellaProfile = sp.FirstOrDefault ();
				db.GetChildren (stellaProfile, true);
			}


			var isabelle = new MeasurementSubjectModel () { Name = "Isabelle" };
			if (db.Table<MeasurementSubjectModel> ().Count (c => c.Name == "Isabelle") == 0) {
				db.Insert (isabelle);
			}else {
				isabelle = db.Table<MeasurementSubjectModel> ().Where (w => w.Name == "Isabelle").FirstOrDefault ();
			}

			var jsBloodPressure = new MeasurementInstanceModel () {DateRecorded = DateTime.Now, MeasurementSubjectId = js.Id, MeasurementDefinitionId = 1 };
			var jsBloodPressure2 = new MeasurementInstanceModel () {DateRecorded = DateTime.Now.AddDays(-1), MeasurementSubjectId = js.Id, MeasurementDefinitionId = 1 };
			var jsBloodPressure3 = new MeasurementInstanceModel () {DateRecorded = DateTime.Now.AddDays(-2), MeasurementSubjectId = js.Id, MeasurementDefinitionId = 1 };

			if (db.Table<MeasurementInstanceModel> ().Count (w => w.MeasurementSubjectId == js.Id && w.MeasurementDefinitionId == 1) == 0) {
				db.Insert (jsBloodPressure);
				db.Insert (new MeasurementGroupInstanceModel () { MeasurementInstanceId = jsBloodPressure.Id, MeasurementGroupDefinitionId = 1, UnitId = 1, Value = 120 });
//.........这里部分代码省略.........