/// <summary>
/// Gets a collection PurchaseOrderItem objects by custom where clause and order by.
/// </summary>
/// <param name="prefix">The prefix clause allows you to inject a distinct or top clause.</param>
/// <param name="where">The where clause to use for the query. Should be parameterized and start with "where"</param>
/// <param name="parameters">The parameters that are listed in the where clause</param>
/// <param name="orderBy">the order by clause. Shoudl start with "order by"</param>
/// <returns>The retrieved collection of PurchaseOrderItem objects.</returns>
protected static EntityList<PurchaseOrderItem> GetPurchaseOrderItems(string prefix, string where, System.Collections.Generic.List<SqlParameter> parameters, string orderBy)
{
string commandText = @"SELECT " + prefix + "" + PurchaseOrderItem.SelectFieldList + "FROM [dbo].[PurchaseOrderItems] " + where + " " + orderBy;
using (SqlHelper helper = new SqlHelper())
{
using (IDataReader reader = helper.ExecuteDataReader(commandText, CommandType.Text, parameters))
{
return EntityBase.InitializeList<PurchaseOrderItem>(reader);
}
}
}
/// <summary>
/// Get Column Info
/// </summary>
/// <param name="entityType"></param>
/// <returns></returns>
public static Dictionary<string, ColumnInfo> GetColumnInfoLookup(System.Type entityType)
{
if (_columnInfo == null)
{
_columnInfo = new Dictionary<Type, Dictionary<string, ColumnInfo>>();
}
if (!_columnInfo.ContainsKey(entityType))
{
_columnInfo[entityType] = new Dictionary<string,ColumnInfo>();
_columnInfoDefaultValueKeys[entityType] = new List<string>();
System.Reflection.PropertyInfo property = entityType.GetProperty("TableName", BindingFlags.Public | BindingFlags.Static);
if (property != null)
{
string tableName = property.GetValue(null, null) as string;
string commandText = @"select c.COLUMN_NAME, c.COLUMN_DEFAULT, c.DATA_TYPE, c.IS_NULLABLE, c.CHARACTER_MAXIMUM_LENGTH, ep.value as FriendlyName from INFORMATION_SCHEMA.COLUMNS c
inner join sys.columns sc ON OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME) = sc.[object_id] AND c.COLUMN_NAME = sc.name
LEFT OUTER JOIN sys.extended_properties ep ON sc.[object_id] = ep.major_id AND sc.[column_id] = ep.minor_id AND ep.class = 1 and ep.Name = 'NSFx_FriendlyName'
WHERE c.TABLE_NAME = @TableName";
System.Collections.Generic.List<SqlParameter> parameters = new System.Collections.Generic.List<SqlParameter>();
parameters.Add(new SqlParameter("@TableName", tableName));
try
{
using (SqlHelper helper = new SqlHelper())
{
System.Data.IDataReader reader = helper.ExecuteDataReader(commandText, CommandType.Text, parameters);
while (reader.Read())
{
ColumnInfo columnInfo = new ColumnInfo(reader);
_columnInfo[entityType].Add(columnInfo.ColumnName, columnInfo);
if (columnInfo.Value != null)
{
try
{
_columnInfoDefaultValueKeys[entityType].Add(columnInfo.ColumnName);
}
catch { }
}
}
}
}
catch { }
}
}
return _columnInfo[entityType];
}