// ----------------------------------------------------------------------------
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);
}
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);
}
}
// ----------------------------------------------------------------------------
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);
}
/// <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();
}
}
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();
}
}
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);
}
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();
}
// ----------------------------------------------------------------------------
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);
}
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);
}
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");
}
}
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);
};
}
// ----------------------------------------------------------------------------
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();
}
}
// ----------------------------------------------------------------------------
// ----------------------------------------------------------------------------
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);
}
public T Get(int id)
{
using (var connection = ConnectionProvider.GetConnection())
{
return(connection.Get <T>(id));
}
}
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);
}
/// <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();
}
}
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();
}
}
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);
});
}
// ----------------------------------------------------------------------------
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);
}