public static async Task Execute(string search) { if (string.IsNullOrWhiteSpace(search)) { throw new ArgumentException("Value cannot be null or whitespace.", nameof(search)); } await using var connection = new Microsoft.Data.SqlClient.SqlConnection(Configuration.ConnectionString); await connection.OpenAsync(); foreach (var table in connection.ExecuteQuery("SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME").ToList()) { var tableName = table["TABLE_NAME"]; //Console.WriteLine(tableName); foreach (var row in connection.ExecuteQuery($"SELECT * FROM [{tableName}]")) { foreach (var value in row.Values) { if (value == null) { continue; } var stringValue = value.ToString(); if (stringValue.Equals(search, StringComparison.OrdinalIgnoreCase)) { Console.WriteLine($"{tableName} " + row.First().Key + " #" + row.First().Value); } } } } }
/// <summary> /// Execute query. Pass isscalar when running identity queries /// </summary> /// <param name="query"></param> /// <param name="isScalar"></param> /// <returns></returns> public async Task <int> ExecuteQuery(string query, bool isScalar) { using (Microsoft.Data.SqlClient.SqlConnection cn = new Microsoft.Data.SqlClient.SqlConnection(ConnectionString)) { await cn.OpenAsync(); using (Microsoft.Data.SqlClient.SqlCommand cmd = new Microsoft.Data.SqlClient.SqlCommand(query)) { cmd.CommandTimeout = CommandTimeOut; cmd.Connection = cn; if (!isScalar) { var result = await cmd.ExecuteNonQueryAsync(); return(result); } else { var result = await cmd.ExecuteScalarAsync(); return(Convert.ToInt32(result)); } } } }
/// <summary> /// Asychronously executes the current collection of registered commands /// </summary> /// <remarks> /// Opens the connection, begins a transaction, initializes all commands, then serially awaits the execution of each command (if async.) Finally commits transaction. /// Upon failure, transaction is rolled-back. Null commands automatically return 1 for excution. /// </remarks> /// <returns>List of integers returned from each registered command</returns> public async Task <IEnumerable <int> > ExecuteAsync(System.Threading.CancellationToken cancellationToken = default) { List <int> retVal = new List <int>(); using (Microsoft.Data.SqlClient.SqlConnection conn = new Microsoft.Data.SqlClient.SqlConnection(_ConnectionString)) { await conn.OpenAsync(); var trans = (Microsoft.Data.SqlClient.SqlTransaction) await conn.BeginTransactionAsync(cancellationToken); try { int initialized = _Commands.Count; for (int i = 0; i < initialized; i++) { if (_Commands[i] != null) { _Commands[i].Initialize(conn, trans); } } for (int i = 0; i < _Commands.Count; i++) { if (_Commands[i] != null) { // This following line allows for chaining. // in other words, a executing command can add more commands. if (i >= initialized) { _Commands[i].Initialize(conn, trans); } if (_Commands[i] is ICommandAsync) { retVal.Add(await((ICommandAsync)_Commands[i]).ExecuteAsync(cancellationToken)); } else { retVal.Add(_Commands[i].Execute()); } } else { retVal.Add(1); } } await trans.CommitAsync(); } catch (Exception) { try { await trans.RollbackAsync(); } catch (Exception) { } throw; } finally { _Commands.Clear(); } } return((IEnumerable <int>)retVal); }
public override async Task KillSessionsAsync(string applicationName, DateTimeOffset?idleSince) { using var connection = new Microsoft.Data.SqlClient.SqlConnection(DefaultConnectionString); await connection.OpenAsync(); var findIdleSessionsCommand = connection.CreateCommand(); findIdleSessionsCommand.CommandText = @" SELECT session_id FROM sys.dm_exec_sessions WHERE session_id != @@SPID AND program_name = @applicationName AND ( @idleSince IS NULL OR ( (last_request_start_time IS NULL OR last_request_start_time <= @idleSince) AND (last_request_end_time IS NULL OR last_request_end_time <= @idleSince) ) )"; findIdleSessionsCommand.Parameters.AddWithValue("applicationName", applicationName); findIdleSessionsCommand.Parameters.AddWithValue("idleSince", idleSince?.DateTime ?? DBNull.Value.As <object>()).SqlDbType = SqlDbType.DateTime; var spidsToKill = new List <short>(); using (var idleSessionsReader = await findIdleSessionsCommand.ExecuteReaderAsync()) { while (await idleSessionsReader.ReadAsync()) { spidsToKill.Add(idleSessionsReader.GetInt16(0)); } } foreach (var spid in spidsToKill) { using var killCommand = connection.CreateCommand(); killCommand.CommandText = "KILL " + spid; try { await killCommand.ExecuteNonQueryAsync(); } catch (Exception ex) { Console.WriteLine($"Failed to kill {spid}: {ex}"); } } }