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