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