/// <summary>
/// Updates the status of an event post based its id
/// </summary>
/// <param name="postStatus"></param>
/// <param name="eventId"></param>
public void UpdateEventPostStatus(int postStatus, int eventId)
{
SqlStatement statement = new SqlStatement(StoredProcedures.UpdateEventPostStatus);
statement.AddParameter("EventId", eventId);
statement.AddParameter("PostStatus", postStatus);
db.ExecuteStoredProc(statement);
}
/// <summary>
/// Gets all of the post for a subcategory
/// </summary>
/// <param name="category">the information about the item category</param>
/// <returns>a collection of post previews for that subcategory</returns>
public IEnumerable <PostPreview> GetAllPostsForSubCategory(Category category)
{
SqlStatement statement = new SqlStatement(StoredProcedures.GetAllItemPostsForSubCategory);
statement.AddParameter("CategoryId", category.CategoryId);
statement.AddParameter("SubCategoryId", category.SubCategoryId);
return(db.ExecuteStoredProc(statement, PostPreviewMapper));
}
/// <summary>
/// Updates the number of remaining wins for an event.
/// </summary>
/// <param name="eventId">the id of the event</param>
/// <param name="remainingWins">the number of remaining winners for the event.</param>
public void UpdateRemainingWinnersForEvent(int eventId, int remainingWins)
{
SqlStatement statement = new SqlStatement(StoredProcedures.UpdateWinsRemaining);
statement.AddParameter("eventId", eventId);
statement.AddParameter("remainingWins", remainingWins);
dataRepository.ExecuteStoredProc(statement);
}
/// <summary>
/// Changes the status of a post to delivered.
/// </summary>
/// <param name="post">the post to be updated</param>
/// <param name="userName">the username of the individual who marked the item as delivered</param>
public void UpdatePostToDelivered(Post post, string userName)
{
SqlStatement statement = new SqlStatement(StoredProcedures.UpdatePostToDelivered);
statement.AddParameter("PostId", post.PostId);
statement.AddParameter("ModifiedBy", userName);
db.ExecuteStoredProc(statement);
}
/// <summary>
/// Updates the status of a post to active and sets a new expiration date.
/// </summary>
/// <param name="expirationDate">The date the post will become expired if it is not claimed</param>
/// <param name="postId">the id of the post to change status to active.</param>
/// <param name="userName">the username of the invidual who is repsting the item.</param>
public void UpdatePostToActiveAndUpdateExpirationDate(int postId, DateTime expirationDate, string userName)
{
SqlStatement statement = new SqlStatement(StoredProcedures.UpDatePostToActiveAndUpdateExpiration);
statement.AddParameter("PostId", postId);
statement.AddParameter("ExpirationDate", expirationDate);
statement.AddParameter("ModifiedBy", userName);
db.ExecuteStoredProc(statement);
}
/// <summary>
/// Insert pictures for an event
/// </summary>
/// <param name="picture"></param>
/// <returns>The new picture object that was created</returns>
public Picture InsertPicture(Picture picture)
{
SqlStatement statement = new SqlStatement(StoredProcedures.InsertPicture);
statement.AddParameter("PostId", picture.PostId);
statement.AddParameter("EventId", picture.EventId);
statement.AddParameter("PictureImagePath", picture.PictureImagePath);
return(db.ExecuteStoredProc(statement, PictureMapper).FirstOrDefault());
}
/// <summary>
/// Remove an image from a post
/// </summary>
/// <param name="picture">information about the image to be removed</param>
/// <returns>a list of all the removed images.</returns>
public List <Picture> RemovePicture(Picture picture)
{
SqlStatement statement = new SqlStatement(StoredProcedures.RemovePhoto);
statement.AddParameter("PostId", picture.PostId);
statement.AddParameter("PictureImagePath", picture.PictureImagePath);
statement.AddParameter("EventId", picture.EventId);
return(db.ExecuteStoredProc(statement, PictureMapper).ToList());
}
/// <summary>
/// Inserts a claim for a post.
/// </summary>
/// <param name="postClaim">information about the post claim.</param>
/// <returns>the claim that was inserted.</returns>
public PostClaim InsertPostClaim(PostClaim postClaim)
{
SqlStatement statement = new SqlStatement(StoredProcedures.InsertPostClaim);
statement.AddParameter("ClaimedPostId", postClaim.ClaimedPostId);
statement.AddParameter("ClaimerName", postClaim.ClaimerName);
statement.AddParameter("EmailAddress", postClaim.EmailAddress);
statement.AddParameter("DateClaimed", postClaim.DateClaimed);
return(db.ExecuteStoredProc(statement, PostClaimMapper).FirstOrDefault());
}
/// <summary>
/// Inserts an individual who was chosen to win tickets for an event.
/// </summary>
/// <param name="ticketWinner">Information about the individual who won tickets.</param>
public void InsertTicketWinner(TicketWinner ticketWinner)
{
SqlStatement statement = new SqlStatement(StoredProcedures.InsertTicketWinner);
statement.AddParameter("EventId", ticketWinner.EventId);
statement.AddParameter("WinnerEmailAddress", ticketWinner.EmployeeEmailAddress);
statement.AddParameter("WinnerChosenBy", ticketWinner.AdminUserName);
statement.AddParameter("WinnerUserName", ticketWinner.UserName);
dataRepository.ExecuteStoredProc(statement);
}
/// <summary>
/// Sends an email
/// </summary>
/// <param name="email">all of the information about sending the email.</param>
public void SendEmail(Email email)
{
SqlStatement statement = new SqlStatement(StoredProcedures.SendEmail);
statement.AddParameter("Profile_name", email.ProfileName);
statement.AddParameter("Recipients", email.Recipients);
statement.AddParameter("Copy_recipients", email.CcRecipients);
statement.AddParameter("Subject", email.Subject);
statement.AddParameter("Body", email.Body);
db.ExecuteStoredProc(statement);
}
private static void SaveToErrorQueue(int taskId, Exception caught)
{
const string sql =
"INSERT INTO dbo.TaskQueueError SELECT TaskQueueId, Action, Payload, @exception, Created, GETDATE() FROM dbo.TaskQueue WHERE TaskQueueId = @taskId";
using (var cmd = new SqlStatement(sql, Config.Settings.ConnectionString))
{
cmd.AddParameter("@taskId", taskId);
cmd.AddParameter("@exception", caught.ToString());
cmd.ExecuteNonQuery();
}
}
protected override void Flush(LogEntry logEntry)
{
using (var sql = new SqlStatement(
"INSERT INTO SysLog (Created, Priority, Tag, Msg) VALUES (@Created, @Priority, @tag, @msg)",
Config.Settings.ConnectionString))
{
sql.AddParameter("@Created", logEntry.Created);
sql.AddParameter("@Priority", logEntry.Priority);
sql.AddParameter("@Tag", logEntry.Tag);
sql.AddParameter("@Msg", logEntry.Message);
sql.ExecuteNonQuery();
}
}
public void Equals_MatchingSqlAndNonMatchingParamValues()
{
//---------------Set up test pack-------------------
var test1 = new SqlStatement(_connection, "test");
var test2 = new SqlStatement(_connection, "test");
test1.AddParameter("param0", 1);
test2.AddParameter("param0", 1);
test1.AddParameter("param1", 2);
test2.AddParameter("param1", 1);
//---------------Test Result -----------------------
Assert.AreNotEqual(test1, test2);
}
public void SetupTestFixture()
{
var config = new DatabaseConfig(DatabaseConfig.SqlServer, "test", "test", "test", "test", "1000");
_connection = new DatabaseConnectionFactory().CreateConnection(config);
_testStatement = new SqlStatement(_connection);
_rawStatement = "insert into tb1 (field1, field2, field3) values (@Param1, @Param2, @Param3)";
_testStatement.Statement.Append(_rawStatement);
_addedParameter = _testStatement.AddParameter("Param1", "12345");
_testStatement.AddParameter("Param2", "67890");
_testStatement.AddParameter("Param3", "13579");
_command = _connection.GetConnection().CreateCommand();
_testStatement.SetupCommand(_command);
}
public int Enqueue(string taskType, string payload)
{
const string sql =
"INSERT INTO TaskQueue (Action, Payload) OUTPUT INSERTED.TaskQueueId VALUES (@task, @payload)";
int taskId;
using (var cmd = new SqlStatement(sql, Config.Settings.ConnectionString))
{
cmd.AddParameter("@task", taskType);
cmd.AddParameter("@payload", payload);
taskId = (int)cmd.ExecuteScalar();
}
return(taskId);
}
public IEnumerable <Category> GetSubCategoriesByParentCategoryId(int categoryId)
{
SqlStatement statement = new SqlStatement(StoredProcedures.GetSubCategoriesByParentCategoryId);
statement.AddParameter("CategoryId", categoryId);
return(database.ExecuteStoredProc(statement, CategoryMapper));
}
public CurrentUser SelectUser(CurrentUser currentUser)
{
SqlStatement statement = new SqlStatement(StoredProcedures.SelectUser);
statement.AddParameter("loginName", currentUser.LoginName);
return(db.ExecuteStoredProc(statement, MapSecurityUser).FirstOrDefault());
}
/// <summary>
/// Gets a post by the post id
/// </summary>
/// <param name="post">information about the post</param>
/// <returns>a single post that matches the post id.</returns>
public Post GetPostByPostId(int postId)
{
SqlStatement statement = new SqlStatement(StoredProcedures.GetPostByPostId);
statement.AddParameter("PostId", postId);
return(db.ExecuteStoredProc(statement, PostMapperWithFullName).FirstOrDefault());
}
/// <summary>
/// Gets all of the posts that match search critera.
/// </summary>
/// <param name="search">the search parameter</param>
/// <returns>a collection of post previews that match the search parameter</returns>
public IEnumerable <PostPreview> LoadAllPostsForSearchStringFixed(string search)
{
SqlStatement statement = new SqlStatement(StoredProcedures.GetAllPostsBySearch);
statement.AddParameter("searchText", search);
return(db.ExecuteStoredProc(statement, PostPreviewMapper));
}
/// <summary>
/// Gets the images for a post.
/// </summary>
/// <param name="post">information about the post</param>
/// <returns>a list of images associated with a post.</returns>
public List <Picture> SelectPicturesForPost(int postId)
{
SqlStatement statement = new SqlStatement(StoredProcedures.SelectPicturesForPost);
statement.AddParameter("PostId", postId);
return(db.ExecuteStoredProc(statement, PictureMapper).ToList());
}
/// <summary>
/// Gets an event based on its event id
/// </summary>
/// <param name="eventId"></param>
/// <returns>Event data attached to an event id</returns>
public Event GetEventByEventId(int eventId)
{
SqlStatement statement = new SqlStatement(StoredProcedures.GetEventByEventId);
statement.AddParameter("EventId", eventId);
return(db.ExecuteStoredProc(statement, EventMapper).FirstOrDefault());
}
/// <summary>
/// Gets all post claims for an individual within 30 days.
/// </summary>
/// <param name="loginName">the login of the individual to get the claims for</param>
/// <returns>a list of all claims for an individual within 30 days</returns>
public List <PostClaim> SelectAllPostClaimsByUserWithinThirtyDays(string loginName)
{
SqlStatement statement = new SqlStatement(StoredProcedures.SelectAllPostClaimsByUserWithinThirtyDays);
statement.AddParameter("UserName", loginName);
return(db.ExecuteStoredProc(statement, PostClaimMapper).ToList());
}
/// <summary>
/// Get the latest post by a user
/// </summary>
/// <param name="username">the login of the individual to use to look for a post</param>
/// <returns>the latest post of an individual</returns>
public Post GetLatestPostByUser(string username)
{
SqlStatement statement = new SqlStatement(StoredProcedures.GetLatestPostByUser);
statement.AddParameter("Username", username);
return(db.ExecuteStoredProc(statement, PostMapperWithFullName).FirstOrDefault());
}
/// <summary>
/// Updates a previously existing post.
/// </summary>
/// <param name="post">the post to be modified</param>
/// <returns>the modified post.</returns>
public Post UpdatePost(Post post)
{
SqlStatement statement = new SqlStatement(StoredProcedures.UpdatePost);
statement.AddParameter("PostId", post.PostId);
statement.AddParameter("PostTitle", post.PostTitle);
statement.AddParameter("PostDescription", post.PostDescription);
statement.AddParameter("DatePosted", post.DatePosted);
statement.AddParameter("PostStatus", post.PostStatus);
statement.AddParameter("ExpirationDate", post.ExpirationDate);
statement.AddParameter("CategoryID", post.CategoryId);
statement.AddParameter("SubCategoryId", post.SubCategoryId);
statement.AddParameter("PostedBy", post.PostedBy);
statement.AddParameter("PurposeTypeId", post.PostPurpose);
return(db.ExecuteStoredProc(statement, PostMapper).FirstOrDefault());
}
/// <summary>
/// Gets photos from the picture table for an event
/// </summary>
/// <param name="eventId"></param>
/// <returns>Image path and event id</returns>
public List <Picture> GetEventPostPictures(int eventId)
{
SqlStatement statement = new SqlStatement(StoredProcedures.GetEventPostPictures);
statement.AddParameter("EventId", eventId);
return(db.ExecuteStoredProc(statement, PictureMapper).ToList());
}
/// <summary>
/// Gets all of the non-winners for an event. (the individuals who have raised their hand but have not won.)
/// </summary>
/// <param name="eventId">the id of the event</param>
/// <returns>A list of email addresses for all of the non-winners for an event.</returns>
public List <string> GetAllNonWinnersForEvent(int eventId)
{
SqlStatement statement = new SqlStatement(StoredProcedures.GetAllNonWinnersForEvent);
statement.AddParameter("eventId", eventId);
return(db.ExecuteStoredProc(statement, MapGetAllNonWinnersForEvent).ToList());
}
/// <summary>
/// Selects all of the posts by a users login name
/// </summary>
/// <param name="loginName">the login name of the user whose posts we are looking for</param>
/// <returns>a list of all posts by the individual</returns>
public List <Post> SelectAllPostsByUser(string loginName)
{
SqlStatement statement = new SqlStatement(StoredProcedures.SelectAllPostsByUser);
statement.AddParameter("UserName", loginName);
return(db.ExecuteStoredProc(statement, PostMapper).ToList());
}
/// <summary>
/// Get all ticket claims from the database based on the event id
/// </summary>
/// <param name="eventId">The id of the event to get the ticket claims for.</param>
/// <returns>A list of ticket claims</returns>
public List <TicketClaims> GetTicketClaimsByEventId(int eventId)
{
SqlStatement statement = new SqlStatement(StoredProcedures.GetTicketClaimsByEventId);
statement.AddParameter("EventId", eventId);
return(dataRepository.ExecuteStoredProc(statement, TicketClaimsMapper).ToList());
}
/// <summary>
/// Gets all the events from database the have been marked with a category id
/// </summary>
/// <param name="categoryId"></param>
/// <returns>All events in a certain category</returns>
public List <EventPreview> GetAllEventsForCategory(int categoryId)
{
SqlStatement statement = new SqlStatement(StoredProcedures.GetAllEventsForCategory);
statement.AddParameter("CategoryId", categoryId);
return(db.ExecuteStoredProc(statement, EventPreviewMapper).ToList());
}
/// <summary>
/// Retrieves an email template from the database.
/// </summary>
/// <param name="emailTemplate">name of the template to retrieve</param>
/// <returns>the information for an email template (body, subject)</returns>
public EmailTemplate GetEmailTemplate(EmailTemplate emailTemplate)
{
SqlStatement statement = new SqlStatement(StoredProcedures.GetEmailTemplate);
statement.AddParameter("templateName", emailTemplate.EmailTemplateName);
return(db.ExecuteStoredProc(statement, EmailMapper).FirstOrDefault());
}