/// <summary>
/// Delete from the database
/// </summary>
public void Delete <TModel>(TModel obj)
{
#if DEBUG
var sw = new Stopwatch();
sw.Start();
try
{
#endif
var tableMap = TableMapping.Get(typeof(TModel));
SqlStatement whereClause = this.CreateSqlStatement();
foreach (var itm in tableMap.Columns)
{
var itmValue = itm.SourceProperty.GetValue(obj);
if (itm.IsPrimaryKey)
{
whereClause.And($"{itm.Name} = ?", itmValue);
}
}
var query = this.CreateSqlStatement <TModel>().DeleteFrom().Where(whereClause);
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);
}
}
#if DEBUG
}
finally
{
sw.Stop();
this.AddProbeResponseTime(sw.ElapsedMilliseconds);
this.m_tracer.TraceEvent(EventLevel.Verbose, "DELETE executed in {0} ms", sw.ElapsedMilliseconds);
}
#endif
}
/// <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
}