예제 #1
0
        /// <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
        }
예제 #2
0
        /// <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
        }
예제 #3
0
        /// <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
        }