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 (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "SELECT [id],[password_hash] FROM [mlac_tbl_users] WHERE LOWER([login_name])=@pLoginName;";
cmd.Parameters.Add(new SQLiteParameter("@pLoginName", pLoginName.ToLower()));
cnn.Open();
using (SQLiteDataReader 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 (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "SELECT [login_name],[first_name],[last_name],[email] FROM [mlac_tbl_users] WHERE [id]=@pUserId;";
cmd.Parameters.Add(new SQLiteParameter("@pUserId", pUserId));
cnn.Open();
using (SQLiteDataReader 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 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 (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "INSERT INTO [mlac_tbl_users] ([login_name],[password_hash],[first_name],[last_name],[email],[created_on],[is_active]) " +
"VALUES (@pLoginName,@pPasswordHash,@pFirstName,@pLastName,@pEmail,@pCreatedOn,@pIsActive)" +
";SELECT last_insert_rowid() AS [id];";
cmd.Parameters.Add(new SQLiteParameter("@pLoginName", pLoginName));
cmd.Parameters.Add(new SQLiteParameter("@pPasswordHash", pPasswordHash));
cmd.Parameters.Add(new SQLiteParameter("@pFirstName", pFirstName));
cmd.Parameters.Add(new SQLiteParameter("@pLastName", pLastName));
cmd.Parameters.Add(new SQLiteParameter("@pEmail", pEmail));
cmd.Parameters.Add(new SQLiteParameter("@pCreatedOn", pCreatedOn));
cmd.Parameters.Add(new SQLiteParameter("@pIsActive", pIsActive));
cnn.Open();
iResult = Convert.ToInt32(cmd.ExecuteScalar());
}
}
return(iResult);
}
public override bool IsEmailAvailable(string pEmail)
{
bool bResult = false;
using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection())
{
using (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(*) FROM [mlac_tbl_users] WHERE LOWER([email])=@pEmail;";
cmd.Parameters.Add(new SQLiteParameter("@pEmail", pEmail.ToLower()));
cnn.Open();
bResult = (Convert.ToInt32(cmd.ExecuteScalar()) == 0);
}
}
return(bResult);
}
public override int DeleteAllSessions()
{
int iResult = 0;
using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection())
{
using (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "DELETE FROM [mlac_tbl_sessions];";
cnn.Open();
iResult = cmd.ExecuteNonQuery();
}
}
return(iResult);
}
public override bool DeleteSession(Guid pSessionGuid)
{
bool bResult = false;
using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection())
{
using (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "DELETE FROM [mlac_tbl_sessions] WHERE [hash]=@pSessionGuid;";
cmd.Parameters.Add(new SQLiteParameter("@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 (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "DELETE FROM [mlac_tbl_sessions] WHERE [last_updated] < @pOlderThanDate;";
DateTime dtOlderThanDate = DateTime.Now - pOlderThan;
cmd.Parameters.Add(new SQLiteParameter("@pOlderThanDate", dtOlderThanDate));
cnn.Open();
iResult = cmd.ExecuteNonQuery();
}
}
return(iResult);
}
public override bool UpdateSession(Guid pSessionGuid)
{
bool bResult = false;
using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection())
{
using (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "UPDATE [mlac_tbl_sessions] SET [last_updated]=@pLastUpdated WHERE [hash]=@pSessionGuid;";
DateTime dtNow = DateTime.Now;
cmd.Parameters.Add(new SQLiteParameter("@pLastUpdated", dtNow));
cmd.Parameters.Add(new SQLiteParameter("@pSessionGuid", pSessionGuid));
cnn.Open();
bResult = (cmd.ExecuteNonQuery() > 0);
}
}
return(bResult);
}
public override ACRole[] ListRoles(RoleListingOptions pListingOptions)
{
List <ACRole> lstResult = new List <ACRole>();
using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection())
{
using (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "SELECT [id],[name],[is_system],[is_deletable],[is_hidden] FROM [mlac_tbl_roles]";
if (pListingOptions != RoleListingOptions.All)
{
cmd.CommandText += " WHERE [is_hidden]=@pListHidden;";
if (pListingOptions == RoleListingOptions.Hidden)
{
cmd.Parameters.Add(new SQLiteParameter("@pListHidden", true));
}
else if (pListingOptions == RoleListingOptions.Non_Hidden)
{
cmd.Parameters.Add(new SQLiteParameter("@pListHidden", false));
}
}
cnn.Open();
using (SQLiteDataReader 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 (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "INSERT INTO [mlac_tbl_sessions] ([hash],[user_id],[access_point],[date_created],[last_updated]) " +
"VALUES (@pHash,@pUserId,@pAccessPoint,@pDateCreated,@pLastUpdated);";
DateTime dtNow = DateTime.Now;
cmd.Parameters.Add(new SQLiteParameter("@pHash", result));
cmd.Parameters.Add(new SQLiteParameter("@pUserId", pUserId));
cmd.Parameters.Add(new SQLiteParameter("@pAccessPoint", pAccessPoint));//(pAccessPoint == null) ? DBNull.Value : (object)pAccessPoint));
cmd.Parameters.Add(new SQLiteParameter("@pDateCreated", dtNow));
cmd.Parameters.Add(new SQLiteParameter("@pLastUpdated", dtNow));
cnn.Open();
cmd.ExecuteNonQuery();
}
}
return(result);
}