private List <Blabber> RetrieveMyHecklers(DbConnection connect, string username) { var hecklers = new List <Blabber>(); var sqlMyHecklers = "SELECT users.username, users.blab_name, users.created_at, users.picture_name " + "FROM users LEFT JOIN listeners ON users.username = listeners.listener " + "WHERE listeners.blabber=@blabber AND listeners.status='Active'"; using (var profile = connect.CreateCommand()) { profile.CommandText = sqlMyHecklers; profile.Parameters.Add(new SqlParameter { ParameterName = "@blabber", Value = username }); using (var myHecklersResults = profile.ExecuteReader()) { hecklers = new List <Blabber>(); while (myHecklersResults.Read()) { var heckler = new Blabber { UserName = myHecklersResults.GetString(0), BlabName = myHecklersResults.GetString(1), CreatedDate = myHecklersResults.GetDateTime(2), PictureName = myHecklersResults.GetString(3) }; hecklers.Add(heckler); } } } return(hecklers); }
private BlabViewModel CreateBlabViewModel(int blabId) { var blabViewModel = new BlabViewModel { BlabId = blabId }; using (var dbContext = new BlabberDB()) { dbContext.Database.Connection.Open(); var blabDetails = dbContext.Database.Connection.CreateCommand(); blabDetails.CommandText = sqlBlabDetails; blabDetails.Parameters.Add(new SqlParameter("@blabId", blabId)); var blabDetailsResults = blabDetails.ExecuteReader(); // If there is a record... if (blabDetailsResults.Read()) { // Get the blab contents blabViewModel.Content = blabDetailsResults.GetString(0); blabViewModel.BlabName = blabDetailsResults.GetString(1); blabDetailsResults.Close(); // Now lets get the comments... var blabComments = dbContext.Database.Connection.CreateCommand(); blabComments.CommandText = sqlBlabComments; blabComments.Parameters.Add(new SqlParameter("@blabId", blabId)); var blabCommentsResults = blabComments.ExecuteReader(); var comments = new List <Comment>(); while (blabCommentsResults.Read()) { var author = new Blabber { UserName = blabCommentsResults.GetString(0), BlabName = blabCommentsResults.GetString(1) }; var comment = new Comment { Content = blabCommentsResults.GetString(2), TimeStamp = blabCommentsResults.GetDateTime(3), Author = author }; comments.Add(comment); } blabViewModel.Comments = comments; } } return(blabViewModel); }
private BlabbersViewModel PopulateBlabbersViewModel(string sort, string username) { var viewModel = new BlabbersViewModel(); var blabbersList = new List <Blabber>(); try { using (var dbContext = new BlabberDB()) { dbContext.Database.Connection.Open(); var blabbers = dbContext.Database.Connection.CreateCommand(); blabbers.CommandText = string.Format(sqlBlabbers, sort); blabbers.Parameters.Add(new SqlParameter("@username", username)); var blabbersResults = blabbers.ExecuteReader(); while (blabbersResults.Read()) { var blabber = new Blabber { UserName = blabbersResults.GetString(0), BlabName = blabbersResults.GetString(1), CreatedDate = blabbersResults.GetDateTime(2), Subscribed = blabbersResults.GetInt32(3) == 1, NumberListeners = blabbersResults.GetInt32(4), NumberListening = blabbersResults.GetInt32(5) }; blabbersList.Add(blabber); } } viewModel.Blabbers = blabbersList; } catch (DbException ex) { viewModel.Error = ex.Message; } return(viewModel); }
public ActionResult GetFeed() { if (IsUserLoggedIn() == false) { return(RedirectToLogin(HttpContext.Request.RawUrl)); } var username = GetLoggedInUsername(); // Find the Blabs that this user listens to var feedBlabs = new List <Blab>(); using (var dbContext = new BlabberDB()) { dbContext.Database.Connection.Open(); var listeningBlabs = dbContext.Database.Connection.CreateCommand(); listeningBlabs.CommandText = string.Format(sqlBlabsForMe, 0, 10); listeningBlabs.Parameters.Add(new SqlParameter("@listener", username)); var blabsForMeResults = listeningBlabs.ExecuteReader(); while (blabsForMeResults.Read()) { var author = new Blabber { UserName = blabsForMeResults.GetString(0), BlabName = blabsForMeResults.GetString(1) }; var post = new Blab { Id = blabsForMeResults.GetInt32(5), Content = blabsForMeResults.GetString(2), PostDate = blabsForMeResults.GetDateTime(3), CommentCount = blabsForMeResults.GetInt32(4), Author = author }; feedBlabs.Add(post); } } // Find Blabs by this user var myBlabs = new List <Blab>(); using (var dbContext = new BlabberDB()) { dbContext.Database.Connection.Open(); var listeningBlabs = dbContext.Database.Connection.CreateCommand(); listeningBlabs.CommandText = sqlBlabsByMe; listeningBlabs.Parameters.Add(new SqlParameter { ParameterName = "@username", Value = username }); var blabsByMeResults = listeningBlabs.ExecuteReader(); while (blabsByMeResults.Read()) { var post = new Blab { Id = blabsByMeResults.GetInt32(3), Content = blabsByMeResults.GetString(0), PostDate = blabsByMeResults.GetDateTime(1), CommentCount = blabsByMeResults.GetInt32(2), }; myBlabs.Add(post); } } return(View(new FeedViewModel { BlabsByMe = myBlabs, BlabsByOthers = feedBlabs, CurrentUser = username } )); }