public static void AddBookmarks(string username, string title, string url, string[] tags, string notes)
{
var dbCon = new BookmarksEntities();
using (dbCon)
{
var bookmark = new Bookmark();
bookmark.Notes = notes;
bookmark.URL = url;
bookmark.Title = title;
bookmark.User = CreateOrLoadUser(dbCon, username);
foreach (var tagName in tags)
{
var tag = CreateOrLoadTag(dbCon, tagName);
bookmark.Tags.Add(tag);
}
var titleTags = Regex.Split(title, @"[ ,-\.;!?]+");
foreach (var titleTag in titleTags)
{
var tag = CreateOrLoadTag(dbCon, titleTag);
bookmark.Tags.Add(tag);
}
dbCon.Bookmarks.Add(bookmark);
dbCon.SaveChanges();
}
}
private static void InsertUsers(int usersCount)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO Users (Username) VALUES ");
int count = 0;
while (count < UsersCount)
{
for (int i = count; i < MaxSqlInsrtRows + count; i++)
{
sqlCommand.Append("('user" + i + "'),");
}
sqlCommand.Length--;
BookmarksEntities context = new BookmarksEntities();
using (context)
{
context.Database.ExecuteSqlCommand(sqlCommand.ToString());
sqlCommand.Clear();
sqlCommand.Append("INSERT INTO Users (Username) VALUES ");
}
count += 500;
}
}
public static void ExecureQueriesFromXml(string filePath)
{
BookmarksEntities context = new BookmarksEntities();
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load("../../query.xml");
string pathQuery = "/query";
XmlNodeList xmlQuery = xmlDocument.SelectNodes(pathQuery);
foreach (XmlNode node in xmlQuery)
{
string username = node.GetInnerText("username");
string tag = node.GetInnerText("tag");
var query =
from b in context.Bookmarks
select b;
if (username != null)
{
query = context.Bookmarks.Where(x => x.User.Username == username);
}
query = query.Where(b => b.Tags.Any(t => t.TagName == tag));
query = query.OrderBy(x => x.Url);
foreach (var bookmark in query.ToList())
{
Console.WriteLine(bookmark.Url);
}
}
}
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 ClearTables()
{
string clearTablesSql =
@"DELETE FROM Bookmarks_Tags;
DELETE FROM Tags;
DELETE FROM Bookmarks;
DELETE FROM Users;" ;
BookmarksEntities context = new BookmarksEntities();
context.Database.ExecuteSqlCommand(clearTablesSql);
}
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 void ClearTables()
{
BookmarksEntities context = new BookmarksEntities();
string commandText =
"DELETE FROM Bookmarks_Tags " +
"DELETE FROM Bookmarks " +
"DELETE FROM Tags " +
"DELETE FROM Users " +
"DBCC CHECKIDENT (Bookmarks, reseed, 0) " +
"DBCC CHECKIDENT (Tags, reseed, 0) " +
"DBCC CHECKIDENT (Users, reseed, 0)";
context.Database.ExecuteSqlCommand(commandText);
}
private static User CreateOrLoadUser(BookmarksEntities context, string username)
{
var existingUser = (from u in context.Users
where u.Username.ToLower() == username.ToLower()
select u).FirstOrDefault();
if (existingUser != null)
{
return existingUser;
}
var newUser = new User();
newUser.Username = username;
context.Users.Add(newUser);
context.SaveChanges();
return newUser;
}
private static Tag CreateOrLoadTag(BookmarksEntities context, string tagName)
{
var existingTag = (from t in context.Tags
where t.Name.ToLower() == tagName.ToLower()
select t).FirstOrDefault();
if (existingTag != null)
{
return existingTag;
}
var newTag = new Tag();
newTag.Name = tagName.ToLower();
context.Tags.Add(newTag);
context.SaveChanges();
return newTag;
}
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();
}
private static Tag CreateOrLoadTag(BookmarksEntities context, string tagName)
{
var tag = context.Tags.FirstOrDefault(x => x.TagName == tagName);
if (tag != null)
{
return(tag);
}
var newTag = new Tag()
{
TagName = tagName
};
context.Tags.Add(newTag);
context.SaveChanges();
return(newTag);
}
private static User CreateOrLoadUser(BookmarksEntities context, string username)
{
var user = context.Users.FirstOrDefault(x => x.Username == username);
if (user != null)
{
return(user);
}
var newUser = new User()
{
Username = username
};
context.Users.Add(newUser);
context.SaveChanges();
return(newUser);
}
static void Main(string[] args)
{
BookmarksEntities context = new BookmarksEntities();
using (context)
{
string simpleSearchXmlPath = @"..\..\simple-query.xml";
XmlDocument doc = new XmlDocument();
doc.Load(simpleSearchXmlPath);
string xpathQuery = "query";
XmlNode bookmarkNode = doc.SelectSingleNode(xpathQuery);
string username = null;
XmlNode usernameNode = bookmarkNode.SelectSingleNode("username");
if (usernameNode != null)
{
username = usernameNode.InnerText;
}
string tag = null;
XmlNode tagNode = bookmarkNode.SelectSingleNode("tag");
if (tagNode != null)
{
tag = tagNode.InnerText;
}
IEnumerable <Bookmark> bookmarks = BookmarksDAO.FindBookmarksByUsernameAndTag(context, username, tag);
if (bookmarks.Count() > 0)
{
foreach (var bookmark in bookmarks)
{
Console.WriteLine(bookmark.URL);
}
}
else
{
Console.WriteLine("Nothing found");
}
}
}
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++;
}
}
public static void InsertData(string username, string title, string url, string[] tags, string notes)
{
using (var scope = new TransactionScope(TransactionScopeOption.Required,
new TransactionOptions {
IsolationLevel = IsolationLevel.RepeatableRead
}))
{
var context = new BookmarksEntities();
User user = CreateOrLoadUser(context, username);
var bookmark = new Bookmark {
User = user, Title = title, Notes = notes, Url = url
};
foreach (var tagName in tags)
{
var tag = CreateOrLoadTag(context, tagName);
bookmark.Tags.Add(tag);
}
context.Bookmarks.Add(bookmark);
context.SaveChanges();
}
}
public static void Main(string[] args)
{
BookmarksEntities context = new BookmarksEntities();
using (context)
{
string fileName = @"..\..\search-results.xml";
using (XmlTextWriter writer = new XmlTextWriter(fileName, Encoding.UTF8))
{
writer.Formatting = Formatting.Indented;
writer.IndentChar = '\t';
writer.Indentation = 1;
writer.WriteStartDocument();
writer.WriteStartElement("search-results");
string complexSearchXmlPath = @"..\..\complex-query.xml";
ProcessSearchQueries(writer, complexSearchXmlPath);
writer.WriteEndDocument();
}
}
}
