// ----------------------------------------------------------------------------

        private void CreateSP_ModifyError()
        {
            IDbDataParameter p;

            sp_ModifyError             = connectionProvider.GetConnection().CreateCommand();
            sp_ModifyError.CommandText = "MAILAGENT.MODIFY_ERROR";
            sp_ModifyError.CommandType = System.Data.CommandType.StoredProcedure;
#if ODP_NET
            ((OracleCommand)sp_ModifyError).BindByName = true;
#endif


            p = sp_ModifyError.CreateParameter();
            p.ParameterName = "MAILID_I";
            p.DbType        = DbType.String;
            p.Size          = 255;
            p.Direction     = ParameterDirection.Input;
            sp_ModifyError.Parameters.Add(p);

            p = sp_ModifyError.CreateParameter();
            p.ParameterName = "ERRORMESSAGE_I";
            p.DbType        = DbType.String;
            p.Size          = 255;
            p.Direction     = ParameterDirection.Input;
            sp_ModifyError.Parameters.Add(p);

            p = sp_ModifyError.CreateParameter();
            p.ParameterName = "ALMID_O";
            p.DbType        = DbType.String;
            p.Size          = 255;
            p.Direction     = ParameterDirection.Output;
            sp_ModifyError.Parameters.Add(p);
        }
Exemple #2
0
        public GetQueriesByTotalTimeResponse GetQueriesByTotalTime(string date, int rowCount = 100,
                                                                   int totalDuration         = 300)
        {
            DateTime parsedDate = DateTime.Parse(date);
            string   cacheKey   =
                $"GetQueriesByTotalTimeResponse_{parsedDate:MM/dd/yyyy}_rows:{rowCount}_total:{totalDuration}";

            return(_memoryCache.GetOrCreate(cacheKey, entry => {
                StoreKey(entry);
                entry.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(10);
                var result = new GetQueriesByTotalTimeResponse();
                _connectionProvider.GetConnection((connection) => {
                    var times = connection.Query("GetQueriesByTotalTime", new
                    {
                        date = parsedDate,
                        totalDuration = totalDuration,
                        rowsCount = rowCount
                    }, commandType: CommandType.StoredProcedure);
                    result.Queries.AddRange(times);
                    result.TotalQueries =
                        connection.QuerySingle <int>(
                            "SELECT COUNT(Id) FROM QueryHistory WHERE DateId = (SELECT Id FROM Dates d WHERE d.Date = CAST(@date as DATE))", new { date = parsedDate.Date });
                });
                return result;
            }));
        }
        async Task EnsureTableIsCreated()
        {
            using (var connection = await _connectionProvider.GetConnection())
            {
                if (connection.GetTableNames().Contains(_tableName))
                {
                    var columns = connection.GetColumns(_tableName.Schema, _tableName.Name);
                    if (!columns.Any(x => x.Name == "CreationTime"))
                    {
                        using (var command = connection.CreateCommand())
                        {
                            command.CommandText = $@"
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name = N'CreationTime' AND Object_ID = Object_ID(N'{_tableName.QualifiedName}'))
BEGIN
    ALTER TABLE {_tableName.QualifiedName} ADD [CreationTime] DATETIMEOFFSET
END
";
                            command.ExecuteNonQuery();
                        }

                        await connection.Complete();
                    }

                    return;
                }

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = $@"
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = '{_tableName.Schema}')
	EXEC('CREATE SCHEMA {_tableName.Schema}')

----

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{_tableName.Schema}' AND TABLE_NAME = '{_tableName.Name}')
    CREATE TABLE {_tableName.QualifiedName} (
        [Id] VARCHAR(200),
        [Meta] VARBINARY(MAX),
        [Data] VARBINARY(MAX),
        [CreationTime] DATETIMEOFFSET,
        [LastReadTime] DATETIMEOFFSET
    );

