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}"); }
}
}
