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