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