";
                    const int tableAlreadyExists = 2714;

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (SqlException exception) when(exception.Number == tableAlreadyExists)
                    {
                        // table already exists - just quit now
                        return;
                    }
                }

                await connection.Complete();
            }
        }
        // ----------------------------------------------------------------------------

        private void CreateSP_Finddepartureroute()
        {
            IDbDataParameter p;
            OracleParameter  oP;

            sp_Finddepartureroute             = connectionProvider.GetConnection().CreateCommand();
            sp_Finddepartureroute.CommandText = "WEB_SERVICES_TRANSPORT.FINDDEPARTUREROUTE";
            sp_Finddepartureroute.CommandType = System.Data.CommandType.StoredProcedure;
#if ODP_NET
            ((OracleCommand)sp_Finddepartureroute).BindByName = true;
#endif

            p = sp_Finddepartureroute.CreateParameter();
            p.ParameterName = "DEPARTUREIDENTITY_I";
            p.DbType        = DbType.String;
            p.Size          = 35;
            p.Direction     = ParameterDirection.Input;
            sp_Finddepartureroute.Parameters.Add(p);

            oP = sp_Finddepartureroute.CreateParameter() as OracleParameter;
            oP.ParameterName = "ROUTE_CUR_O";
#if ODP_NET
            oP.OracleDbType = OracleDbType.RefCursor;
#else
            oP.OracleType = OracleType.Cursor;
#endif
            oP.Direction = ParameterDirection.Output;
            sp_Finddepartureroute.Parameters.Add(oP);
        }
        /// <summary>
        /// Internal function to make sure the table is created
        /// </summary>
        /// <param name="table">Name of the table to create</param>
        private void InnerEnsureTableIsCreated(TableName table)
        {
            using (var connection = _connectionProvider.GetConnection())
            {
                var tableNames = connection.GetTableNames();
                if (tableNames.Contains(table))
                {
                    _log.Info("Database already contains a table named {tableName} - will not create anything", table.QualifiedName);
                }
                else
                {
                    _log.Info("Table {tableName} does not exist - it will be created now", table.QualifiedName);

                    connection.ExecuteCommands($@"
                        CREATE TABLE {table.QualifiedName} (
                            `lock_key` varchar({LockKeyColumnSize}) NOT NULL,
                            `expiration` DATETIME NOT NULL,
                            PRIMARY KEY (`lock_key`)
                        );
                        ----
                        CREATE INDEX `idx_expiration` ON {table.QualifiedName} (
                            `expiration`
                        );");
                }

                connection.Complete();
            }
        }
 public void LogModerationAction(ModerationAction action)
 {
     using (var conn = _connectionProvider.GetConnection())
     {
         conn.Insert(action);
     }
 }
Exemple #7
0
        // ----------------------------------------------------------------------------

        private void CreateSP_Getalmtxt()
        {
            IDbDataParameter p;

            sp_Getalmtxt             = connectionProvider.GetConnection().CreateCommand();
            sp_Getalmtxt.CommandText = "WLSYSTEM.GETALMTXT";
            sp_Getalmtxt.CommandType = System.Data.CommandType.StoredProcedure;
#if ODP_NET
            ((OracleCommand)sp_Getalmtxt).BindByName = true;
#endif

            p = sp_Getalmtxt.CreateParameter();
            p.ParameterName = "ALMID_I";
            p.DbType        = DbType.String;
            p.Size          = 35;
            p.Direction     = ParameterDirection.Input;
            sp_Getalmtxt.Parameters.Add(p);

            p = sp_Getalmtxt.CreateParameter();
            p.ParameterName = "NLANGCOD_I";
            p.DbType        = DbType.String;
            p.Size          = 3;
            p.Direction     = ParameterDirection.Input;
            sp_Getalmtxt.Parameters.Add(p);

            p = sp_Getalmtxt.CreateParameter();
            p.ParameterName = "ALMTXT_O";
            p.DbType        = DbType.String;
            p.Size          = 300;
            p.Direction     = ParameterDirection.Output;
            sp_Getalmtxt.Parameters.Add(p);
        }
Exemple #8
0
        /// <summary>
        /// Creates the subscriptions table if necessary
        /// </summary>
        public void EnsureTableIsCreated()
        {
            using (var connection = _connectionProvider.GetConnection().Result)
            {
                var tableNames = connection.GetTableNames();

                if (tableNames.Contains(_tableName, StringComparer.OrdinalIgnoreCase))
                {
                    return;
                }

                _log.Info("Table '{0}' does not exist - it will be created now", _tableName);

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = string.Format(@"
CREATE TABLE [dbo].[{0}] (
	[topic] [nvarchar](200) NOT NULL,
	[address] [nvarchar](200) NOT NULL,
    CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED 
    (
	    [topic] ASC,
	    [address] ASC
    )
)
", _tableName);
                    command.ExecuteNonQuery();
                }

                connection.Complete();
            }
        }
Exemple #9
0
        void CreateSchema()
        {
            using (var connection = _connectionProvider.GetConnection().Result)
            {
                var tableNames = connection.GetTableNames();

                if (tableNames.Contains(_tableName, StringComparer.OrdinalIgnoreCase))
                {
                    _log.Info("Database already contains a table named '{0}' - will not create anything", _tableName);
                    return;
                }

                _log.Info("Table '{0}' does not exist - it will be created now", _tableName);

                ExecuteCommands(connection, $@"

CREATE TABLE [dbo].[{_tableName}]
(
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[recipient] [nvarchar](200) NOT NULL,
	[priority] [int] NOT NULL,
    [expiration] [datetime2] NOT NULL,
    [visible] [datetime2] NOT NULL,
	[headers] [varbinary](max) NOT NULL,
	[body] [varbinary](max) NOT NULL,
    CONSTRAINT [PK_{_tableName}] PRIMARY KEY CLUSTERED 
    (
	    [recipient] ASC,
	    [priority] ASC,
	    [id] ASC
    )
)

----

CREATE NONCLUSTERED INDEX [IDX_RECEIVE_{_tableName}] ON [dbo].[{_tableName}]
(
	[recipient] ASC,
	[priority] ASC,
    [visible] ASC,
    [expiration] ASC,
	[id] ASC
)

----

CREATE NONCLUSTERED INDEX [IDX_EXPIRATION_{_tableName}] ON [dbo].[{_tableName}]
(
    [expiration] ASC
)

");

                connection.Complete().Wait();
            }
        }
Exemple #10
0
 private Dictionary <string, Guid> InitItemsMap()
 {
     _itemsMapDictionary = new Dictionary <string, Guid>();
     _connectionProvider.GetConnection(connection => {
         string sql = $@"SELECT Id, Code FROM {_lookupName}";
         foreach (BaseLookup baseLookup in connection.Query <BaseLookup>(sql))
         {
             _itemsMapDictionary[baseLookup.Code] = baseLookup.Id;
         }
     });
     return(_itemsMapDictionary);
 }
Exemple #11
0
        async Task EnsureTableIsCreatedAsync()
        {
            using (var connection = await _connectionProvider.GetConnection())
            {
                var tableNames = connection.GetTableNames();

                if (tableNames.Contains(_tableName))
                {
                    return;
                }

                _log.Info("Table {tableName} does not exist - it will be created now", _tableName.QualifiedName);

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = $@"
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = '{_tableName.Schema}')
	EXEC('CREATE SCHEMA {_tableName.Schema}')

----

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{_tableName.Schema}' AND TABLE_NAME = '{
                            _tableName.Name
                        }')
    CREATE TABLE {_tableName.QualifiedName} (
        [id] [int] IDENTITY(1,1) NOT NULL,
	    [due_time] [datetimeoffset](7) NOT NULL,
	    [headers] [nvarchar](MAX) NOT NULL,
	    [body] [varbinary](MAX) NOT NULL,
        CONSTRAINT [PK_{_tableName.Schema}_{_tableName.Name}] PRIMARY KEY NONCLUSTERED 
        (
	        [id] ASC
        )
    )
";
                    command.ExecuteNonQuery();
                }

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = $@"
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_{_tableName.Schema}_{_tableName.Name}_DueTime')
    CREATE CLUSTERED INDEX [IX_{_tableName.Schema}_{_tableName.Name}_DueTime] ON {_tableName.QualifiedName}
    (
	    [due_time] ASC
    )";

                    command.ExecuteNonQuery();
                }

                await connection.Complete();
            }
        }
        public void CreateUser(string email)
        {
            var connection = _connectionProvider.GetConnection();

            connection.Open();

            var query = $"INSERT INTO users (`email`) VALUES ({email})";

            MySqlCommand command = new MySqlCommand(query, (MySqlConnection)connection);

            command.ExecuteNonQuery();
        }
