private static void AddBookmarksInDb(BookmarksDBEntities db, string username, string title, string url, string[] tags, string notes)
{
var user = (from users in db.Users
where users.Username == username
select users).FirstOrDefault();
if (user == null)
{
user = new User() { Username = username };
db.Users.Add(user);
db.SaveChanges();
}
Tag[] tagsList = { };
if (tags != null)
{
tagsList = new Tag[tags.Length];
for (int i = 0; i < tags.Length; i++)
{
Tag currentTag = new Tag() { TagName = tags[i] };
db.Tags.Add(currentTag);
tagsList[i] = currentTag;
db.SaveChanges();
}
}
var bookmark = new Bookmark() { User = user, Title = title, URL = url, Notes = notes, Tags = tagsList };
db.Bookmarks.Add(bookmark);
db.SaveChanges();
}
private static void SearchByUserAndTag(BookmarksDBEntities db, string username, string tag)
{
var resultsBytag = from user in db.Users
join bookmark in db.Bookmarks on user.UserID equals bookmark.UserId
where bookmark.Tags.Any(t => t.TagName == tag)
orderby bookmark.URL
select new { User = user.Username, bookmark.URL };
if (username != null)
{
resultsBytag = from res in resultsBytag
where res.User == username
select res;
}
if (resultsBytag == null)
{
Console.WriteLine("Nothing found");
}
else
{
foreach (var url in resultsBytag)
{
Console.WriteLine(url.URL);
}
}
}
public static void SearchByUsernameAndTag()
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load("../../../SearchInDb/simple-query.xml");
var queries = xmlDoc.SelectNodes("/query");
using (var db = new BookmarksDBEntities())
{
foreach (XmlNode query in queries)
{
string username = query["username"].InnerText;
string tag = query["tag"].InnerText;
SearchByUserAndTag(db, username, tag);
}
}
}
public static void ImportToDb()
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load("../../../ImportDbFromXml/bookmarks.xml");
var bookmarks = xmlDoc.DocumentElement.ChildNodes;
TransactionScope tran = new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions() {
IsolationLevel = IsolationLevel.RepeatableRead });
using (tran)
{
using (var db = new BookmarksDBEntities())
{
foreach (XmlNode bookmark in bookmarks)
{
string username = bookmark["username"].InnerText;
string title = bookmark["title"].InnerText;
string url = bookmark["url"].InnerText;
string[] tags = { };
if (bookmark.SelectSingleNode("tags") != null)
{
tags = bookmark["tags"].InnerText.Split(new char[] { ' ', ',' }, StringSplitOptions.RemoveEmptyEntries);
}
string notes = null;
if (bookmark.SelectSingleNode("notes") != null)
{
notes = bookmark["notes"].InnerText;
}
AddBookmarksInDb(db, username, title, url, tags, notes);
}
tran.Complete();
}
}
}
private static void SearchResults(XmlTextWriter writer, string username, string[] tags, int maxCount)
{
using (var db = new BookmarksDBEntities())
{
var queryResult = from user in db.Bookmarks.Include("User").Include("Tags")
select user;
if (username != null)
{
queryResult = from user in queryResult
where user.User.Username == username
select user;
}
foreach (var tag in tags)
{
queryResult = from ta in queryResult
where ta.Tags.Any(t => t.TagName == tag)
select ta;
}
queryResult = queryResult.Take(maxCount);
var result = queryResult.ToList();
WriteInXml(result, writer);
}
}