public long Handle(SendMessageCommand commandSend)
        {
            if (!_messageExpirationEnabled.HasValue)
            {
                _messageExpirationEnabled = _options.Value.EnableMessageExpiration;
            }

            var jobName       = _jobSchedulerMetaData.GetJobName(commandSend.MessageData);
            var scheduledTime = DateTimeOffset.MinValue;
            var eventTime     = DateTimeOffset.MinValue;

            if (!string.IsNullOrWhiteSpace(jobName))
            {
                scheduledTime = _jobSchedulerMetaData.GetScheduledTime(commandSend.MessageData);
                eventTime     = _jobSchedulerMetaData.GetEventTime(commandSend.MessageData);
            }

            using (var connection = new SqlConnection(_configurationSend.ConnectionInfo.ConnectionString))
            {
                connection.Open();
                using (var trans = connection.BeginTransaction())
                {
                    if (string.IsNullOrWhiteSpace(jobName) || _jobExistsHandler.Handle(new DoesJobExistQuery <SqlConnection, SqlTransaction>(jobName, scheduledTime, connection, trans)) ==
                        QueueStatuses.NotQueued)
                    {
                        using (var command = connection.CreateCommand())
                        {
                            command.Transaction = trans;
                            command.CommandText = _commandCache.GetCommand(CommandStringTypes.InsertMessageBody);
                            var serialization =
                                _serializer.Serializer.MessageToBytes(new MessageBody
                            {
                                Body = commandSend.MessageToSend.Body
                            }, commandSend.MessageToSend.Headers);

                            command.Parameters.Add("@body", SqlDbType.VarBinary, -1);
                            command.Parameters["@body"].Value = serialization.Output;

                            commandSend.MessageToSend.SetHeader(_headers.StandardHeaders.MessageInterceptorGraph,
                                                                serialization.Graph);

                            command.Parameters.Add("@headers", SqlDbType.VarBinary, -1);
                            command.Parameters["@headers"].Value =
                                _serializer.InternalSerializer.ConvertToBytes(commandSend.MessageToSend.Headers);

                            var id = Convert.ToInt64(command.ExecuteScalar());
                            if (id > 0)
                            {
                                var expiration = TimeSpan.Zero;
                                if (_messageExpirationEnabled.Value)
                                {
                                    expiration = MessageExpiration.GetExpiration(commandSend, data => data.GetExpiration());
                                }

                                CreateMetaDataRecord(commandSend.MessageData.GetDelay(), expiration, connection, id,
                                                     commandSend.MessageToSend, commandSend.MessageData, trans);
                                if (_options.Value.EnableStatusTable)
                                {
                                    CreateStatusRecord(connection, id, commandSend.MessageToSend,
                                                       commandSend.MessageData, trans);
                                }

                                if (!string.IsNullOrWhiteSpace(jobName))
                                {
                                    _sendJobStatus.Handle(new SetJobLastKnownEventCommand <SqlConnection, SqlTransaction>(jobName, eventTime,
                                                                                                                          scheduledTime, connection, trans));
                                }
                            }
                            else
                            {
                                throw new DotNetWorkQueueException(
                                          "Failed to insert record - the ID of the new record returned by SQL server was 0");
                            }
                            trans.Commit();
                            return(id);
                        }
                    }
                    throw new DotNetWorkQueueException(
                              "Failed to insert record - the job has already been queued or processed");
                }
            }
        }
        /// <summary>
        /// Handles the specified command.
        /// </summary>
        /// <param name="commandSend">The command.</param>
        /// <returns></returns>
        /// <exception cref="DotNetWorkQueueException">Failed to insert record - the ID of the new record returned by SQLite was 0</exception>
        public long Handle(SendMessageCommand commandSend)
        {
            if (!_databaseExists.Exists(_configurationSend.ConnectionInfo.ConnectionString))
            {
                return(0);
            }

            if (!_messageExpirationEnabled.HasValue)
            {
                _messageExpirationEnabled = _options.Value.EnableMessageExpiration ||
                                            _options.Value.QueueType == QueueTypes.RpcReceive ||
                                            _options.Value.QueueType == QueueTypes.RpcSend;
            }

            using (var connection = _dbFactory.CreateConnection(_configurationSend.ConnectionInfo.ConnectionString, false))
            {
                connection.Open();

                var expiration = TimeSpan.Zero;
                if (_messageExpirationEnabled.Value)
                {
                    expiration = MessageExpiration.GetExpiration(commandSend, _headers, data => data.GetExpiration());
                }

                var jobName       = _jobSchedulerMetaData.GetJobName(commandSend.MessageData);
                var scheduledTime = DateTimeOffset.MinValue;
                var eventTime     = DateTimeOffset.MinValue;
                if (!string.IsNullOrWhiteSpace(jobName))
                {
                    scheduledTime = _jobSchedulerMetaData.GetScheduledTime(commandSend.MessageData);
                    eventTime     = _jobSchedulerMetaData.GetEventTime(commandSend.MessageData);
                }

                IDbCommand commandStatus = null;
                using (var command = SendMessage.GetMainCommand(commandSend, connection, _commandCache, _headers, _serializer))
                {
                    long id;
                    using (var commandMeta = SendMessage.CreateMetaDataRecord(commandSend.MessageData.GetDelay(), expiration,
                                                                              connection, commandSend.MessageToSend, commandSend.MessageData, _tableNameHelper, _headers,
                                                                              _options.Value, _getTime))
                    {
                        if (_options.Value.EnableStatusTable)
                        {
                            commandStatus = CreateStatusRecord(connection, commandSend.MessageToSend,
                                                               commandSend.MessageData);
                        }

                        using (var trans = _dbFactory.CreateTransaction(connection).BeginTransaction())
                        {
                            try
                            {
                                if (string.IsNullOrWhiteSpace(jobName) || _jobExistsHandler.Handle(new DoesJobExistQuery <IDbConnection, IDbTransaction>(jobName, scheduledTime, connection, trans)) ==
                                    QueueStatuses.NotQueued)
                                {
                                    command.Transaction = trans;
                                    command.ExecuteNonQuery();
                                    var commandId = connection.CreateCommand();
                                    commandId.Transaction = trans;
                                    commandId.CommandText = "SELECT last_insert_rowid();";
                                    id = Convert.ToInt64(commandId.ExecuteScalar());
                                    if (id > 0)
                                    {
                                        commandMeta.Transaction = trans;

                                        var param = commandMeta.CreateParameter();
                                        param.ParameterName = "@QueueID";
                                        param.DbType        = DbType.Int64;
                                        param.Value         = id;
                                        commandMeta.Parameters.Add(param);
                                        commandMeta.ExecuteNonQuery();
                                        if (commandStatus != null)
                                        {
                                            commandStatus.Transaction = trans;

                                            param = commandStatus.CreateParameter();
                                            param.ParameterName = "@QueueID";
                                            param.DbType        = DbType.Int64;
                                            param.Value         = id;
                                            commandStatus.Parameters.Add(param);
                                            commandStatus.ExecuteNonQuery();
                                        }

                                        if (!string.IsNullOrWhiteSpace(jobName))
                                        {
                                            _sendJobStatus.Handle(new SetJobLastKnownEventCommand <IDbConnection, IDbTransaction>(jobName, eventTime,
                                                                                                                                  scheduledTime, connection, trans));
                                        }
                                        trans.Commit();
                                    }
                                    else
                                    {
                                        throw new DotNetWorkQueueException(
                                                  "Failed to insert record - the ID of the new record returned by SQLite was 0");
                                    }
                                }
                                else
                                {
                                    throw new DotNetWorkQueueException(
                                              "Failed to insert record - the job has already been queued or processed");
                                }
                            }
                            finally
                            {
                                commandStatus?.Dispose();
                            }
                        }
                    }
                    return(id);
                }
            }
        }
        /// <summary>
        /// Handles the specified command.
        /// </summary>
        /// <param name="commandSend">The command.</param>
        /// <returns></returns>
        /// <exception cref="DotNetWorkQueueException">Failed to insert record - the ID of the new record returned by SQLite was 0</exception>
        public async Task <int> HandleAsync(SendMessageCommand commandSend)
        {
            return(await Task.Run(() => //hack until litedb adds async methods
            {
                if (!_databaseExists.Exists())
                {
                    return 0;
                }

                if (!_messageExpirationEnabled.HasValue)
                {
                    _messageExpirationEnabled = _options.Value.EnableMessageExpiration;
                }


                TimeSpan?expiration = null;
                if (_messageExpirationEnabled.Value)
                {
                    expiration = MessageExpiration.GetExpiration(commandSend, data => data.GetExpiration());
                }

                var jobName = _jobSchedulerMetaData.GetJobName(commandSend.MessageData);
                var scheduledTime = DateTimeOffset.MinValue;
                var eventTime = DateTimeOffset.MinValue;
                if (!string.IsNullOrWhiteSpace(jobName))
                {
                    scheduledTime = _jobSchedulerMetaData.GetScheduledTime(commandSend.MessageData);
                    eventTime = _jobSchedulerMetaData.GetEventTime(commandSend.MessageData);
                }

                int id = 0;
                using (var db = _connectionInformation.GetDatabase())
                {
                    lock (Locker) //we need to block due to jobs
                    {
                        try
                        {
                            db.Database.BeginTrans(); //only blocks on shared connections
                            if (string.IsNullOrWhiteSpace(jobName) || _jobExistsHandler.Handle(
                                    new DoesJobExistQuery(jobName, scheduledTime, db.Database)) ==
                                QueueStatuses.NotQueued)
                            {
                                var serialization =
                                    _serializer.Serializer.MessageToBytes(
                                        new MessageBody {
                                    Body = commandSend.MessageToSend.Body
                                },
                                        commandSend.MessageToSend.Headers);

                                //create queue
                                var queueData = new LiteDb.Schema.QueueTable()
                                {
                                    Body = serialization.Output
                                };
                                commandSend.MessageToSend.SetHeader(_headers.StandardHeaders.MessageInterceptorGraph,
                                                                    serialization.Graph);
                                queueData.Headers =
                                    _serializer.InternalSerializer.ConvertToBytes(commandSend.MessageToSend.Headers);

                                var col = db.Database.GetCollection <QueueTable>(_tableNameHelper.QueueName);
                                id = col.Insert(queueData).AsInt32;

                                //create metadata
                                var metaData = new LiteDb.Schema.MetaDataTable
                                {
                                    QueueId = id,
                                    CorrelationId = (Guid)commandSend.MessageData.CorrelationId.Id.Value,
                                    QueuedDateTime = DateTime.UtcNow
                                };

                                if (!string.IsNullOrWhiteSpace(jobName))
                                {
                                    metaData.QueueProcessTime = scheduledTime.UtcDateTime;
                                }
                                else if (_options.Value.EnableDelayedProcessing)
                                {
                                    var delay = commandSend.MessageData.GetDelay();
                                    if (delay.HasValue)
                                    {
                                        metaData.QueueProcessTime = DateTime.UtcNow.Add(delay.Value);
                                    }
                                }

                                if (_options.Value.EnableMessageExpiration && expiration.HasValue)
                                {
                                    metaData.ExpirationTime = DateTime.UtcNow.Add(expiration.Value);
                                }

                                if (_options.Value.EnableStatus)
                                {
                                    metaData.Status = QueueStatuses.Waiting;
                                }

                                if (_options.Value.EnableRoute)
                                {
                                    metaData.Route = commandSend.MessageData.Route;
                                }

                                var colMeta = db.Database.GetCollection <MetaDataTable>(_tableNameHelper.MetaDataName);
                                colMeta.Insert(metaData);

                                //create status table record
                                if (_options.Value.EnableStatusTable || !string.IsNullOrWhiteSpace(jobName))
                                {
                                    var statusData = new LiteDb.Schema.StatusTable()
                                    {
                                        Status = metaData.Status,
                                        CorrelationId = metaData.CorrelationId,
                                        QueueId = id
                                    };
                                    if (!string.IsNullOrWhiteSpace(jobName))
                                    {
                                        statusData.JobName = jobName;
                                    }
                                    var colStatus = db.Database.GetCollection <StatusTable>(_tableNameHelper.StatusName);
                                    colStatus.Insert(statusData);
                                }

                                //job name
                                if (!string.IsNullOrWhiteSpace(jobName))
                                {
                                    _sendJobStatus.Handle(new SetJobLastKnownEventCommand(jobName, eventTime,
                                                                                          scheduledTime, db.Database));
                                }
                            }
                            else
                            {
                                throw new DotNetWorkQueueException(
                                    "Failed to insert record - the job has already been queued or processed");
                            }

                            db.Database.Commit();
                        }
                        catch
                        {
                            db.Database.Rollback();
                            throw;
                        }
                    }
                }

                return id;
            }));
        }