Exemple #13
0
        // ----------------------------------------------------------------------------

        private void CreateSP_Completeprintjob()
        {
            sp_Completeprintjob             = connectionProvider.GetConnection().CreateCommand();
            sp_Completeprintjob.CommandText = "PRINT_QUEUE.COMPLETEPRINTJOB";
            sp_Completeprintjob.CommandType = System.Data.CommandType.StoredProcedure;
#if ODP_NET
            ((OracleCommand)sp_Completeprintjob).BindByName = true;
#endif
        }
        // ----------------------------------------------------------------------------

        private void CreateSP_NewHapircv()
        {
            IDbDataParameter p;

            sp_NewHapircv             = connectionProvider.GetConnection().CreateCommand();
            sp_NewHapircv.CommandText = "HAPI_RCV.NEW_HAPIRCV";
            sp_NewHapircv.CommandType = System.Data.CommandType.StoredProcedure;

            p               = sp_NewHapircv.CreateParameter();
            p.DbType        = DbType.String;
            p.Size          = 255;
            p.ParameterName = "CHANNEL_ID_I";
            p.Direction     = ParameterDirection.Input;
            sp_NewHapircv.Parameters.Add(p);

            p               = sp_NewHapircv.CreateParameter();
            p.DbType        = DbType.String;
            p.Size          = 255;
            p.ParameterName = "TRANSACTION_ID_I";
            p.Direction     = ParameterDirection.Input;
            sp_NewHapircv.Parameters.Add(p);

            p               = sp_NewHapircv.CreateParameter();
            p.DbType        = DbType.String;
            p.Size          = 255;
            p.ParameterName = "HAPIOBJECTNAME_I";
            p.Direction     = ParameterDirection.Input;
            sp_NewHapircv.Parameters.Add(p);

            p               = sp_NewHapircv.CreateParameter();
            p.DbType        = DbType.String;
            p.Size          = 255;
            p.ParameterName = "COMPANY_ID_O";
            p.Direction     = ParameterDirection.Output;
            sp_NewHapircv.Parameters.Add(p);

            p               = sp_NewHapircv.CreateParameter();
            p.DbType        = DbType.String;
            p.Size          = 255;
            p.ParameterName = "HAPIRCV_ID_O";
            p.Direction     = ParameterDirection.Output;
            sp_NewHapircv.Parameters.Add(p);

            p               = sp_NewHapircv.CreateParameter();
            p.DbType        = DbType.String;
            p.Size          = 255;
            p.ParameterName = "ALMID_O";
            p.Direction     = ParameterDirection.Output;
            sp_NewHapircv.Parameters.Add(p);
        }
        // ----------------------------------------------------------------------------

        private void CreateSP_AddErrorMessage()
        {
            IDbDataParameter p;

            sp_AddErrorMessage             = connectionProvider.GetConnection().CreateCommand();
            sp_AddErrorMessage.CommandText = "PICKORDERCARRIERBUILD.ADD_ERROR_MESSAGE";
            sp_AddErrorMessage.CommandType = System.Data.CommandType.StoredProcedure;
#if ODP_NET
            ((OracleCommand)sp_AddErrorMessage).BindByName = true;
#endif

            p = sp_AddErrorMessage.CreateParameter();
            p.ParameterName = "PBROWGRP_ID_I";
            p.DbType        = DbType.String;
            p.Size          = 140;
            p.Direction     = ParameterDirection.Input;
            sp_AddErrorMessage.Parameters.Add(p);

            p = sp_AddErrorMessage.CreateParameter();
            p.ParameterName = "ERROR_MSG_I";
            p.DbType        = DbType.String;
            p.Size          = 255;
            p.Direction     = ParameterDirection.Input;
            sp_AddErrorMessage.Parameters.Add(p);
        }
