public override int CountActiveSessions(string applicationName) { Invariant.Require(applicationName.Length <= this.MaxApplicationNameLength); using var connection = new Microsoft.Data.SqlClient.SqlConnection(DefaultConnectionString); connection.Open(); using var command = connection.CreateCommand(); command.CommandText = $@"SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE program_name = @applicationName"; command.Parameters.AddWithValue("applicationName", applicationName); return((int)command.ExecuteScalar()); }
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}"); } } }
public static void SaveResults(string connStr, CalcRec.HydrCalcDataRec[] recs, ulong[] edgesIDs, DateTime Calc_Time, Guid Calculation_ID) { var dictO2P = new Dictionary <ulong, Guid>(); using (new StopwatchMs("Загрузка справочника преобразования ID OIS в ID PPM")) using (var conn = new Microsoft.Data.SqlClient.SqlConnection(connStr)) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT OIS_ID, PPM_ID FROM OIS_2_PPM WHERE OIS_TABLE_NAME = 'PipeProstoyUchastok'"; using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { dictO2P[Convert.ToUInt64(rdr[0])] = rdr.GetGuid(1); } } } } using (new StopwatchMs("Bulk save into HYDRAULIC_CALCULATION_RESULT")) using (var loader = new Microsoft.Data.SqlClient.SqlBulkCopy(connStr)) { loader.DestinationTableName = "HYDRAULIC_CALCULATION_RESULT"; //loader.BatchSize = 1; var reader = new BulkDataReader <CalcRec.HydrCalcDataRec>(recs, (iEdge, r, vals) => { int i = 0; var pu_id = edgesIDs[iEdge]; vals[i++] = dictO2P.TryGetValue(pu_id, out var g) ? g : Guid.Empty; vals[i++] = Calc_Time; vals[i++] = Calculation_ID; r.GetValues(vals, ref i); }, 44); loader.WriteToServer(reader); } }