internal static List <T> SqlColumn <T>(this IDbCommand dbCmd, string sql, object anonType = null)
{
if (anonType != null)
{
dbCmd.SetParameters <T>(anonType, excludeDefaults: false);
}
dbCmd.CommandText = sql;
return(IsScalar <T>()
? dbCmd.Column <T>()
: dbCmd.ConvertToList <T>());
}
internal static List <T> SqlColumn <T>(this IDbCommand dbCmd, string sql, Dictionary <string, object> dict)
{
if (dict != null)
{
SetParameters(dbCmd, dict, false);
}
dbCmd.CommandText = sql;
return(IsScalar <T>()
? dbCmd.Column <T>()
: dbCmd.ConvertToList <T>());
}
internal static List <T> Select <T>(this IDbCommand dbCmd, string sql, Dictionary <string, object> dict)
{
if (dict != null)
{
SetParameters(dbCmd, (IDictionary <string, object>)dict, (bool)false);
}
dbCmd.CommandText = OrmLiteConfig.DialectProvider.ToSelectStatement(typeof(T), sql);
return(IsScalar <T>()
? dbCmd.Column <T>()
: dbCmd.ConvertToList <T>());
}
internal static List <T> Select <T>(this IDbCommand dbCmd, string sql, object anonType = null)
{
if (anonType != null)
{
dbCmd.SetParameters <T>(anonType, excludeDefaults: false);
}
dbCmd.CommandText = OrmLiteConfig.DialectProvider.ToSelectStatement(typeof(T), sql);
return(IsScalar <T>()
? dbCmd.Column <T>()
: dbCmd.ConvertToList <T>());
}
internal static List <TOutputModel> SqlProcedureFmt <TOutputModel>(this IDbCommand dbCmd,
object fromObjWithProperties,
string sqlFilter,
params object[] filterParams)
{
var modelType = typeof(TOutputModel);
string sql = dbCmd.GetDialectProvider().ToSelectFromProcedureStatement(
fromObjWithProperties, modelType, sqlFilter, filterParams);
return(dbCmd.ConvertToList <TOutputModel>(sql));
}
public static void LoadReferences <T>(this IDbCommand dbCmd, T instance)
{
var modelDef = ModelDefinition <T> .Definition;
var fieldDefs = modelDef.AllFieldDefinitionsArray.Where(x => x.IsReference);
var pkValue = modelDef.PrimaryKey.GetValue(instance);
var dialectProvider = OrmLiteConfig.DialectProvider;
foreach (var fieldDef in fieldDefs)
{
dbCmd.Parameters.Clear();
var listInterface = fieldDef.FieldType.GetTypeWithGenericInterfaceOf(typeof(IList <>));
if (listInterface != null)
{
var refType = listInterface.GenericTypeArguments()[0];
var refModelDef = refType.GetModelDefinition();
var refField = GetRefFieldDef(modelDef, refModelDef, refType);
var sqlFilter = dialectProvider.GetQuotedColumnName(refField.FieldName) + "={0}";
var sql = dialectProvider.ToSelectStatement(refType, null, sqlFilter, pkValue);
var results = dbCmd.ConvertToList(refType, sql);
fieldDef.SetValueFn(instance, results);
}
else
{
var refType = fieldDef.FieldType;
var refModelDef = refType.GetModelDefinition();
var refSelf = GetSelfRefFieldDefIfExists(modelDef, refModelDef);
var refField = refSelf == null
? GetRefFieldDef(modelDef, refModelDef, refType)
: GetRefFieldDefIfExists(modelDef, refModelDef);
if (refField != null)
{
var sqlFilter = dialectProvider.GetQuotedColumnName(refField.FieldName) + "={0}";
var sql = dialectProvider.ToSelectStatement(refType, null, sqlFilter, pkValue);
var result = dbCmd.ConvertTo(refType, sql);
fieldDef.SetValueFn(instance, result);
}
else if (refSelf != null)
{
//Load Self Table.RefTableId PK
var refPkValue = refSelf.GetValue(instance);
var sqlFilter = dialectProvider.GetQuotedColumnName(refModelDef.PrimaryKey.FieldName) + "={0}";
var sql = dialectProvider.ToSelectStatement(refType, null, sqlFilter, refPkValue);
var result = dbCmd.ConvertTo(refType, sql);
fieldDef.SetValueFn(instance, result);
}
}
}
}
internal static List <TModel> SelectFmt <TModel>(this IDbCommand dbCmd, Type fromTableType, string sqlFilter, params object[] filterParams)
{
var sql = new StringBuilder();
var modelDef = ModelDefinition <TModel> .Definition;
sql.AppendFormat("SELECT {0} FROM {1}", OrmLiteConfig.DialectProvider.GetColumnNames(modelDef),
OrmLiteConfig.DialectProvider.GetQuotedTableName(fromTableType.GetModelDefinition()));
if (!String.IsNullOrEmpty(sqlFilter))
{
sqlFilter = sqlFilter.SqlFmt(filterParams);
sql.Append(" WHERE ");
sql.Append(sqlFilter);
}
return(dbCmd.ConvertToList <TModel>(sql.ToString()));
}
internal static List <T> Where <T>(this IDbCommand dbCmd, object anonType)
{
dbCmd.SetFilters <T>(anonType);
return(dbCmd.ConvertToList <T>());
}
internal static List <TModel> SelectFmt <TModel>(this IDbCommand dbCmd, Type fromTableType, string sqlFilter, params object[] filterParams)
{
var sql = ToSelectFmt <TModel>(dbCmd.GetDialectProvider(), fromTableType, sqlFilter, filterParams);
return(dbCmd.ConvertToList <TModel>(sql.ToString()));
}
internal static List <T> SelectFmt <T>(this IDbCommand dbCmd, string sqlFilter, params object[] filterParams)
{
return(dbCmd.ConvertToList <T>(
dbCmd.GetDialectProvider().ToSelectStatement(typeof(T), sqlFilter, filterParams)));
}
internal static List <T> Select <T>(this IDbCommand dbCmd, string sql, IEnumerable <IDbDataParameter> sqlParams)
{
dbCmd.SetParameters(sqlParams).CommandText = dbCmd.GetDialectProvider().ToSelectStatement(typeof(T), sql);
return(dbCmd.ConvertToList <T>());
}
internal static List <Into> LoadListWithReferences <Into, From>(this IDbCommand dbCmd, SqlExpression <From> expr = null)
{
var dialectProvider = OrmLiteConfig.DialectProvider;
if (expr == null)
{
expr = dialectProvider.SqlExpression <From>();
}
var sql = expr.SelectInto <Into>();
var parentResults = dbCmd.ExprConvertToList <Into>(sql);
var modelDef = ModelDefinition <Into> .Definition;
var fieldDefs = modelDef.AllFieldDefinitionsArray.Where(x => x.IsReference);
expr.Select(dialectProvider.GetQuotedColumnName(modelDef, modelDef.PrimaryKey));
var subSql = expr.ToSelectStatement();
foreach (var fieldDef in fieldDefs)
{
var listInterface = fieldDef.FieldType.GetTypeWithGenericInterfaceOf(typeof(IList <>));
if (listInterface != null)
{
var refType = listInterface.GenericTypeArguments()[0];
var refModelDef = refType.GetModelDefinition();
var refField = GetRefFieldDef(modelDef, refModelDef, refType);
var sqlRef = "SELECT {0} FROM {1} WHERE {2} IN ({3})".Fmt(
dialectProvider.GetColumnNames(refModelDef),
dialectProvider.GetQuotedTableName(refModelDef),
dialectProvider.GetQuotedColumnName(refField),
subSql);
var childResults = dbCmd.ConvertToList(refType, sqlRef);
var map = new Dictionary <object, List <object> >();
List <object> refValues;
foreach (var result in childResults)
{
var refValue = refField.GetValue(result);
if (!map.TryGetValue(refValue, out refValues))
{
map[refValue] = refValues = new List <object>();
}
refValues.Add(result);
}
var untypedApi = dbCmd.CreateTypedApi(refType);
foreach (var result in parentResults)
{
var pkValue = modelDef.PrimaryKey.GetValue(result);
if (map.TryGetValue(pkValue, out refValues))
{
var castResults = untypedApi.Cast(refValues);
fieldDef.SetValueFn(result, castResults);
}
}
}
else
{
var refType = fieldDef.FieldType;
var refModelDef = refType.GetModelDefinition();
var refSelf = GetSelfRefFieldDefIfExists(modelDef, refModelDef);
var refField = refSelf == null
? GetRefFieldDef(modelDef, refModelDef, refType)
: GetRefFieldDefIfExists(modelDef, refModelDef);
var map = new Dictionary <object, object>();
if (refField != null)
{
var sqlRef = "SELECT {0} FROM {1} WHERE {2} IN ({3})".Fmt(
dialectProvider.GetColumnNames(refModelDef),
dialectProvider.GetQuotedTableName(refModelDef),
dialectProvider.GetQuotedColumnName(refField),
subSql);
var childResults = dbCmd.ConvertToList(refType, sqlRef);
foreach (var result in childResults)
{
var refValue = refField.GetValue(result);
map[refValue] = result;
}
foreach (var result in parentResults)
{
object childResult;
var pkValue = modelDef.PrimaryKey.GetValue(result);
if (map.TryGetValue(pkValue, out childResult))
{
fieldDef.SetValueFn(result, childResult);
}
}
}
else if (refSelf != null)
{
//Load Self Table.RefTableId PK
expr.Select(dialectProvider.GetQuotedColumnName(refSelf));
subSql = expr.ToSelectStatement();
var sqlRef = "SELECT {0} FROM {1} WHERE {2} IN ({3})".Fmt(
dialectProvider.GetColumnNames(refModelDef),
dialectProvider.GetQuotedTableName(refModelDef),
dialectProvider.GetQuotedColumnName(refModelDef.PrimaryKey),
subSql);
var childResults = dbCmd.ConvertToList(refType, sqlRef);
foreach (var result in childResults)
{
var pkValue = refModelDef.PrimaryKey.GetValue(result);
map[pkValue] = result;
}
foreach (var result in parentResults)
{
object childResult;
var fkValue = refSelf.GetValue(result);
if (fkValue != null && map.TryGetValue(fkValue, out childResult))
{
fieldDef.SetValueFn(result, childResult);
}
}
}
}
}
return(parentResults);
}
public List <T> SqlProcConvertToList <T>(string procName, object inParams)
{
IDbCommand dbCommand = DBConnection.SqlProc(procName, inParams);
return(dbCommand.ConvertToList <T>());
}
internal static List <T> Where <T>(this IDbCommand dbCmd, string name, object value)
{
SetFilter <T>(dbCmd, name, value);
return(dbCmd.ConvertToList <T>());
}
internal static List <T> SqlColumn <T>(this IDbCommand dbCmd, string sql, IEnumerable <IDbDataParameter> sqlParams)
{
dbCmd.SetParameters(sqlParams).CommandText = sql;
return(dbCmd.ConvertToList <T>());
}
internal static List <T> SelectNonDefaults <T>(this IDbCommand dbCmd, object filter)
{
dbCmd.SetFilters <T>(filter, excludeDefaults: true);
return(dbCmd.ConvertToList <T>());
}
internal static List <T> SqlColumn <T>(this IDbCommand dbCmd, string sql, object anonType = null)
{
dbCmd.SetParameters <T>(anonType, excludeDefaults: false, sql: ref sql).CommandText = sql;
return(dbCmd.ConvertToList <T>());
}