Exemple #16
0
        public bool ModUser(string username)
        {
            if (!UserExists(username))
            {
                return(false);
            }

            using (var conn = _connectionProvider.GetConnection())
            {
                conn.Execute("UPDATE tblUsers SET IsModerator = 1 WHERE Name = @Username", new { Username = username });
            }

            return(true);
        }
Exemple #17
0
        private void BackupDb()
        {
#if DEBUG
            return;
#endif
#pragma warning disable 162
            _connectionProvider.GetConnection(connection => {
                string db = connection.Database;
                connection.Execute($@"BACKUP DATABASE [{db}] TO DISK = N'C:\BAK\{db}_compressed.bak' WITH NAME = N'{db
						}-Full Database backup', COMPRESSION, NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 1"                        ,
                                   commandTimeout: _commandTimeout);
                _logger.LogInformation("Db backup created.");
            });
#pragma warning restore 162
        }
 /// <summary>
 /// Initializes the subscription storage by reading the lengths of the [topic] and [address] columns from MySQL
 /// </summary>
 public void Initialize()
 {
     try
     {
         using (var connection = _connectionProvider.GetConnection())
         {
             _topicLength   = GetColumnWidth("topic", connection);
             _addressLength = GetColumnWidth("address", connection);
         }
     }
     catch (Exception exception)
     {
         throw new RebusApplicationException(exception, "Error during schema reflection");
     }
 }
