public FavoriteList GetList(string uid, int listId) { FavoriteList list = null; using (var cn = new SqlConnection(connectionString)) { cn.Open(); if (!DoesListBelongToUser(uid, listId, cn)) { return(null); } using (var cmd = cn.CreateCommand()) { cmd.CommandText = "SELECT ID, Name, Comment, (SELECT COUNT(*) FROM Favorite WHERE (Favorite.List=FavoriteList.ID)) as ChildCount " + "FROM FavoriteList " + "WHERE ID = @p1"; cmd.Parameters.Add(new SqlParameter { Value = listId, ParameterName = "p1", SqlDbType = SqlDbType.NVarChar }); using (var reader = cmd.ExecuteReader()) { if (reader.Read()) { list = new FavoriteList { Id = Convert.ToInt32(reader["ID"]), Name = reader["Name"] as string, Comment = reader["Comment"] as string, NumberOfItems = Convert.ToInt32(reader["ChildCount"]) }; } } } } return(list); }
private void MigrateFavoritesDetails(FavoriteList favoriteList, string uid, string userEmailAddress, string source) { using (var cn = new SqlConnection(connectionString)) { // Read all favorites lists var getListsSql = "SELECT email, quelle, liste, verzeichnungseinheit_id " + "FROM TempMigrationWorkspace " + "WHERE email = @email AND quelle = @source and liste = @liste"; var da = new SqlDataAdapter(getListsSql, cn); da.SelectCommand.AddParameter("email", SqlDbType.NVarChar, userEmailAddress); da.SelectCommand.AddParameter("source", SqlDbType.NVarChar, source); da.SelectCommand.AddParameter("liste", SqlDbType.NVarChar, favoriteList.Name); var dt = new DataTable(); da.Fill(dt); var existingItems = GetFavoritesContainedOnList(uid, favoriteList.Id).ToList(); foreach (var row in dt.AsEnumerable()) { var veId = Convert.ToInt32(row["verzeichnungseinheit_id"]); var existingItem = existingItems.Cast <VeFavorite>().FirstOrDefault(l => l.VeId == veId); if (existingItem == null) { var newItem = new VeFavorite { VeId = veId, Kind = FavoriteKind.Ve }; // Import the VE to this list Log.Information("Inserting Ve {veId} to list {name}", veId, favoriteList.Name); AddFavorite(uid, favoriteList.Id, newItem); } } } }