/// <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(DbHelpers.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 (DataTable dt = this.DbAccess.GetData(cmd))
{
if (dt.Rows.Count > 0)
{
return(dt.Rows[0]);
}
else
{
return(null);
}
}
}
}
/// <summary>
/// Database Action - Get Roles
/// </summary>
/// <param name="appName">
/// Application Name
/// </param>
/// <param name="username">
/// The username.
/// </param>
/// <returns>
/// Database containing Role Information
/// </returns>
public DataTable GetRoles(object appName, object username)
{
using (var cmd = new SqlCommand(DbHelpers.GetObjectName("prov_role_getroles")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("ApplicationName", appName);
cmd.Parameters.AddWithValue("UserName", username);
return(this.DbAccess.GetData(cmd));
}
}
/// <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(object appName, object roleName)
{
using (var cmd = new SqlCommand(DbHelpers.GetObjectName("prov_role_exists")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("ApplicationName", appName);
cmd.Parameters.AddWithValue("RoleName", roleName);
return(this.DbAccess.ExecuteScalar(cmd));
}
}
/// <summary>
/// Database Action - Find Users in Role
/// </summary>
/// <param name="appName">
/// Application Name
/// </param>
/// <param name="roleName">
/// Role Name
/// </param>
/// <returns>
/// Datatable containing User Information
/// </returns>
public DataTable FindUsersInRole(object appName, object roleName)
{
using (var cmd = new SqlCommand(DbHelpers.GetObjectName("prov_role_findusersinrole")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("ApplicationName", appName);
cmd.Parameters.AddWithValue("Rolename", roleName);
return(this.DbAccess.GetData(cmd));
}
}
/// <summary>
/// Database Action - Create Role
/// </summary>
/// <param name="appName">
/// Application Name
/// </param>
/// <param name="roleName">
/// Role Name
/// </param>
public void CreateRole(object appName, object roleName)
{
using (var cmd = new SqlCommand(DbHelpers.GetObjectName("prov_role_createrole")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("ApplicationName", appName);
cmd.Parameters.AddWithValue("Rolename", roleName);
this.DbAccess.ExecuteNonQuery(cmd);
}
}
/// <summary>
/// The get profile structure.
/// </summary>
/// <returns>
/// </returns>
public DataTable GetProfileStructure()
{
string sql = @"SELECT TOP 1 * FROM {0}".FormatWith(DbHelpers.GetObjectName("prov_Profile"));
using (var cmd = new SqlCommand(sql))
{
cmd.CommandType = CommandType.Text;
return(this.DbAccess.GetData(cmd));
}
}
/// <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(object appName, string userName, string roleName)
{
using (var cmd = new SqlCommand(DbHelpers.GetObjectName("prov_role_removeuserfromrole")))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("ApplicationName", appName);
cmd.Parameters.AddWithValue("UserName", userName);
cmd.Parameters.AddWithValue("Rolename", roleName);
this.DbAccess.ExecuteNonQuery(cmd);
}
}
/// <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(DbHelpers.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(Convert.ToInt32(cmd.Parameters["ReturnValue"].Value));
}
}
/// <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...
string type = columnType.ToString();
if (size > 0)
{
type += "(" + size + ")";
}
string sql = "ALTER TABLE {0} ADD [{1}] {2} NULL".FormatWith(
DbHelpers.GetObjectName("prov_Profile"), name, type);
using (var cmd = new SqlCommand(sql))
{
cmd.CommandType = CommandType.Text;
this.DbAccess.ExecuteNonQuery(cmd);
}
}
/// <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())
{
string table = DbHelpers.GetObjectName("prov_Profile");
StringBuilder 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();
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);
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);
}
}