/// <summary>
/// Gets a count of rows in the mp_Polls table.
/// </summary>
public static bool UserHasVoted(Guid pollGuid, Guid userGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_PollUsers ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("UserGuid = @UserGuid ");
sqlCommand.Append("AND PollGuid = @PollGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@UserGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userGuid.ToString();
arParams[1] = new FbParameter("@PollGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pollGuid.ToString();
int count = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count > 0);
}
public static Guid GetModulesCurrentSurvey(int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT SurveyGuid ");
sqlCommand.Append("FROM mp_SurveyModules ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("moduleId = @ModuleId; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ModuleID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
object id = FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
if (id == null)
{
return(Guid.Empty);
}
return(new Guid(id.ToString()));
}
public static Guid GetFirstPageGuid(Guid surveyGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT FIRST 1 PageGuid ");
sqlCommand.Append("FROM mp_SurveyPages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SurveyGuid = @SurveyGuid ");
sqlCommand.Append("Order By PageOrder ");
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();
object id = FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
if (id == null)
{
return(Guid.Empty);
}
return(new Guid(id.ToString()));
}
/// <summary>
/// Inserts a row in the doan_MediaFiles table.
/// </summary>
/// <param name="trackID">The ID of the Track for which the File is being added.</param>
/// <param name="filePath">The name of the physical Media File.</param>
/// <param name="userGuid">The Guid of the user who is adding the File.</param>
/// <returns>The ID of the Media File in the doan_MediaFiles table.</returns>
public static int Insert(
int trackId,
string filePath,
Guid userGuid)
{
FbParameter[] arParams = new FbParameter[4];
arParams[0] = new FbParameter(":TrackID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = trackId;
arParams[1] = new FbParameter(":FilePath", FbDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = filePath;
arParams[2] = new FbParameter(":AddedDate", FbDbType.TimeStamp);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = DateTime.UtcNow;
arParams[3] = new FbParameter(":UserGuid", FbDbType.Char, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userGuid.ToString();
int newID = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
"EXECUTE PROCEDURE MP_MEDIAFILE_INSERT ("
+ FBSqlHelper.GetParamString(arParams.Length) + ")",
arParams));
return(newID);
}
/// <summary>
/// Gets a count of rows in the mp_Surveys table.
/// </summary>
public static int GetCount()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_Surveys ");
sqlCommand.Append(";");
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
null)));
}
/// <summary>
/// Inserts a row in the mp_MediaTrack table.
/// </summary>
/// <param name="playerID">The ID of the player to which the Media Track is being added.</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>The ID of the Media Track in the doan_MediaTracks table.</returns>
public static int Insert(
int playerId,
string trackType,
int trackOrder,
string name,
string artist,
Guid userGuid)
{
FbParameter[] arParams = new FbParameter[7];
arParams[0] = new FbParameter(":PlayerID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = playerId;
arParams[1] = new FbParameter(":TrackType", FbDbType.VarChar, 10);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = trackType;
arParams[2] = new FbParameter(":TrackOrder", FbDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = trackOrder;
arParams[3] = new FbParameter(":Name", FbDbType.VarChar, 100);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = name;
arParams[4] = new FbParameter(":Artist", FbDbType.VarChar, 100);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = artist;
arParams[5] = new FbParameter(":CreatedDate", FbDbType.TimeStamp);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = DateTime.UtcNow;
arParams[6] = new FbParameter(":UserGuid", FbDbType.Char, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = userGuid.ToString();
int newID = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
"EXECUTE PROCEDURE MP_MEDIATRACK_INSERT ("
+ FBSqlHelper.GetParamString(arParams.Length) + ")",
arParams));
return(newID);
}
/// <summary>
/// Gets a count of rows for a particular Track in the doan_MediaFiles table.
/// </summary>
/// <param name="trackID">The ID of the Track.</param>
/// <returns>The count of rows.</returns>
public static int GetCountByTrack(int trackId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT count(*) FROM mp_MediaFile ");
sqlCommand.Append("WHERE TrackID = @TrackID;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@TrackID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = trackId;
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Gets a count of rows in the mp_SurveyPages table.
/// </summary>
public static int GetQuestionsCount(Guid pageGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SurveyQuestions ");
sqlCommand.Append("WHERE PageGuid = @PageGuid; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@PageGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pageGuid.ToString();
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Inserts a row in the doan_MediaPlayers table.
/// </summary>
/// <param name="moduleID">The ID of the Module</param>
/// <param name="playerType">The Player Type.</param>
/// <param name="createdDate">The Date the Media Player was created.</param>
/// <param name="userGuid">The Guid of the user who created the Media Player.</param>
/// <param name="moduleGuid">The Guid of the Module.</param>
/// <returns>The ID of the Media Player.</returns>
public static int Insert(
int moduleId,
string playerType,
String skin,
Guid userGuid,
Guid moduleGuid)
{
FbParameter[] arParams = new FbParameter[6];
arParams[0] = new FbParameter(":ModuleID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new FbParameter(":PlayerType", FbDbType.VarChar, 10);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = playerType;
arParams[2] = new FbParameter(":Skin", FbDbType.VarChar, 50);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = skin;
arParams[3] = new FbParameter(":CreatedDate", FbDbType.TimeStamp);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = DateTime.UtcNow;
arParams[4] = new FbParameter(":UserGuid", FbDbType.Char, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = userGuid.ToString();
arParams[5] = new FbParameter(":ModuleGuid", FbDbType.Char, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = moduleGuid.ToString();
int newID = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
"EXECUTE PROCEDURE MP_MEDIAPLAYER_INSERT ("
+ FBSqlHelper.GetParamString(arParams.Length) + ")",
arParams));
return(newID);
}
/// <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)));
}