public override int CreateUser(string pLoginName, string pPasswordHash, string pFirstName, string pLastName, string pEmail, DateTime pCreatedOn, bool pIsActive)
{
int iResult = -1;
using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection())
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "mlac_sp_usr_CreateUser";
cmd.Parameters.Add(new SqlParameter("@pLoginName", pLoginName));
cmd.Parameters.Add(new SqlParameter("@pPasswordHash", pPasswordHash));
cmd.Parameters.Add(new SqlParameter("@pFirstName", pFirstName));
cmd.Parameters.Add(new SqlParameter("@pLastName", pLastName));
cmd.Parameters.Add(new SqlParameter("@pEmail", pEmail));
cmd.Parameters.Add(new SqlParameter("@pCreatedOn", pCreatedOn));
cmd.Parameters.Add(new SqlParameter("@pIsActive", pIsActive));
cnn.Open();
iResult = Convert.ToInt32(cmd.ExecuteScalar());
}
}
return(iResult);
}
public override bool GetPasswordHash(string pLoginName, out int pUserId, out string pPasswordHash)
{
bool bResult = false;
pUserId = 0;
pPasswordHash = null;
using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection())
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "mlac_sp_usr_GetPasswordHash";
cmd.Parameters.Add(new SqlParameter("@pLoginName", pLoginName.ToLower()));
cnn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
pUserId = reader.GetInt32(0);
pPasswordHash = reader.GetString(1);
bResult = true;
}
}
}
}
return(bResult);
}
public override ACUser LoadUserInfo(int pUserId)
{
ACUser result = null;
using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection())
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "mlac_sp_usr_LoadUserInfo";
cmd.Parameters.Add(new SqlParameter("@pUserId", pUserId));
cnn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
result = new ACUser()
{
Id = pUserId,
LoginName = reader.GetString(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
Email = reader.GetString(3)
};
}
}
}
}
return(result);
}
public override bool IsEmailAvailable(string pEmail)
{
bool bResult = false;
using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection())
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "mlac_sp_usr_IsEmailAvailable";
cmd.Parameters.Add(new SqlParameter("@pEmail", pEmail));
cnn.Open();
bResult = (Convert.ToInt32(cmd.ExecuteScalar()) == 0);
}
}
return(bResult);
}
public override int DeleteAllSessions()
{
int iResult = 0;
using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection())
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "mlac_sp_ses_DeleteAllSessions";
cnn.Open();
iResult = cmd.ExecuteNonQuery();
}
}
return(iResult);
}
public override bool DeleteSession(Guid pSessionGuid)
{
bool bResult = false;
using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection())
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "mlac_sp_ses_DeleteSession";
cmd.Parameters.Add(new SqlParameter("@pSessionGuid", pSessionGuid));
cnn.Open();
bResult = (cmd.ExecuteNonQuery() > 0);
}
}
return(bResult);
}
public override int DeleteSessions(TimeSpan pOlderThan)
{
int iResult = 0;
using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection())
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "mlac_sp_ses_DeleteSessions";
DateTime dtOlderThanDate = DateTime.Now - pOlderThan;
cmd.Parameters.Add(new SqlParameter("@pOlderThanDate", dtOlderThanDate));
cnn.Open();
iResult = cmd.ExecuteNonQuery();
}
}
return(iResult);
}
public override ACRole[] ListRoles(RoleListingOptions pListingOptions)
{
List <ACRole> lstResult = new List <ACRole>();
using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection())
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "mlac_sp_rol_ListRoles";
if (pListingOptions != RoleListingOptions.All)
{
if (pListingOptions == RoleListingOptions.Hidden)
{
cmd.Parameters.Add(new SqlParameter("@pListHidden", true));
}
else if (pListingOptions == RoleListingOptions.Non_Hidden)
{
cmd.Parameters.Add(new SqlParameter("@pListHidden", false));
}
}
cnn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
lstResult.Add(new ACRole()
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
IsSystem = reader.GetBoolean(2),
IsDeletable = reader.GetBoolean(3),
IsHidden = reader.GetBoolean(4)
});
}
}
}
}
return(lstResult.ToArray());
}
public override Guid CreateSession(int pUserId, string pAccessPoint)
{
Guid result = Guid.NewGuid();
using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection())
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "mlac_sp_ses_CreateSession";
DateTime dtNow = DateTime.Now;
cmd.Parameters.Add(new SqlParameter("@pHash", result));
cmd.Parameters.Add(new SqlParameter("@pUserId", pUserId));
cmd.Parameters.Add(new SqlParameter("@pAccessPoint", (pAccessPoint == null) ? DBNull.Value : (object)pAccessPoint));
cmd.Parameters.Add(new SqlParameter("@pDateCreated", dtNow));
cmd.Parameters.Add(new SqlParameter("@pLastUpdated", dtNow));
cnn.Open();
cmd.ExecuteNonQuery();
}
}
return(result);
}