/// <summary>
///
/// </summary>
/// <param name="code"></param>
/// <param name="isPartialUpdate"></param>
/// <param name="employee"></param>
/// <returns></returns>
internal IDbQuery UpdateEmployee1(string code, bool isPartialUpdate, EmployeeDto employee)
{
return(this.UseQuery("UpdateEmployee1",
p =>
{
var query = DbFluent.UpdateQuery(Table <DbEmployee>())
.From(
DbFluent.TableAsJoin(
DbQueryJoinKind.Left,
Table("RegionalDirectorate"),
JoinCondition("Employee_RegionalDirectorate")))
.WithReturnedIdFields(Field("Code", "DbEmployee"));
query.AddField(
!isPartialUpdate || employee?.Code?.Length > 0,
q => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text)));
query.AddField(
!isPartialUpdate || employee?.ByteArrayField?.Length > 0,
q => DbFluent.FieldAsParameter(nameof(DbEmployee.ByteArrayField), q.UseParameter("byteArrayField", DataValueTypes.ByteArray)));
query.AddField(
!isPartialUpdate || employee?.DateTimeField != null,
q => DbFluent.FieldAsParameter(nameof(DbEmployee.DoubleField), q.UseParameter("doubleField", DataValueTypes.Number)));
query.AddField(
!isPartialUpdate || employee?.DoubleField != null,
q => DbFluent.FieldAsParameter(nameof(DbEmployee.DateTimeField), q.UseParameter("dateTimeField", DataValueTypes.Date)));
query.AddField(
true,
q => DbFluent.FieldAsParameter(nameof(DbEmployee.LongField), q.UseParameter("longField", DataValueTypes.Long)));
query.AddField(
!isPartialUpdate || employee?.RegionalDirectorateCode?.Length > 0,
q => DbFluent.FieldAsQuery(nameof(DbEmployee.RegionalDirectorateId),
DbFluent.SelectQuery(Table("RegionalDirectorate"))
.AddField(DbFluent.Field(nameof(DbRegionalDirectorate.RegionalDirectorateId)))
.WithIdFields(
DbFluent.FieldAsParameter(nameof(DbRegionalDirectorate.Code), q.UseParameter("directorateCode", DataValueTypes.Text)))));
return query;
})
.WithParameters(
ElementFactory.CreateScalar("code", code),
ElementFactory.CreateScalar("byteArrayField", employee.ByteArrayField),
ElementFactory.CreateScalar("doubleField", employee.DoubleField),
ElementFactory.CreateScalar("dateTimeField", employee.DateTimeField),
ElementFactory.CreateScalar("directorateCode", null),
ElementFactory.CreateScalar("longField", employee.LongField)));
}
/// <summary>
///
/// </summary>
/// <param name="code"></param>
/// <param name="isPartialUpdate"></param>
/// <param name="country"></param>
/// <returns></returns>
internal IDbQuery QueryUpdateCountry(string code, bool isPartialUpdate, CountryDto country)
{
var query = DbFluent.UpdateQuery(Table("Country"))
.From(
DbFluent.TableAsJoin(DbQueryJoinKind.Left, Table("RegionalDirectorate"),
JoinCondition("Country_RegionalDirectorate")));
if (!isPartialUpdate || country?.Code?.Length > 0)
{
query.AddField(p => DbFluent.FieldAsParameter(nameof(DbCountry.Code), p.UseParameter("code", nameof(DbCountry.Code))));
}
return(query);
}
/// <summary>
///
/// </summary>
/// <param name="employee"></param>
/// <returns></returns>
internal IDbQuery UpsertEmployee(EmployeeDto employee)
{
return(DbFluent.Upsert(Table <DbEmployee>())
.WithQueries(
DbFluent.UpdateQuery(Table <DbEmployee>())
.WithFields(q => new[]
{
DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.ByteArrayField), q.UseParameter("ByteArrayField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.DoubleField), q.UseParameter("DoubleField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.DoubleField), q.UseParameter("DoubleField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.DateTimeField), q.UseParameter("DateTimeField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.LongField), q.UseParameter("LongField", DataValueTypes.Date))
}),
DbFluent.InsertQuery(Table <DbEmployee>())
.WithFields(q => new[]
{
DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.ByteArrayField), q.UseParameter("ByteArrayField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.DoubleField), q.UseParameter("DoubleField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.DateTimeField), q.UseParameter("DateTimeField", DataValueTypes.Text)),
DbFluent.FieldAsParameter(nameof(DbEmployee.LongField), q.UseParameter("LongField", DataValueTypes.Date))
})
.WithParameters(
ElementFactory.CreateScalar("code", employee.Code),
ElementFactory.CreateScalar("ByteArrayField", employee.ByteArrayField),
ElementFactory.CreateScalar("DoubleField", employee.DoubleField),
ElementFactory.CreateScalar("DateTimeField", employee.DateTimeField),
ElementFactory.CreateScalar("LongField", employee.LongField)))
.WithCTE(
DbFluent.TableAsQuery(
DbFluent.SelectQuery(null)
.WithFields(q => new[]
{
DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text))
}))
.WithAlias("T"))
.WithParameters(
ElementFactory.CreateScalar("code", employee.Code),
ElementFactory.CreateScalar("ByteArrayField", employee.ByteArrayField),
ElementFactory.CreateScalar("DoubleField", employee.DoubleField),
ElementFactory.CreateScalar("DateTimeField", employee.DateTimeField),
ElementFactory.CreateScalar("LongField", employee.LongField)));
}
/// <summary>
/// Updates the regional directorate of the specified Fidal employee.
/// </summary>
/// <param name="regionalDirectorateCode">The code of the regional directorate to consider.</param>
/// <returns>Returns the query.</returns>
internal IDbQuery UpdateEmployee2(string regionalDirectorateCode)
{
return(this.UseQuery("UpdateEmployee2", p =>
DbFluent.UpdateQuery(Table("DbEmployee", "employee"))
.AddField(q => DbFluent.Field <DbEmployee>(q => q.RegionalDirectorateId).AsNull())
.From(Table("RegionalDirectorate", "regionalDirectorate"))
.AddIdField(q =>
DbFluent.FieldAsOther <DbEmployee>(
t => t.RegionalDirectorateId, Table("DbEmployee", "employee"),
DbFluent.Field <DbRegionalDirectorate>(
t => t.RegionalDirectorateId, Table("RegionalDirectorate").WithAlias("regionalDirectorate"))))
.AddIdField(q =>
DbFluent.FieldAsParameter <DbEmployee>(
t => t.Code,
DbFluent.Table("employee"),
q.UseParameter("regionalDirectorateCode", DataValueTypes.Text)))
.WithReturnedIdFields(Field("Code", "DbEmployee", "employee"))
)
.WithParameters(
ElementFactory.CreateScalar("regionalDirectorateCode", regionalDirectorateCode)));
}
/// <summary> /// Creates a new Update advanced database query. /// </summary> /// <returns>Returns a new Update advanced database query</returns> public IDbSingleQuery UpdateQuery <T>(Action <IDbSingleQuery> initAction = null) => DbFluent.UpdateQuery(Table <T>(), initAction);
// Update --------------------------------
/// <summary>
/// Creates a new Update advanced database query.
/// </summary>
/// <returns>Returns a new Update advanced database query</returns>
public IDbSingleQuery UpdateQuery <T>(string name, Action <IDbSingleQuery> initAction = null)
=> DbFluent.UpdateQuery(name, Table <T>(), initAction);