Exemple #19
0
        void InnerEnsureTableIsCreated()
        {
            using (var connection = _connectionProvider.GetConnection())
            {
                var tableNames = connection.GetTableNames();
                if (tableNames.Contains(_tableName))
                {
                    return;
                }

                _log.Info("Table {tableName} does not exist - it will be created now", _tableName.QualifiedName);

                connection.ExecuteCommands($@"
                    CREATE TABLE {_tableName.QualifiedName} (
                        `id` BIGINT NOT NULL AUTO_INCREMENT,
                        `due_time` DATETIME(6) NOT NULL,
                        `headers` LONGTEXT NOT NULL,
                        `body` LONGBLOB NOT NULL,
                        PRIMARY KEY (`id`)
                    );
                    ----
                    CREATE INDEX `idx_due_time` ON {_tableName.QualifiedName} (
                        `due_time`
                    );");
                connection.Complete();
            }
        }
        public HomeModule(IDbConnectionProvider connectionProvider)
        {
            Get["/"] = _ =>
            {
                using (var conn = connectionProvider.GetConnection())
                {
                    return(conn.Query <User>("select * from users"));
                }
            };

            Get["/complicatedquery"] = _ =>
            {
                //What if we need to build up a big view model and need to execute a couple of queries?
                //We could create some methods in this class and separate those queries out and build the view model up.
                //What if we have 3-4 endpoints that need to do the same? We then have a large module class which in my
                //opinion should be anaemic and have its single responsibility be for return http specific things.

                return(200);
            };

            Post["/"] = _ =>
            {
                //So we could inject a dbconnection and return data from this module but I dont like that due to the comments above.
                //What about POST/PUT? We need somewhere to do business logic etc which I think should be a service or command layer
                return(201);
            };
        }
Exemple #21
0
        // ----------------------------------------------------------------------------

        private void CreateSP_Execute()
        {
            OracleParameter p;

            sp_Execute             = connectionProvider.GetConnection().CreateCommand();
            sp_Execute.CommandText = "JOB.EXECUTEXML";
            sp_Execute.CommandType = System.Data.CommandType.StoredProcedure;
#if ODP_NET
            ((OracleCommand)sp_Execute).BindByName = true;
#endif

            p = sp_Execute.CreateParameter() as OracleParameter;
            p.ParameterName = "ARGS_I";
            p.DbType        = DbType.String;
            p.Size          = 32500;
            p.Direction     = ParameterDirection.Input;
            sp_Execute.Parameters.Add(p);

            p = sp_Execute.CreateParameter() as OracleParameter;
            p.ParameterName = "EVENTLIST_O";
            p.DbType        = DbType.String;
            p.Size          = 32500;
            p.Direction     = ParameterDirection.Output;
            sp_Execute.Parameters.Add(p);
        }
        void InnerEnsureTableIsCreatedAsync(TableName table)
        {
            using (var connection = _connectionProvider.GetConnection())
            {
                var tableNames = connection.GetTableNames();
                if (tableNames.Contains(table))
                {
                    _log.Info("Database already contains a table named {tableName} - will not create anything", table.QualifiedName);
                }
                else
                {
                    _log.Info("Table {tableName} does not exist - it will be created now", table.QualifiedName);

                    connection.ExecuteCommands($@"
                        CREATE TABLE {table.QualifiedName} (
                            `id` BIGINT NOT NULL AUTO_INCREMENT,
                            `priority` INT NOT NULL,
                            `expiration` DATETIME(6) NOT NULL,
                            `visible` DATETIME(6) NOT NULL,
                            `headers` LONGBLOB NOT NULL,
                            `body` LONGBLOB NOT NULL,
                            `processing` TINYINT(1) NULL,
                            PRIMARY KEY (`id`)
                        );
                        ----
                        CREATE INDEX `idx_receive` ON {table.QualifiedName} (
                            `visible`,
                            `expiration`,
                            `processing`
                        );
                        ----
                        CREATE INDEX `idx_sort` ON {table.QualifiedName} (
                            `priority` DESC,
                            `visible` ASC,
                            `id` ASC
                        );
                        ----
                        CREATE INDEX `idx_expiration` ON {table.QualifiedName} (
                            `expiration`,
                            `processing`
                        );");
                }

                AdditionalSchemaModifications(connection, table);
                connection.Complete();
            }
        }
Exemple #23
0
        // ----------------------------------------------------------------------------

        // ----------------------------------------------------------------------------

        private void CreateSP_ModifyError()
        {
            IDbDataParameter p;

            sp_ModifyError             = connectionProvider.GetConnection().CreateCommand();
            sp_ModifyError.CommandText = "MAPIOUT_CONFIG.MODIFY_ERROR";
            sp_ModifyError.CommandType = System.Data.CommandType.StoredProcedure;
#if ODP_NET
            ((OracleCommand)sp_ModifyError).BindByName = true;
#endif


            p = sp_ModifyError.CreateParameter();
            p.ParameterName = "MAPI_OUT_ID_I";
            p.DbType        = DbType.String;
            p.Size          = 255;
            p.Direction     = ParameterDirection.Input;
            sp_ModifyError.Parameters.Add(p);

            p = sp_ModifyError.CreateParameter();
            p.ParameterName = "FIRSTSNDDTM_I";
            p.DbType        = DbType.DateTime;
            p.Direction     = ParameterDirection.Input;
            sp_ModifyError.Parameters.Add(p);

            p = sp_ModifyError.CreateParameter();
            p.ParameterName = "SNDERRCODE_I";
            p.DbType        = DbType.String;
            p.Size          = 255;
            p.Direction     = ParameterDirection.Input;
            sp_ModifyError.Parameters.Add(p);

            p = sp_ModifyError.CreateParameter();
            p.ParameterName = "SNDERRMSG_I";
            p.DbType        = DbType.String;
            p.Size          = 255;
            p.Direction     = ParameterDirection.Input;
            sp_ModifyError.Parameters.Add(p);

            p = sp_ModifyError.CreateParameter();
            p.ParameterName = "ALMID_O";
            p.DbType        = DbType.String;
            p.Size          = 255;
            p.Direction     = ParameterDirection.Output;
            sp_ModifyError.Parameters.Add(p);
        }
Exemple #24
0
 public T Get(int id)
 {
     using (var connection = ConnectionProvider.GetConnection())
     {
         return(connection.Get <T>(id));
     }
 }
Exemple #25
0
        private void CreateSP_Enable_Server_Log()
        {
            IDbDataParameter p;

            sp_Enable_Server_Log = connectionProvider.GetConnection().CreateCommand();
            ((OracleCommand)sp_Enable_Server_Log).BindByName = true;
            sp_Enable_Server_Log.CommandText = "LOGG_OUTPUT.ENABLE_SERVER_LOG";
            sp_Enable_Server_Log.CommandType = System.Data.CommandType.StoredProcedure;
            ((OracleCommand)sp_Enable_Server_Log).FetchSize = 65536;

            p = new OracleParameter();
            p.ParameterName = "DIRECTORY_I";
            p.DbType        = ConvertToDbType(typeof(string));
            p.Size          = 255;
            p.Direction     = ParameterDirection.Input;
            sp_Enable_Server_Log.Parameters.Add(p);

            p = new OracleParameter();
            p.ParameterName = "FILENAME_I";
            p.DbType        = ConvertToDbType(typeof(string));
            p.Size          = 255;
            p.Direction     = ParameterDirection.Input;
            sp_Enable_Server_Log.Parameters.Add(p);

            p = new OracleParameter();
            p.ParameterName = "LEVEL_I";
            p.DbType        = ConvertToDbType(typeof(double));
            p.Direction     = ParameterDirection.Input;
            sp_Enable_Server_Log.Parameters.Add(p);
        }
Exemple #26
0
        /// <summary>
        /// Creates the due messages table if necessary
        /// </summary>
        public void EnsureTableIsCreated()
        {
            using (var connection = _connectionProvider.GetConnection().Result)
            {
                var tableNames = connection.GetTableNames();

                if (tableNames.Contains(_tableName, StringComparer.OrdinalIgnoreCase))
                {
                    return;
                }

                _log.Info("Table '{0}' does not exist - it will be created now", _tableName);

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = string.Format(@"
CREATE TABLE [dbo].[{0}](
    [id] [int] IDENTITY(1,1) NOT NULL,
	[due_time] [datetime2](7) NOT NULL,
	[headers] [nvarchar](MAX) NOT NULL,
	[body] [varbinary](MAX) NOT NULL,
    CONSTRAINT [PK_{0}] PRIMARY KEY NONCLUSTERED 
    (
	    [id] ASC
    )
)
", _tableName);
                    command.ExecuteNonQuery();
                }

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = string.Format(@"
CREATE CLUSTERED INDEX [IX_{0}_DueTime] ON [dbo].[{0}]
(
	[due_time] ASC
)
", _tableName);

                    command.ExecuteNonQuery();
                }

                connection.Complete();
            }
        }
Exemple #27
0
        public static DateTime GetLastQueryDate <T>(this IDbConnectionProvider connectionProvider) where T : class, IRecordWithDate
        {
            string   tableName = OrmUtils.GetTableName <T>();
            DateTime result    = DateTime.MinValue;

            connectionProvider.GetConnection(connection => {
                result = connection.ExecuteScalar <DateTime>($"SELECT MAX(Date) FROM [{tableName}]");
            });
            return(result);
        }
        async Task EnsureTableIsCreatedAsync()
        {
            using (var connection = await _connectionProvider.GetConnection())
            {
                var tableNames = connection.GetTableNames();

                if (tableNames.Contains(_tableName))
                {
                    return;
                }

                _log.Info("Table {tableName} does not exist - it will be created now", _tableName.QualifiedName);

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = $@"
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = '{_tableName.Schema}')
	EXEC('CREATE SCHEMA {_tableName.Schema}')

----

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{_tableName.Schema}' AND TABLE_NAME = '{
                            _tableName.Name
                        }')
    CREATE TABLE {_tableName.QualifiedName} (
	    [id] [uniqueidentifier] NOT NULL,
	    [revision] [int] NOT NULL,
	    [data] [nvarchar](max) NOT NULL,
	    [metadata] [nvarchar](max) NOT NULL,
        CONSTRAINT [PK_{_tableName.Schema}_{_tableName.Name}] PRIMARY KEY CLUSTERED 
        (
	        [id] ASC,
            [revision] ASC
        )
    )

";
                    command.ExecuteNonQuery();
                }

                await connection.Complete();
            }
        }
