/// <summary> /// /// </summary> /// <param name="employee"></param> /// <returns></returns> internal IDbQuery InsertEmployee2(EmployeeDto employee) { return(DbFluent.InsertQuery(Table <DbEmployee>()) .WithFields(q => new[] { DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("newCode", 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)) }) .WithIdFields(q => new[] { DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("oldCode", DataValueTypes.Text)) }) .WithReturnedIdFields(new[] { Field <DbEmployee>(p => p.EmployeeId) }) .WithParameters( ElementFactory.CreateScalar("newCode", employee.Code), ElementFactory.CreateScalar("oldCode", "oldCode"), ElementFactory.CreateScalar("ByteArrayField", employee.ByteArrayField), ElementFactory.CreateScalar("DoubleField", employee.DoubleField), ElementFactory.CreateScalar("DateTimeField", employee.DateTimeField), ElementFactory.CreateScalar("LongField", employee.LongField))); }
/// <summary> /// Delete the specified country. /// </summary> /// <param name="code">The code to consider.</param> /// <returns>Returns the generated query.</returns> internal IDbQuery QueryDeleteCountry(string code) { return(this.UseQuery("DeleteCountry", p => DbFluent.DeleteQuery(Table("Country")) .AddIdField(q => DbFluent.FieldAsParameter(nameof(DbCountry.Code), q.UseParameter("code", DataValueTypes.Text)))) .WithParameters( ElementFactory.CreateScalar("code", code))); }
/// <summary> /// Delete the specified employee. /// </summary> /// <param name="code">The code to consider.</param> /// <returns>Returns the generated query.</returns> internal IDbQuery DeleteEmployee2(string code) { var query = DbFluent.DeleteQuery(Table <DbEmployee>()) .AddIdField(q => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text))) .WithParameters( ElementFactory.CreateScalar("code", code)); return(query); }
/// <summary> /// /// </summary> /// <param name="code"></param> /// <returns></returns> internal IDbQuery QuerySelectCommunityWithCode(string code) { return(this.UseQuery("GetCommunityWithCode", p => DbFluent.SelectQuery(Table <DbCommunity>("community")) .From( DbFluent.TableAsJoin(DbQueryJoinKind.Left, Table("RegionalDirectorate"), JoinCondition("Community_RegionalDirectorate"))) .WithFields(Tuple("SelectCommunity")) .AddIdField(q => DbFluent.FieldAsParameter(nameof(DbCommunity.Code), q.UseParameter("code", DataValueTypes.Text)))) .WithParameters( ElementFactory.CreateScalar("code", code))); }
/// <summary> /// /// </summary> /// <param name="code"></param> /// <returns></returns> internal IDbQuery SelectEmployeeWithCode5(string code) { return(this.UseQuery("SelectEmployeeWithCode5", p => p.SelectQuery <DbEmployee>() .WithFields(Tuple("Fields_SelectEmployee")) .Where(q => DbFluent.Exists( q.UseSubQuery( DbFluent.SelectQuery(DbFluent.Table("Employee", "Mdm")) .AddIdField(q => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text)))))) ) .WithParameters( ElementFactory.CreateScalar("code", code))); }
/// <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> /// Delete the specified employee. /// </summary> /// <param name="code">The code to consider.</param> /// <returns>Returns the generated query.</returns> internal IDbQuery DeleteEmployee9(string code) { var query = DbFluent.DeleteQuery(Table <DbEmployee>()) .From( DbFluent.TableAsJoin(DbQueryJoinKind.Left, Table("RegionalDirectorate").WithAlias("directorate"), JoinCondition("Employee_RegionalDirectorate"))) .Where(q => DbFluent.Eq(DbFluent.Field("code"), q.UseParameter("code", DataValueTypes.Text))) .AddIdField(q => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text))) .WithParameters( ElementFactory.CreateScalar("code", code)); return(query); }
/// <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="code"></param> /// <returns></returns> internal IDbQuery SelectEmployeeWithCode2(string code) { return(this.UseQuery("SelectEmployeeWithCode2", p => DbFluent.SelectQuery(Table <DbEmployee>()) .From( DbFluent.TableAsJoin( DbQueryJoinKind.Left, Table("RegionalDirectorate"), JoinCondition("Employee_RegionalDirectorate"))) .WithFields(Tuple("Fields_SelectEmployee")) .WithLimit(100) .AddIdField(q => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text))) .Where(DbFluent.Eq(DbFluent.Field("field1"), DbFluent.Field("field2")))) .WithParameters( ElementFactory.CreateScalar("code", code))); }
/// <summary> /// /// </summary> /// <param name="employee"></param> /// <returns></returns> internal IDbQuery InsertEmployee3(EmployeeDto employee) { return(DbFluent.InsertQuery(Table <DbEmployee>()) .WithFields(q => new[] { DbFluent.Field(nameof(DbEmployee.Code)), DbFluent.Field(nameof(DbEmployee.ByteArrayField)), DbFluent.Field(nameof(DbEmployee.DoubleField)), DbFluent.Field(nameof(DbEmployee.DateTimeField)), DbFluent.Field(nameof(DbEmployee.LongField)) }) .WithReturnedIdFields(new[] { Field <DbEmployee>(p => p.EmployeeId) }) .From(p => p.UseSubQuery( DbFluent.SelectQuery(Table <DbEmployee>()) .WithFields(q => new[] { DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("newCode", 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)), DbFluent.FieldAsQuery <DbContact>(p => p.ContactId, DbFluent.SelectQuery(Table <DbContact>()) .WithLimit(1) .AddField(Field <DbContact>(f => f.ContactId)) .AddIdField( DbFluent.FieldAsParameter <DbContact>(f => f.Code, q.UseParameter("contactCode", DataValueTypes.Text)))), }) .WithIdFields(q => new[] { DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("oldCode", DataValueTypes.Text)) }) .WithParameters( ElementFactory.CreateScalar("newCode", employee.Code), ElementFactory.CreateScalar("contactCode", "contactCodeA"), ElementFactory.CreateScalar("oldCode", "oldCode"), ElementFactory.CreateScalar("ByteArrayField", employee.ByteArrayField), ElementFactory.CreateScalar("DoubleField", employee.DoubleField), ElementFactory.CreateScalar("DateTimeField", employee.DateTimeField), ElementFactory.CreateScalar("LongField", employee.LongField))))); }
/// <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> /// /// </summary> /// <param name="code"></param> /// <returns></returns> internal IDbQuery SelectEmployeeWithCode3(string code) { return(this.UseQuery("SelectEmployeeWithCode3", p => DbFluent.SelectQuery(null) .From( Table <DbEmployee>(), DbFluent.TableAsJoin( DbQueryJoinKind.Left, Table("RegionalDirectorate"), JoinCondition("Employee_RegionalDirectorate"))) .WithFields(Tuple("Fields_SelectEmployee")) .WithLimit(100) .OrderBy( DbFluent.OrderBy(Field <DbEmployee>(p => p.Code, "employee")), DbFluent.OrderBy(Field <DbEmployee>(p => p.DateTimeField, "regionalDirectorate"), DataSortingModes.Descending)) .AddIdField(q => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text)))) .WithParameters( ElementFactory.CreateScalar("code", code))); }
/// <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> /// /// </summary> /// <param name="builder"></param> public void OnCreating_Test1() { AddTable <DbEmployee>(DbFluent.Table(nameof(DbEmployee).Substring(2), "Mdm")) .AddTable("RegionalDirectorate", DbFluent.Table(nameof(DbRegionalDirectorate).Substring(2), "Mdm")) .AddTable <DbContact>(DbFluent.Table(nameof(DbContact).Substring(2), "Mdm")) .AddRelationship("Employee_RegionalDirectorate", Table <DbEmployee>(), Table("RegionalDirectorate"), (nameof(DbEmployee.EmployeeId), nameof(DbRegionalDirectorate.RegionalDirectorateId))) .AddRelationship <DbEmployee, DbContact>((p => p.MainContactId, q => q.ContactId)) .AddRelationship <DbEmployee, DbContact>("secondary", (p => p.MainContactId, q => q.ContactId)) .AddTuple("Fields_SelectEmployee", DbFluent.FieldAsAll(Table <DbEmployee>()), DbFluent.Field(nameof(DbRegionalDirectorate.RegionalDirectorateId), Table("RegionalDirectorate")), DbFluent.Field(nameof(DbRegionalDirectorate.Code), Table("RegionalDirectorate"))) .AddTuple("Fields_SelectEmployee2", DbFluent.FieldAsAll(Table <DbEmployee>("employee")), DbFluent.Field <DbContact>(p => p.Code, Table <DbContact>("contact")).WithAlias("contactCode"), DbFluent.Field(nameof(DbRegionalDirectorate.RegionalDirectorateId), Table("RegionalDirectorate", "regionalDirectorate")), DbFluent.Field(nameof(DbRegionalDirectorate.Code), Table("RegionalDirectorate", "regionalDirectorate"))) .AddQuery( DbFluent.StoredQuery( DbFluent.SelectQuery(Table <DbEmployee>()) .From( Table <DbEmployee>(), DbFluent.TableAsJoin( DbQueryJoinKind.Left, Table("RegionalDirectorate"), JoinCondition("Employee_RegionalDirectorate"))) .WithFields(Tuple("Fields_SelectEmployee")) .AddIdField(p => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), p.UseParameter("code"))))); }