/// <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); }