C# SQLiteConnection类代码示例

 public void Clear()
 {
     using (var c = new SQLiteConnection(platform, DbPath))
     {
         c.Execute("DELETE FROM BackgroundTrackItem");
     }
 }

 private static SQLiteConnection GetDatabse()
 {
     var connection = new SQLiteConnection(new SQLitePlatformWinRT(), DbPath);
        // 创建 Person 模型对应的表,如果已存在,则忽略该操作。
        connection.CreateTable<PostDetail>();
        return connection;
 }

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

 public LibraryService(
     SQLiteConnection sqLiteConnection,
     IDispatcherUtility dispatcherUtility)
 {
     _sqLiteConnection = sqLiteConnection;
     _dispatcherUtility = dispatcherUtility;
 }

		public void MultipleResultSets()
		{
			using (SQLiteConnection conn = new SQLiteConnection(m_csb.ConnectionString))
			{
				conn.Open();
				conn.ExecuteNonQuery(@"create table Test(Id integer not null primary key autoincrement, Value text not null);");
				conn.ExecuteNonQuery(@"insert into Test(Id, Value) values(1, 'one'), (2, 'two');");

				using (var cmd = (SQLiteCommand) conn.CreateCommand())
				{
					cmd.CommandText = @"select Value from Test where Id = @First; select Value from Test where Id = @Second;";
					cmd.Parameters.Add("First", DbType.Int64).Value = 1L;
					cmd.Parameters.Add("Second", DbType.Int64).Value = 2L;

					using (var reader = cmd.ExecuteReader())
					{
						Assert.IsTrue(reader.Read());
						Assert.AreEqual("one", reader.GetString(0));
						Assert.IsFalse(reader.Read());

						Assert.IsTrue(reader.NextResult());

						Assert.IsTrue(reader.Read());
						Assert.AreEqual("two", reader.GetString(0));
						Assert.IsFalse(reader.Read());

						Assert.IsFalse(reader.NextResult());
					}
				}
			}
		}

        public void AutoGuid_HasGuid()
        {
            var db = new SQLiteConnection(new SQLitePlatformTest(), 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"
            };

            int numIn1 = db.Insert(obj1);
            int numIn2 = db.Insert(obj2);
            Assert.AreEqual(guid1, obj1.Id);
            Assert.AreEqual(guid2, obj2.Id);

            db.Close();
        }

 public static Options GetOptions()
 {
     using (SQLiteConnection conn = new SQLiteConnection(dbLocation))
       {
     return conn.Get<Options>(1);
       }
 }

 public static int SaveOptions(Options options)
 {
     using (SQLiteConnection conn = new SQLiteConnection(dbLocation))
       {
     return conn.Update(options);
       }
 }

 public static void SetSqlConnection(SQLiteConnection newConnection)
 {
     conn = newConnection;
     var db = Container.Resolve<ICurrencyDatabase>();
     db.Connection = conn;
     db.RegisterTables();
 }

 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 static void Install(SQLiteConnection connection, string schema)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            Log.Info("Start installing Hangfire SQL objects...");

            var script = GetStringResource(
                typeof(SQLiteObjectsInstaller).Assembly,
                "Hangfire.SQLite.Install.sql");

            script = script.Replace("SET @TARGET_SCHEMA_VERSION = 5;", "SET @TARGET_SCHEMA_VERSION = " + RequiredSchemaVersion + ";");

            script = script.Replace("$(HangFireSchema)", !string.IsNullOrWhiteSpace(schema) ? schema : Constants.DefaultSchema);

            for (var i = 0; i < RetryAttempts; i++)
            {
                try
                {
                    connection.Execute(script);
                    break;
                }
                catch (SQLiteException ex)
                {
                    throw;
                }
            }

            Log.Info("Hangfire SQL objects installed.");
        }

        public void Invoke(ServerMessage message, IIrcClient ircClient, ILogger logger)
        {
            if (message.Command.Equals("376") && !_isInitialized) //We use this to initialize the plugin. This should only happen once.
            {
                _ircClient = ircClient;
                _logger = logger;

                //Subscribe to when the stream startes/ends events
                _ircClient.TwitchService.OnStreamOnline += TwitchService_OnStreamOnline;
                _ircClient.TwitchService.OnStreamOffline += TwitchService_OnStreamOffline;

                _timer.AutoReset = true;
                _timer.Interval = 60000;
                _timer.Elapsed += _timer_Elapsed;
                _timer.Enabled = true;

                _dataFolder = PluginHelper.GetPluginDataFolder(this);

                _database = new SQLiteConnection(new SQLitePlatformWin32(), Path.Combine(_dataFolder, "CurrencyPlugin.db"));
                _database.CreateTable<CurrencyUserModel>();

                //This is for debugging
                //_watchedChannelsList.Add("#unseriouspid");
                //_onlineChannelsList.Add("#unseriouspid");

                _logger.Write($"{GetType().Name} Initialized.");
                _isInitialized = true;
            }
            else if (message.Command.Equals("PRIVMSG") &&
                        _isInitialized &&
                        _watchedChannelsList.Contains(message.GetChannelName()))
            {
                var userCommand = message.Trailing.Split(' ')[0].ToLower().TrimStart();
                var nickName = message.GetSender();
                var channelName = message.GetChannelName();

                switch (userCommand)
                {
                    case "!points":
                        if (IsUserCommandOnCooldown(userCommand, nickName, channelName)) return;
                        DisplayPoints(nickName, channelName);
                        CooldownUserCommand(userCommand, nickName, channelName);
                        break;

                    case "!gamble":
                        if (IsUserCommandOnCooldown(userCommand, nickName, channelName)) return;
                        DoGamble(message);
                        CooldownUserCommand(userCommand, nickName, channelName);
                        break;

                    case "!bonus":
                        DoAddBonus(message);
                        break;

                    case "!bonusall":
                        DoAddBonusAll(message);
                        break;
                }
            }
        }

        public string DeleteItem(int itemId)
        {
            string result = string.Empty;
            using (var dbConn = new SQLiteConnection(App.SQLITE_PLATFORM, App.DB_PATH))
            {
                var existingItem = dbConn.Query<Media>("select * from Media where Id =" + itemId).FirstOrDefault();
                if (existingItem != null)
                {
                    dbConn.RunInTransaction(() =>
                    {
                        dbConn.Delete(existingItem);

                        if (dbConn.Delete(existingItem) > 0)
                        {
                            result = "Success";
                        }
                        else
                        {
                            result = "This item was not removed";
                        }

                    });
                }

                return result;
            }
        }

 public SQLiteConnection GetSqlConnection(string fileName)
 {
     var path = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
     var plat=new SQLite.Net.Platform.WindowsPhone8.SQLitePlatformWP8();
     var conn=new SQLiteConnection(plat, path);
     return conn;
 }

        private static void ListAllBooks(string connectionString)
        {
            SQLiteConnection databaseConnection = new SQLiteConnection(connectionString);
            databaseConnection.Open();

            using (databaseConnection)
            {
                string sqlStringCommand = "SELECT * FROM Books";

                SQLiteCommand allBooks = new SQLiteCommand(sqlStringCommand, databaseConnection);
                SQLiteDataReader reader = allBooks.ExecuteReader();
                using (reader)
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(
                            "Title: {0}, \nAuthor: {1}, \nPublishDate: {2}, \nISBN: {3}",
                            reader["Title"],
                            reader["Author"],
                            (DateTime) reader["PublishDate"],
                            reader["ISBN"]);
                        Console.WriteLine();
                    }
                }
            }
        }