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