public IEnumerable <string> SelectRoleNamesByRoleId(IEnumerable <string> roleIDs)
{
string prefix = nameof(SelectRoleNamesByRoleId) + Constants.FNSUFFIX;
if (!roleIDs.Any())
{
return(new List <string>()); // No IDs, no roles to return.
}
List <string> sqlizedRoleIDs = new List <string>();
foreach (string roleID in roleIDs)
{
sqlizedRoleIDs.Add(Sqlize(roleID));
}
string sql = $"SELECT Name FROM {_table} WHERE Id IN ({string.Join(",",sqlizedRoleIDs)});";
int countRecord = 0;
List <string> loadedRecords = new List <string>();
try
{
using (DbCommand cmd = CreateCmd(sql))
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
++countRecord;
string name = "";
if (reader["Name"] != DBNull.Value)
{
name = (string)reader["Name"];
}
if (!string.IsNullOrWhiteSpace(name))
{
loadedRecords.Add(name);
}
} // end of while...
}// end of using...
}
catch (Exception ex)
{
string msg = $"Exception=[{ex.ToString()}]";
Log4NetAsyncLog.Error(prefix + msg);
}
return(loadedRecords);
}
public IEnumerable <string> SelectUsersInRole(String roleId)
{
string prefix = nameof(SelectUsersInRole) + Constants.FNSUFFIX;
if (string.IsNullOrWhiteSpace(roleId))
{
throw new ArgumentNullException(nameof(roleId));
}
string sql = $"SELECT UserId FROM {_table} WHERE RoleId={SqlizeNoSanitize(roleId)};";
int countRecord = 0;
List <string> loadedRecords = new List <string>();
try
{
using (DbCommand cmd = CreateCmd(sql))
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
++countRecord;
string userID = "";
if (reader["UserId"] != DBNull.Value)
{
userID = (string)reader["UserId"];
}
if (!string.IsNullOrWhiteSpace(userID))
{
loadedRecords.Add(userID);
}
} // end of while...
}// end of using...
}
catch (Exception ex)
{
string msg = $"Exception=[{ex.ToString()}]";
Log4NetAsyncLog.Error(prefix + msg);
}
return(loadedRecords);
}
// CTOR/DTOR
public WrappedConnection(string connString)
{
string prefix = nameof(WrappedConnection) + Constants.FNSUFFIX;
//conn = DbAccess.DbUtils.ConnectionFactory.CreateConn(connString);
conn = new SqlConnection(connString);
if (conn == null)
{
Log4NetAsyncLog.Error(prefix + $"Failed to get connection to database using connection string [{connString}]");
return;
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
}
public ApplicationUser SelectByEmail(string normalizedEmail)
{
string prefix = nameof(SelectByEmail) + Constants.FNSUFFIX;
IEnumerable <ApplicationUser> users = selectByColumn("NormalizedEmail", new List <string> {
normalizedEmail.ToUpper()
});
if (users.Count() == 0)
{
return(null);
}
if (users.Count() == 1)
{
return(users.First());
}
// Uh oh, More than one user with this name...
string msg = $"More than one record exists where UserName is [{normalizedEmail}]; This should never happen.";
Log4NetAsyncLog.Error(prefix + msg);
return(users.First());
}
public ApplicationUser SelectByUserId(string userId)
{
string prefix = nameof(SelectByUserId) + Constants.FNSUFFIX;
IEnumerable <ApplicationUser> users = selectByColumn("Id", new List <string> {
userId
});
if (users.Count() == 0)
{
return(null);
}
if (users.Count() == 1)
{
return(users.First());
}
// Uh oh, More than one user with this unique ID...
string msg = $"More than one record exists where UserID is [{userId}]; This should never happen.";
Log4NetAsyncLog.Error(prefix + msg);
return(users.First());
}
public ApplicationJwtRefreshToken SelectByColumnValue(string column, string colValue)
{
string prefix = nameof(SelectByColumnValue) + Constants.FNSUFFIX;
if (string.IsNullOrWhiteSpace(colValue))
{
throw new ArgumentNullException(nameof(colValue));
}
if (string.IsNullOrWhiteSpace(column))
{
throw new ArgumentNullException(nameof(column));
}
List <string> cols = new List <string>();
cols.Add("Name");
cols.Add("IP");
cols.Add("Guid");
string csvCols = string.Join(",", cols);
string sql = $"SELECT {csvCols} FROM {_table} WHERE {column}={SqlizeNoSanitize(colValue)};";
int countRecord = 0;
List <ApplicationJwtRefreshToken> loadedRecords = new List <ApplicationJwtRefreshToken>();
try
{
using (DbCommand cmd = CreateCmd(sql))
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
++countRecord;
string ip = "";
string guid = "";
string name = "";
if (reader["IP"] != DBNull.Value)
{
ip = (string)reader["IP"];
}
if (reader["Guid"] != DBNull.Value)
{
guid = (string)reader["Guid"];
}
if (reader["Name"] != DBNull.Value)
{
name = (string)reader["Name"];
}
ApplicationJwtRefreshToken token = new ApplicationJwtRefreshToken
{
Name = name,
Guid = guid,
IP = ip
};
loadedRecords.Add(token);
} // end of while...
}// end of using...
}
catch (Exception ex)
{
string msg = $"Failed to find record where {column}={SqlizeNoSanitize(colValue)}; Exception=[{ex.ToString()}]";
Log4NetAsyncLog.Error(prefix + msg);
}
int countLoadedRecords = loadedRecords.Count();
if (countLoadedRecords == 1)
{
return(loadedRecords.First());
}
if (countLoadedRecords > 1)
{
string msg = $"More than one record exists where {column}={SqlizeNoSanitize(colValue)}; This should never happen.";
Log4NetAsyncLog.Error(prefix + msg);
return(loadedRecords.FirstOrDefault());
}
// Zero case
return(null);
}
public bool ExecNonQueryTransaction(IEnumerable <string> sqlNonQueries, string prefix)
{
if (string.IsNullOrWhiteSpace(prefix))
{
prefix = nameof(ExecNonQuery) + Constants.FNSUFFIX;
}
if (_wrappedconn == null)
{
throw new Exception("The wrapped connection was null.");
}
if (!sqlNonQueries.Any())
{
throw new ArgumentException("No query strings were provided in the sqlNonQueries collection.");
}
Stopwatch stopwatch = new Stopwatch();
bool result = true;
try
{
using (DbCommand cmd = CreateTransCmd())
{
if (cmd != null)
{
try
{
// Loop the NonQuery sql strings attempting to execute them
int i = 0;
int length = sqlNonQueries.Count();
stopwatch.Start();
foreach (string sql in sqlNonQueries)
{
++i;
cmd.CommandText = sql;
string msg = string.Format("Issuing SQL command [transactional] ({0} of {1}): [{2}]", i, length, sql);
Log4NetAsyncLog.Info(prefix + msg);
cmd.ExecuteNonQuery();
}
// Execute as a transaction
cmd.Transaction.Commit();
}
catch (Exception ex1)
{
string msg1 = string.Format("Transaction failed; Rollback will be attempted; Error:{0}", ex1.Message);
Log4NetAsyncLog.Error(prefix + msg1);
try
{
cmd.Transaction.Rollback();
}
catch (Exception ex2)
{
string msg2 = string.Format("Transaction rollback failed, transaction was not active; Error:{0}", ex2.Message);
Log4NetAsyncLog.Error(prefix + msg2);
throw;
}
throw;
}
finally
{
stopwatch.Stop();
Int64 elapsedMillis = stopwatch.ElapsedMilliseconds;
Log4NetAsyncLog.Debug(prefix + string.Format("Transaction elapsed time: {0}ms", elapsedMillis));
if (stopwatch.ElapsedMilliseconds > QueryPerformanceWarningLimitMillis)
{
string msg = string.Format("Transaction elapsed time {0}ms exceeded warning limit {1}ms;", elapsedMillis, QueryPerformanceWarningLimitMillis);
Log4NetAsyncLog.Warn(prefix + msg);
}
}
}
else
{
throw new Exception("CreateTransCmd() failed to return a valid command object.");
} // end of "if (cmd != null)"
}
}
catch
{
throw;
}
return(result);
}
private IEnumerable <ApplicationUser> selectSub(string sql)
{
string prefix = nameof(selectSub) + Constants.FNSUFFIX;
int countRecord = 0;
List <ApplicationUser> loadedRecords = new List <ApplicationUser>();
try
{
using (DbCommand cmd = CreateCmd(sql))
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
++countRecord;
string id = "";
int accessFailedCount = 0;
string concurrencyStamp = "";
string email = "";
bool emailConfirmed = false;
bool lockoutEnabled = false;
DateTime lockoutEnd = DateTime.MinValue;
string passwordHash = "";
string phoneNumber = "";
bool phoneNumberConfirmed = false;
string securityStamp = "";
bool twoFactorEnabled = false;
string username = "";
bool enabled = false;
if (reader["Id"] != DBNull.Value)
{
id = (string)reader["Id"];
}
if (reader["AccessFailedCount"] != DBNull.Value)
{
accessFailedCount = (int)reader["AccessFailedCount"];
}
if (reader["ConcurrencyStamp"] != DBNull.Value)
{
concurrencyStamp = (string)reader["ConcurrencyStamp"];
}
if (reader["Email"] != DBNull.Value)
{
email = (string)reader["Email"];
}
if (reader["EmailConfirmed"] != DBNull.Value)
{
emailConfirmed = (bool)reader["EmailConfirmed"];
}
if (reader["LockoutEnabled"] != DBNull.Value)
{
lockoutEnabled = (bool)reader["LockoutEnabled"];
}
if (reader["LockoutEnd"] != DBNull.Value)
{
lockoutEnd = (DateTime)reader["LockoutEnd"];
}
if (reader["PasswordHash"] != DBNull.Value)
{
passwordHash = (string)reader["PasswordHash"];
}
if (reader["PhoneNumber"] != DBNull.Value)
{
phoneNumber = (string)reader["PhoneNumber"];
}
if (reader["PhoneNumberConfirmed"] != DBNull.Value)
{
phoneNumberConfirmed = (bool)reader["PhoneNumberConfirmed"];
}
if (reader["SecurityStamp"] != DBNull.Value)
{
securityStamp = (string)reader["SecurityStamp"];
}
if (reader["TwoFactorEnabled"] != DBNull.Value)
{
twoFactorEnabled = (bool)reader["TwoFactorEnabled"];
}
if (reader["UserName"] != DBNull.Value)
{
username = (string)reader["UserName"];
}
if (reader["Enabled"] != DBNull.Value)
{
enabled = (bool)reader["Enabled"];
}
ApplicationUser appUser = new ApplicationUser
{
UserId = id,
AccessFailedCount = accessFailedCount,
ConcurrencyStamp = concurrencyStamp,
Email = email,
EmailConfirmed = emailConfirmed,
LockoutEnabled = lockoutEnabled,
LockoutEnd = lockoutEnd,
PasswordHash = passwordHash,
PhoneNumber = phoneNumber,
PhoneNumberConfirmed = phoneNumberConfirmed,
SecurityStamp = securityStamp,
TwoFactorEnabled = twoFactorEnabled,
UserName = username,
Enabled = enabled
};
loadedRecords.Add(appUser);
} // end of while...
}// end of using...
}
catch (Exception ex)
{
string msg = $"Exception=[{ex.ToString()}]";
Log4NetAsyncLog.Error(prefix + msg);
}
return(loadedRecords);
}
