public string GetColumnNamesAsCommandText(string overrideTableName = "") { if (!string.IsNullOrEmpty(overrideTableName)) { overrideTableName = TableName; } var sbCommandText = new StringBuilder(); string comma = ""; for (int i = 0; i < DataModelColumnProperties.Count(); i++) { sbCommandText.Append($"{comma}{overrideTableName}.{DataModelColumnProperties[i].ColumnName}"); comma = " ,"; } for (int i = 0; i < DataModelForeignKeyProperties.Count(); i++) { sbCommandText.Append(" ,"); sbCommandText.Append( DataModelForeignKeyProperties[i].ForeignDataModelFactory .GetColumnNamesAsCommandText(DataModelForeignKeyProperties[i].GetJoinName())); } return(sbCommandText.ToString()); }
public DataUsersFactory(DataCountriesFactory dataCountriesFactory) : base(DataUsers.TABLE_NAME) { FKCountryProperties = new DataModel.DataModeForeignKey(dataCountriesFactory, true); DataModelForeignKeyProperties.Clear(); DataModelForeignKeyProperties.Add(FKCountryProperties); }
/* * protected virtual string GetCommandText_BasicSelectCommon() * { * var sbQuery = new StringBuilder(); * var sbQueryJoin = new StringBuilder(); * * sbQuery.Append($"SELECT {GetColumnNamesAsCommandText()}"); * * for(int i = 0; i < DataModelForeignKeyProperties.Count(); i++) * { * sbQueryJoin.AppendLine($" {GetForeignKeyCommandText(DataModelForeignKeyProperties[i])}"); * } * * sbQuery.AppendFormat(" FROM {0}", TableName); * sbQuery.Append(sbQueryJoin.ToString()); * return sbQuery.ToString(); * } */ public virtual string GetCommandText_Select(List <DataModel.IDataModelColumn> parameters = null, int pageSize = 0, int startIndex = 0, List <DataModel.IDataModelColumnProperties> orderby = null) { var sbQuery = new StringBuilder(); var sbSelect = new StringBuilder(); sbSelect.AppendFormat("SELECT {0}", DataModelColumnProperties[0].ColumnName); // Iterate the Factory's Columns(exluding foreign keys) to add them to select query for (int i = 1; i < DataModelColumnProperties.Count; i++) { sbSelect.AppendFormat(", {0}", DataModelColumnProperties[i].ColumnName); } sbQuery.AppendFormat(" FROM {0}", TableName); // Iterate the Factory's Foreign Keys to add them to select query and to join to their // respective tables for (int i = 0; i < DataModelForeignKeyProperties.Count(); i++) { if (DataModelForeignKeyProperties[i].PerformJoin) { sbSelect.Append(DataModelForeignKeyProperties[i].GetColumnsForSelectQuery()); sbQuery.Append($" JOIN {DataModelForeignKeyProperties[i].GetJoinName()}"); sbQuery.Append($" ON {DataModelForeignKeyProperties[i].ColumnName} = {DataModelForeignKeyProperties[i].GetForeignTablePK()}"); } else { sbSelect.Append($", {DataModelForeignKeyProperties[i].ColumnName}"); } } // If parameters have been provided include WHERE to the SELECT query if (parameters != null && parameters.Count > 0) { sbQuery.AppendLine(" WHERE"); // Iterate all parameters to add to the WHERE query for (int pi = 0; pi < parameters.Count; pi++) { if (pi > 0) { sbQuery.Append(" AND"); } sbQuery.AppendLine(string.Format(" {0} = @{0}", parameters[pi].GetColumnProperties().ColumnName)); } } sbQuery.AppendLine(" ORDER BY"); // Add Order By if (orderby != null) { for (int i = 0; i < orderby.Count; i++) { if (i > 0) { sbQuery.Append(", "); } sbQuery.Append($"{orderby[i].ColumnName}"); } } else { // If Order By not provided, order by primary key sbQuery.AppendLine($"{Get_PrimaryKeyProperties().ColumnName}"); } if (pageSize > 0) { sbQuery.AppendLine(string.Format(" LIMIT {0}, {1}", pageSize, startIndex)); } return($"{sbSelect.ToString()} {sbQuery.ToString()}"); }