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 bool ExecNonQuery(string sql, string prefix = "") { if (string.IsNullOrWhiteSpace(prefix)) { prefix = nameof(ExecNonQuery) + Constants.FNSUFFIX; } if (_wrappedconn == null) { throw new Exception("The wrapped connection was null."); } if (string.IsNullOrWhiteSpace(sql)) { throw new ArgumentException("The argument sql string was null, white space or empty."); } Log4NetAsyncLog.Info(prefix + $"Issuing SQL Command [{sql}]"); Stopwatch stopwatch = new Stopwatch(); bool result = false; try { using (DbCommand cmd = CreateCmd(sql)) { stopwatch.Start(); if (cmd != null) { cmd.ExecuteNonQuery(); result = true; } } } catch { throw; } finally { stopwatch.Stop(); if (stopwatch.ElapsedMilliseconds > QueryPerformanceWarningLimitMillis) { Log4NetAsyncLog.Warn(prefix + $"Query elapsed time {stopwatch.ElapsedMilliseconds}ms exceeded limit {QueryPerformanceWarningLimitMillis}ms for SQL command:[{sql}]"); } } return(result); }
protected virtual void Dispose(bool bDisposing) { var prefix = "Dispose(bool) - "; string msg = ""; // Do not dispose twice. if (this.bDisposed) { return; } if (bDisposing) { // Manual disposal via explicit call in code //msg = "This function has been called from code: EXPLICIT"; //logger.Debug(prefix + msg); if (conn == null) { //msg = "The connection is null; Cannot close connection."; //logger.Warn(prefix + msg); } else { //msg = "The connection is not null; Attempting to close connection."; //logger.Debug(prefix + msg); try { conn.Close(); //msg = "The connection has been successfully closed."; //logger.Debug(prefix + msg); } catch (Exception ex) { msg = string.Format("Failed to close database connection: {0}", ex.Message); Log4NetAsyncLog.Warn(prefix + msg); } } // end of "if (conn == null)" } bDisposed = true; }
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 void Log <TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func <TState, Exception, string> formatter) { ++CountCalls_Log; if (!IsEnabled(logLevel)) { return; } if (formatter == null) { throw new ArgumentNullException(nameof(formatter)); } if (state != null || exception != null) { Log4NetAsyncLog.Enqueue(logLevel, eventId, (object)state, exception, formatter, state.GetType(), _name); } }
static void Main(string[] args) { string prefix = nameof(Main) + _fnSuffix; _logger.LogDebug(prefix + "Entering"); // Example: Find your service you created in ConfigureServices and make it do something //var work = _serviceProvider.GetService<Work>(); //work.DoSomething(); Console.WriteLine(prefix + "Press return to exit..."); Console.ReadLine(); _logger.LogDebug(prefix + "Exiting"); Log4NetAsyncLog.Stop(); // Gracefully stop logs }
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); }