public static IList <Bookmark> FindBookmarks(string username, IList <string> tags, int maxResults)
{
BookmarksEntities context = new BookmarksEntities();
var bookmarksQuery =
from b in context.Bookmarks
select b;
if (username != null)
{
bookmarksQuery = bookmarksQuery.Where(
b => b.User.Username == username);
}
foreach (var tag in tags)
{
bookmarksQuery = bookmarksQuery.Where(
b => b.Tags.Any(t => t.Text == tag));
}
bookmarksQuery = bookmarksQuery.OrderBy(b => b.URL);
bookmarksQuery = bookmarksQuery.Take(maxResults);
return(bookmarksQuery.ToList());
}
public static void AddBookmark(string username, string title, string url, string[] tags, string notes)
{
//Console.WriteLine("{0} {1} {2} {3} {4}", username, title, url, string.Join(", ", tags), notes);
BookmarksEntities context = new BookmarksEntities();
Bookmark newBookmark = new Bookmark();
newBookmark.User = CreateOrLoadUser(context, username);
newBookmark.Title = title;
newBookmark.URL = url;
newBookmark.Notes = notes;
foreach (var tagName in tags)
{
Tag tag = CreateOrLoadTag(context, tagName);
newBookmark.Tags.Add(tag);
}
//dopulnitelnite tagotve------------------------
//string[] titleTags = Regex.Split(title, @"\W+");
string[] titleTags = Regex.Split(title, @"[,'!\. ;?-]+");
foreach (var titleTagName in titleTags)
{
Tag titleTag = CreateOrLoadTag(context, titleTagName);
newBookmark.Tags.Add(titleTag);
}
//dopulnitelnite tagotve-----------------------
context.Bookmarks.Add(newBookmark);
context.SaveChanges();
}
public static IEnumerable <Bookmark> FindBookmarksByUsernameAndTag(BookmarksEntities context, string username, string tag)
{
IEnumerable <Bookmark> bookmarks = new List <Bookmark>();
using (context)
{
var bookmarksQuery =
from b in context.Bookmarks
select b;
if (username != null)
{
bookmarksQuery =
from b in context.Bookmarks
where b.User.Username == username
select b;
}
if (tag != null)
{
bookmarksQuery = bookmarksQuery.Where(
b => b.Tags.Any(t => t.Text == tag));
}
bookmarksQuery = bookmarksQuery.OrderBy(b => b.URL);
bookmarks = bookmarksQuery.ToList();
}
return(bookmarks);
}
private static void ClearTables()
{
string clearTablesSql =
@"DELETE FROM Bookmarks_Tags;
DELETE FROM Tags;
DELETE FROM Bookmarks;
DELETE FROM Users;";
BookmarksEntities context = new BookmarksEntities();
context.Database.ExecuteSqlCommand(clearTablesSql);
}
public static Dictionary <string, Tag> GetExistingTags(BookmarksEntities context)
{
Dictionary <string, Tag> existingTags = new Dictionary <string, Tag>();
foreach (var tag in context.Tags)
{
existingTags.Add(tag.Text, tag);
}
return(existingTags);
}
private static void ConnectBookmarksWithTags()
{
BookmarksEntities context = new BookmarksEntities();
int maxTagId = context.Database.SqlQuery<int>(
"SELECT MAX(TagID) FROM Tags").First();
int minTagId = maxTagId - TAGS_COUNT + 1;
int maxBookmarkId = context.Database.SqlQuery<int>(
"SELECT MAX(BookmarkID) FROM Bookmarks").First();
int minBookmarkId = maxBookmarkId - BOOKMARKS_COUNT + 1;
Random rnd = new Random();
StringBuilder insertSql = new StringBuilder();
insertSql.Append(
"INSERT Into Bookmarks_Tags(BookmarkId, TagId) " +
"VALUES ");
int insertCount = 0;
int tagId = minTagId;
for (int bookmarkId = minBookmarkId; bookmarkId <= maxBookmarkId; bookmarkId++)
{
int tagsCount = rnd.Next(1, 10);
for (int i = 0; i < tagsCount; i++)
{
string valueToInsert = String.Format(
"({0},{1}),", bookmarkId, tagId);
insertSql.Append(valueToInsert);
insertCount++;
if (insertCount > MAX_SQL_INSERT_ROWS)
{
insertSql.Length--;
context.Database.ExecuteSqlCommand(
insertSql.ToString());
insertSql.Clear();
insertSql.Append(
"INSERT Into Bookmarks_Tags(BookmarkId, TagId) " +
"VALUES ");
insertCount = 0;
}
tagId++;
if (tagId > maxTagId)
{
tagId = minTagId;
}
}
}
insertSql.Length--;
context.Database.ExecuteSqlCommand(
insertSql.ToString());
}
private static Tag CreateOrLoadTag(
BookmarksEntities context, string tagName)
{
Tag existingTag =
(from t in context.Tags
where t.Name.ToLower() == tagName.ToLower()
select t).FirstOrDefault();
if (existingTag != null)
{
return existingTag;
}
Tag newTag = new Tag();
newTag.Name = tagName.ToLower();
context.Tags.Add(newTag);
context.SaveChanges();
return newTag;
}
private static User CreateOrLoadUser(BookmarksEntities context, string username)
{
//BookmarksEntities context = new BookmarksEntities();
User existingUser =
(from u in context.Users
where u.Username.ToLower() == username.ToLower()
select u).FirstOrDefault();
if (existingUser != null)
{
return existingUser;
}
User newUser = new User();
newUser.Username = username;
context.Users.Add(newUser);
return newUser;
}
private static User CreateOrLoadUser(
BookmarksEntities context, string username)
{
User existingUser =
(from u in context.Users
where u.Username.ToLower() == username.ToLower()
select u).FirstOrDefault();
if (existingUser != null)
{
return(existingUser);
}
User newUser = new User();
newUser.Username = username;
context.Users.Add(newUser);
context.SaveChanges();
return(newUser);
}
private static Tag CreateOrLoadTag(
BookmarksEntities context, string tagName)
{
Tag existingTag =
(from t in context.Tags
where t.Name.ToLower() == tagName.ToLower()
select t).FirstOrDefault();
if (existingTag != null)
{
return(existingTag);
}
Tag newTag = new Tag();
newTag.Name = tagName.ToLower();
context.Tags.Add(newTag);
context.SaveChanges();
return(newTag);
}
public static User CreateOrLoadUser(BookmarksEntities context, string username, out bool wasCreated)
{
wasCreated = false;
User user =
(from u in context.Users
where u.Username == username
select u).FirstOrDefault();
if (user == null)
{
wasCreated = true;
User newUser = new User();
newUser.Username = username;
return(newUser);
}
else
{
return(user);
}
}
public static void AddBookmark(string username,
string title, string url, string[] tags, string notes)
{
BookmarksEntities context = new BookmarksEntities();
Bookmark newBookmark = new Bookmark();
newBookmark.User = CreateOrLoadUser(context, username);
newBookmark.Title = title;
newBookmark.URL = url;
newBookmark.Notes = notes;
foreach (var tagName in tags)
{
Tag tag = CreateOrLoadTag(context, tagName);
newBookmark.Tags.Add(tag);
}
string[] titleTags = Regex.Split(title, @"[,'!\. ;?-]+");
foreach (var titleTagName in titleTags)
{
Tag titleTag = CreateOrLoadTag(context, titleTagName);
newBookmark.Tags.Add(titleTag);
}
context.Bookmarks.Add(newBookmark);
context.SaveChanges();
}
public static IList<Bookmark> FindBookmarksByUsernameAndTag(
string username, string tag)
{
BookmarksEntities context = new BookmarksEntities();
var bookmarksQuery =
from b in context.Bookmarks
select b;
if (username != null)
{
bookmarksQuery =
from b in context.Bookmarks
where b.User.Username == username
select b;
}
if (tag != null)
{
bookmarksQuery = bookmarksQuery.Where(
b => b.Tags.Any(t => t.Name == tag));
}
bookmarksQuery = bookmarksQuery.OrderBy(b => b.URL);
return bookmarksQuery.ToList();
}
public static IList <Bookmark> FindBookmarksByUsernameAndTag(
string username, string tag)
{
BookmarksEntities context = new BookmarksEntities();
var bookmarksQuery =
from b in context.Bookmarks
select b;
if (username != null)
{
bookmarksQuery =
from b in context.Bookmarks
where b.User.Username == username
select b;
}
if (tag != null)
{
bookmarksQuery = bookmarksQuery.Where(
b => b.Tags.Any(t => t.Name == tag));
}
bookmarksQuery = bookmarksQuery.OrderBy(b => b.URL);
return(bookmarksQuery.ToList());
}
public static void AddBookmark(string username,
string title, string url, string[] tags, string notes)
{
BookmarksEntities context = new BookmarksEntities();
Bookmark newBookmark = new Bookmark();
newBookmark.User = CreateOrLoadUser(context, username);
newBookmark.Title = title;
newBookmark.URL = url;
newBookmark.Notes = notes;
foreach (var tagName in tags)
{
Tag tag = CreateOrLoadTag(context, tagName);
newBookmark.Tags.Add(tag);
}
string[] titleTags = Regex.Split(title, @"[,'!\. ;?-]+");
foreach (var titleTagName in titleTags)
{
Tag titleTag = CreateOrLoadTag(context, titleTagName);
newBookmark.Tags.Add(titleTag);
}
context.Bookmarks.Add(newBookmark);
context.SaveChanges();
}
private static void InsertDuplicatedBookmarks(int minCount)
{
BookmarksEntities context = new BookmarksEntities();
int counter = 1;
while (true)
{
int bookmarksCount = context.Bookmarks.Count();
if (bookmarksCount > minCount)
{
return;
}
string command =
String.Format(
"INSERT INTO Bookmarks(Title, Url, UserId) " +
"SELECT Title + '{0}', Url + '{1}', UserId " +
"FROM Bookmarks", counter, counter * 10);
context.Database.ExecuteSqlCommand(command);
counter++;
}
}
private static void InsertTags(int count)
{
BookmarksEntities context = new BookmarksEntities();
int tagsCount = context.Tags.Count();
StringBuilder insertTagsSql = new StringBuilder();
insertTagsSql.Append(
"INSERT Into Tags(Name) VALUES ");
int insertCount = 0;
for (int i = tagsCount + 1; i <= tagsCount + count; i++)
{
string tagName = "('tag" + i + "'),";
insertTagsSql.Append(tagName);
insertCount++;
if (insertCount > MAX_SQL_INSERT_ROWS)
{
insertTagsSql.Length--;
context.Database.ExecuteSqlCommand(
insertTagsSql.ToString());
insertTagsSql.Clear();
insertTagsSql.Append(
"INSERT Into Tags(Name) VALUES ");
insertCount = 0;
}
}
insertTagsSql.Length--;
context.Database.ExecuteSqlCommand(
insertTagsSql.ToString());
}
private static void InsertBookmarks(int count)
{
BookmarksEntities context = new BookmarksEntities();
var userIDs =
context.Users.Select(u => u.UserId).ToList();
for (int i = 0; i < count; i++)
{
Bookmark bookmark = new Bookmark();
bookmark.Title = "Title" + i;
bookmark.URL = "http://telerikacademy.com/bookmarks/" + i;
bookmark.UserId = userIDs[i % userIDs.Count];
context.Bookmarks.Add(bookmark);
}
context.SaveChanges();
}
//for task 4 ----------------------------------------------------------------
public static IList<Bookmark> FindBookmarksByUsernameAndTag(string username, string tag)
{
BookmarksEntities context = new BookmarksEntities();
var bookmarkQuery =
from b in context.Bookmarks
select b;
if (username != null)
{
bookmarkQuery =
from b in context.Bookmarks
where b.User.Username == username
select b;
//bookmarkQuery = bookmarkQuery.Where(b => b.User.Username == username);
}
if (tag != null)
{
bookmarkQuery = bookmarkQuery.Where(b => b.Tags.Any(t => t.Name == tag));
}
bookmarkQuery = bookmarkQuery.OrderBy(b => b.URL);
//for dummies-------------------------------------------------
//List<Bookmark> results = new List<Bookmark>();
//foreach (var b in bookmarkQuery)
//{
// if (b.User.Username == username)
// {
// bool foundTag = false;
// foreach (var t in b.Tags)
// {
// if (t.Name == tag)
// {
// foundTag = true;
// }
// }
// if (foundTag)
// {
// results.Add(b);
// }
// }
//}
return bookmarkQuery.ToList();
}
//for task 4 -------------------------------------------------------------------
//for task 5 -----------------------------------------------------------------
public static IList<Bookmark> FindBookmarks(
string username, IList<string> tags, int maxResults)
{
BookmarksEntities context = new BookmarksEntities();
var bookmarksQuery =
from b in context.Bookmarks.Include("User").Include("Tags")
select b;
if (username != null)
{
bookmarksQuery = bookmarksQuery.Where(
b => b.User.Username == username);
}
foreach (var tag in tags)
{
bookmarksQuery = bookmarksQuery.Where(
b => b.Tags.Any(t => t.Name == tag));
}
bookmarksQuery = bookmarksQuery.OrderBy(b => b.URL);
bookmarksQuery = bookmarksQuery.Take(maxResults);
return bookmarksQuery.ToList();
}
// Problem 3
public static void ImportXmlIntoDatabase(string xmlPath)
{
TransactionScope tranScope = new TransactionScope(TransactionScopeOption.Required,
new TransactionOptions()
{
IsolationLevel = IsolationLevel.RepeatableRead
});
using (tranScope)
{
BookmarksEntities context = new BookmarksEntities();
using (context)
{
XmlDocument doc = new XmlDocument();
doc.Load(xmlPath);
string xpathQuery = "bookmarks/bookmark";
XmlNodeList bookmarksList = doc.SelectNodes(xpathQuery);
foreach (XmlNode node in bookmarksList)
{
string username = node.SelectSingleNode("username").InnerText;
string title = node.SelectSingleNode("title").InnerText;
string url = node.SelectSingleNode("url").InnerText;
string notes = null;
string[] tags = { };
string[] tagsFromTitle = { };
HashSet <string> allTags = new HashSet <string>();
XmlNode notesNode = node.SelectSingleNode("notes");
if (notesNode != null)
{
notes = notesNode.InnerText;
}
XmlNode tagsNode = node.SelectSingleNode("tags");
if (tagsNode != null)
{
string tagsAsText = tagsNode.InnerText;
tags = tagsAsText.Split(new string[] { ", " }, StringSplitOptions.RemoveEmptyEntries);
foreach (var tag in tags)
{
allTags.Add(tag.ToLower().Trim());
}
}
tagsFromTitle = title.Split(new char[] { ',', '!', '.', ';', '?', '-', ' ' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var tagFromTitle in tagsFromTitle)
{
allTags.Add(tagFromTitle.ToLower().Trim());
}
bool wasCreated = false;
User user = CreateOrLoadUser(context, username, out wasCreated);
if (wasCreated)
{
context.Users.Add(user);
}
Bookmark bookmark = new Bookmark();
bookmark.User = user;
bookmark.Notes = notes;
bookmark.Title = title;
bookmark.URL = url;
Dictionary <string, Tag> existingTags = GetExistingTags(context);
foreach (var tag in allTags)
{
if (!existingTags.ContainsKey(tag))
{
Tag newTag = new Tag();
newTag.Text = tag;
bookmark.Tags.Add(newTag);
context.Tags.Add(newTag);
}
else
{
bookmark.Tags.Add(existingTags[tag]);
}
}
context.Bookmarks.Add(bookmark);
context.SaveChanges();
}
}
tranScope.Complete();
}
}
