public static async Task <bool> Update <T>(Interfaces.IConnectionUtility connUtility, IEnumerable <T> entitiesToUpdate)
where T : class, Interfaces.IDBEntity
{
if (entitiesToUpdate == null || entitiesToUpdate.Count() == 0)
{
return(false);
}
foreach (var entity in entitiesToUpdate)
{
entity.modified_on = DateTime.UtcNow;
}
using (var conn = connUtility.GetConnection())
{
bool result = true;
foreach (var entity in entitiesToUpdate)
{
result = result && await conn.UpdateAsync <T>(entity);
}
return(result);
}
}
public async Task <DateTime?> RenewSession(Interfaces.IConnectionUtility connectionUtility, ViewModels.SessionVM session, int?timeoutInSeconds = null)
{
using (var connection = connectionUtility.GetConnection())
{
return(await RenewSession(connection, session, timeoutInSeconds));
}
}
public async Task <Logic.ViewModels.UserVM> GetUser(Interfaces.IConnectionUtility connectionUtility, string email_address)
{
using (var conn = connectionUtility.GetConnection())
{
return(await getUser(conn, email_address));
}
}
public async Task <DBModels.SessionsEntity> GetSessionModel(Interfaces.IConnectionUtility connectionUtility, string sessionToken)
{
using (var conn = connectionUtility.GetConnection())
{
return(await GetSessionModel(conn, sessionToken));
}
}
public async Task <Logic.Models.CreateUserResult> CreateUser(Interfaces.IConnectionUtility connectionUtility, DBModels.UserEntity userEntity, string password)
{
using (var conn = connectionUtility.GetConnection())
{
return(await CreateUser(conn, userEntity, password));
}
}
public async Task <Models.SessionValidResult> IsSessionValid(Interfaces.IConnectionUtility connectionUtility, string sessionToken, string user_id)
{
using (var conn = connectionUtility.GetConnection())
{
return(await IsSessionValid(conn, sessionToken, user_id));
}
}
public static async Task <T> GetScalar <T>(Interfaces.IConnectionUtility connUtility, string sql, Dictionary <string, object> parameters)
{
if (string.IsNullOrEmpty(sql))
{
return(default(T));
}
return(await GetScalar <T>(connUtility.GetConnection(), sql, parameters));
}
public static async Task <IEnumerable <T> > GetData <T>(Interfaces.IConnectionUtility connUtility, string sql, Dictionary <string, object> parameters)
where T : class, Interfaces.IDBEntity
{
if (string.IsNullOrEmpty(sql))
{
return(null);
}
using (var connection = connUtility.GetConnection())
{
return(await GetData <T>(connection, sql, parameters));
}
}
public async Task <bool> Logout(Interfaces.IConnectionUtility connectionUtility, string sessionToken)
{
if (string.IsNullOrEmpty(sessionToken))
{
return(false);
}
using (var conn = connectionUtility.GetConnection())
{
try
{
string logoutSql = @"
SET @user_id = '';
Select @user_id = user_id From
sessions where session_token = @session_token;
Update users
SET last_log_out = UTC_TIMESTAMP()
Where id = @user_id;
INSERT INTO session_archives(id, user_id, session_token, login_time, last_activity_time, login_method, log_out_time, is_deleted, modified_on)
Select id, user_id, session_token, login_time, last_activity_time, login_method, UTC_TIMESTAMP(), 0, UTC_TIMESTAMP()
from sessions
Where session_token = @session_token;
delete from sessions
Where session_token = @session_token;
Select ROW_COUNT();
";
var parameters = new System.Collections.Generic.Dictionary <string, object>();
parameters.Add("@session_token", sessionToken);
int result = await DB.DBUtility.GetScalar <int>(conn, logoutSql, parameters);
if (result > 0)
{
return(true);
}
}
finally
{
}
}
return(false);
}
public async Task <ViewModels.AuthenticateResultVM> Login(Interfaces.IConnectionUtility connectionUtility, string email, string password,
Interfaces.ISessionTokenProvider provider,
int?sessionTimeoutInSeconds = null)
{
if (string.IsNullOrEmpty(email) || string.IsNullOrEmpty(password))
{
return(null);
}
sessionTimeoutInSeconds = sessionTimeoutInSeconds.HasValue ? sessionTimeoutInSeconds.Value : Constants.Constants.SESSION_TIMEOUT_IN_SECONDS;
using (var conn = connectionUtility.GetConnection())
{
try
{
var entity = await authenticate(conn, email, password);
if (entity == null)
{
return(new ViewModels.AuthenticateResultVM {
ResultType = Enums.AuthenticateResultType.UserDoesNotExists,
Session = null
});
}
DBModels.SessionViewEntity sessionEntity = null;
if (entity.ResultType == Enums.AuthenticateResultType.Success)
{
sessionEntity = await createSession(conn, entity.id, provider.GetToken(), sessionTimeoutInSeconds.Value);
}
else if (entity.ResultType == Enums.AuthenticateResultType.PasswordDoesNotMatch)
{
await increasePasswordFailCount(conn, entity.id);
}
ViewModels.AuthenticateResultVM result = new ViewModels.AuthenticateResultVM()
{
ResultType = entity.ResultType,
Session = Mappers.ObjectMapper.Instance.Mapper.Map <ViewModels.SessionVM>(sessionEntity)
};
return(result);
}
finally
{
}
}
}
public static async Task <IEnumerable <T> > GetDataFromTable <T>(Interfaces.IConnectionUtility connUtility, string tableName, Dictionary <string, object> parameters)
where T : class, Interfaces.IDBEntity
{
if (string.IsNullOrEmpty(tableName))
{
return(null);
}
using (var connection = connUtility.GetConnection())
{
var data = await Dapper.SqlMapper.QueryAsync <T>(connection, $"Select * From {tableName}", parameters);
return(data);
}
}
public static async Task <object> Insert <T>(Interfaces.IConnectionUtility connUtility, IEnumerable <T> entitesToInsert)
where T : class, Interfaces.IDBEntity
{
object result = 0;
if (entitesToInsert == null || entitesToInsert.Count() == 0)
{
return(result);
}
using (var connection = connUtility.GetConnection())
{
result = await Insert <T>(connection, entitesToInsert);
}
return(result);
}
public async Task <bool> ChangePassword(Interfaces.IConnectionUtility connectionUtility, ViewModels.SessionVM session, string newPassword)
{
if (session == null)
{
return(false);
}
using (var conn = connectionUtility.GetConnection())
{
try
{
var sessionValidResult = await IsSessionValid(conn, session.SessionToken, session.UserId);
if (sessionValidResult.IsValid)
{
string logoutSql = @"
Update users
SET login_password = aes_encrypt(@password, @token),
modified_on = UTC_TIMESTAMP()
Where id = @user_id;
Select ROW_COUNT();
";
var parameters = new System.Collections.Generic.Dictionary <string, object>();
parameters.Add("@user_id", session.UserId);
parameters.Add("@sessionid", session.Id);
int result = await DB.DBUtility.GetScalar <int>(conn, logoutSql, parameters);
if (result > 0)
{
return(true);
}
}
}
finally
{
}
}
return(false);
}
public async Task <bool> ChangePassword(Interfaces.IConnectionUtility connectionUtility, string userId, string oldPassword, string newPassword)
{
if (string.IsNullOrEmpty(userId))
{
return(false);
}
using (var conn = connectionUtility.GetConnection())
{
try
{
string logoutSql = @"
Update users
SET login_password = aes_encrypt(@password, @token),
modified_on = UTC_TIMESTAMP()
Where id = @user_id AND cast(aes_decrypt(login_password, @token) AS CHAR(50)) = @oldPassword;
Select ROW_COUNT();
";
var parameters = new System.Collections.Generic.Dictionary <string, object>();
parameters.Add("@user_id", userId);
parameters.Add("@oldPassword", oldPassword);
parameters.Add("@password", newPassword);
parameters.Add("@token", Constants.Constants.TOKEN_TEXT);
int result = await DB.DBUtility.GetScalar <int>(conn, logoutSql, parameters);
if (result > 0)
{
return(true);
}
}
finally
{
}
}
return(false);
}
private async Task <bool> saveStudent(Interfaces.IConnectionUtility connection, ViewModels.StudentVM student)
{
student.Id = Guid.NewGuid().ToString();
using (var conn = connection.GetConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
await DB.DBUtility.Insert <DBModels.StudentEntity>(tran.Connection,
new DBModels.StudentEntity[] { Mappers.ObjectMapper.Instance.Mapper.Map <DBModels.StudentEntity>(student) });
var userEntity = new DBModels.UserEntity();
userEntity.id = student.Id;
userEntity.user_email = student.StudentEmail;
userEntity.is_confirmed = true;
userEntity.is_locked_out = false;
userEntity.num_of_failed_password_attempt = 0;
var createUserResult = await CreateUser(tran.Connection, userEntity, student.Password);
tran.Commit();
return(createUserResult.Success);
}
catch
{
tran.Rollback();
}
finally
{
tran.Dispose();
}
}
return(false);
}