public List <ReportVacanciesResultItem> ReportVacanciesList(DateTime fromDate, DateTime toDate) { _logger.Debug($"Executing ReportVacanciesList report with toDate {toDate} and fromdate {fromDate}..."); var response = new List <ReportVacanciesResultItem>(); var command = new SqlCommand("dbo.ReportVacanciesList", (SqlConnection)_getOpenConnection.GetOpenConnection()); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("ManagedBy", SqlDbType.Int).Value = -1; command.Parameters.Add("Type", SqlDbType.Int).Value = -1; command.Parameters.Add("lscRegion", SqlDbType.Int).Value = -1; command.Parameters.Add("localauthority", SqlDbType.Int).Value = -1; command.Parameters.Add("Postcode", SqlDbType.VarChar).Value = "n/a"; command.Parameters.Add("sector", SqlDbType.Int).Value = -1; command.Parameters.Add("framework", SqlDbType.Int).Value = -1; command.Parameters.Add("vacancyType", SqlDbType.Int).Value = -1; command.Parameters.Add("dateFrom", SqlDbType.DateTime).Value = fromDate; command.Parameters.Add("dateTo", SqlDbType.DateTime).Value = toDate; command.Parameters.Add("VacancyStatus", SqlDbType.Int).Value = -1; command.Parameters.Add("ProviderSiteID", SqlDbType.Int).Value = 0; command.Parameters.Add("RecAgentID", SqlDbType.Int).Value = -1; command.Parameters.Add("EmployerID", SqlDbType.Int).Value = -1; command.Parameters.Add("rowcount", SqlDbType.Int).Value = 0; command.CommandTimeout = 180; var reader = command.ExecuteReader(); while (reader.Read()) { response.Add(new ReportVacanciesResultItem() { vacancyid = reader[0].ToString(), VacancyTitle = reader[1].ToString(), VacancyType = reader[2].ToString(), Reference = reader[3].ToString(), EmployerName = reader[4].ToString(), EmployerNameActual = reader[5].ToString(), EmployerAnonymousName = reader[6].ToString(), IsEmployerAnonymous = reader[7].ToString(), Postcode = reader[8].ToString(), Sector = reader[9].ToString(), Framework = reader[10].ToString(), FrameworkStatus = reader[11].ToString(), LearningProvider = reader[12].ToString(), NumberOfPositions = reader[13].ToString(), DatePosted = reader[14].ToString(), ClosingDate = reader[15].ToString(), NoOfPositionsAvailable = reader[16].ToString(), NoOfApplications = reader[17].ToString(), Status = reader[18].ToString(), DeliverySite = reader[19].ToString() }); } _logger.Debug($"Done executing report with toDate {toDate} and fromdate {fromDate}."); return(response); }
public void DeleteByCandidateGuid(ICollection <Guid> candidateGuids) { var connection = _getOpenConnection.GetOpenConnection(); var personIds = _getOpenConnection.Query <int>("SELECT PersonId FROM Candidate WHERE CandidateGuid IN @CandidateGuids", new { candidateGuids }); const string schoolAttendedSql = @"DELETE FROM SchoolAttended WHERE CandidateId IN (SELECT CandidateId FROM Candidate WHERE CandidateGuid IN @CandidateGuids)"; connection.Execute(schoolAttendedSql, new { candidateGuids }); const string candidateHistorySql = @"DELETE FROM CandidateHistory WHERE CandidateId IN (SELECT CandidateId FROM Candidate WHERE CandidateGuid IN @CandidateGuids)"; connection.Execute(candidateHistorySql, new { candidateGuids }); const string candidateSql = @"DELETE FROM Candidate WHERE CandidateGuid IN @CandidateGuids"; connection.Execute(candidateSql, new { candidateGuids }); const string personSql = @"DELETE FROM Person WHERE PersonId IN @PersonIds"; connection.Execute(personSql, new { personIds }); }
public void DeleteByCandidateId(IEnumerable <int> candidateIds) { var connection = _getOpenConnection.GetOpenConnection(); const string subVacancySql = @"DELETE FROM SubVacancy WHERE AllocatedApplicationId IN (SELECT ApplicationId FROM Application WHERE CandidateId IN @CandidateIds)"; connection.Execute(subVacancySql, new { candidateIds }); const string schoolAttendedSql = @"DELETE FROM SchoolAttended WHERE CandidateId IN (SELECT ApplicationId FROM Application WHERE CandidateId IN @CandidateIds)"; connection.Execute(schoolAttendedSql, new { candidateIds }); const string applicationHistorySql = @"DELETE FROM ApplicationHistory WHERE ApplicationId IN (SELECT ApplicationId FROM Application WHERE CandidateId IN @CandidateIds)"; connection.Execute(applicationHistorySql, new { candidateIds }); const string applicationSql = @"DELETE FROM Application WHERE CandidateId IN @CandidateIds"; connection.Execute(applicationSql, new { candidateIds }); }
/// <summary> /// Peform a mutating query returning the specified type. This may update multiple tables. /// The caller must perform logging of the changes made. /// Very similar to Dapper's IDbConnection.Query except: /// <list type="bullet"> /// <item><description>It manages (obtains, opens, closes and returns) the database connection itself</description></item> /// <item><description>The result is always entirely loaded and returned as an IList ("buffered" cannot be set to false)</description></item> /// <item><description>Transient errors are automatically retried</description></item> /// <item><description>Transactions are not supported (in order to support retries)</description></item> /// </list> /// </summary> /// <typeparam name="T"></typeparam> /// <param name="goc"></param> /// <param name="sql"></param> /// <param name="param"></param> /// <param name="commandTimeout"></param> /// <param name="commandType"></param> /// <returns></returns> public static IList <T> MutatingQuery <T>(this IGetOpenConnection goc, string sql, object param = null, int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?)) { // TODO: Log that user did this query return(RetryPolicy.ExecuteAction <IList <T> >(() => { using (var conn = goc.GetOpenConnection()) { return (IList <T>)conn.Query <T>(sql, param, transaction: null, buffered: true, commandTimeout: commandTimeout, commandType: commandType); } })); }
/// <summary> /// Insert a new record containing data from the specified object. The primary key in the specified record must be zero. /// Very similar to Dapper.Contrib.SqlMapperExtensions Insert method except: /// <list type="bullet"> /// <item><description>It manages (obtains, opens, closes and returns) the database connection itself</description></item> /// <item><description>Transient errors are automatically retried</description></item> /// <item><description>Transactions are not supported (in order to support retries)</description></item> /// <item><description>Table / primary key naming conventions are <EntityName>.<EntityName>Id rather than <EntityName>s.Id</description></item> /// </list> /// </summary> /// <typeparam name="T"></typeparam> /// <param name="goc"></param> /// <param name="entity"></param> /// <param name="commandTimeout"></param> /// <returns>The primary key of the </returns> public static long Insert <T>(this IGetOpenConnection goc, T entity, int?commandTimeout = null) where T : class { // TODO: Log that user did this query return(RetryPolicy.ExecuteAction <long>(() => { using (var conn = goc.GetOpenConnection()) { return conn.Insert <T>(entity, null, commandTimeout); } })); }
/// <summary> /// Update the single record with a primary key matching the specified object. /// Very similar to Dapper.Contrib.SqlMapperExtensions Update method except: /// <list type="bullet"> /// <item><description>It manages (obtains, opens, closes and returns) the database connection itself</description></item> /// <item><description>Transient errors are automatically retried</description></item> /// <item><description>Transactions are not supported (in order to support retries)</description></item> /// <item><description>Table / primary key naming conventions are <EntityName>.<EntityName>Id rather than <EntityName>s.Id</description></item> /// <item><description>It only supports updating a single record at a time and may rollback / throw an exception if more than one is affected</description></item> /// </list> /// </summary> /// <typeparam name="T"></typeparam> /// <param name="goc"></param> /// <param name="entity"></param> /// <param name="commandTimeout"></param> /// <returns>true if at least one record was updated, otherwise false</returns> /// <remarks>Consider issuing a custom update if not all columns are changed.</remarks> public static bool UpdateSingle <T>(this IGetOpenConnection goc, T entity, int?commandTimeout = null) where T : class { // TODO: Log that user did this query // TODO: Do in a transaction and check that only one record updated before committing return(RetryPolicy.ExecuteAction <bool>(() => { using (var conn = goc.GetOpenConnection()) { return conn.Update <T>(entity, null, commandTimeout); } })); }
/// <summary> /// Delete the single record with a primary key matching the specified object. /// Very similar to Dapper.Contrib.SqlMapperExtensions Delete method except: /// <list type="bullet"> /// <item><description>It manages (obtains, opens, closes and returns) the database connection itself</description></item> /// <item><description>Transient errors are automatically retried</description></item> /// <item><description>Transactions are not supported (in order to support retries)</description></item> /// <item><description>Table / primary key naming conventions are <EntityName>.<EntityName>Id rather than <EntityName>s.Id</description></item> /// <item><description>It only supports deleting a single record at a time and may rollback / throw an exception if more than one is affected</description></item> /// </list> /// </summary> /// <typeparam name="T"></typeparam> /// <param name="goc"></param> /// <param name="entity"></param> /// <param name="commandTimeout"></param> /// <returns></returns> public static bool DeleteSingle <T>(this IGetOpenConnection goc, T entity, int?commandTimeout = null) where T : class { // TODO: Replace with method that takes primary key (does this have the same design fault as entity framework?) // TODO: Log that user did this query // TODO: Do in a transaction and check that only one record deleted before committing return(RetryPolicy.ExecuteAction <bool>(() => { using (var conn = goc.GetOpenConnection()) { return conn.Delete <T>(entity, null, commandTimeout); } })); }
/// <summary> /// Execute a query that returns multiple datasets. Similar in principal to Dapper's IDbConnection.QueryMultiple except: /// <list type="bullet"> /// <item><description>It manages (obtains, opens, closes and returns) the database connection itself</description></item> /// <item><description>All the results are fully loaded and returned in a Tuple of ILists (Dapper defers loading until GridReader.Read is called)</description></item> /// <item><description>Transient errors are automatically retried</description></item> /// <item><description>Transactions are not supported (in order to support retries)</description></item> /// </list> /// </summary> /// <param name="goc"></param> /// <param name="sql"></param> /// <param name="param"></param> /// <param name="commandTimeout"></param> /// <param name="commandType"></param> /// <returns>An enumerable of the results. This must either be fully iterated or disposed of to avoid a resource leak. Using foreach will automatically dispose. It can only be iterated through once.</returns> /// <remarks>Once the first value has been returned any transient errors will not be retried. Ideally the caller would be carrying out an idempotent operation and would retry from the beginning. /// Transient errors can be detected with "new SqlDatabaseTransientErrorDetectionStrategy().IsTransient(ex)"</remarks> public static Tuple <IList <T1>, IList <T2>, IList <T3> > QueryMultiple <T1, T2, T3>(this IGetOpenConnection goc, string sql, object param = null, int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?)) { // TODO: Log that user did this query return(RetryPolicy.ExecuteAction <Tuple <IList <T1>, IList <T2>, IList <T3> > >(() => { using (var conn = goc.GetOpenConnection()) { var allResults = conn.QueryMultiple(sql, param, transaction: null, commandTimeout: commandTimeout, commandType: commandType); return new Tuple <IList <T1>, IList <T2>, IList <T3> >((IList <T1>)allResults.Read <T1>(), (IList <T2>)allResults.Read <T2>(), (IList <T3>)allResults.Read <T3>()); } } )); }
/// <summary> /// Execute a query and progressively load the data. Very similar to Dapper's IDbConnection.Query except: /// <list type="bullet"> /// <item><description>It manages (obtains, opens, closes and returns) the database connection itself</description></item> /// <item><description>Data will be progressively loaded as the result is iterated through (i.e. buffered=false in Dapper).</description></item> /// <item><description>Transient errors are automatically retried, up to and including the first row (only)</description></item> /// <item><description>Transactions are not supported (in order to support retries)</description></item> /// </list> /// </summary> /// <typeparam name="T"></typeparam> /// <param name="goc"></param> /// <param name="sql"></param> /// <param name="param"></param> /// <param name="commandTimeout"></param> /// <param name="commandType"></param> /// <returns>An enumerable of the results. This must either be fully iterated or disposed of to avoid a resource leak. Using foreach will automatically dispose. It can only be iterated through once.</returns> /// <remarks>Once the first value has been returned any transient errors will not be retried. Ideally the caller would be carrying out an idempotent operation and would retry from the beginning. /// Transient errors can be detected with "new SqlDatabaseTransientErrorDetectionStrategy().IsTransient(ex)"</remarks> public static IEnumerable <T> QueryProgressive <T>(this IGetOpenConnection goc, string sql, object param = null, int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?)) { // TODO: Log that user did this query IDbConnection conn = null; IEnumerator <T> enumerator = null; try { var hasFirstRecord = RetryPolicy.ExecuteAction <bool>(() => { conn = goc.GetOpenConnection(); var results = conn.Query <T>(sql, param, transaction: null, buffered: false, commandTimeout: commandTimeout, commandType: commandType); enumerator = results.GetEnumerator(); return(enumerator.MoveNext()); }); Debug.Assert(enumerator != null); if (hasFirstRecord) { yield return(enumerator.Current); } while (enumerator.MoveNext()) { yield return(enumerator.Current); } } finally { if (enumerator != null) { enumerator.Dispose(); } if (conn != null) { conn.Dispose(); } } }
/// <summary> /// Execute a query. Very similar to Dapper's IDbConnection.Query except: /// <list type="bullet"> /// <item><description>It manages (obtains, opens, closes and returns) the database connection itself</description></item> /// <item><description>The result is always entirely loaded and returned as an IList ("buffered" cannot be set to false)</description></item> /// <item><description>Transient errors are automatically retried</description></item> /// <item><description>Transactions are not supported (in order to support retries)</description></item> /// </list> /// </summary> /// <typeparam name="TFirst"></typeparam> /// <typeparam name="TSecond"></typeparam> /// <typeparam name="TReturn"></typeparam> /// <param name="goc"></param> /// <param name="sql"></param> /// <param name="map"></param> /// <param name="param"></param> /// <param name="splitOn"></param> /// <param name="commandTimeout"></param> /// <param name="commandType"></param> /// <returns></returns> public static IList <TReturn> Query <TFirst, TSecond, TReturn>(this IGetOpenConnection goc, string sql, Func <TFirst, TSecond, TReturn> map, object param = null, string splitOn = "Id", int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?)) { // TODO: Log that user did this query return(RetryPolicy.ExecuteAction <IList <TReturn> >(() => { using (var conn = goc.GetOpenConnection()) { try { return (IList <TReturn>) conn.Query <TFirst, TSecond, TReturn>(sql, map, param, transaction: null, buffered: true, commandTimeout: commandTimeout, commandType: commandType, splitOn: splitOn); } catch (Exception ex) { throw ex; } } } )); }