System.Data.IDataReader.Read()

private void UpdateRelativePaths(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand getSeriesCmd = conn.CreateCommand())
            {
                getSeriesCmd.Transaction = tran;
                getSeriesCmd.CommandText = @"SELECT Id, Path FROM Series";
                using (IDataReader seriesReader = getSeriesCmd.ExecuteReader())
                {
                    while (seriesReader.Read())
                    {
                        var seriesId = seriesReader.GetInt32(0);
                        var seriesPath = seriesReader.GetString(1) + Path.DirectorySeparatorChar;

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE EpisodeFiles SET RelativePath = REPLACE(Path, ?, '') WHERE SeriesId = ?";
                            updateCmd.AddParameter(seriesPath);
                            updateCmd.AddParameter(seriesId);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

private void UpdateTransmissionSettings(IDbConnection conn, IDbTransaction tran)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandText = "SELECT Id, Settings FROM Notifications WHERE Implementation = 'PushBullet'";

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var id = reader.GetInt32(0);
                        var settingsJson = reader.GetString(1);
                        var settings = Json.Deserialize<Dictionary<string, object>>(settingsJson);

                        if (settings.ContainsKey("deviceId"))
                        {
                            var deviceId = settings.GetValueOrDefault("deviceId", "") as string;

                            settings.Add("deviceIds", new[] { deviceId });
                            settings.Remove("deviceId");

                            using (var updateCmd = conn.CreateCommand())
                            {
                                updateCmd.Transaction = tran;
                                updateCmd.CommandText = "UPDATE Notifications SET Settings = ? WHERE Id = ?";
                                updateCmd.AddParameter(settings.ToJson());
                                updateCmd.AddParameter(id);

                                updateCmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
        }

private void ConvertQualityreplacedle(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand namingConfigCmd = conn.CreateCommand())
            {
                namingConfigCmd.Transaction = tran;
                namingConfigCmd.CommandText = @"SELECT StandardEpisodeFormat, DailyEpisodeFormat, AnimeEpisodeFormat FROM NamingConfig LIMIT 1";

                using (IDataReader configReader = namingConfigCmd.ExecuteReader())
                {
                    while (configReader.Read())
                    {
                        var currentStandard = configReader.GetString(0);
                        var currentDaily = configReader.GetString(1);
                        var currentAnime = configReader.GetString(2);

                        var newStandard = GetNewFormat(currentStandard);
                        var newDaily = GetNewFormat(currentDaily);
                        var newAnime = GetNewFormat(currentAnime);

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;

                            updateCmd.CommandText = "UPDATE NamingConfig SET StandardEpisodeFormat = ?, DailyEpisodeFormat = ?, AnimeEpisodeFormat = ?";
                            updateCmd.AddParameter(newStandard);
                            updateCmd.AddParameter(newDaily);
                            updateCmd.AddParameter(newAnime);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

private void SetMetadataFileExtension(IDbConnection conn, IDbTransaction tran)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandText = "SELECT Id, RelativePath FROM MetadataFiles";

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var id = reader.GetInt32(0);
                        var relativePath = reader.GetString(1);
                        var extension = relativePath.Substring(relativePath.LastIndexOf(".", StringComparison.InvariantCultureIgnoreCase));

                        using (var updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE MetadataFiles SET Extension = ? WHERE Id = ?";
                            updateCmd.AddParameter(extension);
                            updateCmd.AddParameter(id);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

private void SetSortreplacedles(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand getSeriesCmd = conn.CreateCommand())
            {
                getSeriesCmd.Transaction = tran;
                getSeriesCmd.CommandText = @"SELECT Id, RelativePath FROM MovieFiles";
                using (IDataReader seriesReader = getSeriesCmd.ExecuteReader())
                {
                    while (seriesReader.Read())
                    {
                        var id = seriesReader.GetInt32(0);
                        var relativePath = seriesReader.GetString(1);
                        
                        var result = Parser.Parser.ParseMoviereplacedle(relativePath, false);
                        
                        var edition = "";
                        
                        if (result != null)
                        {
                            edition = Parser.Parser.ParseMoviereplacedle(relativePath, false).Edition;
                        }

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE MovieFiles SET Edition = ? WHERE Id = ?";
                            updateCmd.AddParameter(edition);
                            updateCmd.AddParameter(id);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

private void ConvertQualities(IDbConnection conn, IDbTransaction tran)
        {
            // Convert QualitySizes to a more generic QualityDefinitions table.
            using (IDbCommand qualitySizeCmd = conn.CreateCommand())
            {
                qualitySizeCmd.Transaction = tran;
                qualitySizeCmd.CommandText = @"SELECT QualityId, MinSize, MaxSize FROM QualitySizes";
                using (IDataReader qualitySizeReader = qualitySizeCmd.ExecuteReader())
                {
                    while (qualitySizeReader.Read())
                    {
                        var qualityId = qualitySizeReader.GetInt32(0);
                        var minSize = qualitySizeReader.GetInt32(1);
                        var maxSize = qualitySizeReader.GetInt32(2);

                        var defaultConfig = Quality.DefaultQualityDefinitions.Single(p => (int)p.Quality == qualityId);

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "INSERT INTO QualityDefinitions (Quality, replacedle, Weight, MinSize, MaxSize) VALUES (?, ?, ?, ?, ?)";
                            updateCmd.AddParameter(qualityId);
                            updateCmd.AddParameter(defaultConfig.replacedle);
                            updateCmd.AddParameter(defaultConfig.Weight);
                            updateCmd.AddParameter(minSize);
                            updateCmd.AddParameter(maxSize);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

private void UpdateTransmissionSettings(IDbConnection conn, IDbTransaction tran)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandText = "SELECT Id, Settings FROM DownloadClients WHERE Implementation = 'Transmission'";

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var id = reader.GetInt32(0);
                        var settingsJson = reader.GetString(1);

                        var settings = Json.Deserialize<Dictionary<string, object>>(settingsJson);

                        var urlBase = settings.GetValueOrDefault("urlBase", "") as string;
                        
                        if (urlBase.IsNullOrWhiteSpace())
                        {
                            settings["urlBase"] = "/transmission/";
                        }
                        else
                        {
                            settings["urlBase"] = string.Format("/{0}/transmission/", urlBase.Trim('/'));
                        }

                        using (var updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE DownloadClients SET Settings = ? WHERE Id = ?";
                            updateCmd.AddParameter(settings.ToJson());
                            updateCmd.AddParameter(id);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

public System.Collections.Generic.Dictionary<int, ActorData> LoadActors(){
        System.Collections.Generic.Dictionary<int, ActorData> actors;
        actors = new System.Collections.Generic.Dictionary<int, ActorData>();
        
        string sql = @"
            SELECT * from actors;
        ";

        cmd.CommandText = sql;
        IDataReader rdr = cmd.ExecuteReader();
        while(rdr.Read()){
            string json = (string)rdr["data"];
            ActorData dat = ActorData.FromJson(json);
            actors.Add(dat.id, dat);
        }
        rdr.Close();

        return actors;
    }

public void WriteToServer(IDataReader reader)
        {
            if (reader.Read())
            {
                // initialize bulk copy

                using (var trans = mConnection.BeginTransaction())
                {
                    using (var command = mConnection.CreateCommand())
                    {
                        var fieldNames = "";
                        var paramNames = "";
                        SqliteParameter[] parms = new SqliteParameter[reader.FieldCount];
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            string xFieldName = reader.GetName(i);
                            fieldNames += $"{xFieldName},";
                            paramNames += $"@_{xFieldName},";
                            parms[i] = new SqliteParameter($"@_{xFieldName}", SqliteType.Text);
                            command.Parameters.Add(parms[i]);
                        }
                        fieldNames = fieldNames.TrimEnd(',');
                        paramNames = paramNames.TrimEnd(',');

                        command.Transaction = trans;
                        command.CommandText = $"insert into [{DestinationTableName}] ({fieldNames}) values ({paramNames})";
                        command.Prepare();
                        do
                        {
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                if (parms[i] != null)
                                {
                                    parms[i].Value = reader.GetValue(i);
                                }
                            }
                            command.ExecuteNonQuery();
                        }
                        while (reader.Read());
                    }
                    trans.Commit();
                }
            }
        }

public string SelectSetting(string name){
        string sql = @"
            SELECT value FROM settings
            WHERE name = @name
        ";
        cmd.CommandText = sql;
        cmd.Parameters.Add(new SqliteParameter ("@name", name));
        IDataReader rdr = cmd.ExecuteReader();
        string ret = "";
        if(rdr.Read()){
            ret = rdr["value"] as string;
        }
        rdr.Close();
        return ret;
    }

private void ConvertFolderSettings(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand downloadClientsCmd = conn.CreateCommand())
            {
                downloadClientsCmd.Transaction = tran;
                downloadClientsCmd.CommandText = @"SELECT Value FROM Config WHERE Key = 'downloadedepisodesfolder'";
                var downloadedEpisodesFolder = downloadClientsCmd.ExecuteScalar() as string;

                downloadClientsCmd.Transaction = tran;
                downloadClientsCmd.CommandText = @"SELECT Id, Implementation, Settings, ConfigContract FROM DownloadClients WHERE ConfigContract = 'FolderSettings'";
                using (IDataReader downloadClientReader = downloadClientsCmd.ExecuteReader())
                {
                    while (downloadClientReader.Read())
                    {
                        var id = downloadClientReader.GetInt32(0);
                        var implementation = downloadClientReader.GetString(1);
                        var settings = downloadClientReader.GetString(2);
                        var configContract = downloadClientReader.GetString(3);

                        var settingsJson = JsonConvert.DeserializeObject(settings) as Newtonsoft.Json.Linq.JObject;

                        if (implementation == "Blackhole")
                        {
                            var newSettings = new
                            {
                                NzbFolder = settingsJson.Value<string>("folder"),
                                WatchFolder = downloadedEpisodesFolder
                            }.ToJson();

                            using (IDbCommand updateCmd = conn.CreateCommand())
                            {
                                updateCmd.Transaction = tran;
                                updateCmd.CommandText = "UPDATE DownloadClients SET Implementation = ?, Settings = ?, ConfigContract = ? WHERE Id = ?";
                                updateCmd.AddParameter("UsenetBlackhole");
                                updateCmd.AddParameter(newSettings);
                                updateCmd.AddParameter("UsenetBlackholeSettings");
                                updateCmd.AddParameter(id);

                                updateCmd.ExecuteNonQuery();
                            }
                        }
                        else if (implementation == "Pneumatic")
                        {
                            var newSettings = new
                            {
                                NzbFolder = settingsJson.Value<string>("folder")
                            }.ToJson();

                            using (IDbCommand updateCmd = conn.CreateCommand())
                            {
                                updateCmd.Transaction = tran;
                                updateCmd.CommandText = "UPDATE DownloadClients SET Settings = ?, ConfigContract = ? WHERE Id = ?";
                                updateCmd.AddParameter(newSettings);
                                updateCmd.AddParameter("PneumaticSettings");
                                updateCmd.AddParameter(id);

                                updateCmd.ExecuteNonQuery();
                            }
                        }
                        else
                        {
                            using (IDbCommand updateCmd = conn.CreateCommand())
                            {
                                updateCmd.Transaction = tran;
                                updateCmd.CommandText = "DELETE FROM DownloadClients WHERE Id = ?";
                                updateCmd.AddParameter(id);

                                updateCmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
        }

private void AddExisting(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand getSeriesCmd = conn.CreateCommand())
            {
                getSeriesCmd.Transaction = tran;
                getSeriesCmd.CommandText = @"SELECT Key, Value FROM Config WHERE Key = 'importexclusions'";
                TextInfo textInfo = new CultureInfo("en-US", false).TextInfo;
                using (IDataReader seriesReader = getSeriesCmd.ExecuteReader())
                {
                    while (seriesReader.Read())
                    {
                        var Key = seriesReader.GetString(0);
                        var Value = seriesReader.GetString(1);

                        var importExclusions = Value.Split(',').Select(x => {
                            return string.Format("(\"{0}\", \"{1}\")", Regex.Replace(x, @"^.*\-(.*)$", "$1"),
                                                 textInfo.ToreplacedleCase(string.Join(" ", x.Split('-').DropLast(1))));
                        }).ToList();

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "INSERT INTO ImportExclusions (tmdbid, Moviereplacedle) VALUES " + string.Join(", ", importExclusions);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

private void ConvertConfig(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand namingConfigCmd = conn.CreateCommand())
            {
                namingConfigCmd.Transaction = tran;
                namingConfigCmd.CommandText = @"SELECT [Value] FROM Config WHERE [Key] = 'seasonfolderformat'";
                var seasonFormat = "Season {season}";

                using (IDataReader namingConfigReader = namingConfigCmd.ExecuteReader())
                {
                    while (namingConfigReader.Read())
                    {
                        //only getting one column, so its index is 0
                        seasonFormat = namingConfigReader.GetString(0);

                        seasonFormat = seasonFormat.Replace("%sn", "{Series replacedle}")
                                                   .Replace("%s.n", "{Series.replacedle}")
                                                   .Replace("%s", "{season}")
                                                   .Replace("%0s", "{season:00}")
                                                   .Replace("%e", "{episode}")
                                                   .Replace("%0e", "{episode:00}");
                    }
                }

                using (IDbCommand updateCmd = conn.CreateCommand())
                {
                    var text = string.Format("UPDATE NamingConfig " +
                                             "SET SeasonFolderFormat = '{0}'",
                                             seasonFormat);

                    updateCmd.Transaction = tran;
                    updateCmd.CommandText = text;
                    updateCmd.ExecuteNonQuery();
                }
            }
        }

private void ConvertQualityProfiles(IDbConnection conn, IDbTransaction tran)
        {
            var qualityProfileItemConverter = new EmbeddedDoreplacedentConverter(new QualityIntConverter());

            // Convert 'Allowed' column in QualityProfiles from Json List<object> to Json List<int> (int = Quality)
            using (IDbCommand qualityProfileCmd = conn.CreateCommand())
            {
                qualityProfileCmd.Transaction = tran;
                qualityProfileCmd.CommandText = @"SELECT Id, Allowed FROM QualityProfiles";
                using (IDataReader qualityProfileReader = qualityProfileCmd.ExecuteReader())
                {
                    while (qualityProfileReader.Read())
                    {
                        var id = qualityProfileReader.GetInt32(0);
                        var allowedJson = qualityProfileReader.GetString(1);

                        var allowed = Json.Deserialize<List<Quality>>(allowedJson);

                        var items = Quality.DefaultQualityDefinitions.OrderBy(v => v.Weight).Select(v => new ProfileQualityItem { Quality = v.Quality, Allowed = allowed.Contains(v.Quality) }).ToList();

                        var allowedNewJson = qualityProfileItemConverter.ToDB(items);

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE QualityProfiles SET Items = ? WHERE Id = ?";
                            updateCmd.AddParameter(allowedNewJson);
                            updateCmd.AddParameter(id);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

public void List(DbContext db, Region region, Action<IDataReader> handler)
        {
            if (region == null)
            {
                region = new Region(0, 100);
            }
            int index = 0;
            Command cmd = GetCommand();

            var conn = db.Database.GetDbConnection();
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            var dbcmd = cmd.CreateCommand(conn);
#if NETCOREAPP2_1
            using (CodeTrackFactory.Track(dbcmd.CommandText, CodeTrackLevel.Function, null, "EFCore", "ExecuteToObject"))
            {
#endif
                dbcmd.Transaction = db.Database.CurrentTransaction?.GetDbTransaction();
                SQLExecuting?.Invoke(dbcmd);
                int count = 0;
                using (IDataReader reader = dbcmd.ExecuteReader())
                {

                    while (reader.Read())
                    {
                        if (index >= region.Start)
                        {
                            handler?.Invoke(reader);
                            count++;
                            if (count >= region.Size)
                            {
                                cmd.DbCommand.Cancel();
                                reader.Dispose();
                                break;
                            }
                        }
                        index++;
                    }

                }
#if NETCOREAPP2_1
            }
#endif
        }

private void UpdateTaggedModel(IDbConnection conn, IDbTransaction tran, string table, List<TagReplacement079> replacements)
        {
            var tagged = new List<TaggedModel079>();

            using (IDbCommand tagCmd = conn.CreateCommand())
            {
                tagCmd.Transaction = tran;
                tagCmd.CommandText = string.Format("SELECT Id, Tags FROM {0}", table);

                using (IDataReader tagReader = tagCmd.ExecuteReader())
                {
                    while (tagReader.Read())
                    {
                        if (!tagReader.IsDBNull(1))
                        {
                            var id = tagReader.GetInt32(0);
                            var tags = tagReader.GetString(1);

                            tagged.Add(new TaggedModel079
                                       {
                                           Id = id,
                                           Tags = Json.Deserialize<HashSet<int>>(tags)
                                       });
                        }
                    }
                }
            }

            var toUpdate = new List<TaggedModel079>();

            foreach (var model in tagged)
            {
                foreach (var replacement in replacements)
                {
                    if (model.Tags.Contains(replacement.OldId))
                    {
                        model.Tags.Remove(replacement.OldId);
                        model.Tags.Add(replacement.NewId);

                        toUpdate.Add(model);
                    }
                }
            }

            foreach (var model in toUpdate.DistinctBy(m => m.Id))
            {
                using (IDbCommand updateCmd = conn.CreateCommand())
                {
                    updateCmd.Transaction = tran;
                    updateCmd.CommandText = string.Format(@"UPDATE {0} SET Tags = ? WHERE Id = ?", table);
                    updateCmd.AddParameter(model.Tags.ToJson());
                    updateCmd.AddParameter(model.Id);

                    updateCmd.ExecuteNonQuery();
                }
            }
        }

private void ConvertQualityModelsOnTable(IDbConnection conn, IDbTransaction tran, string tableName)
        {
            var qualitiesToUpdate = new Dictionary<string, string>();

            using (IDbCommand qualityModelCmd = conn.CreateCommand())
            {
                qualityModelCmd.Transaction = tran;
                qualityModelCmd.CommandText = @"SELECT Distinct Quality FROM " + tableName;

                using (IDataReader qualityModelReader = qualityModelCmd.ExecuteReader())
                {
                    while (qualityModelReader.Read())
                    {
                        var qualityJson = qualityModelReader.GetString(0);

                        LegacyQualityModel062 quality;

                        if (!Json.TryDeserialize<LegacyQualityModel062>(qualityJson, out quality))
                        {
                            continue;
                        }

                        var newQualityModel = new QualityModel062 { Quality = quality.Quality, Revision = new Revision() };
                        if (quality.Proper)
                            newQualityModel.Revision.Version = 2;
                        var newQualityJson = newQualityModel.ToJson();

                        qualitiesToUpdate.Add(qualityJson, newQualityJson);
                    }
                }
            }

            foreach (var quality in qualitiesToUpdate)
            {
                using (IDbCommand updateCmd = conn.CreateCommand())
                {
                    updateCmd.Transaction = tran;
                    updateCmd.CommandText = "UPDATE " + tableName + " SET Quality = ? WHERE Quality = ?";
                    updateCmd.AddParameter(quality.Value);
                    updateCmd.AddParameter(quality.Key);

                    updateCmd.ExecuteNonQuery();
                }
            }
        }

private List<Profile70> GetProfiles()
        {
            var profiles = new List<Profile70>();

            using (var getProfilesCmd = _connection.CreateCommand())
            {
                getProfilesCmd.Transaction = _transaction;
                getProfilesCmd.CommandText = @"SELECT Id, Name, Cutoff, Items, Language FROM Profiles";

                using (var profileReader = getProfilesCmd.ExecuteReader())
                {
                    while (profileReader.Read())
                    {
                        profiles.Add(new Profile70
                        {
                            Id = profileReader.GetInt32(0),
                            Name = profileReader.GetString(1),
                            Cutoff = profileReader.GetInt32(2),
                            Items = Json.Deserialize<List<ProfileItem70>>(profileReader.GetString(3)),
                            Language = profileReader.GetInt32(4)
                        });
                    }
                }
            }

            return profiles;
        }

public System.Drawing.Image GetThumbnailByCategory(int categoryId)
        {
            byte[] imageRawData = null;
            var cn = (SqlConnection) this.Database.Connection;
            using (var cmd = new SqlCommand("SELECT Picture FROM Categories WHERE CategoryID=" + categoryId, cn))
            {
                cn.Open();
                using (IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (myReader.Read())
                    {
                        imageRawData = (byte[])myReader.GetValue(0);
                    }
                }
            }
            return byteArrayToImage(imageRawData, true);
        }

private void SetreplacedleSlug(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand getSeriesCmd = conn.CreateCommand())
            {
                getSeriesCmd.Transaction = tran;
                getSeriesCmd.CommandText = @"SELECT Id, Value FROM Config WHERE Key = 'filedate'";
                using (IDataReader seriesReader = getSeriesCmd.ExecuteReader())
                {
                    while (seriesReader.Read())
                    {
                        var id = seriesReader.GetInt32(0);
                        var value = seriesReader.GetString(1);

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE Config SET Value = 'Release' WHERE Id = ?";
                            updateCmd.AddParameter(id);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

private List<Tag079> GetTags(IDbConnection conn, IDbTransaction tran)
        {
            var tags = new List<Tag079>();

            using (IDbCommand tagCmd = conn.CreateCommand())
            {
                tagCmd.Transaction = tran;
                tagCmd.CommandText = @"SELECT Id, Label FROM Tags";

                using (IDataReader tagReader = tagCmd.ExecuteReader())
                {
                    while (tagReader.Read())
                    {
                        var id = tagReader.GetInt32(0);
                        var label = tagReader.GetString(1);

                        tags.Add(new Tag079 { Id = id, Label = label });
                    }
                }
            }

            return tags;
        }

private void SetSortreplacedles(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand getSeriesCmd = conn.CreateCommand())
            {
                getSeriesCmd.Transaction = tran;
                getSeriesCmd.CommandText = @"SELECT Id, replacedle FROM Movies";
                using (IDataReader seriesReader = getSeriesCmd.ExecuteReader())
                {
                    while (seriesReader.Read())
                    {
                        var id = seriesReader.GetInt32(0);
                        var replacedle = seriesReader.GetString(1);

                        var sortreplacedle = Parser.Parser.Normalizereplacedle(replacedle).ToLower();

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE Movies SET Sortreplacedle = ? WHERE Id = ?";
                            updateCmd.AddParameter(sortreplacedle);
                            updateCmd.AddParameter(id);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

public System.Drawing.Image GetThumbnailByCategory(int categoryId)
        {
            byte[] imageRawData = null;
            IDbConnection cn = (this.Connection as EnreplacedyConnection).StoreConnection;
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "SELECT Picture FROM Categories WHERE CategoryID=" + categoryId;
                cmd.Connection = cn;
                cn.Open();
                using (IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (myReader.Read())
                    {
                        imageRawData = (byte[])myReader.GetValue(0);
                    }
                }    
            }
            return byteArrayToImage(imageRawData, true);
        }

private void SetreplacedleSlug(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand getSeriesCmd = conn.CreateCommand())
            {
                getSeriesCmd.Transaction = tran;
                getSeriesCmd.CommandText = @"SELECT Id, replacedle, Year, TmdbId FROM Movies";
                using (IDataReader seriesReader = getSeriesCmd.ExecuteReader())
                {
                    while (seriesReader.Read())
                    {
                        var id = seriesReader.GetInt32(0);
                        var replacedle = seriesReader.GetString(1);
                        var year = seriesReader.GetInt32(2);
                        var tmdbId = seriesReader.GetInt32(3);

                        var replacedleSlug = Parser.Parser.ToUrlSlug(replacedle + "-" + tmdbId);

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE Movies SET replacedleSlug = ? WHERE Id = ?";
                            updateCmd.AddParameter(replacedleSlug);
                            updateCmd.AddParameter(id);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

public void PrintSettings(){
        string sql = @"
            SELECT * from settings;
        ";
        cmd.CommandText = sql;
        IDataReader rdr = cmd.ExecuteReader();
        while(rdr.Read()){
            GD.Print(rdr["name"] + ":" + rdr["value"]);
        }
        rdr.Close();
    }

private void UpdatePushoverSettings(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand selectCommand = conn.CreateCommand())
            {
                selectCommand.Transaction = tran;
                selectCommand.CommandText = @"SELECT * FROM Notifications WHERE ConfigContract = 'PushoverSettings'";

                using (IDataReader reader = selectCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var idIndex = reader.GetOrdinal("Id");
                        var settingsIndex = reader.GetOrdinal("Settings");

                        var id = reader.GetInt32(idIndex);
                        var settings = Json.Deserialize<PushoverSettingsForV33>(reader.GetString(settingsIndex));
                        settings.ApiKey = API_KEY;

                        //Set priority to high if its currently emergency
                        if (settings.Priority == 2)
                        {
                            settings.Priority = 1;
                        }

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            var text = string.Format("UPDATE Notifications " +
                                                     "SET Settings = '{0}'" +
                                                     "WHERE Id = {1}",
                                settings.ToJson(), id
                                );

                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = text;
                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

private void RenameUrlToBaseUrl(IDbConnection conn, IDbTransaction tran)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandText = "SELECT Id, Settings FROM Indexers WHERE ConfigContract IN ('NewznabSettings', 'TorznabSettings', 'IPTorrentsSettings', 'OmgwtfnzbsSettings')";

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var id = reader.GetInt32(0);
                        var settings = reader.GetString(1);

                        if (settings.IsNotNullOrWhiteSpace())
                        {
                            var jsonObject = Json.Deserialize<JObject>(settings);

                            if (jsonObject.Property("url") != null)
                            {
                                jsonObject.AddFirst(new JProperty("baseUrl", jsonObject["url"]));
                                jsonObject.Remove("url");
                                settings = jsonObject.ToJson();

                                using (var updateCmd = conn.CreateCommand())
                                {
                                    updateCmd.Transaction = tran;
                                    updateCmd.CommandText = "UPDATE Indexers SET Settings = ? WHERE Id = ?";
                                    updateCmd.AddParameter(settings);
                                    updateCmd.AddParameter(id);
                                    updateCmd.ExecuteNonQuery();
                                }
                            }
                        }
                    }
                }
            }
        }

private void ConvertConfig(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand namingConfigCmd = conn.CreateCommand())
            {
                namingConfigCmd.Transaction = tran;
                namingConfigCmd.CommandText = @"SELECT * FROM NamingConfig LIMIT 1";
                using (IDataReader namingConfigReader = namingConfigCmd.ExecuteReader())
                {
                    var separatorIndex = namingConfigReader.GetOrdinal("Separator");
                    var numberStyleIndex = namingConfigReader.GetOrdinal("NumberStyle");
                    var includeSeriesreplacedleIndex = namingConfigReader.GetOrdinal("IncludeSeriesreplacedle");
                    var includeEpisodereplacedleIndex = namingConfigReader.GetOrdinal("IncludeEpisodereplacedle");
                    var includeQualityIndex = namingConfigReader.GetOrdinal("IncludeQuality");
                    var replaceSpacesIndex = namingConfigReader.GetOrdinal("ReplaceSpaces");

                    while (namingConfigReader.Read())
                    {
                        var separator = namingConfigReader.GetString(separatorIndex);
                        var numberStyle = namingConfigReader.GetInt32(numberStyleIndex);
                        var includeSeriesreplacedle = namingConfigReader.GetBoolean(includeSeriesreplacedleIndex);
                        var includeEpisodereplacedle = namingConfigReader.GetBoolean(includeEpisodereplacedleIndex);
                        var includeQuality = namingConfigReader.GetBoolean(includeQualityIndex);
                        var replaceSpaces = namingConfigReader.GetBoolean(replaceSpacesIndex);

                        //Output settings
                        var seriesreplacedlePattern = "";
                        var episodereplacedlePattern = "";
                        var dailyEpisodePattern = "{Air-Date}";
                        var qualityFormat = " [{Quality replacedle}]";

                        if (includeSeriesreplacedle)
                        {
                            if (replaceSpaces)
                            {
                                seriesreplacedlePattern = "{Series.replacedle}";
                            }

                            else
                            {
                                seriesreplacedlePattern = "{Series replacedle}";
                            }

                            seriesreplacedlePattern += separator;
                        }

                        if (includeEpisodereplacedle)
                        {
                            episodereplacedlePattern = separator;

                            if (replaceSpaces)
                            {
                                episodereplacedlePattern += "{Episode.replacedle}";
                            }

                            else
                            {
                                episodereplacedlePattern += "{Episode replacedle}";
                            }
                        }

                        var standardEpisodeFormat = string.Format("{0}{1}{2}", seriesreplacedlePattern,
                                                                             GetNumberStyle(numberStyle).Pattern,
                                                                             episodereplacedlePattern);

                        var dailyEpisodeFormat = string.Format("{0}{1}{2}", seriesreplacedlePattern,
                                                                            dailyEpisodePattern,
                                                                            episodereplacedlePattern);

                        if (includeQuality)
                        {
                            if (replaceSpaces)
                            {
                                qualityFormat = ".[{Quality.replacedle}]";
                            }

                            standardEpisodeFormat += qualityFormat;
                            dailyEpisodeFormat += qualityFormat;
                        }

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            var text = string.Format("UPDATE NamingConfig " +
                                                     "SET StandardEpisodeFormat = '{0}', " +
                                                     "DailyEpisodeFormat = '{1}'",
                                                     standardEpisodeFormat,
                                                     dailyEpisodeFormat);

                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = text;
                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

private void replacedociateImportedHistoryItems(IDbConnection conn, IDbTransaction tran)
        {
            var historyItems = new List<MigrationHistoryItem>();

            using (IDbCommand historyCmd = conn.CreateCommand())
            {
                historyCmd.Transaction = tran;
                historyCmd.CommandText = @"SELECT Id, EpisodeId, SeriesId, Sourcereplacedle, Date, Data, EventType FROM History WHERE EventType NOT NULL";
                using (IDataReader historyRead = historyCmd.ExecuteReader())
                {
                    while (historyRead.Read())
                    {
                        historyItems.Add(new MigrationHistoryItem
                            {
                                Id = historyRead.GetInt32(0),
                                EpisodeId = historyRead.GetInt32(1),
                                SeriesId = historyRead.GetInt32(2),
                                Sourcereplacedle = historyRead.GetString(3),
                                Date = historyRead.GetDateTime(4),
                                Data = Json.Deserialize<Dictionary<string, string>>(historyRead.GetString(5)),
                                EventType = (MigrationHistoryEventType)historyRead.GetInt32(6)
                            });
                    }
                }
            }

            var numHistoryItemsNotreplacedociated = historyItems.Count(v => v.EventType == MigrationHistoryEventType.DownloadFolderImported &&
                                                                       v.Data.GetValueOrDefault("downloadClientId") == null);

            if (numHistoryItemsNotreplacedociated == 0)
            {
                return;
            }

            var historyItemsToreplacedociate = new Dictionary<MigrationHistoryItem, MigrationHistoryItem>();

            var historyItemsLookup = historyItems.ToLookup(v => v.EpisodeId);

            foreach (var historyItemGroup in historyItemsLookup)
            {
                var list = historyItemGroup.ToList();

                for (int i = 0; i < list.Count - 1; i++)
                {
                    var grabbedEvent = list[i];
                    if (grabbedEvent.EventType != MigrationHistoryEventType.Grabbed) continue;
                    if (grabbedEvent.Data.GetValueOrDefault("downloadClient") == null || grabbedEvent.Data.GetValueOrDefault("downloadClientId") == null) continue;

                    // Check if it is already replacedociated with a failed/imported event.
                    int j;
                    for (j = i + 1; j < list.Count;j++)
                    {
                        if (list[j].EventType != MigrationHistoryEventType.DownloadFolderImported &&
                            list[j].EventType != MigrationHistoryEventType.DownloadFailed)
                        {
                            continue;
                        }

                        if (list[j].Data.ContainsKey("downloadClient") && list[j].Data["downloadClient"] == grabbedEvent.Data["downloadClient"] &&
                            list[j].Data.ContainsKey("downloadClientId") && list[j].Data["downloadClientId"] == grabbedEvent.Data["downloadClientId"])
                        {
                            break;
                        }
                    }

                    if (j != list.Count)
                    {
                        list.RemoveAt(j);
                        list.RemoveAt(i--);
                        continue;
                    }

                    var importedEvent = list[i + 1];
                    if (importedEvent.EventType != MigrationHistoryEventType.DownloadFolderImported) continue;

                    var droppedPath = importedEvent.Data.GetValueOrDefault("droppedPath");
                    if (droppedPath != null && new FileInfo(droppedPath).Directory.Name == grabbedEvent.Sourcereplacedle)
                    {
                        historyItemsToreplacedociate[importedEvent] = grabbedEvent;

                        list.RemoveAt(i + 1);
                        list.RemoveAt(i--);
                    }
                }
            }

            foreach (var pair in historyItemsToreplacedociate)
            {
                using (IDbCommand updateHistoryCmd = conn.CreateCommand())
                {
                    pair.Key.Data["downloadClient"] = pair.Value.Data["downloadClient"];
                    pair.Key.Data["downloadClientId"] = pair.Value.Data["downloadClientId"];

                    updateHistoryCmd.Transaction = tran;
                    updateHistoryCmd.CommandText = "UPDATE History SET Data = ? WHERE Id = ?";
                    updateHistoryCmd.AddParameter(pair.Key.Data.ToJson());
                    updateHistoryCmd.AddParameter(pair.Key.Id);

                    updateHistoryCmd.ExecuteNonQuery();
                }
            }
            
            _logger.Info("Updated old History items. {0}/{1} old ImportedEvents were replacedociated with GrabbedEvents.", historyItemsToreplacedociate.Count, numHistoryItemsNotreplacedociated);
        }

public ExcelRangeBase LoadFromDataReader(IDataReader Reader, bool PrintHeaders)
	    {
	        if (Reader == null)
	        {
	            throw (new ArgumentNullException("Reader", "Reader can't be null"));
	        }
	        int fieldCount = Reader.FieldCount;
	  
	        int col = _fromCol, row = _fromRow;
	        if (PrintHeaders)
	        {
	            for (int i = 0; i < fieldCount; i++)
	            {
	                // If no caption is set, the ColumnName property is called implicitly.
	                _worksheet.SetValueInner(row, col++, Reader.GetName(i));
	            }
	            row++;
	            col = _fromCol;
	        }
	        while(Reader.Read())
	        {
	            for (int i = 0; i < fieldCount; i++)
	            {
	                _worksheet.SetValueInner(row, col++, Reader.GetValue(i));
	            }
	            row++;
	            col = _fromCol;
	        }
	        return _worksheet.Cells[_fromRow, _fromCol, row - 1, _fromCol + fieldCount - 1];
	    }

private void ConvertConfig(IDbConnection conn, IDbTransaction tran)
        {

            using (IDbCommand namingConfigCmd = conn.CreateCommand())
            {
                namingConfigCmd.Transaction = tran;
                namingConfigCmd.CommandText = @"SELECT * FROM NamingConfig LIMIT 1";
                using (IDataReader namingConfigReader = namingConfigCmd.ExecuteReader())
                {

                    while (namingConfigReader.Read())
                    {
                        // Output Settings
                        var moviereplacedlePattern = "";
                        var movieYearPattern = "({Release Year})";
                        var qualityFormat = "[{Quality replacedle}]";

                        moviereplacedlePattern = "{Movie replacedle}";

                        var standardMovieFormat = string.Format("{0} {1} {2}", moviereplacedlePattern, 
                                                                             movieYearPattern,
                                                                             qualityFormat);

                        var movieFolderFormat = string.Format("{0} {1}", moviereplacedlePattern, movieYearPattern);
                        

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            var text = string.Format("UPDATE NamingConfig " +
                                                     "SET StandardMovieFormat = '{0}', " +
                                                     "MovieFolderFormat = '{1}'",
                                                     standardMovieFormat,
                                                     movieFolderFormat);

                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = text;
                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

private void ConvertQualityModel(IDbConnection conn, IDbTransaction tran, string tableName)
        {
            var qualityModelConverter = new EmbeddedDoreplacedentConverter(new QualityIntConverter());

            using (IDbCommand qualityModelCmd = conn.CreateCommand())
            {
                qualityModelCmd.Transaction = tran;
                qualityModelCmd.CommandText = @"SELECT Distinct Quality FROM " + tableName;
                using (IDataReader qualityModelReader = qualityModelCmd.ExecuteReader())
                {
                    while (qualityModelReader.Read())
                    {
                        var qualityJson = qualityModelReader.GetString(0);

                        SourceQualityModel036 sourceQuality;

                        if (!Json.TryDeserialize<SourceQualityModel036>(qualityJson, out sourceQuality))
                        {
                            continue;
                        }

                        var qualityNewJson = qualityModelConverter.ToDB(new DestinationQualityModel036
                                                                        {
                                                                            Quality = sourceQuality.Quality.Id,
                                                                            Proper = sourceQuality.Proper
                                                                        });

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE " + tableName + " SET Quality = ? WHERE Quality = ?";
                            updateCmd.AddParameter(qualityNewJson);
                            updateCmd.AddParameter(qualityJson);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

private void SetSortreplacedles(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand getSeriesCmd = conn.CreateCommand())
            {
                getSeriesCmd.Transaction = tran;
                getSeriesCmd.CommandText = @"SELECT Id, replacedle FROM Series";
                using (IDataReader seriesReader = getSeriesCmd.ExecuteReader())
                {
                    while (seriesReader.Read())
                    {
                        var id = seriesReader.GetInt32(0);
                        var replacedle = seriesReader.GetString(1);

                        var sortreplacedle = Parser.Parser.Normalizereplacedle(replacedle).ToLower();

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE Series SET Sortreplacedle = ? WHERE Id = ?";
                            updateCmd.AddParameter(sortreplacedle);
                            updateCmd.AddParameter(id);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

public System.Drawing.Image GetThumbnailByCategory(int categoryId)
        {
            byte[] imageRawData = null;
            IDbConnection cn = this.ActiveContext.Database.Connection;
            IDbCommand cmd = cn.CreateCommand();
            cmd.CommandText = string.Format("SELECT Picture FROM Categories WHERE CategoryID={0}", categoryId);
            cmd.Connection = cn;
            IDataReader myReader = cmd.ExecuteReader();
            while (myReader.Read())
            {
                imageRawData = (byte[])myReader.GetValue(0);
            }
            return byteArrayToImage(imageRawData, true);  
        }

private List<Profile69> GetProfiles(IDbConnection conn, IDbTransaction tran)
        {
            var profiles = new List<Profile69>();

            using (IDbCommand getProfilesCmd = conn.CreateCommand())
            {
                getProfilesCmd.Transaction = tran;
                getProfilesCmd.CommandText = @"SELECT Id, GrabDelay FROM Profiles";
                
                using (IDataReader profileReader = getProfilesCmd.ExecuteReader())
                {
                    while (profileReader.Read())
                    {
                        var id = profileReader.GetInt32(0);
                        var delay = profileReader.GetInt32(1);

                        profiles.Add(new Profile69
                        {
                            Id = id,
                            GrabDelay = delay * 60
                        });
                    }
                }
            }

            return profiles;
        }

private void ConvertToThingyProvder(IDbConnection conn, IDbTransaction tran)
        {
            var config = new Dictionary<string, string>();

            using (IDbCommand configCmd = conn.CreateCommand())
            {
                configCmd.Transaction = tran;
                configCmd.CommandText = @"SELECT * FROM Config";
                using (IDataReader configReader = configCmd.ExecuteReader())
                {
                    var keyIndex = configReader.GetOrdinal("Key");
                    var valueIndex = configReader.GetOrdinal("Value");

                    while (configReader.Read())
                    {
                        var key = configReader.GetString(keyIndex);
                        var value = configReader.GetString(valueIndex);

                        config.Add(key.ToLowerInvariant(), value);
                    }
                }
            }

            var client = GetConfigValue(config, "DownloadClient", "");

            if (string.IsNullOrWhiteSpace(client))
            {
                return;
            }

            if (client.Equals("sabnzbd", StringComparison.InvariantCultureIgnoreCase))
            {
                var settings = new ClientSettingsForMigration
                               {
                                   Host = GetConfigValue(config, "SabHost", "localhost"),
                                   Port = GetConfigValue(config, "SabPort", 8080),
                                   ApiKey = GetConfigValue(config, "SabApiKey", ""),
                                   Username = GetConfigValue(config, "SabUsername", ""),
                                   Preplacedword = GetConfigValue(config, "SabPreplacedword", ""),
                                   TvCategory = GetConfigValue(config, "SabTvCategory", "tv"),
                                   RecentTvPriority = GetSabnzbdPriority(GetConfigValue(config, "NzbgetRecentTvPriority", "Default")),
                                   OlderTvPriority = GetSabnzbdPriority(GetConfigValue(config, "NzbgetOlderTvPriority", "Default")),
                                   UseSsl = GetConfigValue(config, "SabUseSsl", false)
                               };

                AddDownloadClient(conn, tran, "Sabnzbd", "Sabnzbd", settings.ToJson(), "SabnzbdSettings", 1);
            }

            else if (client.Equals("nzbget", StringComparison.InvariantCultureIgnoreCase))
            {
                var settings = new ClientSettingsForMigration
                {
                    Host = GetConfigValue(config, "NzbGetHost", "localhost"),
                    Port = GetConfigValue(config, "NzbgetPort", 6789),
                    Username = GetConfigValue(config, "NzbgetUsername", "nzbget"),
                    Preplacedword = GetConfigValue(config, "NzbgetPreplacedword", ""),
                    TvCategory = GetConfigValue(config, "NzbgetTvCategory", "tv"),
                    RecentTvPriority = GetNzbgetPriority(GetConfigValue(config, "NzbgetRecentTvPriority", "Normal")),
                    OlderTvPriority = GetNzbgetPriority(GetConfigValue(config, "NzbgetOlderTvPriority", "Normal")),
                };

                AddDownloadClient(conn, tran, "Nzbget", "Nzbget", settings.ToJson(), "NzbgetSettings", 1);
            }

            else if (client.Equals("pneumatic", StringComparison.InvariantCultureIgnoreCase))
            {
                var settings = new FolderSettingsForMigration
                               {
                                   Folder = GetConfigValue(config, "PneumaticFolder", "")
                               };

                AddDownloadClient(conn, tran, "Pneumatic", "Pneumatic", settings.ToJson(), "FolderSettings", 1);
            }

            else if (client.Equals("blackhole", StringComparison.InvariantCultureIgnoreCase))
            {
                var settings = new FolderSettingsForMigration
                {
                    Folder = GetConfigValue(config, "BlackholeFolder", "")
                };

                AddDownloadClient(conn, tran, "Blackhole", "Blackhole", settings.ToJson(), "FolderSettings", 1);
            }

            DeleteOldConfigValues(conn, tran);
        }

private void UpdateTransmissionSettings(IDbConnection conn, IDbTransaction tran)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandText = "SELECT Id, Settings FROM DownloadClients WHERE Implementation = 'Transmission'";

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var id = reader.GetInt32(0);
                        var settingsJson = reader.GetString(1);

                        var settings = Json.Deserialize<Dictionary<string, object>>(settingsJson);

                        var tvCategory = settings.GetValueOrDefault("tvCategory") as string;
                        if (tvCategory.IsNotNullOrWhiteSpace())
                        {
                            settings["tvCategory"] = "." + tvCategory;

                            using (var updateCmd = conn.CreateCommand())
                            {
                                updateCmd.Transaction = tran;
                                updateCmd.CommandText = "UPDATE DownloadClients SET Settings = ? WHERE Id = ?";
                                updateCmd.AddParameter(settings.ToJson());
                                updateCmd.AddParameter(id);

                                updateCmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
        }

internal IList List(Type type, DbContext db, Region region)
        {
            System.Type itemstype = System.Type.GetType("System.Collections.Generic.List`1");
            itemstype = itemstype.MakeGenericType(type);
            IList result;
            if (region == null)
            {
                region = new Region(0, 100);
            }
            result = (IList)Activator.CreateInstance(itemstype, region.Size);
            EnreplacedyReader cr = EnreplacedyReader.GetReader(mBaseSql, type);
            int index = 0;
            Command cmd = GetCommand();
            var conn = db.Database.GetDbConnection();
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            var dbcmd = cmd.CreateCommand(conn);
#if NETCOREAPP2_1
            using (CodeTrackFactory.Track(dbcmd.CommandText, CodeTrackLevel.Function, null, "EFCore", "ExecuteToObject"))
            {
#endif
                dbcmd.Transaction = db.Database.CurrentTransaction?.GetDbTransaction();
                SQLExecuting?.Invoke(dbcmd);
                using (IDataReader reader = dbcmd.ExecuteReader())
                {

                    while (reader.Read())
                    {
                        if (index >= region.Start)
                        {
                            object item = Activator.CreateInstance(type);
                            cr.ReaderToObject(reader, item);
                            result.Add(item);
                            if (result.Count >= region.Size)
                            {
                                cmd.DbCommand.Cancel();
                                reader.Dispose();
                                break;
                            }
                        }
                        index++;
                    }

                }
                return result;
#if NETCOREAPP2_1
            }
#endif

        }

private void SetMetadataFileExtension(IDbConnection conn, IDbTransaction tran)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandText = "SELECT Id, Extension FROM MetadataFiles";

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var id = reader.GetInt32(0);
                        var extension = reader.GetString(1);
                        extension = extension.Substring(extension.LastIndexOf(".", StringComparison.InvariantCultureIgnoreCase));

                        using (var updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE MetadataFiles SET Extension = ? WHERE Id = ?";
                            updateCmd.AddParameter(extension);
                            updateCmd.AddParameter(id);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

private void ConvertRestrictions(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand getRestictionsCmd = conn.CreateCommand())
            {
                getRestictionsCmd.Transaction = tran;
                getRestictionsCmd.CommandText = @"SELECT [Value] FROM Config WHERE [Key] = 'releaserestrictions'";

                using (IDataReader configReader = getRestictionsCmd.ExecuteReader())
                {
                    while (configReader.Read())
                    {
                        var restrictions = configReader.GetString(0);
                        restrictions = restrictions.Replace("\n", ",");

                        using (IDbCommand insertCmd = conn.CreateCommand())
                        {
                            insertCmd.Transaction = tran;
                            insertCmd.CommandText = "INSERT INTO Restrictions (Ignored, Tags) VALUES (?, '[]')";
                            insertCmd.AddParameter(restrictions);

                            insertCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

private void MoveToColumn(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand getHistory = conn.CreateCommand())
            {
                getHistory.Transaction = tran;
                getHistory.CommandText = @"SELECT Id, Data FROM History WHERE Data LIKE '%downloadClientId%'";

                using (var historyReader = getHistory.ExecuteReader())
                {
                    while (historyReader.Read())
                    {
                        var id = historyReader.GetInt32(0);
                        var data = historyReader.GetString(1);

                        UpdateHistory(tran, conn, id, data);
                    }
                }
            }
        }

internal static IList DataReaderConverter(Transaction.Transaction repository, IDataReader reader, ISqlCommand command, Type type)
        {
            var tType = type.GetActualType();
            var attachable = tType.GetPrimaryKey() != null;
            var baseListType = typeof(List<>);
            var listType = baseListType.MakeGenericType(tType);
            var iList = DeepCloner.CreateInstance(listType) as IList;
            var props = DeepCloner.GetFastDeepClonerProperties(tType);
            try
            {
                var colNames = new SafeValueType<int, string>();
                var pp = new SafeValueType<int, IFastDeepClonerProperty>();
                while (reader.Read())
                {
                    object item = null;
                    object replacedem = null;

                    item = DeepCloner.CreateInstance(tType);
                    replacedem = attachable ? DeepCloner.CreateInstance(tType) : null;
                    var col = 0;

                    while (col < reader.FieldCount)
                    {
                        string columnName;
                        if (colNames.ContainsKey(col))
                            columnName = colNames[col];
                        else
                        {
                            columnName = reader.GetName(col);
                            colNames.TryAdd(col, columnName);
                        }

                        var value = reader[columnName];

                        IFastDeepClonerProperty prop;
                        if (!pp.ContainsKey(col))
                        {
                            prop = DeepCloner.GetProperty(tType, columnName);
                            if (prop == null)
                                prop = props.FirstOrDefault(x => string.Equals(x.GetPropertyName(), columnName, StringComparison.CurrentCultureIgnoreCase) || x.GetPropertyName().ToLower() == columnName);
                            pp.TryAdd(col, prop);
                        }
                        else prop = pp[col];
                        if (prop != null && value != DBNull.Value && value != null && prop.CanRead)
                        {
                            if (value as byte[] != null && prop.PropertyType.FullName.Contains("Guid"))
                                value = new Guid(value as byte[]);

                            var dataEncode = prop.GetCustomAttribute<DataEncode>();
                            if (prop.ContainAttribute<ToBase64String>())
                            {
                                if (value.ConvertValue<string>().IsBase64String())
                                    value = MethodHelper.DecodeStringFromBase64(value.ConvertValue<string>());
                                else value = MethodHelper.ConvertValue(value, prop.PropertyType);
                            }
                            else if (dataEncode != null)
                                value = new DataCipher(dataEncode.Key, dataEncode.KeySize).Decrypt(value.ConvertValue<string>());
                            else if (prop.ContainAttribute<JsonDoreplacedent>())
                                value = value?.ToString().FromJson(prop.PropertyType);
                            else if (prop.ContainAttribute<XmlDoreplacedent>())
                                value = value?.ToString().FromXml();
                            else value = MethodHelper.ConvertValue(value, prop.PropertyType);

                            prop.SetValue(item, value);
                            if (attachable)
                                prop.SetValue(replacedem, value);
                        }
                        col++;
                    }

                    if (replacedem != null && !(repository?.IsAttached(replacedem) ?? true))
                        repository?.AttachNew(replacedem);
                    iList.Add(item);

                }
            }
            catch (Exception e)
            {
                throw new EnreplacedyException(e.Message);
            }
            finally
            {
                reader.Close();
                reader.Dispose();
                if (repository.OpenedDataReaders.ContainsKey(reader))
                    repository.OpenedDataReaders.Remove(reader);
            }

            return iList;
        }

private void UpdateTransmissionSettings(IDbConnection conn, IDbTransaction tran)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandText = "SELECT Id, Settings FROM Notifications WHERE Implementation = 'PushBullet'";

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var id = reader.GetInt32(0);
                        var settingsJson = reader.GetString(1);
                        var settings = Json.Deserialize<Dictionary<string, object>>(settingsJson);

                        if (settings.ContainsKey("deviceIds"))
                        {
                            var deviceIdsString = settings.GetValueOrDefault("deviceIds", "") as string;

                            if (deviceIdsString.IsNotNullOrWhiteSpace())
                            {
                                var deviceIds = deviceIdsString.Split(new[] { "," }, StringSplitOptions.RemoveEmptyEntries);

                                settings["deviceIds"] = deviceIds;
                            }
                        }

                        if (settings.ContainsKey("channelTags"))
                        {
                            var channelTagsString = settings.GetValueOrDefault("channelTags", "") as string;

                            if (channelTagsString.IsNotNullOrWhiteSpace())
                            {
                                var channelTags = channelTagsString.Split(new[] {","}, StringSplitOptions.RemoveEmptyEntries);

                                settings["channelTags"] = channelTags;
                            }
                        }

                        using (var updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE Notifications SET Settings = ? WHERE Id = ?";
                            updateCmd.AddParameter(settings.ToJson());
                            updateCmd.AddParameter(id);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

private void UpdateSeries(IDbConnection conn, IDbTransaction tran, IEnumerable<int> profileIds, int tagId)
        {
            using (IDbCommand getSeriesCmd = conn.CreateCommand())
            {
                getSeriesCmd.Transaction = tran;
                getSeriesCmd.CommandText = "SELECT Id, Tags FROM Series WHERE ProfileId IN (?)";
                getSeriesCmd.AddParameter(string.Join(",", profileIds));

                using (IDataReader seriesReader = getSeriesCmd.ExecuteReader())
                {
                    while (seriesReader.Read())
                    {
                        var id = seriesReader.GetInt32(0);
                        var tagString = seriesReader.GetString(1);

                        var tags = Json.Deserialize<List<int>>(tagString);
                        tags.Add(tagId);

                        using (IDbCommand updateSeriesCmd = conn.CreateCommand())
                        {
                            updateSeriesCmd.Transaction = tran;
                            updateSeriesCmd.CommandText = "UPDATE Series SET Tags = ? WHERE Id = ?";
                            updateSeriesCmd.AddParameter(tags.ToJson());
                            updateSeriesCmd.AddParameter(id);

                            updateSeriesCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

public System.Collections.Generic.Dictionary<int, ItemData> LoadItems(){
        System.Collections.Generic.Dictionary<int, ItemData> items;
        items = new System.Collections.Generic.Dictionary<int, ItemData>();
        
        string sql = @"
            SELECT * from items;
        ";

        cmd.CommandText = sql;
        IDataReader rdr = cmd.ExecuteReader();

        while(rdr.Read()){
            string json = (string)rdr["data"];
            ItemData data = ItemData.FromJson(json);
            items.Add(data.id, data);
        }

        rdr.Close();

        return items;
    }

internal static ILightDataTable ReadData(this ILightDataTable data, DataBaseTypes dbType, IDataReader reader, ISqlCommand command, string primaryKey = null, bool closeReader = true)
        {
            var i = 0;
            data.TablePrimaryKey = primaryKey;
            if (reader.FieldCount <= 0)
            {
                if (closeReader)
                {
                    reader.Close();
                    reader.Dispose();
                }
                return data;
            }
            var key = command.Command.CommandText;
            try
            {
                if (!CachedSqlException.ContainsKey(key))
                {
                    if (!CachedGetSchemaTable.ContainsKey(key))
                        CachedGetSchemaTable.Add(key, new LightDataTable(reader.GetSchemaTable()));

                    foreach (var item in CachedGetSchemaTable[key].Rows)
                    {
                        var columnName = item.Value<string>("ColumnName");
                        data.TablePrimaryKey = data.TablePrimaryKey == null && item.Columns.ContainsKey("IsKey") && item.TryValueAndConvert<bool>("IsKey", false) ? columnName : data.TablePrimaryKey;
                        var dataType = TypeByTypeAndDbIsNull(item["DataType"] as Type,
                            item.TryValueAndConvert<bool>("AllowDBNull", true));
                        if (data.Columns.ContainsKey(columnName))
                            columnName = columnName + i;
                        data.AddColumn(columnName, dataType);
                        i++;
                    }
                }
                else
                {
                    for (var col = 0; col < reader.FieldCount; col++)
                    {
                        var columnName = reader.GetName(col);
                        var dataType = TypeByTypeAndDbIsNull(reader.GetFieldType(col) as Type, true);
                        if (data.Columns.ContainsKey(columnName))
                            columnName = columnName + i;
                        data.AddColumn(columnName, dataType);
                        i++;
                    }
                }
            }
            catch (Exception e)
            {
                if (!string.IsNullOrEmpty(key))
                {
                    CachedSqlException.Add(key, e);
                    return ReadData(data, dbType, reader, command, primaryKey);
                }
                else throw new EnreplacedyException(e.Message);
            }

            while (reader.Read())
            {
                var row = data.NewRow();
                reader.GetValues(row._itemArray);
                data.AddRow(row);
            }

            if (closeReader)
            {
                reader.Close();
                reader.Dispose();
            }

            return data;
        }

public Image GetCategoryThumbnail(int categoryId)
        {
            byte[] imageRawData = null;
            var cn = (SqlConnection)this.Context.Database.Connection;
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "SELECT Picture FROM Categories WHERE CategoryID=" + categoryId;
                cmd.Connection = cn;
                cn.Open();
                using (IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (myReader.Read())
                    {
                        imageRawData = (byte[])myReader.GetValue(0);
                    }
                }
            }
            return byteArrayToImage(imageRawData, true);
        }

public System.Drawing.Image GetThumbnailByCategory(int categoryId)
        {
            byte[] imageRawData = null;
            var cn = (SqlConnection)this.Database.Connection;
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "SELECT Picture FROM Categories WHERE CategoryID=" + categoryId;
                cmd.Connection = cn;
                cn.Open();
                using (IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (myReader.Read())
                    {
                        imageRawData = (byte[])myReader.GetValue(0);
                    }
                }    
            }
            return byteArrayToImage(imageRawData, true);
        }

private IEnumerable<IGrouping<T, KeyValuePair<int, T>>> GetDuplicates<T>(IDbConnection conn, IDbTransaction tran, string tableName, string columnName)
        {
            var getDuplicates = conn.CreateCommand();
            getDuplicates.Transaction = tran;
            getDuplicates.CommandText = string.Format("select id, {0} from {1}", columnName, tableName);

            var result = new List<KeyValuePair<int, T>>();

            using (var reader = getDuplicates.ExecuteReader())
            {
                while (reader.Read())
                {
                    result.Add(new KeyValuePair<int, T>(reader.GetInt32(0), (T)Convert.ChangeType(reader[1], typeof(T))));
                }
            }

            return result.GroupBy(c => c.Value).Where(g => g.Count() > 1);
        }

private void ConvertSeasons(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand allSeriesCmd = conn.CreateCommand())
            {
                allSeriesCmd.Transaction = tran;
                allSeriesCmd.CommandText = @"SELECT Id FROM Series";
                using (IDataReader allSeriesReader = allSeriesCmd.ExecuteReader())
                {
                    while (allSeriesReader.Read())
                    {
                        int seriesId = allSeriesReader.GetInt32(0);
                        var seasons = new List<dynamic>();

                        using (IDbCommand seasonsCmd = conn.CreateCommand())
                        {
                            seasonsCmd.Transaction = tran;
                            seasonsCmd.CommandText = string.Format(@"SELECT SeasonNumber, Monitored FROM Seasons WHERE SeriesId = {0}", seriesId);

                            using (IDataReader seasonReader = seasonsCmd.ExecuteReader())
                            {
                                while (seasonReader.Read())
                                {
                                    int seasonNumber = seasonReader.GetInt32(0);
                                    bool monitored = seasonReader.GetBoolean(1);

                                    if (seasonNumber == 0)
                                    {
                                        monitored = false;
                                    }

                                    seasons.Add(new { seasonNumber, monitored });
                                }
                            }
                        }

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            var text = string.Format("UPDATE Series SET Seasons = '{0}' WHERE Id = {1}", seasons.ToJson() , seriesId);

                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = text;
                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

See More Examples