/// <summary>
/// Gets an IDataReader with one row from the mp_PollOptions table.
/// </summary>
/// <param name="optionGuid"> optionGuid </param>
public static IDataReader GetPollOption(Guid optionGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_PollOptions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("OptionGuid = @OptionGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@OptionGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = optionGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Selects a particular Track from the doan_MediaTracks table.
/// </summary>
/// <param name="trackID">The ID of the track.</param>
/// <returns>An IDataReader containing the MediaTrack data.</returns>
public static IDataReader Select(int trackId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_MediaTrack ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("TrackID = @TrackID ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@TrackID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = trackId;
return(FBSqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_SurveyPages table.
/// </summary>
public static IDataReader GetAll(Guid surveyGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT sp.*, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_SurveyQuestions sq WHERE sp.PageGuid = sq.PageGuid) AS QuestionCount ");
sqlCommand.Append("FROM mp_SurveyPages sp ");
sqlCommand.Append("WHERE sp.SurveyGuid = @SurveyGuid ");
sqlCommand.Append("ORDER BY sp.PageOrder; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SurveyGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets a count of responses in a survey.
/// </summary>
public static int GetResponseCount(Guid surveyGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SurveyResponses ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SurveyGuid = @SurveyGuid ");
sqlCommand.Append("And Complete = 1; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SurveyGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Deletes a row from the mp_PollOptions table. Returns true if row deleted.
/// </summary>
/// <param name="optionGuid"> optionGuid </param>
/// <returns>bool</returns>
public static bool Delete(Guid optionGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_PollOptions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("OptionGuid = @OptionGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@OptionGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = optionGuid.ToString();
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_SurveyQuestionOptions table.
/// </summary>
public static IDataReader GetAll(Guid questionGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SurveyQuestionOptions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("QuestionGuid = @QuestionGuid ");
sqlCommand.Append("ORDER BY \"Order\" ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@QuestionGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = questionGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with the first response to a survey
/// </summary>
public static IDataReader GetFirst(Guid surveyGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT FIRST 1 * ");
sqlCommand.Append("FROM mp_SurveyResponses ");
sqlCommand.Append("WHERE SurveyGuid = @SurveyGuid ");
sqlCommand.Append("AND Complete = 1 ");
sqlCommand.Append("ORDER BY SubmissionDate, ResponseGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SurveyGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows from the mp_Polls table matching the siteGuid.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
public static IDataReader GetPolls(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_Polls ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = @SiteGuid ");
sqlCommand.Append("ORDER BY ActiveFrom DESC, Question ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// to comment
/// </summary>
/// <param name="moduleID"> moduleID </param>
/// <returns>bool</returns>
public static bool RemoveFromModule(int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_PollModules ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleID = @ModuleID ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ModuleID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
/// <summary>
/// Gets the guid of the next page in the survey
/// </summary>
/// <param name="pageGuid">The guid of the current page</param>
/// <returns></returns>
public static Guid GetNextPageGuid(Guid pageGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT FIRST 1 PageGuid ");
sqlCommand.Append("FROM mp_SurveyPages ");
sqlCommand.Append("WHERE PageOrder > (");
sqlCommand.Append("SELECT PageOrder ");
sqlCommand.Append("FROM mp_SurveyPages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("PageGuid = @PageGuid) ");
sqlCommand.Append("AND ");
sqlCommand.Append("SurveyGuid = (");
sqlCommand.Append("SELECT SurveyGuid ");
sqlCommand.Append("FROM mp_SurveyPages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("PageGuid = @PageGuid) ");
sqlCommand.Append("Order By PageOrder ");
sqlCommand.Append("; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@PageGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pageGuid.ToString();
object id = FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
if (id == null)
{
return(Guid.Empty);
}
return(new Guid(id.ToString()));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_Polls table.
/// </summary>
/// <param name="pollGuid"> pollGuid </param>
public static IDataReader GetPoll(Guid pollGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT p.*, ");
sqlCommand.Append("(SELECT SUM(Votes) FROM mp_PollOptions WHERE mp_PollOptions.PollGuid = @PollGuid) As TotalVotes ");
sqlCommand.Append("FROM mp_Polls p ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("p.PollGuid = @PollGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@PollGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pollGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static bool DeleteByTrack(int trackId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_MediaFile ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("TrackID = @TrackID ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@TrackID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = trackId;
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Gets an IDataReader with one row from the mp_Surveys table.
/// </summary>
/// <param name="surveyGuid"> surveyGuid </param>
public static IDataReader GetOne(
Guid surveyGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT s.*, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_SurveyPages sp WHERE sp.SurveyGuid = s.SurveyGuid) AS PageCount, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_SurveyResponses sr WHERE sr.SurveyGuid = s.SurveyGuid) AS ResponseCount ");
sqlCommand.Append("FROM mp_Surveys s ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("s.SurveyGuid = @SurveyGuid; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SurveyGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_SurveyQuestionAnswers table.
/// </summary>
public static IDataReader GetOneResult(Guid responseGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_Surveys s ");
sqlCommand.Append("JOIN mp_SurveyResponses sr ");
sqlCommand.Append("ON s.SurveyGuid = sr.SurveyGuid ");
sqlCommand.Append("JOIN mp_SurveyPages sp ");
sqlCommand.Append("ON sr.SurveyGuid = sp.SurveyGuid ");
sqlCommand.Append("JOIN mp_SurveyQuestions sq ");
sqlCommand.Append("ON sp.PageGuid = sq.PageGuid ");
sqlCommand.Append("LEFT JOIN mp_SurveyQuestionAnswers qa ");
sqlCommand.Append("ON sq.QuestionGuid = qa.QuestionGuid ");
sqlCommand.Append("AND sr.REsponseGuid = qa.ResponseGuid ");
sqlCommand.Append("WHERE sr.ResponseGuid = @ResponseGuid ");
sqlCommand.Append("AND sr.Complete = 1 ");
sqlCommand.Append("AND sp.PageEnabled = 1 ");
sqlCommand.Append("ORDER BY sp.PageOrder, sq.QuestionOrder; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ResponseGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = responseGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_Surveys table.
/// </summary>
public static IDataReader GetAll(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("s.SurveyGuid, ");
sqlCommand.Append("s.SiteGuid, ");
sqlCommand.Append("s.SurveyName, ");
sqlCommand.Append("s.CreationDate, ");
sqlCommand.Append("s.StartPageText, ");
sqlCommand.Append("s.EndPageText, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_SurveyPages sp WHERE sp.SurveyGuid = s.SurveyGuid) AS PageCount, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_SurveyResponses sr WHERE sr.SurveyGuid = s.SurveyGuid) AS ResponseCount ");
sqlCommand.Append(" ");
sqlCommand.Append(" ");
sqlCommand.Append(" ");
sqlCommand.Append("FROM mp_Surveys s ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("s.SiteGuid = @SiteGuid ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("s.SurveyName ");
//sqlCommand.Append(" ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Updates a row in the mp_PollOptions table. Returns true if row updated.
/// </summary>
/// <param name="optionGuid"> optionGuid </param>
/// <param name="pollGuid"> pollGuid </param>
/// <param name="answer"> answer </param>
/// <param name="votes"> votes </param>
/// <param name="order"> order </param>
/// <returns>bool</returns>
public static bool Update(
Guid optionGuid,
string answer,
int order)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_PollOptions ");
sqlCommand.Append("SET ");
sqlCommand.Append("Answer = @Answer, ");
sqlCommand.Append("\"Order\" = @Order ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("OptionGuid = @OptionGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter("@OptionGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = optionGuid.ToString();
arParams[1] = new FbParameter("@Answer", FbDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = answer;
arParams[2] = new FbParameter("@Order", FbDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = order;
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static void RemoveFromModule(Guid surveyGuid, int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE ");
sqlCommand.Append("FROM mp_SurveyModules ");
sqlCommand.Append("WHERE ModuleId = @ModuleId ");
sqlCommand.Append("AND SurveyGuid = @SurveyGuid; ");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SurveyGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
arParams[1] = new FbParameter("@ModuleID", FbDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moduleId;
FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
}
public static bool DeleteBySite(int siteId)
{
DBMediaTrack.DeleteBySite(siteId);
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_MediaPlayer ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleID IN (SELECT ModuleID FROM mp_Modules WHERE SiteID = @SiteID) ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Inserts a row in the mp_Polls table. Returns rows affected count.
/// </summary>
/// <param name="pollGuid"> pollGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="question"> question </param>
/// <param name="active"> active </param>
/// <param name="anonymousVoting"> anonymousVoting </param>
/// <param name="allowViewingResultsBeforeVoting"> allowViewingResultsBeforeVoting </param>
/// <param name="showOrderNumbers"> showOrderNumbers </param>
/// <param name="showResultsWhenDeactivated"> showResultsWhenDeactivated </param>
/// <param name="activeFrom"> activeFrom </param>
/// <param name="activeTo"> activeTo </param>
/// <returns>int</returns>
public static int Add(
Guid pollGuid,
Guid siteGuid,
string question,
bool active,
bool anonymousVoting,
bool allowViewingResultsBeforeVoting,
bool showOrderNumbers,
bool showResultsWhenDeactivated,
DateTime activeFrom,
DateTime activeTo)
{
#region Bit Conversion
int intActive;
if (active)
{
intActive = 1;
}
else
{
intActive = 0;
}
int intAnonymousVoting;
if (anonymousVoting)
{
intAnonymousVoting = 1;
}
else
{
intAnonymousVoting = 0;
}
int intAllowViewingResultsBeforeVoting;
if (allowViewingResultsBeforeVoting)
{
intAllowViewingResultsBeforeVoting = 1;
}
else
{
intAllowViewingResultsBeforeVoting = 0;
}
int intShowOrderNumbers;
if (showOrderNumbers)
{
intShowOrderNumbers = 1;
}
else
{
intShowOrderNumbers = 0;
}
int intShowResultsWhenDeactivated;
if (showResultsWhenDeactivated)
{
intShowResultsWhenDeactivated = 1;
}
else
{
intShowResultsWhenDeactivated = 0;
}
#endregion
FbParameter[] arParams = new FbParameter[10];
arParams[0] = new FbParameter("@PollGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pollGuid.ToString();
arParams[1] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new FbParameter("@Question", FbDbType.VarChar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = question;
arParams[3] = new FbParameter("@Active", FbDbType.SmallInt);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = intActive;
arParams[4] = new FbParameter("@AnonymousVoting", FbDbType.SmallInt);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = intAnonymousVoting;
arParams[5] = new FbParameter("@AllowViewingResultsBeforeVoting", FbDbType.SmallInt);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = intAllowViewingResultsBeforeVoting;
arParams[6] = new FbParameter("@ShowOrderNumbers", FbDbType.SmallInt);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = intShowOrderNumbers;
arParams[7] = new FbParameter("@ShowResultsWhenDeactivated", FbDbType.SmallInt);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = intShowResultsWhenDeactivated;
arParams[8] = new FbParameter("@ActiveFrom", FbDbType.TimeStamp);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = activeFrom;
arParams[9] = new FbParameter("@ActiveTo", FbDbType.TimeStamp);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = activeTo;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_Polls (");
sqlCommand.Append("PollGuid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("Question, ");
sqlCommand.Append("\"Active\", ");
sqlCommand.Append("AnonymousVoting, ");
sqlCommand.Append("AllowViewingResultsBeforeVoting, ");
sqlCommand.Append("ShowOrderNumbers, ");
sqlCommand.Append("ShowResultsWhenDeactivated, ");
sqlCommand.Append("ActiveFrom, ");
sqlCommand.Append("ActiveTo )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("@PollGuid, ");
sqlCommand.Append("@SiteGuid, ");
sqlCommand.Append("@Question, ");
sqlCommand.Append("@Active, ");
sqlCommand.Append("@AnonymousVoting, ");
sqlCommand.Append("@AllowViewingResultsBeforeVoting, ");
sqlCommand.Append("@ShowOrderNumbers, ");
sqlCommand.Append("@ShowResultsWhenDeactivated, ");
sqlCommand.Append("@ActiveFrom, ");
sqlCommand.Append("@ActiveTo )");
sqlCommand.Append(";");
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Inserts a row in the mp_SurveyResponses table. Returns rows affected count.
/// </summary>
/// <param name="responseGuid"> responseGuid </param>
/// <param name="surveyGuid"> surveyGuid </param>
/// <param name="userId"> userId </param>
/// <param name="annonymous"> annonymous </param>
/// <param name="complete"> complete </param>
/// <returns>int</returns>
public static int Add(
Guid responseGuid,
Guid surveyGuid,
Guid userGuid,
bool annonymous,
bool complete)
{
#region Bit Conversion
int intAnnonymous;
if (annonymous)
{
intAnnonymous = 1;
}
else
{
intAnnonymous = 0;
}
int intComplete;
if (complete)
{
intComplete = 1;
}
else
{
intComplete = 0;
}
#endregion
FbParameter[] arParams = new FbParameter[5];
arParams[0] = new FbParameter("@ResponseGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = responseGuid.ToString();
arParams[1] = new FbParameter("@SurveyGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = surveyGuid.ToString();
//arParams[2] = new FbParameter("@SubmissionDate", FbDbType.TimeStamp);
//arParams[2].Direction = ParameterDirection.Input;
//arParams[2].Value = submissionDate;
arParams[2] = new FbParameter("@Annonymous", FbDbType.SmallInt);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = intAnnonymous;
arParams[3] = new FbParameter("@Complete", FbDbType.SmallInt);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = intComplete;
arParams[4] = new FbParameter("@UserGuid", FbDbType.Char, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = userGuid.ToString();
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SurveyResponses (");
sqlCommand.Append("ResponseGuid, ");
sqlCommand.Append("SurveyGuid, ");
//sqlCommand.Append("SubmissionDate, ");
sqlCommand.Append("Annonymous, ");
sqlCommand.Append("Complete, ");
sqlCommand.Append("UserGuid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("@ResponseGuid, ");
sqlCommand.Append("@SurveyGuid, ");
//sqlCommand.Append("@SubmissionDate, ");
sqlCommand.Append("@Annonymous, ");
sqlCommand.Append("@Complete, ");
sqlCommand.Append("@UserGuid )");
sqlCommand.Append(";");
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Updates a row in the mp_SurveyPages table. Returns true if row updated.
/// </summary>
/// <param name="pageGuid"> pageGuid </param>
/// <param name="surveyGuid"> surveyGuid </param>
/// <param name="pageTitle"> pageTitle </param>
/// <param name="pageOrder"> pageOrder </param>
/// <param name="pageEnabled"> pageEnabled </param>
/// <returns>bool</returns>
public static bool Update(
Guid pageGuid,
Guid surveyGuid,
string pageTitle,
int pageOrder,
bool pageEnabled)
{
#region Bit Conversion
int intPageEnabled;
if (pageEnabled)
{
intPageEnabled = 1;
}
else
{
intPageEnabled = 0;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_SurveyPages ");
sqlCommand.Append("SET ");
sqlCommand.Append("SurveyGuid = @SurveyGuid, ");
sqlCommand.Append("PageTitle = @PageTitle, ");
sqlCommand.Append("PageOrder = @PageOrder, ");
sqlCommand.Append("PageEnabled = @PageEnabled ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("PageGuid = @PageGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[5];
arParams[0] = new FbParameter("@PageGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pageGuid.ToString();
arParams[1] = new FbParameter("@SurveyGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = surveyGuid.ToString();
arParams[2] = new FbParameter("@PageTitle", FbDbType.VarChar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageTitle;
arParams[3] = new FbParameter("@PageOrder", FbDbType.Integer);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = pageOrder;
arParams[4] = new FbParameter("@PageEnabled", FbDbType.SmallInt);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = intPageEnabled;
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Updates a row in the mp_MediaTrack table.
/// </summary>
/// <param name="trackID">The ID of the track.</param>
/// <param name="playerID">The ID of the player instance.</param>
/// <param name="trackType">The type of the track.</param>
/// <param name="trackOrder">The order position of the Media Track.</param>
/// <param name="name">The name of the Media Track.</param>
/// <param name="artist">The artist of the Media Track.</param>
/// <param name="userGuid">The Guid of the user who added the Media Track.</param>
/// <returns>True if the row is successfully updated.</returns>
public static bool Update(
int trackId,
int playerId,
string trackType,
int trackOrder,
string name,
string artist,
Guid userGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_MediaTrack ");
sqlCommand.Append("SET ");
sqlCommand.Append("PlayerID = @PlayerID, ");
sqlCommand.Append("TrackType = @TrackType, ");
sqlCommand.Append("TrackOrder = @TrackOrder, ");
sqlCommand.Append("Name = @Name, ");
sqlCommand.Append("Artist = @Artist, ");
sqlCommand.Append("UserGuid = @UserGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("TrackID = @TrackID ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[7];
arParams[0] = new FbParameter("@TrackID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = trackId;
arParams[1] = new FbParameter("@PlayerID", FbDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = playerId;
arParams[2] = new FbParameter("@TrackType", FbDbType.VarChar, 10);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = trackType;
arParams[3] = new FbParameter("@TrackOrder", FbDbType.Integer);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = trackOrder;
arParams[4] = new FbParameter("@Name", FbDbType.VarChar, 100);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = name;
arParams[5] = new FbParameter("@Artist", FbDbType.VarChar, 100);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = artist;
arParams[6] = new FbParameter("@UserGuid", FbDbType.Char, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = userGuid.ToString();
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Deletes a row from the mp_Surveys table. Returns true if row deleted.
/// </summary>
/// <param name="surveyGuid"> surveyGuid </param>
/// <returns>bool</returns>
public static void Delete(Guid surveyGuid)
{
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SurveyGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
//first delete questionOptions
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_SurveyQuestionOptions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("QuestionGuid IN (");
sqlCommand.Append("SELECT QuestionGuid ");
sqlCommand.Append("FROM mp_SurveyQuestions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("PageGuid IN (");
sqlCommand.Append("SELECT PageGuid ");
sqlCommand.Append("FROM mp_SurveyPages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SurveyGuid = @SurveyGuid)); ");
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
//now delete survey questions
sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_SurveyQuestions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("PageGuid IN (");
sqlCommand.Append("SELECT PageGuid ");
sqlCommand.Append("FROM mp_SurveyPages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SurveyGuid = @SurveyGuid);");
rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
//now delete survey pages
sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_SurveyPages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SurveyGuid = @SurveyGuid; ");
rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
//now delete survey
sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_Surveys ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SurveyGuid = @SurveyGuid;");
rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
}
/// <summary>
/// deletes all survey content for the passed in siteid
/// </summary>
/// <param name="siteId"></param>
/// <returns></returns>
public static bool DeleteBySite(int siteId)
{
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
//first delete questionOptions
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_SurveyQuestionOptions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("QuestionGuid IN (");
sqlCommand.Append("SELECT QuestionGuid ");
sqlCommand.Append("FROM mp_SurveyQuestions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("PageGuid IN (");
sqlCommand.Append("SELECT PageGuid ");
sqlCommand.Append("FROM mp_SurveyPages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SurveyGuid IN (SELECT SurveyGuid FROM mp_Surveys ");
sqlCommand.Append("WHERE SiteGuid IN (SELECT SiteGuid FROM mp_Sites WHERE SiteID = @SiteID) ");
sqlCommand.Append("))); ");
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
//now delete survey questions
sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_SurveyQuestions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("PageGuid IN (");
sqlCommand.Append("SELECT PageGuid ");
sqlCommand.Append("FROM mp_SurveyPages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SurveyGuid IN (SELECT SurveyGuid FROM mp_Surveys ");
sqlCommand.Append("WHERE SiteGuid IN (SELECT SiteGuid FROM mp_Sites WHERE SiteID = @SiteID) ");
sqlCommand.Append("))); ");
rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
//now delete survey pages
sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_SurveyPages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SurveyGuid IN (SELECT SurveyGuid FROM mp_Surveys ");
sqlCommand.Append("WHERE SiteGuid IN (SELECT SiteGuid FROM mp_Sites WHERE SiteID = @SiteID) ");
sqlCommand.Append("))); ");
rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
//now delete survey
sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_Surveys ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid IN (SELECT SiteGuid FROM mp_Sites WHERE SiteID = @SiteID);");
rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
/// <summary>
/// Inserts a row in the mp_SurveyQuestions table. Returns rows affected count.
/// </summary>
/// <param name="questionGuid"> questionGuid </param>
/// <param name="pageGuid"> pageGuid </param>
/// <param name="questionText"> questionText </param>
/// <param name="questionTypeId"> questionTypeId </param>
/// <param name="answerIsRequired"> answerIsRequired </param>
/// <param name="questionOrder"> questionOrder </param>
/// <param name="validationMessage"> validationMessage </param>
/// <returns>int</returns>
public static int Add(
Guid questionGuid,
Guid pageGuid,
string questionText,
int questionTypeId,
bool answerIsRequired,
string validationMessage)
{
#region Bit Conversion
int intAnswerIsRequired;
if (answerIsRequired)
{
intAnswerIsRequired = 1;
}
else
{
intAnswerIsRequired = 0;
}
#endregion
FbParameter[] arParams = new FbParameter[6];
arParams[0] = new FbParameter("@QuestionGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = questionGuid.ToString();
arParams[1] = new FbParameter("@PageGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageGuid.ToString();
arParams[2] = new FbParameter("@QuestionText", FbDbType.VarChar);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = questionText;
arParams[3] = new FbParameter("@QuestionTypeId", FbDbType.Integer);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = questionTypeId;
arParams[4] = new FbParameter("@AnswerIsRequired", FbDbType.SmallInt);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = intAnswerIsRequired;
arParams[5] = new FbParameter("@ValidationMessage", FbDbType.VarChar, 256);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = validationMessage;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SurveyQuestions (");
sqlCommand.Append("QuestionGuid, ");
sqlCommand.Append("PageGuid, ");
sqlCommand.Append("QuestionText, ");
sqlCommand.Append("QuestionTypeId, ");
sqlCommand.Append("AnswerIsRequired, ");
sqlCommand.Append("QuestionOrder, ");
sqlCommand.Append("ValidationMessage )");
sqlCommand.Append("SELECT @QuestionGuid, @PageGuid, @QuestionText, ");
sqlCommand.Append("@QuestionTypeId, @AnswerIsRequired, Count(*), @ValidationMessage ");
sqlCommand.Append("FROM mp_SurveyPages; ");
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Updates a row in the mp_SurveyQuestions table. Returns true if row updated.
/// </summary>
/// <param name="questionGuid"> questionGuid </param>
/// <param name="pageGuid"> pageGuid </param>
/// <param name="questionText"> questionText </param>
/// <param name="questionTypeId"> questionTypeId </param>
/// <param name="answerIsRequired"> answerIsRequired </param>
/// <param name="questionOrder"> questionOrder </param>
/// <param name="validationMessage"> validationMessage </param>
/// <returns>bool</returns>
public static bool Update(
Guid questionGuid,
Guid pageGuid,
string questionText,
int questionTypeId,
bool answerIsRequired,
int questionOrder,
string validationMessage)
{
#region Bit Conversion
int intAnswerIsRequired;
if (answerIsRequired)
{
intAnswerIsRequired = 1;
}
else
{
intAnswerIsRequired = 0;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_SurveyQuestions ");
sqlCommand.Append("SET ");
sqlCommand.Append("PageGuid = @PageGuid, ");
sqlCommand.Append("QuestionText = @QuestionText, ");
sqlCommand.Append("QuestionTypeId = @QuestionTypeId, ");
sqlCommand.Append("AnswerIsRequired = @AnswerIsRequired, ");
sqlCommand.Append("QuestionOrder = @QuestionOrder, ");
sqlCommand.Append("ValidationMessage = @ValidationMessage ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("QuestionGuid = @QuestionGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[7];
arParams[0] = new FbParameter("@QuestionGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = questionGuid.ToString();
arParams[1] = new FbParameter("@PageGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageGuid.ToString();
arParams[2] = new FbParameter("@QuestionText", FbDbType.VarChar);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = questionText;
arParams[3] = new FbParameter("@QuestionTypeId", FbDbType.Integer);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = questionTypeId;
arParams[4] = new FbParameter("@AnswerIsRequired", FbDbType.SmallInt);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = intAnswerIsRequired;
arParams[5] = new FbParameter("@QuestionOrder", FbDbType.Integer);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = questionOrder;
arParams[6] = new FbParameter("@ValidationMessage", FbDbType.VarChar, 256);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = validationMessage;
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static bool IncrementVotes(
Guid pollGuid,
Guid optionGuid,
Guid userGuid)
{
if (DBPoll.UserHasVoted(pollGuid, userGuid))
{
return(false);
}
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter("@PollGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pollGuid.ToString();
arParams[1] = new FbParameter("@OptionGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = optionGuid.ToString();
arParams[2] = new FbParameter("@UserGuid", FbDbType.Char, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = userGuid.ToString();
StringBuilder sqlCommand = new StringBuilder();
if (userGuid != Guid.Empty)
{
sqlCommand.Append("INSERT INTO mp_PollUsers (");
sqlCommand.Append("PollGuid, ");
sqlCommand.Append("OptionGuid, ");
sqlCommand.Append("UserGuid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("@PollGuid, ");
sqlCommand.Append("@OptionGuid, ");
sqlCommand.Append("@UserGuid )");
sqlCommand.Append(";");
}
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_PollOptions ");
sqlCommand.Append("SET ");
sqlCommand.Append("Votes = Votes + 1 ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("OptionGuid = @OptionGuid ");
sqlCommand.Append(";");
rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
