/// <summary>
/// Binds the data.
/// </summary>
private void BindData()
{
using (var ds = this.GetRepository<Types.Models.Forum>().ForumAdminAsDataSet(this.PageContext.PageBoardID))
{
this.CategoryList.DataSource = ds.Tables[CommandTextHelpers.GetObjectName("Category")];
}
// Hide the New Forum Button if there are no Categories.
this.NewForum.Visible = this.CategoryList.Items.Count < 1;
this.DataBind();
}
/// <summary>
/// The get user name by email.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="email">
/// The email.
/// </param>
/// <returns>
/// </returns>
public DataTable GetUserNameByEmail([NotNull] string appName, [NotNull] string email)
{
using (var cmd = new SqlCommand(CommandTextHelpers.GetObjectName("prov_getusernamebyemail")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ApplicationName", appName);
// Nonstandard args
cmd.Parameters.AddWithValue("@Email", email);
return(this.DbAccess.GetData(cmd));
}
}
/// <summary>
/// The method returns an integer value for a found parent forum
/// if a forum is a parent of an existing child to avoid circular dependency
/// while creating a new forum
/// </summary>
/// <param name="repository">
/// The repository.
/// </param>
/// <param name="forumId">
/// The forum Id.
/// </param>
/// <param name="parentId">
/// The parent Id.
/// </param>
/// <returns>
/// Integer value for a found dependency
/// </returns>
public static int SaveParentsChecker([NotNull] this IRepository <Forum> repository, int forumId, int parentId)
{
using (var cmd = repository.DbAccess.GetCommand(
$"select {CommandTextHelpers.GetObjectName("forum_save_parentschecker")}(@ForumID, @ParentID)",
CommandType.Text))
{
cmd.AddParam("@ForumID", forumId);
cmd.AddParam("@ParentID", parentId);
return((int)repository.DbAccess.ExecuteScalar(cmd));
}
}
/// <summary>
/// The unlock user.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="userName">
/// The user name.
/// </param>
public void UnlockUser([NotNull] string appName, [NotNull] string userName)
{
using (var cmd = new SqlCommand(CommandTextHelpers.GetObjectName("prov_unlockuser")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ApplicationName", appName);
// Nonstandard args
cmd.Parameters.AddWithValue("@UserName", userName);
this.DbAccess.ExecuteNonQuery(cmd);
}
}
/// <summary>
/// The delete user.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="username">
/// The username.
/// </param>
/// <param name="deleteAllRelatedData">
/// The delete all related data.
/// </param>
public void DeleteUser([NotNull] string appName, [NotNull] string username, bool deleteAllRelatedData)
{
using (var cmd = new SqlCommand(CommandTextHelpers.GetObjectName("prov_deleteuser")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ApplicationName", appName);
// Nonstandard args
cmd.Parameters.AddWithValue("@UserName", username);
cmd.Parameters.AddWithValue("@DeleteAllRelated", deleteAllRelatedData);
this.DbAccess.ExecuteNonQuery(cmd);
}
}
/// <summary>
/// The get all users.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="pageIndex">
/// The page index.
/// </param>
/// <param name="pageSize">
/// The page size.
/// </param>
/// <returns>
/// </returns>
public DataTable GetAllUsers([NotNull] string appName, int pageIndex, int pageSize)
{
using (var cmd = new SqlCommand(CommandTextHelpers.GetObjectName("prov_getallusers")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ApplicationName", appName);
// Nonstandard args
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", pageSize);
return(this.DbAccess.GetData(cmd));
}
}
/// <summary>
/// The upgrade membership.
/// </summary>
/// <param name="previousVersion">
/// The previous version.
/// </param>
/// <param name="newVersion">
/// The new version.
/// </param>
public void UpgradeMembership(int previousVersion, int newVersion)
{
using (var cmd = new SqlCommand(CommandTextHelpers.GetObjectName("prov_upgrade")))
{
cmd.CommandType = CommandType.StoredProcedure;
// Nonstandard args
cmd.Parameters.AddWithValue("@PreviousVersion", previousVersion);
cmd.Parameters.AddWithValue("@NewVersion", newVersion);
cmd.Parameters.AddWithValue("@UTCTIMESTAMP", DateTime.UtcNow);
this.DbAccess.ExecuteNonQuery(cmd);
}
}
/// <summary>
/// The get user password info.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="username">
/// The username.
/// </param>
/// <param name="updateUser">
/// The update user.
/// </param>
/// <returns>
/// </returns>
public DataTable GetUserPasswordInfo([NotNull] string appName, [NotNull] string username, bool updateUser)
{
using (var cmd = new SqlCommand(CommandTextHelpers.GetObjectName("prov_getuser")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ApplicationName", appName);
// Nonstandard args
cmd.Parameters.AddWithValue("@UserName", username);
cmd.Parameters.AddWithValue("@UserIsOnline", updateUser);
cmd.Parameters.AddWithValue("@UTCTIMESTAMP", DateTime.UtcNow);
return(this.DbAccess.GetData(cmd));
}
}
/// <summary>
/// The find users by name.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="usernameToMatch">
/// The username to match.
/// </param>
/// <param name="pageIndex">
/// The page index.
/// </param>
/// <param name="pageSize">
/// The page size.
/// </param>
/// <returns>
/// </returns>
public DataTable FindUsersByName([NotNull] string appName, [NotNull] string usernameToMatch, int pageIndex,
int pageSize)
{
using (var cmd = new SqlCommand(CommandTextHelpers.GetObjectName("prov_findusersbyname")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ApplicationName", appName);
// Nonstandard args
cmd.Parameters.AddWithValue("@UserName", usernameToMatch);
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", pageSize);
return(this.DbAccess.GetData(cmd));
}
}
/// <summary>
/// The change password question and answer.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="username">
/// The username.
/// </param>
/// <param name="passwordQuestion">
/// The password question.
/// </param>
/// <param name="passwordAnswer">
/// The password answer.
/// </param>
public void ChangePasswordQuestionAndAnswer([NotNull] string appName, [NotNull] string username,
[NotNull] string passwordQuestion, [NotNull] string passwordAnswer)
{
using (var cmd = new SqlCommand(CommandTextHelpers.GetObjectName("prov_changepasswordquestionandanswer")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ApplicationName", appName);
// Nonstandard args
cmd.Parameters.AddWithValue("@UserName", username);
cmd.Parameters.AddWithValue("@PasswordQuestion", passwordQuestion);
cmd.Parameters.AddWithValue("@PasswordAnswer", passwordAnswer);
this.DbAccess.ExecuteNonQuery(cmd);
}
}
/// <summary>
/// Bind data for this control.
/// </summary>
private void BindData()
{
// get list of forums and their moderating data
using (var ds = this.GetRepository <Forum>().ModerateListADataSet(this.PageContext.PageUserID, this.PageContext.PageBoardID))
{
this.CategoryList.DataSource = ds.Tables[CommandTextHelpers.GetObjectName("Category")];
}
// bind data to controls
this.DataBind();
if (this.CategoryList.Items.Count.Equals(0))
{
this.InfoPlaceHolder.Visible = true;
}
}
/// <summary>
/// The get number of users online.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="timeWindow">
/// The time window.
/// </param>
/// <returns>
/// The get number of users online.
/// </returns>
public int GetNumberOfUsersOnline([NotNull] string appName, int timeWindow)
{
using (var cmd = new SqlCommand(CommandTextHelpers.GetObjectName("prov_getnumberofusersonline")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ApplicationName", appName);
// Nonstandard args
cmd.Parameters.AddWithValue("@TimeWindow", timeWindow);
cmd.Parameters.AddWithValue("@CurrentTimeUtc", DateTime.UtcNow);
var p = new SqlParameter("ReturnValue", SqlDbType.Int);
p.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p);
this.DbAccess.ExecuteNonQuery(cmd);
return(Convert.ToInt32(cmd.Parameters["ReturnValue"].Value));
}
}
private void InitializeForumIfNotExist()
{
var logger = LoggerSource.Instance.GetLogger(typeof(YafDnnModule));
try
{
var isForumInstalled = this.Get <InstallUpgradeService>().IsForumInstalled;
if (!isForumInstalled)
{
var userInfo = UserController.Instance.GetUserById(
this.PortalSettings.PortalId,
this.PortalSettings.AdministratorId);
this.Get <InstallUpgradeService>().InitializeForum(
Guid.NewGuid(),
$"{this.PortalSettings.PortalName} Forum",
"en-US",
null,
"YAFLogo.svg",
BaseUrlBuilder.GetBaseUrlFromVariables(),
userInfo.Username,
userInfo.Email,
userInfo.UserID.ToString());
}
}
catch (Exception exception)
{
logger.Error(exception);
}
var scriptFile = HttpContext.Current.Request.MapPath(
Path.Combine(
"DesktopModules",
"YetAnotherForumDotNet",
"03.00.006100.sql"));
// read script file for installation
var script = FileSystemUtils.ReadFile(scriptFile);
// execute SQL installation script
var exceptions = DataProvider.Instance().ExecuteScript(CommandTextHelpers.GetCommandTextReplaced(script));
logger.Error(exceptions);
}
/// <summary>
/// Database Action - Delete Role
/// </summary>
/// <param name="appName">
/// Application Name
/// </param>
/// <param name="roleName">
/// Role Name
/// </param>
/// <param name="deleteOnlyIfRoleIsEmpty">
/// The delete Only If Role Is Empty.
/// </param>
/// <returns>
/// Status as integer
/// </returns>
public int DeleteRole(object appName, object roleName, object deleteOnlyIfRoleIsEmpty)
{
using (var cmd = new SqlCommand(CommandTextHelpers.GetObjectName("prov_role_deleterole")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("ApplicationName", appName);
cmd.Parameters.AddWithValue("Rolename", roleName);
cmd.Parameters.AddWithValue("DeleteOnlyIfRoleIsEmpty", deleteOnlyIfRoleIsEmpty);
var p = new SqlParameter("ReturnValue", SqlDbType.Int);
p.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p);
this.DbAccess.ExecuteNonQuery(cmd);
return(cmd.Parameters["ReturnValue"].Value.ToType <int>());
}
}
/// <summary>
/// The add profile column.
/// </summary>
/// <param name="name">
/// The name.
/// </param>
/// <param name="columnType">
/// The column type.
/// </param>
/// <param name="size">
/// The size.
/// </param>
public void AddProfileColumn([NotNull] string name, SqlDbType columnType, int size)
{
// get column type...
var type = columnType.ToString();
if (size > 0)
{
type += "(" + size + ")";
}
var sql = $"ALTER TABLE {CommandTextHelpers.GetObjectName("prov_Profile")} ADD [{name}] {type} NULL";
using (var cmd = new SqlCommand(sql))
{
cmd.CommandType = CommandType.Text;
this.DbAccess.ExecuteNonQuery(cmd);
}
}
/// <summary>
/// The get user.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="providerUserKey">
/// The provider user key.
/// </param>
/// <param name="userName">
/// The user name.
/// </param>
/// <param name="userIsOnline">
/// The user is online.
/// </param>
/// <returns>
/// </returns>
public DataRow GetUser([NotNull] string appName, [NotNull] object providerUserKey, [NotNull] string userName,
bool userIsOnline)
{
using (var cmd = new SqlCommand(CommandTextHelpers.GetObjectName("prov_getuser")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ApplicationName", appName);
// Nonstandard args
cmd.Parameters.AddWithValue("@UserName", userName);
cmd.Parameters.AddWithValue("@UserKey", providerUserKey);
cmd.Parameters.AddWithValue("@UserIsOnline", userIsOnline);
cmd.Parameters.AddWithValue("@UTCTIMESTAMP", DateTime.UtcNow);
using (var dt = this.DbAccess.GetData(cmd))
{
return(dt.HasRows() ? dt.Rows[0] : null);
}
}
}
public static DataSet ForumAdminAsDataSet(this IRepository <Forum> repository, [NotNull] object boardID)
{
// TODO: this function is TERRIBLE. Recode or remove completely.
using (var ds = new DataSet())
{
using (var trans = repository.DbAccess.BeginTransaction())
{
var sqlConnection = trans.Connection as SqlConnection;
using (var da = new SqlDataAdapter(
CommandTextHelpers.GetObjectName("category_list"),
sqlConnection))
{
da.SelectCommand.Transaction = trans as SqlTransaction;
da.SelectCommand.AddParam("BoardID", boardID);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(ds, CommandTextHelpers.GetObjectName("Category"));
da.SelectCommand.CommandText = CommandTextHelpers.GetObjectName("forum_list");
da.Fill(ds, CommandTextHelpers.GetObjectName("ForumUnsorted"));
var forumListSorted = ds.Tables[CommandTextHelpers.GetObjectName("ForumUnsorted")].Clone();
forumListSorted.TableName = CommandTextHelpers.GetObjectName("Forum");
ds.Tables.Add(forumListSorted);
forumListSorted.Dispose();
ForumListSortBasic(
ds.Tables[CommandTextHelpers.GetObjectName("ForumUnsorted")],
ds.Tables[CommandTextHelpers.GetObjectName("Forum")],
0,
0);
ds.Tables.Remove(CommandTextHelpers.GetObjectName("ForumUnsorted"));
ds.Relations.Add(
"FK_Forum_Category",
ds.Tables[CommandTextHelpers.GetObjectName("Category")].Columns["CategoryID"],
ds.Tables[CommandTextHelpers.GetObjectName("Forum")].Columns["CategoryID"]);
trans.Commit();
}
return(ds);
}
}
}
/// <summary>
/// The reset password.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="userName">
/// The user name.
/// </param>
/// <param name="password">
/// The password.
/// </param>
/// <param name="passwordSalt">
/// The password salt.
/// </param>
/// <param name="passwordFormat">
/// The password format.
/// </param>
/// <param name="maxInvalidPasswordAttempts">
/// The max invalid password attempts.
/// </param>
/// <param name="passwordAttemptWindow">
/// The password attempt window.
/// </param>
public void ResetPassword([NotNull] string appName, [NotNull] string userName, [NotNull] string password,
[NotNull] string passwordSalt,
int passwordFormat,
int maxInvalidPasswordAttempts,
int passwordAttemptWindow)
{
using (var cmd = new SqlCommand(CommandTextHelpers.GetObjectName("prov_resetpassword")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ApplicationName", appName);
// Nonstandard args
cmd.Parameters.AddWithValue("@UserName", userName);
cmd.Parameters.AddWithValue("@Password", password);
cmd.Parameters.AddWithValue("@PasswordSalt", passwordSalt);
cmd.Parameters.AddWithValue("@PasswordFormat", passwordFormat);
cmd.Parameters.AddWithValue("@MaxInvalidAttempts", maxInvalidPasswordAttempts);
cmd.Parameters.AddWithValue("@PasswordAttemptWindow", passwordAttemptWindow);
cmd.Parameters.AddWithValue("@CurrentTimeUtc", DateTime.UtcNow);
this.DbAccess.ExecuteNonQuery(cmd);
}
}
/// <summary>
/// The create user.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="username">
/// The username.
/// </param>
/// <param name="password">
/// The password.
/// </param>
/// <param name="passwordSalt">
/// The password salt.
/// </param>
/// <param name="passwordFormat">
/// The password format.
/// </param>
/// <param name="email">
/// The email.
/// </param>
/// <param name="passwordQuestion">
/// The password question.
/// </param>
/// <param name="passwordAnswer">
/// The password answer.
/// </param>
/// <param name="isApproved">
/// The is approved.
/// </param>
/// <param name="providerUserKey">
/// The provider user key.
/// </param>
public void CreateUser([NotNull] string appName, [NotNull] string username, [NotNull] string password,
[NotNull] string passwordSalt,
int passwordFormat, [NotNull] string email, [NotNull] string passwordQuestion,
[NotNull] string passwordAnswer,
bool isApproved, [NotNull] object providerUserKey)
{
using (var cmd = new SqlCommand(CommandTextHelpers.GetObjectName("prov_createuser")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ApplicationName", appName);
// Input Parameters
cmd.Parameters.AddWithValue("Username", username);
cmd.Parameters.AddWithValue("Password", password);
cmd.Parameters.AddWithValue("PasswordSalt", passwordSalt);
cmd.Parameters.AddWithValue("PasswordFormat", passwordFormat);
cmd.Parameters.AddWithValue("Email", email);
cmd.Parameters.AddWithValue("PasswordQuestion", passwordQuestion);
cmd.Parameters.AddWithValue("PasswordAnswer", passwordAnswer);
cmd.Parameters.AddWithValue("IsApproved", isApproved);
cmd.Parameters.AddWithValue("@UTCTIMESTAMP", DateTime.UtcNow);
// Input Output Parameters
var paramUserKey = new SqlParameter("UserKey", SqlDbType.UniqueIdentifier)
{
Direction = ParameterDirection.InputOutput, Value = providerUserKey
};
cmd.Parameters.Add(paramUserKey);
// Execute
this.DbAccess.ExecuteNonQuery(cmd);
// Retrieve Output Parameters
providerUserKey = paramUserKey.Value;
}
}
/// <summary>
/// System initialize and execute script's.
/// </summary>
/// <param name="script">The script.</param>
/// <param name="scriptFile">The script file.</param>
/// <param name="useTransactions">The use transactions.</param>
public static void SystemInitializeExecutescripts(
this IDbAccess dbAccess,
[NotNull] string script,
[NotNull] string scriptFile,
bool useTransactions)
{
script = CommandTextHelpers.GetCommandTextReplaced(script);
var statements = Regex.Split(script, "\\sGO\\s", RegexOptions.IgnoreCase).ToList();
// use transactions...
if (useTransactions)
{
using (var trans = dbAccess.CreateConnectionOpen().BeginTransaction())
{
foreach (var sql in statements.Select(sql0 => sql0.Trim()))
{
try
{
if (sql.ToLower().IndexOf("setuser") >= 0)
{
continue;
}
if (sql.Length <= 0)
{
continue;
}
using (var cmd = trans.Connection.CreateCommand())
{
// added so command won't timeout anymore...
cmd.CommandTimeout = int.Parse(Config.SqlCommandTimeout);
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql.Trim();
cmd.ExecuteNonQuery();
}
}
catch (Exception x)
{
trans.Rollback();
throw new Exception(
"FILE:\n{0}\n\nERROR:\n{2}\n\nSTATEMENT:\n{1}".FormatWith(scriptFile, sql, x.Message));
}
}
trans.Commit();
}
}
else
{
// don't use transactions
foreach (var sql in statements.Select(sql0 => sql0.Trim()))
{
try
{
if (sql.ToLower().IndexOf("setuser") >= 0)
{
continue;
}
if (sql.Length <= 0)
{
continue;
}
using (var cmd = dbAccess.GetCommand(sql.Trim(), CommandType.Text))
{
dbAccess.ExecuteScalar(cmd).ToType <string>();
}
}
catch (Exception x)
{
throw new Exception(
"FILE:\n{0}\n\nERROR:\n{2}\n\nSTATEMENT:\n{1}".FormatWith(scriptFile, sql, x.Message));
}
}
}
}
public static DataSet ModerateListADataSet(
this IRepository <Forum> repository,
[NotNull] object userID,
[NotNull] object boardID)
{
using (var ds = new DataSet())
{
var sqlConnection = repository.DbAccess.CreateConnectionOpen() as SqlConnection;
using (var da = new SqlDataAdapter(CommandTextHelpers.GetObjectName("category_list"), sqlConnection))
{
using (var trans = da.SelectCommand.Connection.BeginTransaction())
{
da.SelectCommand.Transaction = trans;
da.SelectCommand.AddParam("BoardID", boardID);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(ds, CommandTextHelpers.GetObjectName("Category"));
da.SelectCommand.CommandText = CommandTextHelpers.GetObjectName("forum_moderatelist");
da.SelectCommand.AddParam("UserID", userID);
da.Fill(ds, CommandTextHelpers.GetObjectName("ForumUnsorted"));
var forumListSorted = ds.Tables[CommandTextHelpers.GetObjectName("ForumUnsorted")].Clone();
forumListSorted.TableName = CommandTextHelpers.GetObjectName("Forum");
ds.Tables.Add(forumListSorted);
forumListSorted.Dispose();
ForumListSortBasic(
ds.Tables[CommandTextHelpers.GetObjectName("ForumUnsorted")],
ds.Tables[CommandTextHelpers.GetObjectName("Forum")],
0,
0);
ds.Tables.Remove(CommandTextHelpers.GetObjectName("ForumUnsorted"));
// vzrus: Remove here all forums with no reports. Would be better to do it in query...
// Array to write categories numbers
var categories = new int[ds.Tables[CommandTextHelpers.GetObjectName("Forum")].Rows.Count];
var count = 0;
// We should make it before too as the collection was changed
ds.Tables[CommandTextHelpers.GetObjectName("Forum")].AcceptChanges();
foreach (DataRow dr in ds.Tables[CommandTextHelpers.GetObjectName("Forum")].Rows)
{
categories[count] = dr["CategoryID"].ToType <int>();
if (dr["ReportedCount"].ToType <int>() == 0 && dr["MessageCount"].ToType <int>() == 0)
{
dr.Delete();
categories[count] = 0;
}
count++;
}
ds.Tables[CommandTextHelpers.GetObjectName("Forum")].AcceptChanges();
(from DataRow dr in ds.Tables[CommandTextHelpers.GetObjectName("Category")].Rows
let dr1 = dr
where categories.All(category => category != dr1["CategoryID"].ToType <int>())
select dr).ForEach(dr => dr.Delete());
ds.Tables[CommandTextHelpers.GetObjectName("Category")].AcceptChanges();
ds.Relations.Add(
"FK_Forum_Category",
ds.Tables[CommandTextHelpers.GetObjectName("Category")].Columns["CategoryID"],
ds.Tables[CommandTextHelpers.GetObjectName("Forum")].Columns["CategoryID"]);
trans.Commit();
}
return(ds);
}
}
}
/// <summary>
/// The method saves many questions and answers to them in a single transaction
/// </summary>
/// <param name="repository">
/// The repository.
/// </param>
/// <param name="pollList">
/// List to hold all polls data
/// </param>
/// <returns>
/// Last saved poll id.
/// </returns>
public static int?Save(this IRepository <Poll> repository, [NotNull] List <PollSaveList> pollList)
{
foreach (var question in pollList)
{
var sb = new StringBuilder();
// Check if the group already exists
if (question.TopicId > 0)
{
sb.Append("select @PollGroupID = PollID from ");
sb.Append(CommandTextHelpers.GetObjectName("Topic"));
sb.Append(" WHERE TopicID = @TopicID; ");
}
else if (question.ForumId > 0)
{
sb.Append("select @PollGroupID = PollGroupID from ");
sb.Append(CommandTextHelpers.GetObjectName("Forum"));
sb.Append(" WHERE ForumID = @ForumID");
}
else if (question.CategoryId > 0)
{
sb.Append("select @PollGroupID = PollGroupID from ");
sb.Append(CommandTextHelpers.GetObjectName("Category"));
sb.Append(" WHERE CategoryID = @CategoryID");
}
// the group doesn't exists, create a new one
sb.Append("IF @PollGroupID IS NULL BEGIN INSERT INTO ");
sb.Append(CommandTextHelpers.GetObjectName("PollGroupCluster"));
sb.Append("(UserID,Flags ) VALUES(@UserID, @Flags) SET @NewPollGroupID = SCOPE_IDENTITY(); END; ");
sb.Append("INSERT INTO ");
sb.Append(CommandTextHelpers.GetObjectName("Poll"));
sb.Append(
question.Closes > DateTime.MinValue
? "(Question,Closes, UserID,PollGroupID,ObjectPath,MimeType,Flags) "
: "(Question,UserID, PollGroupID, ObjectPath, MimeType,Flags) ");
sb.Append(" VALUES(");
sb.Append("@Question");
if (question.Closes > DateTime.MinValue)
{
sb.Append(",@Closes");
}
sb.Append(
",@UserID, (CASE WHEN @NewPollGroupID IS NULL THEN @PollGroupID ELSE @NewPollGroupID END), @QuestionObjectPath,@QuestionMimeType,@PollFlags");
sb.Append("); ");
sb.Append("SET @PollID = SCOPE_IDENTITY(); ");
// The cycle through question reply choices
for (uint choiceCount = 0; choiceCount < question.Choice.GetUpperBound(1) + 1; choiceCount++)
{
if (question.Choice[0, choiceCount].IsNotSet())
{
continue;
}
sb.Append("INSERT INTO ");
sb.Append(CommandTextHelpers.GetObjectName("Choice"));
sb.Append("(PollID,Choice,Votes,ObjectPath,MimeType) VALUES (");
sb.AppendFormat(
"@PollID,@Choice{0},@Votes{0},@ChoiceObjectPath{0}, @ChoiceMimeType{0}",
choiceCount);
sb.Append("); ");
}
// we don't update if no new group is created
sb.Append("IF @PollGroupID IS NULL BEGIN ");
// fill a pollgroup field - double work if a poll exists
if (question.TopicId > 0)
{
sb.Append("UPDATE ");
sb.Append(CommandTextHelpers.GetObjectName("Topic"));
sb.Append(" SET PollID = @NewPollGroupID WHERE TopicID = @TopicID; ");
}
// fill a pollgroup field in Forum Table if the call comes from a forum's topic list
if (question.ForumId > 0)
{
sb.Append("UPDATE ");
sb.Append(CommandTextHelpers.GetObjectName("Forum"));
sb.Append(" SET PollGroupID= @NewPollGroupID WHERE ForumID= @ForumID; ");
}
// fill a pollgroup field in Category Table if the call comes from a category's topic list
if (question.CategoryId > 0)
{
sb.Append("UPDATE ");
sb.Append(CommandTextHelpers.GetObjectName("Category"));
sb.Append(" SET PollGroupID= @NewPollGroupID WHERE CategoryID= @CategoryID; ");
}
// fill a pollgroup field in Board Table if the call comes from the main page poll
sb.Append("END; ");
using (var cmd = repository.DbAccess.GetCommand(sb.ToString(), CommandType.Text))
{
var ret = new SqlParameter
{
ParameterName = "@PollID",
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(ret);
var ret2 = new SqlParameter
{
ParameterName = "@PollGroupID",
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(ret2);
var ret3 = new SqlParameter
{
ParameterName = "@NewPollGroupID",
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(ret3);
cmd.AddParam("@Question", question.Question);
if (question.Closes > DateTime.MinValue)
{
cmd.AddParam("@Closes", question.Closes);
}
// set poll group flags
var groupFlags = 0;
if (question.IsBound)
{
groupFlags = groupFlags | 2;
}
cmd.AddParam("@UserID", question.UserId);
cmd.AddParam("@Flags", groupFlags);
cmd.AddParam(
"@QuestionObjectPath",
question.QuestionObjectPath.IsNotSet() ? string.Empty : question.QuestionObjectPath);
cmd.AddParam(
"@QuestionMimeType",
question.QuestionMimeType.IsNotSet() ? string.Empty : question.QuestionMimeType);
var pollFlags = question.IsClosedBound ? 0 | 4 : 0;
pollFlags = question.AllowMultipleChoices ? pollFlags | 8 : pollFlags;
pollFlags = question.ShowVoters ? pollFlags | 16 : pollFlags;
pollFlags = question.AllowSkipVote ? pollFlags | 32 : pollFlags;
cmd.AddParam("@PollFlags", pollFlags);
for (uint choiceCount1 = 0; choiceCount1 < question.Choice.GetUpperBound(1) + 1; choiceCount1++)
{
if (question.Choice[0, choiceCount1].IsNotSet())
{
continue;
}
cmd.AddParam($"@Choice{choiceCount1}", question.Choice[0, choiceCount1]);
cmd.AddParam($"@Votes{choiceCount1}", 0);
cmd.AddParam(
$"@ChoiceObjectPath{choiceCount1}",
question.Choice[1, choiceCount1].IsNotSet()
? string.Empty
: question.Choice[1, choiceCount1]);
cmd.AddParam(
$"@ChoiceMimeType{choiceCount1}",
question.Choice[2, choiceCount1].IsNotSet()
? string.Empty
: question.Choice[2, choiceCount1]);
}
if (question.TopicId > 0)
{
cmd.AddParam("@TopicID", question.TopicId);
}
if (question.ForumId > 0)
{
cmd.AddParam("@ForumID", question.ForumId);
}
if (question.CategoryId > 0)
{
cmd.AddParam("@CategoryID", question.CategoryId);
}
repository.DbAccess.ExecuteNonQuery(cmd, true);
if (ret.Value != DBNull.Value)
{
return((int?)ret.Value);
}
}
}
return(null);
}
/// <summary>
/// The set profile properties.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="userID">
/// The user id.
/// </param>
/// <param name="values">
/// The values.
/// </param>
/// <param name="settingsColumnsList">
/// The settings columns list.
/// </param>
public void SetProfileProperties(
[NotNull] object appName,
[NotNull] object userID,
[NotNull] SettingsPropertyValueCollection values,
[NotNull] List <SettingsPropertyColumn> settingsColumnsList)
{
using (var cmd = new SqlCommand())
{
var table = CommandTextHelpers.GetObjectName("prov_Profile");
var sqlCommand = new StringBuilder("IF EXISTS (SELECT 1 FROM ").Append(table);
sqlCommand.Append(" WHERE UserId = @UserID) ");
cmd.Parameters.AddWithValue("@UserID", userID);
// Build up strings used in the query
var columnStr = new StringBuilder();
var valueStr = new StringBuilder();
var setStr = new StringBuilder();
var count = 0;
foreach (var column in settingsColumnsList)
{
// only write if it's dirty
if (values[column.Settings.Name].IsDirty)
{
columnStr.Append(", ");
valueStr.Append(", ");
columnStr.Append(column.Settings.Name);
var valueParam = "@Value" + count;
valueStr.Append(valueParam);
cmd.Parameters.AddWithValue(valueParam, values[column.Settings.Name].PropertyValue);
if (column.DataType != SqlDbType.Timestamp)
{
if (count > 0)
{
setStr.Append(",");
}
setStr.Append(column.Settings.Name);
setStr.Append("=");
setStr.Append(valueParam);
}
count++;
}
}
columnStr.Append(",LastUpdatedDate ");
valueStr.Append(",@LastUpdatedDate");
setStr.Append(",LastUpdatedDate=@LastUpdatedDate");
cmd.Parameters.AddWithValue("@LastUpdatedDate", DateTime.UtcNow);
sqlCommand.Append("BEGIN UPDATE ").Append(table).Append(" SET ").Append(setStr.ToString());
sqlCommand.Append(" WHERE UserId = '").Append(userID.ToString()).Append("'");
sqlCommand.Append(" END ELSE BEGIN INSERT ").Append(table).Append(" (UserId")
.Append(columnStr.ToString());
sqlCommand.Append(") VALUES ('").Append(userID.ToString()).Append("'").Append(valueStr.ToString())
.Append(") END");
cmd.CommandText = sqlCommand.ToString();
cmd.CommandType = CommandType.Text;
this.DbAccess.ExecuteNonQuery(cmd);
}
}
