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