Exemple #29
0
 public static void BulkInsert <T>(this IEnumerable <T> items, IDbConnectionProvider connectionProvider,
                                   bool checkConstraints = true)
     where T : class
 {
     if (IsItemsEmpty(items))
     {
         return;
     }
     connectionProvider.GetConnection(connection => {
         Insert(connection, items, checkConstraints);
     });
 }
Exemple #30
0
        // ----------------------------------------------------------------------------

        private void CreateSP_CheckMessageTypeForMhid()
        {
            IDbDataParameter p;

            sp_CheckMessageTypeForMhid             = connectionProvider.GetConnection().CreateCommand();
            sp_CheckMessageTypeForMhid.CommandText = "MAPIIN.CHECK_MESSAGE_TYPE_FOR_MHID";
            sp_CheckMessageTypeForMhid.CommandType = System.Data.CommandType.StoredProcedure;
#if ODP_NET
            ((OracleCommand)sp_CheckMessageTypeForMhid).BindByName = true;
#endif

            p = sp_CheckMessageTypeForMhid.CreateParameter();
            p.ParameterName = "MHID_I";
            p.DbType        = DbType.String;
            p.Size          = 35;
            p.Direction     = ParameterDirection.Input;
            sp_CheckMessageTypeForMhid.Parameters.Add(p);

            p = sp_CheckMessageTypeForMhid.CreateParameter();
            p.ParameterName = "MSG_ID_I";
            p.DbType        = DbType.String;
            p.Size          = 35;
            p.Direction     = ParameterDirection.Input;
            sp_CheckMessageTypeForMhid.Parameters.Add(p);

            p = sp_CheckMessageTypeForMhid.CreateParameter();
            p.ParameterName = "ALMID_O";
            p.DbType        = DbType.String;
            p.Size          = 35;
            p.Direction     = ParameterDirection.Output;
            sp_CheckMessageTypeForMhid.Parameters.Add(p);
        }