public static void __CreateUser(string connectionStringName, string appName, string userName, string password, string passwordSalt, int passwordFormat, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey)
{
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.ToString()));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_email", email));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_passwordquestion", passwordQuestion));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_passwordanswer", passwordAnswer));
sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "i_isapproved", isApproved));
sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_newguid", Guid.NewGuid()));
sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_newuserkey", Guid.NewGuid()));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_utctimestamp", DateTime.UtcNow));
sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_userkey", providerUserKey, ParameterDirection.InputOutput));
sc.CommandText.AppendObjectQuery("prov_createuser", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure, true);
providerUserKey = sc.Parameters["i_userkey"].Value;
}
}
/// <summary>
/// Deletes the access mask.
/// </summary>
/// <param name="mid">
/// The module id.
/// </param>
/// <param name="accessMaskId">
/// The access mask id.
/// </param>
/// <returns>
/// A <see cref="T:System.Boolean"/> with true if access mask was deleted and false if deletion failed.
/// </returns>
public static bool accessmask_delete(int? mid, object accessMaskId)
{
using (var sc = new VzfSqlCommand(mid))
{
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_AccessMaskID", accessMaskId));
sc.CommandText.AppendObjectQuery("accessmask_delete", mid);
return Convert.ToBoolean(sc.ExecuteScalar(CommandType.StoredProcedure));
}
}
/// <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>
/// 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);
}
}
/// <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);
}
}
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);
}
}
public void CreateUser(string connectionStringName, string appName, string userName, string password, string passwordSalt, int passwordFormat, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey)
{
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.ToString()));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_Email", email));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_PasswordQuestion", passwordQuestion));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_PasswordAnswer", passwordAnswer));
sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "i_IsApproved", isApproved));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_UserKey", providerUserKey, ParameterDirection.InputOutput));
// sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "I_UTCTIMESTAMP", DateTime.UtcNow));
sc.CommandText.AppendObjectQuery("prov_createuser", connectionStringName);
sc.ExecuteNonQuery(CommandType.StoredProcedure, true);
providerUserKey = sc.Parameters["i_UserKey"].Value;
}
}
/// <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 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>
/// 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);
}
}
/// <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>
/// DataTable with user information
/// </returns>
public DataTable IsUserInRole(string connectionStringName, object appName, object userName, object 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_isuserinrole", connectionStringName);
return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, false);
}
}
/*
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
}
}
}
}
public static int __DeleteProfiles(string connectionStringName, object appName, object userNames)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_ApplicationName", appName)); ;
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_UserNames", userNames));
sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_newguid", Guid.NewGuid()));
sc.CommandText.AppendObjectQuery("prov_profile_deleteprofiles", connectionStringName);
return Convert.ToInt32(sc.ExecuteScalar(CommandType.StoredProcedure, false));
}
}
public static DataTable __GetProfiles(string connectionStringName, object appName, object pageIndex, object pageSize, object userNameToMatch, object inactiveSinceDate)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_ApplicationName", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_PageIndex", pageIndex));
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_PageSize", pageSize));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_UserNameToMatch", userNameToMatch));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_InactiveSinceDate", inactiveSinceDate));
sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_newguid", Guid.NewGuid()));
sc.CommandText.AppendObjectQuery("prov_profile_getprofiles", connectionStringName);
return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, 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 update user.
/// </summary>
/// <param name="connectionString">
/// The connection string.
/// </param>
/// <param name="appName">
/// The app name.
/// </param>
/// <param name="user">
/// The user.
/// </param>
/// <param name="requiresUniqueEmail">
/// The requires unique email.
/// </param>
/// <returns>
/// The <see cref="int"/>.
/// </returns>
public int UpdateUser(string connectionStringName, object appName, MembershipUser user, bool requiresUniqueEmail)
{
// 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_userkey", user.ProviderUserKey));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_USERNAME", user.UserName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_EMAIL", user.Email));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_COMMENT", user.Comment));
sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "i_isapproved", user.IsApproved));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "I_LASTLOGIN", user.LastLoginDate));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "I_LASTACTIVITY", user.LastActivityDate));
sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "I_UNIQUEEMAIL", requiresUniqueEmail));
sc.CommandText.AppendObjectQuery("P_updateuser", connectionStringName);
return Convert.ToInt32(sc.ExecuteScalar(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 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);
}
}
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 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(string connectionStringName, [NotNull] string appName, int pageIndex, int pageSize)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@ApplicationName", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@PageIndex", pageIndex));
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@PageSize", pageSize));
sc.CommandText.AppendObjectQuery("prov_getallusers", connectionStringName);
return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, false);
}
}
public static int __GetNumberInactiveProfiles(string connectionStringName, object appName, object inactiveSinceDate)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_ApplicationName", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_InactiveSinceDate", inactiveSinceDate));
sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_newguid", Guid.NewGuid()));
sc.CommandText.AppendObjectQuery("prov_profile_getnumberinactiveprofiles", connectionStringName);
return Convert.ToInt32(sc.ExecuteScalar(CommandType.StoredProcedure, 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);
}
}
private static object GetApplicationIdFromName(string connectionStringName, string appName)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_applicationname", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_newguid", Guid.NewGuid()));
sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_applicationid", null, ParameterDirection.Output));
// cmd.Parameters.Add(new FbParameter("@I_USERID", FbDbType.VarChar)).Value = userID;
sc.CommandText.AppendObjectQuery("prov_createapplication", connectionStringName);
return sc.ExecuteScalar(CommandType.StoredProcedure, false);
}
}
/// <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(string connectionStringName, [NotNull] string appName, [NotNull] object providerUserKey, [NotNull] string userName, bool userIsOnline)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
var providerUserKeyNew = providerUserKey != null ? providerUserKey.ToString() : null;
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, "@UserKey", providerUserKeyNew));
sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "@UserIsOnline", userIsOnline));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "@UTCTIMESTAMP", DateTime.UtcNow));
sc.CommandText.AppendObjectQuery("prov_getuser", connectionStringName);
using (var dt = sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, true))
{
return dt.Rows.Count > 0 ? dt.Rows[0] : null;
}
}
}
/// <summary>
/// Database Action - Get Role Exists
/// </summary>
/// <param name="appName">
/// Application Name
/// </param>
/// <param name="roleName">
/// Role Name
/// </param>
/// <returns>
/// Database containing Role Information
/// </returns>
public object GetRoleExists(string connectionStringName, object appName, object roleName)
{
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.CommandText.AppendObjectQuery("prov_role_exists", connectionStringName);
return sc.ExecuteScalar(CommandType.StoredProcedure, false);
}
}
/// <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(string connectionStringName, [NotNull] string appName, [NotNull] string email)
{
using (var sc = new VzfSqlCommand(connectionStringName))
{
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@ApplicationName", appName));
sc.Parameters.Add(sc.CreateParameter(DbType.String, "@Email", email));
sc.CommandText.AppendObjectQuery("prov_getusernamebyemail", connectionStringName);
return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, false);
}
}
/// <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 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(string connectionStringName, [NotNull] string appName, [NotNull] string userName, bool updateUser)
{
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, "@UserIsOnline", updateUser));
sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "@UTCTIMESTAMP", DateTime.UtcNow));
sc.CommandText.AppendObjectQuery("prov_getuser", connectionStringName);
return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, false);
}
}
