/// <summary>
/// The add user to role.
/// </summary>
/// <param name="connectionString">
/// The connection string.
/// </param>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="userName">
/// The user name.
/// </param>
/// <param name="roleName">
/// The role name.
/// </param>
public void AddUserToRole(string connectionStringName, object appName, object userName, object roleName)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_ApplicationName", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_UserName", userName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_RoleName", roleName));
sc.CommandText.AppendObjectQuery("prov_role_addusertorole", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
/// <summary>
/// The upgrade membership.
/// </summary>
/// <param name="connectionString">
/// The connection string.
/// </param>
/// <param name="I_PREVIOUSVERSION">
/// The previous version.
/// </param>
/// <param name="I_NEWVERSION">
/// The new version.
/// </param>
public void UpgradeMembership(string connectionStringName, int I_PREVIOUSVERSION, int I_NEWVERSION)
{
// connectionStringName = SqlDbAccess.GetConnectionStringNameFromConnectionString(connectionStringName);
using (var sc = new VzfSqlCommand(connectionStringName))
{
// sc.DataSource.ProviderName
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_PREVIOUSVERSION", I_PREVIOUSVERSION));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_NEWVERSION", I_NEWVERSION));
sc.CommandText.AppendObjectQuery("p_upgrade", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
/// <summary>
/// Database Action - Create Role
/// </summary>
/// <param name="appName">Application Name</param>
/// <param name="roleName">Role Name</param>
/// <returns></returns>
public static void __CreateRole(string connectionStringName, object appName, object roleName)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_applicationname", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_rolename", roleName));
sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_newguid", Guid.NewGuid()));
sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_newroleguid", Guid.NewGuid()));
sc.CommandText.AppendObjectQuery("prov_role_createrole", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
/// <summary>
/// Database Action - Add User to Role
/// </summary>
/// <param name="appName">Application Name</param>
/// <param name="userName">User Name</param>
/// <param name="roleName">Role Name</param>
/// <returns></returns>
public void AddUserToRole(string connectionStringName, object appName, object userName, object roleName)
{
// connectionStringName = SqlDbAccess.GetConnectionStringNameFromConnectionString(connectionStringName);
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_applicationname", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_USERNAME", userName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_ROLENAME", roleName));
sc.CommandText.AppendObjectQuery("P_role_addusertorole", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
public void ChangePasswordQuestionAndAnswer(string connectionStringName, string appName, string userName, string passwordQuestion, string passwordAnswer)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_ApplicationName", appName));
// sc.DataSource.ProviderName
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_UserName", userName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_PasswordQuestion", passwordQuestion));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_PasswordAnswer", passwordAnswer));
sc.CommandText.AppendObjectQuery("prov_changepasswordquestionandanswer", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
public void ChangePassword(string connectionStringName, string appName, string userName, string newPassword, string newSalt, int passwordFormat, string newPasswordAnswer)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_ApplicationName", appName));
// sc.DataSource.ProviderName
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_UserName", userName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_Password", newPassword));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_PasswordSalt", newSalt));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_PasswordFormat", passwordFormat));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_PasswordAnswer", newPasswordAnswer));
sc.CommandText.AppendObjectQuery("prov_changepassword", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
/// <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(string connectionStringName, object appName, object roleName, object deleteOnlyIfRoleIsEmpty)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@ApplicationName", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@RoleName", roleName));
sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "@DeleteOnlyIfRoleIsEmpty", deleteOnlyIfRoleIsEmpty));
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@ReturnValue", null, ParameterDirection.ReturnValue));
sc.CommandText.AppendObjectQuery("prov_role_deleterole", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
return Convert.ToInt32(sc.Parameters["@ReturnValue"].Value);
}
}
public static void __AddProfileColumn(string connectionStringName, string name, string type, int size)
{
// get column type...
type = DataTypeMappers.typeToDbValueMap(name, type, size);
using (var sc = new VzfSqlCommand(connectionStringName))
{
string sql = String.Format("ALTER TABLE {0} ADD {1} {2} ", SqlDbAccess.GetVzfObjectNameFromConnectionString("prov_profile", connectionStringName), name, type);
sc.CommandText.AppendQuery(sql);
sc.ExecuteNonQuery(CommandType.Text, false);
}
}
/// <summary>
/// The update user.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="user">
/// The user.
/// </param>
/// <param name="requiresUniqueEmail">
/// The requires unique email.
/// </param>
/// <returns>
/// The update user.
/// </returns>
public int UpdateUser(string connectionStringName, [NotNull] object appName, [NotNull] MembershipUser user, bool requiresUniqueEmail)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@ApplicationName", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@UserKey", user.ProviderUserKey));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@UserName", user.UserName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@Email", user.Email));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@Comment", user.Comment));
sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "@IsApproved", user.IsApproved));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "@LastLogin", user.LastLoginDate));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "@LastActivity", user.LastActivityDate));
sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "@UniqueEmail", requiresUniqueEmail));
var p = sc.CreateParameter(DbType.Boolean, "@ReturnValue", null, ParameterDirection.ReturnValue);
sc.Parameters.Add(p);
sc.CommandText.AppendObjectQuery("prov_updateuser", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
return Convert.ToInt32(p.Value);
}
}
/// <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(string connectionStringName, [NotNull] string appName, [NotNull] string userName, [NotNull] string password, [NotNull] string passwordSalt,
int passwordFormat,
int maxInvalidPasswordAttempts,
int passwordAttemptWindow)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@ApplicationName", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@UserName", userName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@Password", password));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@PasswordSalt", passwordSalt));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@PasswordFormat", passwordFormat));
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@MaxInvalidAttempts", maxInvalidPasswordAttempts));
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@PasswordAttemptWindow", passwordAttemptWindow));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "@CurrentTimeUtc", DateTime.UtcNow));
sc.CommandText.AppendObjectQuery("prov_resetpassword", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
/// <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(string connectionStringName, [NotNull] string appName, [NotNull] string userName, bool deleteAllRelatedData)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@ApplicationName", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@UserName", userName));
sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "@DeleteAllRelated", deleteAllRelatedData));
sc.CommandText.AppendObjectQuery("prov_deleteuser", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
/// <summary>
/// The load from property value collection.
/// </summary>
/// <param name="mid">
/// The mid.
/// </param>
/// <param name="collection">
/// The collection.
/// </param>
/// <param name="tableName">
/// The table name.
/// </param>
/// <returns>
/// The <see cref="List{T}"/>.
/// </returns>
/// <exception cref="ArgumentOutOfRangeException">
/// </exception>
private static List<SettingsPropertyColumn> LoadFromPropertyValueCollection(
[NotNull] int? mid,
SettingsPropertyValueCollection collection,
string tableName)
{
var settingsColumnsList = new List<SettingsPropertyColumn>();
// clear it out just in case something is still in there...
string dataEngine;
string connectionString;
string namePattern = string.Empty;
SqlDbAccess.GetConnectionData(mid, namePattern, out dataEngine, out connectionString);
// validiate all the properties and populate the internal settings collection
foreach (SettingsPropertyValue value in collection)
{
var tempProperty = value.Property.Attributes["CustomProviderData"];
if (tempProperty == null)
{
continue;
}
int size = -1;
// split the data
string[] chunk = tempProperty.ToString().Split(new[] { ';' });
// parse custom provider data...
switch (dataEngine)
{
case SqlDbAccess.MsSql:
chunk = MsProfile.GetDbTypeAndSizeFromString(chunk);
break;
case SqlDbAccess.Npgsql:
chunk = PgProfile.GetDbTypeAndSizeFromString(chunk);
break;
case SqlDbAccess.MySql:
chunk = MySqlProfile.GetDbTypeAndSizeFromString(chunk);
break;
case SqlDbAccess.Firebird:
chunk = FbProfile.GetDbTypeAndSizeFromString(chunk);
break;
default:
throw new ArgumentOutOfRangeException(dataEngine);
}
// get the datatype and ignore case...
DbType dbType = (DbType)Enum.Parse(typeof(DbType), chunk[1], true);
if (chunk.Length > 2)
{
// handle size...
if (!int.TryParse(chunk[2], out size))
{
throw new ArgumentException("Unable to parse as integer: " + chunk[2]);
}
}
// default the size to 256 if no size is specified
if (dbType == DbType.String && size == -1)
{
size = 256;
}
settingsColumnsList.Add(new SettingsPropertyColumn(value.Property, dbType, size));
}
// getting current profile table colimns...
DataTable structure = CommonDb.GetProfileStructure(SqlDbAccess.GetConnectionStringName(mid, string.Empty), Constants.SpecialObjectNames.UserProfileMirrorTable);
// verify all the columns are there...
foreach (var column in settingsColumnsList)
{
// see if this column exists and add it if required
if (structure.Columns.Contains(column.Settings.Name)) continue;
string dataTypeName = column.DataType.ToString();
// if not, create it...
// parse custom provider data...
string sqlAddColumn;
switch (dataEngine)
{
case SqlDbAccess.MsSql:
sqlAddColumn = MsProfile.AddProfileColumn(
column.Settings.Name,
dataTypeName,
column.Size,
SqlDbAccess.GetVzfObjectName(tableName, mid));
break;
case SqlDbAccess.Npgsql:
sqlAddColumn = PgProfile.AddProfileColumn(
column.Settings.Name,
dataTypeName,
column.Size,
SqlDbAccess.GetVzfObjectName(tableName, mid));
break;
case SqlDbAccess.MySql:
sqlAddColumn = MySqlProfile.AddProfileColumn(
column.Settings.Name,
dataTypeName,
column.Size,
SqlDbAccess.GetVzfObjectName(tableName, mid));
break;
case SqlDbAccess.Firebird:
sqlAddColumn = FbProfile.AddProfileColumn(
column.Settings.Name,
dataTypeName,
column.Size,
SqlDbAccess.GetVzfObjectName(tableName, mid));
break;
default:
throw new ArgumentOutOfRangeException(dataEngine);
}
if (sqlAddColumn.IsNotSet())
{
continue;
}
using (var sc = new VzfSqlCommand(mid))
{
sc.CommandText.AppendQuery(sqlAddColumn);
sc.ExecuteNonQuery(CommandType.Text, true);
}
}
return settingsColumnsList;
}
/// <summary>
/// The set property values.
/// </summary>
/// <param name="mid">
/// The mid.
/// </param>
/// <param name="boardId">
/// The board id.
/// </param>
/// <param name="appname">
/// The appname.
/// </param>
/// <param name="tableName">
/// The table name.
/// </param>
/// <param name="userId">
/// The user id.
/// </param>
/// <param name="userName">
/// The user name.
/// </param>
/// <param name="collection">
/// The collection.
/// </param>
/// <param name="dirtyOnly">
/// The dirty only.
/// </param>
/// <exception cref="ArgumentOutOfRangeException">
/// </exception>
public static void SetPropertyValues(
int? mid,
int boardId,
string appname,
string tableName,
int userId,
string userName,
SettingsPropertyValueCollection collection,
bool dirtyOnly = true)
{
string dataEngine;
string connectionString;
string namePattern = string.Empty;
SqlDbAccess.GetConnectionData(mid, namePattern, out dataEngine, out connectionString);
// guest should not be in profile
int? userIdG = user_guest(mid, boardId);
if (userId <= 0 || userIdG == userId || collection.Count < 1)
{
return;
}
bool itemsToSave = true;
if (dirtyOnly)
{
itemsToSave = collection.Cast<SettingsPropertyValue>().Any(pp => pp.IsDirty);
}
// First make sure we have at least one item to save
if (!itemsToSave)
{
return;
}
// load the data for the configuration
List<SettingsPropertyColumn> spc = LoadFromPropertyValueCollection(mid, collection, tableName);
// Save properties to database.
if (spc == null || spc.Count <= 0 || !userName.IsSet())
{
return;
}
if (userName.IsNotSet())
{
return;
}
// check if profile exits to select insert or update
bool profileExists;
string profileExistsSql;
switch (dataEngine)
{
case SqlDbAccess.MsSql:
profileExistsSql = MsProfile.ProfileExists;
break;
case SqlDbAccess.Npgsql:
profileExistsSql = PgProfile.ProfileExists;
break;
case SqlDbAccess.MySql:
profileExistsSql = MySqlProfile.ProfileExists;
break;
case SqlDbAccess.Firebird:
profileExistsSql = FbProfile.ProfileExists;
break;
default:
throw new ArgumentOutOfRangeException(dataEngine);
}
using (var sc = new VzfSqlCommand(mid))
{
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_UserID", userId));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_ApplicationName", appname));
sc.CommandText.AppendQuery(profileExistsSql.FormatWith(SqlDbAccess.GetVzfObjectName(tableName, mid)));
profileExists = Convert.ToInt32(sc.ExecuteScalar(CommandType.Text, true)) > 0;
}
// create data for saving profile properties
string sql;
var paramData = new List<Tuple<DbType, string, object>>();
// Build up strings used in the query
var columnStr = new StringBuilder();
var valueStr = new StringBuilder();
var setStr = new StringBuilder();
spc.ForEach(
column =>
{
if (!collection[column.Settings.Name].IsDirty) return;
var nameParam = collection[column.Settings.Name].Name;
var valParam = collection[column.Settings.Name].PropertyValue;
nameParam = "@i_" + nameParam;
paramData.Add(new Tuple<DbType, string, object>(column.DataType, nameParam, valParam));
valueStr.Append(nameParam);
valueStr.Append(",");
columnStr.Append(column.Settings.Name);
columnStr.Append(",");
setStr.Append(column.Settings.Name);
setStr.Append("=");
setStr.Append(nameParam);
setStr.Append(",");
});
// first predefined parameter without separator
columnStr.Append("LastUpdatedDate ");
valueStr.Append("@i_LastUpdatedDate");
setStr.Append("LastUpdatedDate=@i_LastUpdatedDate");
paramData.Add(new Tuple<DbType, string, object>(DbType.DateTime, "@i_LastUpdatedDate", DateTime.UtcNow));
columnStr.Append(",LastActivity ");
valueStr.Append(",@i_LastActivity");
setStr.Append(",LastActivity=@i_LastActivity");
paramData.Add(new Tuple<DbType, string, object>(DbType.DateTime, "@i_LastActivity", DateTime.UtcNow));
columnStr.Append(",ApplicationName ");
valueStr.Append(",@i_ApplicationName");
setStr.Append(",ApplicationName=@i_ApplicationName");
columnStr.Append(",IsAnonymous ");
valueStr.Append(",@i_IsAnonymous");
setStr.Append(",IsAnonymous=@i_IsAnonymous");
paramData.Add(new Tuple<DbType, string, object>(DbType.Boolean, "@i_IsAnonymous", false));
columnStr.Append(",UserName ");
valueStr.Append(",@i_UserName");
setStr.Append(",UserName=@i_UserName");
paramData.Add(new Tuple<DbType, string, object>(DbType.String, "@i_UserName", userName));
paramData.Add(new Tuple<DbType, string, object>(DbType.Int32, "@i_UserID", userId));
paramData.Add(new Tuple<DbType, string, object>(DbType.String, "@i_ApplicationName", appname));
// start saving...
switch (dataEngine)
{
case SqlDbAccess.MsSql:
sql = MsProfile.SetProfileProperties(
setStr.ToString(),
columnStr.ToString(),
valueStr.ToString(),
profileExists);
break;
case SqlDbAccess.Npgsql:
sql = PgProfile.SetProfileProperties(
setStr.ToString(),
columnStr.ToString(),
valueStr.ToString(),
profileExists);
break;
case SqlDbAccess.MySql:
sql = MySqlProfile.SetProfileProperties(
setStr.ToString(),
columnStr.ToString(),
valueStr.ToString(),
profileExists);
break;
case SqlDbAccess.Firebird:
sql = FbProfile.SetProfileProperties(
setStr.ToString(),
columnStr.ToString(),
valueStr.ToString(),
profileExists);
break;
default:
throw new ArgumentOutOfRangeException(dataEngine);
}
using (var sc = new VzfSqlCommand(mid))
{
// add parameters in a loop
paramData.ForEach(
parameter => sc.Parameters.Add(sc.CreateParameter(parameter.Item1, parameter.Item2, parameter.Item3)));
sc.CommandText.AppendQuery(sql.FormatWith(SqlDbAccess.GetVzfObjectName(tableName, mid)));
sc.ExecuteNonQuery(CommandType.Text, true);
}
}
/// <summary>
/// The replace_words_save.
/// </summary>
/// <param name="mid">
/// The mid.
/// </param>
/// <param name="boardId">
/// The board id.
/// </param>
/// <param name="id">
/// The id.
/// </param>
/// <param name="badword">
/// The badword.
/// </param>
/// <param name="goodword">
/// The goodword.
/// </param>
public static void replace_words_save(int? mid, object boardId, object id, object badword, object goodword)
{
using (var sc = new VzfSqlCommand(mid))
{
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_BoardID", boardId));
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_ID", id));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_BadWord", badword));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_GoodWord", goodword));
sc.CommandText.AppendObjectQuery("replace_words_save", mid);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
/// <summary>
/// The replace_words_delete.
/// </summary>
/// <param name="mid">
/// The mid.
/// </param>
/// <param name="id">
/// The id.
/// </param>
public static void replace_words_delete(int? mid, object id)
{
using (var sc = new VzfSqlCommand(mid))
{
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_ID", id));
sc.CommandText.AppendObjectQuery("replace_words_delete", mid);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
/// <summary>
/// The reset password.
/// </summary>
/// <param name="connectionString">
/// The connection string.
/// </param>
/// <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(string connectionStringName, string appName, string userName, string password, string passwordSalt, int passwordFormat, int maxInvalidPasswordAttempts, int passwordAttemptWindow)
{
// connectionStringName = SqlDbAccess.GetConnectionStringNameFromConnectionString(connectionStringName);
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_APPLICATIONNAME", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_USERNAME", userName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_password", password));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_passwordsalt", passwordSalt));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_passwordformat", passwordFormat));
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_maxinvalidattempts", maxInvalidPasswordAttempts));
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_passwordattemptwindow", passwordAttemptWindow));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_currenttimeutc", DateTime.UtcNow));
sc.CommandText.AppendObjectQuery("P_resetpassword", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
/// <summary>
/// The unlock user.
/// </summary>
/// <param name="connectionString">
/// The connection string.
/// </param>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="userName">
/// The user name.
/// </param>
public void UnlockUser(string connectionStringName, string appName, string userName)
{
// connectionStringName = SqlDbAccess.GetConnectionStringNameFromConnectionString(connectionStringName);
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_APPLICATIONNAME", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_USERNAME", userName));
sc.CommandText.AppendObjectQuery("P_unlockuser", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
/// <summary>
/// The add profile column.
/// </summary>
/// <param name="connectionStringName">
/// The connection string name.
/// </param>
/// <param name="name">
/// The name.
/// </param>
/// <param name="type">
/// The type.
/// </param>
/// <param name="size">
/// The size.
/// </param>
public void AddProfileColumn(string connectionStringName, string name, string type, int size)
{
type = DataTypeMappers.typeToDbValueMap(name, type, size);
if (type.ToLowerInvariant().Contains("varchar") && Config.DatabaseEncoding != null)
{
type += " CHARACTER SET " + Config.DatabaseEncoding;
if (Config.DatabaseCollation != null)
{
type += " COLLATE " + Config.DatabaseEncoding + "_" + Config.DatabaseCollation;
}
}
using (var sc = new VzfSqlCommand(connectionStringName))
{
string sql = string.Format("ALTER TABLE {0} ADD `{1}` {2};", SqlDbAccess.GetVzfObjectNameFromConnectionString("prov_Profile", connectionStringName), name, type);
sc.CommandText.AppendQuery(sql);
sc.ExecuteNonQuery(CommandType.Text, false);
}
}
/// <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(string connectionStringName, [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 sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@ApplicationName", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@Username", userName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@Password", password));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@PasswordSalt", passwordSalt));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@PasswordFormat", passwordFormat.ToString()));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@Email", email));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@PasswordQuestion", passwordQuestion));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@PasswordAnswer", passwordAnswer));
sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "@IsApproved", isApproved));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "@UTCTIMESTAMP", DateTime.UtcNow));
var paramUserKey = sc.CreateParameter(DbType.Guid, "@UserKey", providerUserKey, ParameterDirection.InputOutput);
sc.Parameters.Add(paramUserKey);
sc.CommandText.AppendObjectQuery("prov_createuser", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure, true);
providerUserKey = paramUserKey.Value;
}
}
public void SetProfilePropertiesOld(string connectionStringName, object appName, object userID, SettingsPropertyValueCollection values, List<SettingsPropertyColumn> settingsColumnsList)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
bool profileExists = false;
string table =
SqlDbAccess.GetVzfObjectNameFromConnectionString("prov_Profile", connectionStringName);
using (var sc1 = new VzfSqlCommand(connectionStringName))
{
// cmd.Parameters.Add(new FbParameter("@I_USERID", FbDbType.VarChar)).Value = userID;
sc1.CommandText.AppendQuery(string.Format("SELECT COUNT(1) FROM {0} WHERE UserID =UNHEX(REPLACE('{1}','-',''));", table, MySqlHelpers.GuidConverter(new Guid(userID.ToString())).ToString()));
profileExists = Convert.ToBoolean(sc1.ExecuteScalar(CommandType.Text, false));
}
StringBuilder MySqlCommandTextMain =
new StringBuilder("");
sc.Parameters.Add(sc.CreateParameter(DbType.String, "?i_UserID", MySqlHelpers.GuidConverter(new Guid(userID.ToString())).ToString()));
// Build up strings used in the query
StringBuilder columnStr = new StringBuilder();
StringBuilder valueStr = new StringBuilder();
StringBuilder setStr = new StringBuilder();
int count = 0;
foreach (SettingsPropertyColumn column in settingsColumnsList)
{
// only write if it's dirty
if (values[column.Settings.Name].IsDirty)
{
columnStr.Append(", ");
valueStr.Append(", ");
columnStr.Append(column.Settings.Name);
string valueParam = "?Value" + count;
valueStr.Append(valueParam);
sc.Parameters.Add(sc.CreateParameter(column.DataType, valueParam, values[column.Settings.Name].PropertyValue));
if (column.DataType != DbType.DateTime)
{
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");
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "?LastUpdatedDate", DateTime.UtcNow));
if (profileExists)
{
MySqlCommandTextMain.Append(" UPDATE ").Append(table).Append(" SET ").Append(setStr.ToString());
MySqlCommandTextMain.Append(" WHERE UserID =UNHEX(REPLACE(@i_UserID,'-',''))");
MySqlCommandTextMain.Append(";");
}
else
{
MySqlCommandTextMain.Append("INSERT INTO ").Append(table).Append(" (UserID").Append(columnStr.ToString());
MySqlCommandTextMain.Append(") VALUES (UNHEX(REPLACE(@i_UserID,'-',''))").Append(valueStr.ToString()).Append(");");
}
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@i_UserID", MySqlHelpers.GuidConverter(new Guid(userID.ToString())).ToString()));
sc.CommandText.AppendQuery(MySqlCommandTextMain.ToString());
sc.ExecuteNonQuery(CommandType.Text, false);
}
}
/// <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(string connectionStringName, [NotNull] string appName, int timeWindow)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@ApplicationName", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@TimeWindow", timeWindow));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "@CurrentTimeUtc", DateTime.UtcNow));
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@ReturnValue", null, ParameterDirection.ReturnValue));
sc.CommandText.AppendObjectQuery("prov_getnumberofusersonline", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
return Convert.ToInt32(sc.Parameters["@ReturnValue"].Value);
}
}
public void SetProfileProperties(string connectionStringName, object appName, object userID, SettingsPropertyValueCollection values, List<SettingsPropertyColumn> settingsColumnsList)
{
if (YAF.Classes.Config.GetConfigValueAsBool("YAF.OldProfileProvider", true))
SetProfilePropertiesOld(connectionStringName, appName, userID, values, settingsColumnsList);
// Apply here new profile properties
SettingsContext sctxt = new SettingsContext();
sctxt.Add("IsAuthenticated", true);
sctxt.Add("UserID", userID);
sctxt.Add("ApplicationName", appName);
bool isAuthenticated = true;
if (string.IsNullOrEmpty(userID.ToString())) return;
if (values.Count < 1) return;
string index = string.Empty;
string stringData = string.Empty;
byte[] binaryData = null;
if (PackProfileData(values, isAuthenticated, ref index, ref stringData, ref binaryData) < 1) return;
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_userId", MySqlHelpers.GuidConverter(new Guid(userID.ToString())).ToString()));
sc.Parameters.Add(sc.CreateParameter(DbType.Binary, "I_index", index));
sc.Parameters.Add(sc.CreateParameter(DbType.Binary, "I_stringdata", stringData));
sc.Parameters.Add(sc.CreateParameter(DbType.Binary, "I_binarydata", binaryData));
// cmd.Parameters.Add(new FbParameter("@I_USERID", FbDbType.VarChar)).Value = userID;
sc.CommandText.AppendObjectQuery("prov_setprofileproperties", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure, false);
}
// EOF 'apply new profile properties'
}
/// <summary>
/// The unlock user.
/// </summary>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="userName">
/// The user name.
/// </param>
public void UnlockUser(string connectionStringName, [NotNull] string appName, [NotNull] string userName)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@ApplicationName", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@UserName", userName));
sc.CommandText.AppendObjectQuery("prov_unlockuser", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
/// <summary>
/// The change password.
/// </summary>
/// <param name="connectionString">
/// The connection string.
/// </param>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="username">
/// The username.
/// </param>
/// <param name="newPassword">
/// The new password.
/// </param>
/// <param name="newSalt">
/// The new salt.
/// </param>
/// <param name="passwordFormat">
/// The password format.
/// </param>
/// <param name="newPasswordAnswer">
/// The new password answer.
/// </param>
public void ChangePassword(string connectionStringName, string appName, string username, string newPassword, string newSalt, int passwordFormat, string newPasswordAnswer)
{
// connectionStringName = SqlDbAccess.GetConnectionStringNameFromConnectionString(connectionStringName);
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_APPLICATIONNAME", appName));
// sc.DataSource.ProviderName
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_USERNAME", username));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_PASSWORD", newPassword));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_PASSWORDSALT", newSalt));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_PASSWORDFORMAT", passwordFormat));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_PASSWORDANSWER", newPasswordAnswer));
sc.CommandText.AppendObjectQuery("P_changepassword", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
/// <summary>
/// The upgrade membership.
/// </summary>
/// <param name="previousVersion">
/// The previous version.
/// </param>
/// <param name="newVersion">
/// The new version.
/// </param>
public void UpgradeMembership(string connectionStringName, int previousVersion, int newVersion)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
// sc.DataSource.ProviderName
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@PreviousVersion", previousVersion));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@NewVersion", newVersion));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "@UTCTIMESTAMP", DateTime.UtcNow));
sc.CommandText.AppendObjectQuery("prov_upgrade", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
/// <summary>
/// The change password question and answer.
/// </summary>
/// <param name="connectionString">
/// The connection string.
/// </param>
/// <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(string connectionStringName, string appName, string username, string passwordQuestion, string passwordAnswer)
{
// connectionStringName = SqlDbAccess.GetConnectionStringNameFromConnectionString(connectionStringName);
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_APPLICATIONNAME", appName));
// sc.DataSource.ProviderName
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_USERNAME", username));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_PASSWORDQUESTION", passwordQuestion));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_PASSWORDANSWER", passwordAnswer));
sc.CommandText.AppendObjectQuery("P_CHANGEPASSQUESTIONANDANSWER", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
/*
public void ValidateAddColumnInProfile( string columnName, NpgsqlTypes.NpgsqlDbType columnType )
{
NpgsqlCommand cmd = new NpgsqlCommand( sprocName );
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( "@ApplicationName", appName );
cmd.Parameters.Add( "@Username", username );
cmd.Parameters.Add( "@IsUserAnonymous", isAnonymous );
return cmd;
}
*/
public static void __SetPropertyValues(
string connectionStringName,
SettingsContext context,
SettingsPropertyValueCollection collection,
List<SettingsPropertyColumn> settingsColumnsList)
{
bool isAuthenticated = (bool)context["IsAuthenticated"];
string appName = (string)context["ApplicationName"];
// sc.Add("IsAnonymous", isAnonymous);
// sc.Add("LastActivityDate", lastActivityDate);
if (context["UserID"] == null)
{
return;
}
var userid = (Guid)context["UserID"];
if (collection.Count < 1)
{
return;
}
string index = string.Empty;
string stringData = string.Empty;
byte[] binaryData = null;
int count = EncodeProfileData(collection, isAuthenticated, ref index, ref stringData, ref binaryData);
if (count < 1)
{
return;
}
// save the encoded profile data to the database
// using (TransactionScope ts = new TransactionScope())
// {
// either create a new user or fetch the existing user id
Guid userId = SchemaManager.CreateOrFetchUserId(userid, isAuthenticated);
bool profileExists = false;
string table =
SqlDbAccess.GetVzfObjectNameFromConnectionString("prov_profile", connectionStringName);
using (var sc1 = new VzfSqlCommand(connectionStringName))
{
// cmd.Parameters.Add(new FbParameter("@I_USERID", FbDbType.VarChar)).Value = userID;
sc1.CommandText.AppendQuery(String.Format("SELECT COUNT(1) FROM {0} WHERE userid ='{1}';", table, new Guid(userId.ToString())));
profileExists = Convert.ToBoolean(sc1.ExecuteScalar(CommandType.Text, false));
sc1.CommandText.Clear();
}
var mu = Membership.GetUser(userId);
if (profileExists)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_valueindex", index));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_stringdata", stringData));
sc.Parameters.Add(sc.CreateParameter(DbType.Binary, "I_binarydata", binaryData));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_lastupdateddate", DateTime.UtcNow));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_lastactivitydate", mu.LastActivityDate));
sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_applicationid", (Guid)GetApplicationIdFromName(connectionStringName,appName)));
sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "i_isanonymous", false));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_username", mu.UserName));
sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_userId", userId));
sc.CommandText.AppendQuery(string.Format(
@"UPDATE {0} SET valueindex = :i_valueindex,stringdata= :i_stringData,binarydata= :i_binaryData,
lastupdateddate= :i_lastupdateddate,lastactivitydate= :i_lastactivitydate,
username= :i_username WHERE userid = :i_userid and applicationid = :i_applicationid;",
table));
int res = sc.ExecuteNonQuery(CommandType.Text, false);
if (res == 0)
{
// Error
}
}
}
else
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_userId", userId));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_valueindex", index));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_stringdata", stringData));
sc.Parameters.Add(sc.CreateParameter(DbType.Binary, "I_binarydata", binaryData));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_lastupdateddate", DateTime.UtcNow));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_lastactivitydate", mu.LastActivityDate));
sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_applicationid", (Guid)GetApplicationIdFromName(connectionStringName,appName)));
sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "i_isanonymous", false));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_username", mu.UserName));
sc.CommandText.AppendQuery(string.Format(@"INSERT INTO {0}
(userid,valueindex,stringdata,binarydata,lastupdateddate,lastactivitydate,
applicationid,isanonymous,username)
VALUES (:i_userid,:i_valueindex,:i_stringData,:i_binaryData,:i_lastupdateddate,
:i_lastactivitydate,:i_applicationid,:i_isanonymous,:i_username) ;",
table));
int res = sc.ExecuteNonQuery(CommandType.Text, false);
if (res == 0)
{
// Error
}
}
}
}
/// <summary>
/// The delete user.
/// </summary>
/// <param name="connectionString">
/// The connection string.
/// </param>
/// <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(string connectionStringName, string appName, string username, bool deleteAllRelatedData)
{
// connectionStringName = SqlDbAccess.GetConnectionStringNameFromConnectionString(connectionStringName);
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_APPLICATIONNAME", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_USERNAME", username));
sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "i_deleteallrelated", deleteAllRelatedData));
sc.CommandText.AppendObjectQuery("P_deleteuser", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
/// <summary>
/// Database Action - Remove User From Role
/// </summary>
/// <param name="appName">
/// Application Name
/// </param>
/// <param name="userName">
/// User Name
/// </param>
/// <param name="roleName">
/// Role Name
/// </param>
public void RemoveUserFromRole(string connectionStringName, object appName, string userName, string roleName)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@ApplicationName", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@UserName", userName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@RoleName", roleName));
sc.CommandText.AppendObjectQuery("prov_role_removeuserfromrole", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
}
}
/// <summary>
/// The get number of users online.
/// </summary>
/// <param name="connectionString">
/// The connection string.
/// </param>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="TimeWindow">
/// The time window.
/// </param>
/// <returns>
/// The <see cref="int"/>.
/// </returns>
public int GetNumberOfUsersOnline(string connectionStringName, string appName, int TimeWindow)
{
// connectionStringName = SqlDbAccess.GetConnectionStringNameFromConnectionString(connectionStringName);
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_APPLICATIONNAME", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "I_TIMEWINDOW", TimeWindow));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_currenttimeutc", DateTime.UtcNow));
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_returnvalue", null, ParameterDirection.ReturnValue));
sc.CommandText.AppendObjectQuery("P_getnumberofusersonline", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure);
return Convert.ToInt32(sc.Parameters["@i_returnvalue"].Value);
}
}
