C# SQLiteConnection.Exec方法代码示例

        public SQLiteImageCache(string dbPath)
        {
            if (sqliteAssembly == null)
            {
                sqliteAssembly = System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(ApplicationPaths.AppConfigPath, "system.data.sqlite.dll"));
                AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(SqliteResolver);
            }

            SQLiteConnectionStringBuilder connectionstr = new SQLiteConnectionStringBuilder();
            connectionstr.PageSize = 4096;
            connectionstr.CacheSize = 4096;
            connectionstr.SyncMode = SynchronizationModes.Normal;
            connectionstr.DataSource = dbPath;
            connection = new SQLiteConnection(connectionstr.ConnectionString);
            connection.Open();

            string[] queries = {"create table if not exists images (guid, width, height, updated, stream_size, data blob)",
                                "create unique index if not exists idx_images on images(guid, width)",
                               };

            foreach (var query in queries) {
                try {

                    connection.Exec(query);
                } catch (Exception e) {
                    Logger.ReportInfo(e.ToString());
                }
            }

            alive = true; // tell writer to keep going
            Async.Queue("ImageCache Writer", DelayedWriter);
        }

        public SQLiteDisplayRepository(string dbPath)
        {
            SQLiteConnectionStringBuilder connectionstr = new SQLiteConnectionStringBuilder();
            connectionstr.PageSize = 4096;
            connectionstr.CacheSize = 1024;
            connectionstr.SyncMode = SynchronizationModes.Normal;
            connectionstr.DataSource = dbPath;
            connectionstr.JournalMode = SQLiteJournalModeEnum.Persist; //maybe better performance...?
            connection = new SQLiteConnection(connectionstr.ConnectionString);
            int retries = 0;
            bool connected = false;
            while (!connected && retries < MAX_RETRIES)
            {
                try
                {
                    connection.Open();
                    connected = true;
                }
                catch (Exception e)
                {
                    Logger.ReportException("Error connecting to database! Will retry " + MAX_RETRIES + " times.", e);
                    retries++;
                    Thread.Sleep(250);
                }
            }

            if (!connected) throw new ApplicationException("CRITICAL ERROR - Unable to connect to database: " + dbPath + ".  Program cannot function.");

            string[] queries = {
                               "create table if not exists display_prefs (guid primary key, view_type, show_labels, vertical_scroll, sort_order, index_by, use_banner, thumb_constraint_width, thumb_constraint_height, use_coverflow, use_backdrop )",
                               "create index if not exists idx_display on display_prefs (guid)"
                               };

            foreach (var query in queries) {
                try {

                    connection.Exec(query);
                } catch (Exception e) {
                    Logger.ReportInfo(e.ToString());
                }
            }

            alive = true; // tell writer to keep going
            Async.Queue("Sqlite Display Writer", DelayedWriter);
        }

 public void FixUpSchema(SQLiteConnection connection)
 {
     //make sure all our columns are in the db
     var cmd = connection.CreateCommand();
     cmd.CommandText = "PRAGMA table_info(items)";
     List<string> dbCols = new List<string>();
     using (var reader = cmd.ExecuteReader())
     {
         while (reader.Read())
         {
             dbCols.Add(reader[1].ToString());
         }
     }
     if (!dbCols.Contains("obj_type")) connection.Exec("Alter table items add column obj_type");
     foreach (var col in this.AtomicColumns)
     {
         if (!dbCols.Contains(col.ColName))
         {
             Logger.ReportInfo("Discovered new attribute: " + col.ColName + " on object type: "+ ObjType+". Adding to schema.");
             connection.Exec("Alter table items add column "+col.ColName);
         }
     }
 }

        private SqliteItemRepository(string dbPath)
        {
            Logger.ReportInfo("==========Using new SQL Repo========");
            dbFileName = dbPath;

            SQLiteConnectionStringBuilder connectionstr = new SQLiteConnectionStringBuilder();
            connectionstr.PageSize = 4096;
            connectionstr.CacheSize = 4096;
            connectionstr.SyncMode = SynchronizationModes.Normal;
            connectionstr.DataSource = dbPath;
            connectionstr.JournalMode = SQLiteJournalModeEnum.Delete;
            connection = new SQLiteConnection(connectionstr.ConnectionString);
            int retries = 0;
            bool connected = false;
            while (!connected && retries < MAX_RETRIES)
            {
                try
                {
                    connection.Open();
                    connected = true;
                }
                catch (Exception e)
                {
                    Logger.ReportException("Error connecting to database! Will retry "+MAX_RETRIES+" times.", e);
                    retries++;
                    Thread.Sleep(250);
                }
            }

            if (!connected) throw new ApplicationException("CRITICAL ERROR - Unable to connect to database: " + dbPath + ".  Program cannot function.");

            displayRepo = new SQLiteDisplayRepository(Path.Combine(ApplicationPaths.AppUserSettingsPath, "display.db"));
            playbackStatus = new FileBasedDictionary<PlaybackStatus>(GetPath("playstate", ApplicationPaths.AppUserSettingsPath));

            //string playStateDBPath = Path.Combine(ApplicationPaths.AppUserSettingsPath, "playstate.db");

            string[] queries = {"create table if not exists provider_data (guid, full_name, data)",
                                "create unique index if not exists idx_provider on provider_data(guid, full_name)",
                                "create table if not exists items (guid primary key)",
                                "create index if not exists idx_items on items(guid)",
                                "create table if not exists children (guid, child)",
                                "create unique index if not exists idx_children on children(guid, child)",
                                "create table if not exists list_items(guid, property, value, sort_order)",
                                "create index if not exists idx_list on list_items(guid, property)",
                                "create unique index if not exists idx_list_constraint on list_items(guid, property, value)",
                                "create table if not exists schema_version (table_name primary key, version)",
                                //"create table if not exists recent_list(top_parent, child, date_added)",
                                //"create index if not exists idx_recent on recent_list(top_parent, child)",
                                //"attach database '"+playStateDBPath+"' as playstate_db",
                                //"create table if not exists playstate_db.play_states (guid primary key, play_count, position_ticks, playlist_position, last_played)",
                                "pragma temp_store = memory",
                               // @"create table display_prefs (guid primary key, view_type, show_labels, vertical_scroll
                               //        sort_order, index_by, use_banner, thumb_constraint_width, thumb_constraint_height, use_coverflow, use_backdrop )"
                                //,   "create table play_states (guid primary key, play_count, position_ticks, playlist_position, last_played)"
                               };

            foreach (var query in queries) {
                try {
                    connection.Exec(query);
                } catch (Exception e) {
                    Logger.ReportInfo(e.ToString());
                }
            }

            alive = true; // tell writer to keep going
            Async.Queue("Sqlite Writer", DelayedWriter);
        }