public static OperationResult ImportSeriale()
        {
            var result = new OperationResult();

            try
            {
                var connDest   = new SqlCeConnection(Constants.ConnectionString);
                var connSource = new SqlCeConnection("Data Source = Desene.sdf;Max Database Size=4091;Persist Security Info=False");

                connDest.Open();
                connSource.Open();

                const string insertString =
                    @"
                    INSERT INTO FileDetail (
                        ParentId,
                        FileName,
                        Year,
                        Theme,
                        Season,
                        InsertedDate,
                        LastChangeDate,
                        Recommended,
                        RecommendedLink,
                        DescriptionLink,
                        Poster,
                        Notes)
                    VALUES (
                        @ParentId,
                        @FileName,
                        @Year,
                        @Theme,
                        @Season,
                        @InsertedDate,
                        @LastChangeDate,
                        @Recommended,
                        @RecommendedLink,
                        @DescriptionLink,
                        @Poster,
                        @Notes)";

                try
                {
                    var commandSource = new SqlCeCommand("select * from Seriale", connSource);

                    using (var reader = commandSource.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (string.IsNullOrEmpty(reader["Titlu"].ToString()))
                            {
                                continue;
                            }

                            var cmd = new SqlCeCommand(insertString, connDest)
                            {
                                CommandType = CommandType.Text
                            };
                            cmd.Parameters.AddWithValue("@ParentId", -1);
                            cmd.Parameters.AddWithValue("@FileName", reader["Titlu"].ToString());
                            cmd.Parameters.AddWithValue("@Theme", string.Empty);
                            cmd.Parameters.AddWithValue("@Year", string.Empty);
                            cmd.Parameters.AddWithValue("@Season", string.Empty);

                            if (!(reader["DataAdaugare"] is DBNull))
                            {
                                cmd.Parameters.AddWithValue("@InsertedDate", (DateTime)reader["DataAdaugare"]);
                            }
                            else
                            {
                                cmd.Parameters.AddWithValue("@InsertedDate", DBNull.Value);
                            }

                            if (!(reader["DataUM"] is DBNull))
                            {
                                cmd.Parameters.AddWithValue("@LastChangeDate", (DateTime)reader["DataUM"]);
                            }
                            else
                            {
                                cmd.Parameters.AddWithValue("@LastChangeDate", DBNull.Value);
                            }

                            cmd.Parameters.AddWithValue("@Recommended", reader["Recomandat"].ToString());
                            cmd.Parameters.AddWithValue("@RecommendedLink", reader["RecomandatLink"].ToString() != "0" ? reader["RecomandatLink"].ToString() : string.Empty);
                            cmd.Parameters.AddWithValue("@DescriptionLink", reader["MoreInfo"].ToString());
                            cmd.Parameters.AddWithValue("@Notes", reader["Obs"].ToString());

                            if (!(reader["Cover"] is DBNull))
                            {
                                cmd.Parameters.AddWithValue("@Poster", (byte[])reader["Cover"]);
                            }
                            else
                            {
                                cmd.Parameters.AddWithValue("@Poster", DBNull.Value);
                            }


                            cmd.ExecuteNonQuery();
                            cmd.CommandText = "Select @@Identity";
                            var seriesId = (int)(decimal)cmd.ExecuteScalar();

                            var commandSource2 = new SqlCeCommand("select * from Episoade where SerialId = " + reader["Id"], connSource);

                            using (var readerEp = commandSource2.ExecuteReader())
                            {
                                while (readerEp.Read())
                                {
                                    var cmdEp = new SqlCeCommand(insertString, connDest)
                                    {
                                        CommandType = CommandType.Text
                                    };
                                    cmdEp.Parameters.AddWithValue("@ParentId", seriesId);
                                    cmdEp.Parameters.AddWithValue("@FileName", readerEp["Titlu"].ToString());
                                    cmdEp.Parameters.AddWithValue("@Year", readerEp["An"].ToString());
                                    cmdEp.Parameters.AddWithValue("@Theme", readerEp["Tematica"].ToString());
                                    cmdEp.Parameters.AddWithValue("@Season", readerEp["Sezon"].ToString());

                                    if (!(readerEp["DataAdaugare"] is DBNull))
                                    {
                                        cmdEp.Parameters.AddWithValue("@InsertedDate", (DateTime)readerEp["DataAdaugare"]);
                                    }
                                    else
                                    {
                                        cmdEp.Parameters.AddWithValue("@InsertedDate", DBNull.Value);
                                    }

                                    if (!(readerEp["DataUM"] is DBNull))
                                    {
                                        cmdEp.Parameters.AddWithValue("@LastChangeDate", (DateTime)readerEp["DataUM"]);
                                    }
                                    else
                                    {
                                        cmdEp.Parameters.AddWithValue("@LastChangeDate", DBNull.Value);
                                    }

                                    cmdEp.Parameters.AddWithValue("@Recommended", DBNull.Value);
                                    cmdEp.Parameters.AddWithValue("@RecommendedLink", DBNull.Value);
                                    cmdEp.Parameters.AddWithValue("@DescriptionLink", DBNull.Value);
                                    cmdEp.Parameters.AddWithValue("@Notes", DBNull.Value);
                                    cmdEp.Parameters.AddWithValue("@Poster", DBNull.Value);

                                    cmdEp.ExecuteNonQuery();
                                }
                            }
                        }
                    }
                }
                finally
                {
                    connDest.Close();
                    connSource.Close();
                }
            }
            catch (Exception e)
            {
                result.FailWithMessage(e);
            }

            return(result);
        }
        /*
         * public static OperationResult ImportFilmeHD()
         * {
         *  var result = new OperationResult();
         *
         *  try
         *  {
         *      var connDest = new SqlCeConnection(Constants.ConnectionString);
         *      var connSource = new SqlCeConnection("Data Source = Desene.sdf;Persist Security Info=False");
         *      connDest.Open();
         *      connSource.Open();
         *
         *      const string insertString =
         *          @"
         *          INSERT INTO MovieOrSeries (
         *              Recommended,
         *              RecommendedLink,
         *              DescriptionLink,
         *              Poster,
         *              IsSerie,
         *              Notes)
         *          VALUES (
         *              @Recommended,
         *              @RecommendedLink,
         *              @DescriptionLink,
         *              @Poster,
         *              @IsSerie,
         *              @Notes)";
         *
         *      const string insertString2 =
         *          @"
         *          INSERT INTO FileDetail (
         *              MovieOrSeriesId,
         *              FileName,
         *              Year,
         *              Theme,
         *              InsertedDate,
         *              LastChangeDate)
         *          VALUES (
         *              @MovieOrSeriesId,
         *              @FileName,
         *              @Year,
         *              @Theme,
         *              @InsertedDate,
         *              @LastChangeDate)";
         *
         *      //var sr =  File.CreateText("googllinks.txt");
         *      var googleUrls = File.ReadAllLines(@"goo.gl to long url.csv")
         *                           .Select(ShortToLongUrl.FromCsv)
         *                           .ToList();
         *
         *      var urlSLErrors = "";
         *
         *      try
         *      {
         *          var commandSource = new SqlCeCommand("select * from Filme", connSource);
         *
         *          using (var reader = commandSource.ExecuteReader())
         *          {
         *              while (reader.Read())
         *              {
         *                  if (string.IsNullOrEmpty(reader["Titlu"].ToString())) continue;
         *
         *                  var cmd = new SqlCeCommand(insertString, connDest) { CommandType = CommandType.Text };
         *
         *                  cmd.Parameters.AddWithValue("@Recommended", reader["Recomandat"].ToString());
         *                  cmd.Parameters.AddWithValue("@RecommendedLink", reader["RecomandatLink"].ToString() != "0" ?  reader["RecomandatLink"].ToString() : string.Empty);
         *                  cmd.Parameters.AddWithValue("@IsSerie", false);
         *                  cmd.Parameters.AddWithValue("@Notes", reader["Obs"].ToString());
         *                  //cmd.Parameters.AddWithValue("@DescriptionLink", reader["MoreInfo"].ToString() != "0" ?  reader["MoreInfo"].ToString() : string.Empty);
         *
         *
         *                  if (reader["MoreInfo"].ToString() == "0" || string.IsNullOrEmpty(reader["MoreInfo"].ToString()))
         *                  {
         *                      cmd.Parameters.AddWithValue("@DescriptionLink", string.Empty);
         *                  }
         *                  else
         *                  {
         *                      if (reader["MoreInfo"].ToString().Contains("goo.gl"))
         *                      {
         *                          var shortUrl = reader["MoreInfo"].ToString().Replace("#", "");
         *                          var longUrlObj = googleUrls.FirstOrDefault(s => s.ShortUrl == shortUrl);
         *
         *                          if (longUrlObj == null)
         *                          {
         *                              cmd.Parameters.AddWithValue("@DescriptionLink", reader["MoreInfo"].ToString());
         *                              urlSLErrors += reader["Id"].ToString() + ", ";
         *                          }
         *                          else
         *                          {
         *                              cmd.Parameters.AddWithValue("@DescriptionLink", longUrlObj.LongUrl);
         *                          }
         *
         *                          //sr.WriteLine(reader["MoreInfo"].ToString().Replace("#", ""));
         *                      }
         *                      else
         *                      {
         *                          cmd.Parameters.AddWithValue("@DescriptionLink", reader["MoreInfo"].ToString());
         *                      }
         *                  }
         *
         *                  if (!(reader["Cover"] is DBNull))
         *                      cmd.Parameters.AddWithValue("@Poster", (byte[])reader["Cover"]);
         *                  else
         *                      cmd.Parameters.AddWithValue("@Poster", DBNull.Value);
         *
         *
         *                  cmd.ExecuteNonQuery();
         *
         *
         *                  cmd.CommandText = "Select @@Identity";
         *                  var newId = (int)(decimal)cmd.ExecuteScalar();
         *
         *                  cmd = new SqlCeCommand(insertString2, connDest) { CommandType = CommandType.Text };
         *                  cmd.Parameters.AddWithValue("@MovieOrSeriesId", newId);
         *                  cmd.Parameters.AddWithValue("@FileName", reader["Titlu"].ToString());
         *                  cmd.Parameters.AddWithValue("@Year", reader["An"].ToString());
         *                  cmd.Parameters.AddWithValue("@Theme", reader["Tematica"].ToString());
         *
         *                  if (!(reader["DataAdaugare"] is DBNull))
         *                      cmd.Parameters.AddWithValue("@InsertedDate", (DateTime)reader["DataAdaugare"]);
         *                  else
         *                      cmd.Parameters.AddWithValue("@InsertedDate", DBNull.Value);
         *
         *                  if (!(reader["DataUM"] is DBNull))
         *                      cmd.Parameters.AddWithValue("@LastChangeDate", (DateTime)reader["DataUM"]);
         *                  else
         *                      cmd.Parameters.AddWithValue("@LastChangeDate", DBNull.Value);
         *
         *                  cmd.ExecuteNonQuery();
         *              }
         *          }
         *      }
         *      finally
         *      {
         *          connDest.Close();
         *          connSource.Close();
         *
         *          //sr.Close();
         *      }
         *  }
         *  catch (Exception e)
         *  {
         *      result.FailWithMessage(e);
         *  }
         *
         *  return result;
         * }
         *
         * public static OperationResult ImportSeriale()
         * {
         *  var result = new OperationResult();
         *
         *  try
         *      {
         *      var connDest = new SqlCeConnection(Constants.ConnectionString);
         *      var connSource = new SqlCeConnection("Data Source = Desene.sdf;Persist Security Info=False");
         *
         *      connDest.Open();
         *      connSource.Open();
         *
         *      const string insertString =
         *          @"
         *          INSERT INTO MovieOrSeries (
         *              Recommended,
         *              RecommendedLink,
         *              DescriptionLink,
         *              Poster,
         *              IsSerie,
         *              Notes)
         *          VALUES (
         *              @Recommended,
         *              @RecommendedLink,
         *              @DescriptionLink,
         *              @Poster,
         *              @IsSerie,
         *              @Notes)";
         *
         *      const string insertString2 =
         *          @"
         *          INSERT INTO FileDetail (
         *              MovieOrSeriesId,
         *              ParentId,
         *              FileName,
         *              Year,
         *              Theme,
         *              Season,
         *              InsertedDate,
         *              LastChangeDate)
         *          VALUES (
         *              @MovieOrSeriesId,
         *              @ParentId,
         *              @FileName,
         *              @Year,
         *              @Theme,
         *              @Season,
         *              @InsertedDate,
         *              @LastChangeDate)";
         *
         *      try
         *      {
         *          var commandSource = new SqlCeCommand("select * from Seriale", connSource);
         *
         *          using (var reader = commandSource.ExecuteReader())
         *          {
         *              while (reader.Read())
         *              {
         *                  if (string.IsNullOrEmpty(reader["Titlu"].ToString())) continue;
         *
         *                  var cmd = new SqlCeCommand(insertString, connDest) { CommandType = CommandType.Text };
         *
         *                  cmd.Parameters.AddWithValue("@Recommended", reader["Recomandat"].ToString());
         *                  cmd.Parameters.AddWithValue("@RecommendedLink", reader["RecomandatLink"].ToString() != "0" ?  reader["RecomandatLink"].ToString() : string.Empty);
         *                  cmd.Parameters.AddWithValue("@DescriptionLink", reader["MoreInfo"].ToString());
         *                  cmd.Parameters.AddWithValue("@IsSerie", true);
         *                  cmd.Parameters.AddWithValue("@Notes", reader["Obs"].ToString());
         *
         *                  if (!(reader["Cover"] is DBNull))
         *                      cmd.Parameters.AddWithValue("@Poster", (byte[])reader["Cover"]);
         *                  else
         *                      cmd.Parameters.AddWithValue("@Poster", DBNull.Value);
         *
         *
         *                  cmd.ExecuteNonQuery();
         *
         *                  cmd.CommandText = "Select @@Identity";
         *                  var newId = (int)(decimal)cmd.ExecuteScalar();
         *
         *                  cmd = new SqlCeCommand(insertString2, connDest) { CommandType = CommandType.Text };
         *                  cmd.Parameters.AddWithValue("@MovieOrSeriesId", newId);
         *                  cmd.Parameters.AddWithValue("@ParentId", -1);
         *                  cmd.Parameters.AddWithValue("@FileName", reader["Titlu"].ToString());
         *                  cmd.Parameters.AddWithValue("@Theme", string.Empty);
         *                  cmd.Parameters.AddWithValue("@Year", string.Empty);
         *                  cmd.Parameters.AddWithValue("@Season", string.Empty);
         *
         *                  if (!(reader["DataAdaugare"] is DBNull))
         *                      cmd.Parameters.AddWithValue("@InsertedDate", (DateTime)reader["DataAdaugare"]);
         *                  else
         *                      cmd.Parameters.AddWithValue("@InsertedDate", DBNull.Value);
         *
         *                  if (!(reader["DataUM"] is DBNull))
         *                      cmd.Parameters.AddWithValue("@LastChangeDate", (DateTime)reader["DataUM"]);
         *                  else
         *                      cmd.Parameters.AddWithValue("@LastChangeDate", DBNull.Value);
         *
         *
         *                  cmd.ExecuteNonQuery();
         *                  cmd.CommandText = "Select @@Identity";
         *                  var newId2 = (int)(decimal)cmd.ExecuteScalar();
         *
         *                  var commandSource2 = new SqlCeCommand("select * from Episoade where SerialId = " + reader["Id"], connSource);
         *
         *                  using (var readerEp = commandSource2.ExecuteReader())
         *                  {
         *                      while (readerEp.Read())
         *                      {
         *                          var cmdEp = new SqlCeCommand(insertString2, connDest) { CommandType = CommandType.Text };
         *                          cmdEp.Parameters.AddWithValue("@MovieOrSeriesId", newId);
         *                          cmdEp.Parameters.AddWithValue("@ParentId", newId2);
         *                          cmdEp.Parameters.AddWithValue("@FileName", readerEp["Titlu"].ToString());
         *                          cmdEp.Parameters.AddWithValue("@Year", readerEp["An"].ToString());
         *                          cmdEp.Parameters.AddWithValue("@Theme", readerEp["Tematica"].ToString());
         *                          cmdEp.Parameters.AddWithValue("@Season", readerEp["Sezon"].ToString());
         *
         *                          if (!(readerEp["DataAdaugare"] is DBNull))
         *                              cmdEp.Parameters.AddWithValue("@InsertedDate", (DateTime)readerEp["DataAdaugare"]);
         *                          else
         *                              cmdEp.Parameters.AddWithValue("@InsertedDate", DBNull.Value);
         *
         *                          if (!(readerEp["DataUM"] is DBNull))
         *                              cmdEp.Parameters.AddWithValue("@LastChangeDate", (DateTime)readerEp["DataUM"]);
         *                          else
         *                              cmdEp.Parameters.AddWithValue("@LastChangeDate", DBNull.Value);
         *
         *
         *                          cmdEp.ExecuteNonQuery();
         *                      }
         *                  }
         *              }
         *          }
         *      }
         *      finally
         *      {
         *          connDest.Close();
         *          connSource.Close();
         *      }
         *  }
         *  catch (Exception e)
         *  {
         *      result.FailWithMessage(e);
         *  }
         *
         *  return result;
         * }
         */

        public static OperationResult ImportFilmeHD()
        {
            var result = new OperationResult();

            try
            {
                var connDest   = new SqlCeConnection(Constants.ConnectionString);
                var connSource = new SqlCeConnection("Data Source = Desene.sdf;Max Database Size=4091;Persist Security Info=False");
                connDest.Open();
                connSource.Open();

                const string insertString =
                    @"
                    INSERT INTO FileDetail (
                        FileName,
                        Year,
                        Theme,
                        InsertedDate,
                        LastChangeDate,
                        Recommended,
                        RecommendedLink,
                        DescriptionLink,
                        Poster,
                        Trailer,
                        Notes,
                        NlAudioSource)
                    VALUES (
                        @FileName,
                        @Year,
                        @Theme,
                        @InsertedDate,
                        @LastChangeDate,
                        @Recommended,
                        @RecommendedLink,
                        @DescriptionLink,
                        @Poster,
                        @Trailer,
                        @Notes,
                        @NlAudioSource)";

                //var sr =  File.CreateText("googllinks.txt");
                var googleUrls = File.ReadAllLines(@"goo.gl to long url.csv")
                                 .Select(ShortToLongUrl.FromCsv)
                                 .ToList();

                var urlSLErrors = "";

                try
                {
                    var commandSource = new SqlCeCommand("select * from Filme", connSource);

                    using (var reader = commandSource.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (string.IsNullOrEmpty(reader["Titlu"].ToString()))
                            {
                                continue;
                            }

                            var cmd = new SqlCeCommand(insertString, connDest)
                            {
                                CommandType = CommandType.Text
                            };
                            var titluCorectat = reader["Titlu"].ToString().Replace(": ", " - ");
                            cmd.Parameters.AddWithValue("@FileName", titluCorectat);
                            cmd.Parameters.AddWithValue("@Year", reader["An"].ToString());
                            cmd.Parameters.AddWithValue("@Theme", reader["Tematica"].ToString());

                            if (!(reader["DataAdaugare"] is DBNull))
                            {
                                cmd.Parameters.AddWithValue("@InsertedDate", (DateTime)reader["DataAdaugare"]);
                            }
                            else
                            {
                                cmd.Parameters.AddWithValue("@InsertedDate", DBNull.Value);
                            }

                            if (!(reader["DataUM"] is DBNull))
                            {
                                cmd.Parameters.AddWithValue("@LastChangeDate", (DateTime)reader["DataUM"]);
                            }
                            else
                            {
                                cmd.Parameters.AddWithValue("@LastChangeDate", DBNull.Value);
                            }

                            cmd.Parameters.AddWithValue("@Recommended", reader["Recomandat"].ToString());
                            cmd.Parameters.AddWithValue("@RecommendedLink", reader["RecomandatLink"].ToString() != "0" ? reader["RecomandatLink"].ToString() : string.Empty);

                            var obsList = new List <string>
                            {
                                reader["Obs"].ToString().Trim(),
                                reader["Obs2"].ToString().Trim(),
                                reader["Obs3"].ToString().Trim()
                            };
                            obsList.RemoveAll(n => n == string.Empty);

                            var notes = string.Join(Environment.NewLine, obsList);

                            cmd.Parameters.AddWithValue("@Notes", notes);
                            //cmd.Parameters.AddWithValue("@DescriptionLink", reader["MoreInfo"].ToString() != "0" ?  reader["MoreInfo"].ToString() : string.Empty);
                            cmd.Parameters.AddWithValue("@Trailer", reader["Trailer"].ToString());
                            cmd.Parameters.AddWithValue("@NlAudioSource", reader["Nlsource"].ToString());

                            if (reader["MoreInfo"].ToString() == "0" || string.IsNullOrEmpty(reader["MoreInfo"].ToString()))
                            {
                                cmd.Parameters.AddWithValue("@DescriptionLink", string.Empty);
                            }
                            else
                            {
                                if (reader["MoreInfo"].ToString().Contains("goo.gl"))
                                {
                                    var shortUrl   = reader["MoreInfo"].ToString().Replace("#", "");
                                    var longUrlObj = googleUrls.FirstOrDefault(s => s.ShortUrl == shortUrl);

                                    if (longUrlObj == null)
                                    {
                                        cmd.Parameters.AddWithValue("@DescriptionLink", reader["MoreInfo"].ToString());
                                        urlSLErrors += reader["Id"].ToString() + ", ";
                                    }
                                    else
                                    {
                                        cmd.Parameters.AddWithValue("@DescriptionLink", longUrlObj.LongUrl);
                                    }

                                    //sr.WriteLine(reader["MoreInfo"].ToString().Replace("#", ""));
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("@DescriptionLink", reader["MoreInfo"].ToString());
                                }
                            }

                            if (!(reader["Cover"] is DBNull))
                            {
                                cmd.Parameters.AddWithValue("@Poster", (byte[])reader["Cover"]);
                            }
                            else
                            {
                                cmd.Parameters.AddWithValue("@Poster", DBNull.Value);
                            }



                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                finally
                {
                    connDest.Close();
                    connSource.Close();

                    //sr.Close();
                }
            }
            catch (Exception e)
            {
                result.FailWithMessage(e);
            }

            return(result);
        }