/// <summary>
/// Select the specified column
/// </summary>
public OrmResultSet <dynamic> Select(params Expression <Func <TData, dynamic> >[] columns)
{
var mapping = TableMapping.Get(typeof(TData));
return(new OrmResultSet <dynamic>(this.Context, this.Context.CreateSqlStatement($"SELECT {String.Join(",", columns.Select(o => mapping.GetColumn(this.GetMember(o.Body))).Select(o => o.Name))} FROM (").Append(this.Statement).Append(") AS I")));
}
/// <summary>
/// Select the specified column
/// </summary>
public OrmResultSet <T> Select <T>(Expression <Func <TData, T> > column)
{
var mapping = TableMapping.Get(typeof(TData)).GetColumn(this.GetMember(column.Body));
return(new OrmResultSet <T>(this.Context, this.Context.CreateSqlStatement($"SELECT I.{mapping.Name} FROM (").Append(this.Statement).Append(") AS I")));
}
/// <summary>
/// Get the maximum value of the specifed column
/// </summary>
public T Min <T>(Expression <Func <TData, T> > column)
{
var mapping = TableMapping.Get(typeof(TData)).GetColumn(this.GetMember(column.Body));
return(this.Context.ExecuteScalar <T>(this.Context.CreateSqlStatement($"SELECT MIN({mapping.Name}) FROM (").Append(this.Statement).Append(") AS I")));
}
/// <summary>
/// Insert the specified object
/// </summary>
public TModel Insert <TModel>(TModel value)
{
#if DEBUG
var sw = new Stopwatch();
sw.Start();
try
{
#endif
// First we want to map object to columns
var tableMap = TableMapping.Get(typeof(TModel));
SqlStatement columnNames = this.CreateSqlStatement(),
values = this.CreateSqlStatement();
foreach (var col in tableMap.Columns)
{
var val = col.SourceProperty.GetValue(value);
if (val == null ||
!col.IsNonNull &&
col.SourceProperty.PropertyType.StripNullable() == col.SourceProperty.PropertyType &&
(
val.Equals(default(Int32)) ||
val.Equals(default(Int64)) ||
val.Equals(default(Guid)) ||
val.Equals(default(DateTime)) ||
val.Equals(default(DateTimeOffset)) ||
val.Equals(default(Decimal))))
{
val = null;
}
if (col.IsAutoGenerated && val == null)
{
// Uh-oh, the column is auto-gen, the type of uuid and the engine can't do it!
if (col.SourceProperty.PropertyType.StripNullable() == typeof(Guid) &&
!this.m_provider.Features.HasFlag(SqlEngineFeatures.AutoGenerateGuids))
{
val = Guid.NewGuid();
col.SourceProperty.SetValue(value, val);
}
else
{
continue;
}
}
columnNames.Append($"{col.Name}");
// Append value
values.Append("?", val);
values.Append(",");
columnNames.Append(",");
}
values.RemoveLast();
columnNames.RemoveLast();
var returnKeys = tableMap.Columns.Where(o => o.IsAutoGenerated);
// Return arrays
var stmt = this.m_provider.Returning(
this.CreateSqlStatement($"INSERT INTO {tableMap.TableName} (").Append(columnNames).Append(") VALUES (").Append(values).Append(")"),
returnKeys.ToArray()
);
// Execute
lock (this.m_lockObject)
{
var dbc = this.m_lastCommand = this.m_provider.CreateCommand(this, stmt);
try
{
this.IncrementProbe(Diagnostics.OrmPerformanceMetric.ActiveStatements);
if (this.CommandTimeout.HasValue)
{
dbc.CommandTimeout = this.CommandTimeout.Value;
}
// There are returned keys and we support simple mode returned inserts
if (returnKeys.Any() && this.m_provider.Features.HasFlag(SqlEngineFeatures.ReturnedInsertsAsReader))
{
using (var rdr = dbc.ExecuteReader())
if (rdr.Read())
{
foreach (var itm in returnKeys)
{
object ov = this.m_provider.ConvertValue(rdr[itm.Name], itm.SourceProperty.PropertyType);
if (ov != null)
{
itm.SourceProperty.SetValue(value, ov);
}
}
}
}
// There are returned keys and the provider requires an output parameter to hold the keys
else if (returnKeys.Any() && this.m_provider.Features.HasFlag(SqlEngineFeatures.ReturnedInsertsAsParms))
{
// Define output parameters
foreach (var rt in returnKeys)
{
var parm = dbc.CreateParameter();
parm.ParameterName = rt.Name;
parm.DbType = this.m_provider.MapParameterType(rt.SourceProperty.PropertyType);
parm.Direction = ParameterDirection.Output;
dbc.Parameters.Add(parm);
}
dbc.ExecuteNonQuery();
// Get the parameter values
foreach (IDataParameter parm in dbc.Parameters)
{
if (parm.Direction != ParameterDirection.Output)
{
continue;
}
var itm = returnKeys.First(o => o.Name == parm.ParameterName);
object ov = this.m_provider.ConvertValue(parm.Value, itm.SourceProperty.PropertyType);
if (ov != null)
{
itm.SourceProperty.SetValue(value, ov);
}
}
}
else // Provider does not support returned keys
{
dbc.ExecuteNonQuery();
// But... the query wants the keys so we have to query them back if the RETURNING clause fields aren't populated in the source object
if (returnKeys.Count() > 0 &&
returnKeys.Any(o => o.SourceProperty.GetValue(value) == (o.SourceProperty.PropertyType.IsValueType ? Activator.CreateInstance(o.SourceProperty.PropertyType) : null)))
{
dbc.Dispose();
this.DecrementProbe(Diagnostics.OrmPerformanceMetric.ActiveStatements);
var pkcols = tableMap.Columns.Where(o => o.IsPrimaryKey);
var where = new SqlStatement <TModel>(this.m_provider);
foreach (var pk in pkcols)
{
where.And($"{pk.Name} = ?", pk.SourceProperty.GetValue(value));
}
stmt = new SqlStatement <TModel>(this.m_provider).SelectFrom().Where(where);
// Create command and exec
dbc = this.m_provider.CreateCommand(this, stmt);
this.IncrementProbe(Diagnostics.OrmPerformanceMetric.ActiveStatements);
if (this.CommandTimeout.HasValue)
{
dbc.CommandTimeout = this.CommandTimeout.Value;
}
using (var rdr = dbc.ExecuteReader())
if (rdr.Read())
{
foreach (var itm in returnKeys)
{
object ov = this.m_provider.ConvertValue(rdr[itm.Name], itm.SourceProperty.PropertyType);
if (ov != null)
{
itm.SourceProperty.SetValue(value, ov);
}
}
}
}
}
}
finally
{
dbc.Dispose();
this.DecrementProbe(Diagnostics.OrmPerformanceMetric.ActiveStatements);
}
}
return(value);
#if DEBUG
}
finally
{
sw.Stop();
this.AddProbeResponseTime(sw.ElapsedMilliseconds);
this.m_tracer.TraceEvent(EventLevel.Verbose, "INSERT executed in {0} ms", sw.ElapsedMilliseconds);
}
#endif
}
/// <summary>
/// Updates the specified object
/// </summary>
public TModel Update <TModel>(TModel value)
{
#if DEBUG
var sw = new Stopwatch();
sw.Start();
try
{
#endif
// Build the command
var tableMap = TableMapping.Get(typeof(TModel));
SqlStatement <TModel> query = this.CreateSqlStatement <TModel>().UpdateSet();
SqlStatement whereClause = this.CreateSqlStatement();
int nUpdatedColumns = 0;
foreach (var itm in tableMap.Columns)
{
var itmValue = itm.SourceProperty.GetValue(value);
if (itmValue == null ||
!itm.IsNonNull &&
itm.SourceProperty.PropertyType.StripNullable() == itm.SourceProperty.PropertyType &&
(
itmValue.Equals(default(Guid)) && !tableMap.OrmType.IsConstructedGenericType ||
itmValue.Equals(default(DateTime)) ||
itmValue.Equals(default(DateTimeOffset)) ||
itmValue.Equals(default(Decimal))))
{
itmValue = null;
}
// Only update if specified
if (itmValue == null &&
!itm.SourceSpecified(value))
{
continue;
}
nUpdatedColumns++;
query.Append($"{itm.Name} = ? ", itmValue ?? DBNull.Value);
query.Append(",");
if (itm.IsPrimaryKey)
{
whereClause.And($"{itm.Name} = ?", itmValue);
}
}
// Nothing being updated
if (nUpdatedColumns == 0)
{
m_tracer.TraceInfo("Nothing to update, will skip");
return(value);
}
query.RemoveLast();
query.Where(whereClause);
// Now update
lock (this.m_lockObject)
{
var dbc = this.m_lastCommand = this.m_provider.CreateCommand(this, query);
try
{
this.IncrementProbe(Diagnostics.OrmPerformanceMetric.ActiveStatements);
if (this.CommandTimeout.HasValue)
{
dbc.CommandTimeout = this.CommandTimeout.Value;
}
dbc.ExecuteNonQuery();
}
finally
{
dbc.Dispose();
this.DecrementProbe(Diagnostics.OrmPerformanceMetric.ActiveStatements);
}
}
return(value);
#if DEBUG
}
finally
{
sw.Stop();
this.AddProbeResponseTime(sw.ElapsedMilliseconds);
this.m_tracer.TraceEvent(EventLevel.Verbose, "UPDATE executed in {0} ms", sw.ElapsedMilliseconds);
}
#endif
}
/// <summary>
/// Return a select from
/// </summary>
public SqlStatement SelectFrom(Type dataType, params ColumnMapping[] columns)
{
var tableMap = TableMapping.Get(dataType);
return(this.Append(new SqlStatement(this.m_provider, $"SELECT {String.Join(",", columns.Select(o => $"{o.Table.TableName}.{o.Name}"))} FROM {tableMap.TableName} AS {tableMap.TableName} ")));
}
/// <summary>
/// Return a select from
/// </summary>
public SqlStatement SelectFrom(Type dataType)
{
var tableMap = TableMapping.Get(dataType);
return(this.Append(new SqlStatement(this.m_provider, $"SELECT * FROM {tableMap.TableName} AS {tableMap.TableName} ")));
}
/// <summary>
/// Visit member access
/// </summary>
private Expression VisitMemberAccess(MemberExpression node)
{
switch (node.Member.Name)
{
case "Now":
this.m_sqlStatement.Append(" CURRENT_TIMESTAMP ");
break;
case "NewGuid":
this.m_sqlStatement.Append(" ? ", Guid.NewGuid());
break;
case "HasValue":
this.Visit(node.Expression);
this.m_sqlStatement.Append(" IS NOT NULL ");
break;
default:
if (node.Expression != null)
{
if (node.Expression.Type.IsGenericType &&
node.Expression.Type.GetGenericTypeDefinition() == typeof(Nullable <>))
{
this.Visit(node.Expression);
}
else
{
var expr = node.Expression;
while (expr.NodeType == ExpressionType.Convert)
{
expr = (expr as UnaryExpression)?.Operand;
}
// Ignore typeas
switch (expr.NodeType)
{
case ExpressionType.Parameter:
// Translate
var tableMap = TableMapping.Get(expr.Type);
var columnMap = tableMap.GetColumn(node.Member);
this.Visit(expr);
this.m_sqlStatement.Append($".{columnMap.Name}");
break;
case ExpressionType.Constant:
case ExpressionType.TypeAs:
case ExpressionType.MemberAccess:
// Ok, this is a constant member access.. so ets get the value
var cons = this.GetConstantValue(expr);
if (node.Member is PropertyInfo)
{
var value = (node.Member as PropertyInfo).GetValue(cons);
if (value == null)
{
var stmt = this.m_sqlStatement.RemoveLast().SQL.Trim();
if (stmt == "<>")
{
this.m_sqlStatement.Append(" IS NOT NULL ");
}
else if (stmt == "=")
{
this.m_sqlStatement.Append(" IS NULL ");
}
else
{
throw new InvalidOperationException($"Cannot determine how to convert {node} in SQL");
}
}
else
{
this.m_sqlStatement.Append(" ? ", value);
}
}
else if (node.Member is FieldInfo)
{
var value = (node.Member as FieldInfo).GetValue(cons);
if (value == null)
{
var stmt = this.m_sqlStatement.RemoveLast().SQL.Trim();
if (stmt == "<>")
{
this.m_sqlStatement.Append(" IS NOT NULL ");
}
else
{
this.m_sqlStatement.Append(" IS NULL ");
}
}
else
{
this.m_sqlStatement.Append(" ? ", value);
}
}
else
{
throw new NotSupportedException();
}
break;
}
}
}
else // constant expression
{
if (node.Member is PropertyInfo)
{
this.m_sqlStatement.Append(" ? ", (node.Member as PropertyInfo).GetValue(null));
}
else if (node.Member is FieldInfo)
{
this.m_sqlStatement.Append(" ? ", (node.Member as FieldInfo).GetValue(null));
}
}
break;
}
// Member expression is node... This has the limitation of only going one deep :/
return(node);
}