/// <summary>
/// Returns the list of columns of the specified table.
/// </summary>
/// <param name="tableName">The table name</param>
/// <returns>The list of columns</returns>
public virtual IList <Entity> GetTableColumns(string tableName)
{
LoggerHelper.Info("Start");
IList <Entity> list = null;
try
{
string[] tableInfo = tableName.Split('.');
LoggerHelper.Debug("tableInfo,length = " + tableInfo.Length.ToString());
StringBuilder query = new StringBuilder();
IList <DBParam> queryParams = new List <DBParam>();
query.Append("SELECT COLUMN_NAME AS [Name], IS_NULLABLE AS [Required], DATA_TYPE AS [Type], ");
query.Append("CHARACTER_MAXIMUM_LENGTH AS [MaxLength], ORDINAL_POSITION AS [Order] ");
query.Append("FROM INFORMATION_SCHEMA.COLUMNS ");
query.Append("WHERE TABLE_NAME = @p0 ");
if (tableInfo.Length == 1)
{
queryParams.Add(new DBParam(queryParams, tableInfo[0], DbType.String));
}
else if (tableInfo.Length == 2)
{
query.Append("AND TABLE_SCHEMA = @p1 ");
queryParams.Add(new DBParam(queryParams, tableInfo[1], DbType.String));
queryParams.Add(new DBParam(queryParams, tableInfo[0], DbType.String));
}
else
{
queryParams.Add(new DBParam(queryParams, tableName, DbType.String));
}
query.Append("ORDER BY TABLE_NAME,ORDINAL_POSITION ");
LoggerHelper.Debug(query.ToString());
//TODO : Create an entity object
Entity entity = new Entity("Columns");
entity.SetField(new Field("Name"));
entity.SetField(new Field("Required"));
entity.SetField(new Field("Type"));
entity.SetField(new Field("MaxLength"));
entity.SetField(new Field("Order"));
StatementWrapper wrapper = new StatementWrapper(query, queryParams);
ResultSetHandler <IList <Entity> > h = new EntityHandler <Entity>(entity);
list = GetQueryRunner().Query(GetConnection(), wrapper, h);
}
catch (Exception e)
{
LoggerHelper.Error(e);
throw new Exception("Unable to get table columns.", e);
}
finally
{
LoggerHelper.Info("End");
}
return(list);
}
/// <summary>
/// Saves the specified page configuration of the specified application name into the database.
/// </summary>
/// <param name="appName">the application name</param>
/// <param name="page">The page to be saved</param>
public virtual void SavePage(string appName, Page page)
{
LoggerHelper.Info("Start");
try
{
if (string.IsNullOrEmpty(page.PageAppId))
{
StringBuilder query = new StringBuilder("SELECT PageAppId FROM PageApp WHERE AppName = @p0");
IList <DBParam> queryParams = new List <DBParam>();
queryParams.Add(new DBParam(queryParams, appName, DbType.String));
LoggerHelper.Debug(query.ToString());
StatementWrapper wrapper = new StatementWrapper(query, queryParams);
int pageAppId = GetQueryRunner().ExecuteScalar(GetConnection(), wrapper);
page.PageAppId = pageAppId.ToString();
}
SavePage(page, false);
}
catch (Exception e)
{
LoggerHelper.Error(e);
throw new Exception("Unable to save application page.", e);
}
finally
{
LoggerHelper.Info("End");
}
}
/// <summary>
/// Deletes the specified entity from an SQL database.
/// </summary>
/// <param name="entity">The entity to be deleted</param>
public virtual void DeleteEntity(Entity entity)
{
LoggerHelper.Info("Start");
try
{
if (entity.GetFieldId() == null)
{
LoggerHelper.Error("Entity does not have an Id field defined.");
return;
}
StatementWrapper stmtWrapper = GetQueryBuilder().BuildDeleteStatement(entity);
LoggerHelper.Debug(stmtWrapper.Query.ToString());
GetQueryRunner().ExecuteNonQuery(GetConnection(), stmtWrapper);
}
catch (Exception e)
{
if (e.Message.Contains("FK") || e.Message.Contains("REFERENCE"))
{
throw new Exception(entity.GetTableName() + " cannot be deleted, because is being used.");
}
else
{
throw new Exception("Unable to delete " + entity.GetTableName() + " record.", e);
}
}
finally
{
LoggerHelper.Info("End");
}
}
/// <summary>
/// Returns a list of pages for the specified AppName
/// </summary>
/// <param name="appName">The application name </param>
/// <returns>the page list</returns>
public virtual IList <Page> GetPageList(string appName)
{
LoggerHelper.Info("Start");
IList <Page> list = null;
IList <DBParam> queryParams = new List <DBParam>();
try
{
StringBuilder query = new StringBuilder();
query.Append("SELECT * FROM Page P ");
query.Append("INNER JOIN PageApp A ON P.PageAppId = A.PageAppId ");
query.Append("WHERE A.AppName = @p0 ");
query.Append("ORDER BY Name ");
queryParams.Add(new DBParam(queryParams, appName, DbType.String));
LoggerHelper.Debug(query.ToString());
StatementWrapper wrapper = new StatementWrapper(query, queryParams);
ResultSetHandler <IList <Page> > h = new BeanListHandler <Page>(typeof(Page));
list = GetQueryRunner().Query(GetConnection(), wrapper, h);
}
catch (Exception e)
{
LoggerHelper.Error(e);
throw new Exception("Unable to get page list.", e);
}
finally
{
LoggerHelper.Info("End");
}
return(list);
}
/// <summary>
/// Updates the specified entity and then deletes the entity from the SQL database as a single transacion.
/// The purpose of this method is to be used when you have a delete trigger in the SQL database and you need to update
/// first who is doing the delete.
/// </summary>
/// <param name="entity"></param>
public virtual void UpdateDeleteEntity(Entity entity)
{
LoggerHelper.Info("Start");
try
{
if (entity.GetFieldId() == null)
{
LoggerHelper.Error("Entity does not have an Id field defined.");
return;
}
IList <StatementWrapper> statements = new List <StatementWrapper>();
StatementWrapper stmtWrapper = GetQueryBuilder().BuildUpdateEntityStatement(entity);
LoggerHelper.Debug(stmtWrapper.Query.ToString());
statements.Add(stmtWrapper);
stmtWrapper = GetQueryBuilder().BuildDeleteStatement(entity);
LoggerHelper.Debug(stmtWrapper.Query.ToString());
statements.Add(stmtWrapper);
GetQueryRunner().ExecuteTransaction(GetConnection(), statements);
}
catch (Exception e)
{
throw new Exception("Unable to delete " + entity.GetTableName() + ".", e);
}
finally
{
LoggerHelper.Info("End");
}
}
public IActionResult Post([FromBody] JArray requestJArray)
{
var requestContent = requestJArray.ToString();
_logger.LogInformation($"Received events: {requestContent}");
EventGridSubscriber eventGridSubscriber = new EventGridSubscriber();
EventGridEvent[] eventGridEvents = eventGridSubscriber.DeserializeEventGridEvents(requestContent);
foreach (EventGridEvent eventGridEvent in eventGridEvents)
{
if (eventGridEvent.Data is SubscriptionValidationEventData eventData)
{
_logger.LogInformation($"Got SubscriptionValidation event data, validationCode: {eventData.ValidationCode}, validationUrl: {eventData.ValidationUrl}, topic: {eventGridEvent.Topic}");
// Do any additional validation (as required) such as validating that the Azure resource ID of the topic matches
// the expected topic and then return back the below response
var responseData = new SubscriptionValidationResponse()
{
ValidationResponse = eventData.ValidationCode
};
return(Ok(responseData));
}
else
{
var statement = new StatementExtension((JObject)eventGridEvent.Data);
var statementWrapper = new StatementWrapper(eventGridEvent.Subject, statement);
_decisionChannel.Next(statementWrapper);
}
}
return(Ok(null));
}
/// <summary>
/// Returns the result of the specified aggregated function(s) executed in a SQL Database.
/// </summary>
/// <param name="entity">The entity type</param>
/// <param name="aggregateInfo">The aggregateInfo data</param>
/// <param name="searchType">The search type</param>
/// <param name="filter">The filter info</param>
/// <returns>The agregated list of entities</returns>
public IList <Entity> GetAggregateEntities(Entity entity, AggregateInfo aggregateInfo, FilterInfo.SearchType searchType, FilterInfo filter)
{
LoggerHelper.Info("Start");
IList <Entity> list = new List <Entity>();
try
{
Entity aggregateEntity = new Entity();
StatementWrapper stmtWrapper = GetQueryBuilder().BuildAggregateStatement(entity, aggregateInfo, aggregateEntity, searchType, filter);
LoggerHelper.Debug(stmtWrapper.Query.ToString());
ResultSetHandler <IList <Entity> > h = new EntityHandler <Entity>(aggregateEntity);
list = GetQueryRunner().Query(GetConnection(), stmtWrapper, h);
}
catch (Exception e)
{
LoggerHelper.Error(e);
throw new Exception("Unable to fetch " + entity.GetTableName() + " list.", e);
}
finally
{
LoggerHelper.Info("End");
}
return(list);
}
/// <summary>
/// Returns an INSERT statement of the specified page.
/// <para>If the request user is not part of the App support team an exeception is returned.</para>
/// <para>The returned statement type depends on the specified page.connName provider</para>
/// </summary>
/// <param name="request">the request</param>
/// <returns>the statement</returns>
public virtual string GetInsertStatement(HttpRequest request)
{
LoggerHelper.Info("Start");
try
{
if (!CheckPermissions())
{
return(ErrorResponse("User don't have permissions to use this functionality."));
}
Page page = GetPage(request);
Entity entity = CreateEntity(request, page);
IQueryBuilder queryBuilder = GetQueryBuilder(page);
StatementWrapper stmt = queryBuilder.BuildInsertStatement(entity);
return(CreateStatementResponse(stmt));
}
catch (Exception e)
{
LoggerHelper.Error(e);
return(ErrorResponse(e));
}
finally
{
LoggerHelper.Info("End");
}
}
/// <summary>
/// Return the filtered list of entities related to the specified entity type and applying the
/// specified filter from an SQL database.
/// </summary>
/// <param name="entity">The entity type</param>
/// <param name="filter">The Filter</param>
/// <returns>The filtered list of entities</returns>
public virtual IList <Entity> GetEntities(Entity entity, FilterInfo filter)
{
LoggerHelper.Info("Start");
IList <Entity> list = new List <Entity>();
try
{
filter.Total = GetTotalRecords(entity);
filter.FilteredRecords = GetFilteredTotalRecords(entity, filter);
StatementWrapper stmtWrapper = GetQueryBuilder().BuildSelectStatement(entity, filter);
LoggerHelper.Debug(stmtWrapper.Query.ToString());
ResultSetHandler <IList <Entity> > h = new EntityHandler <Entity>(entity);
list = GetQueryRunner().Query(GetConnection(), stmtWrapper, h);
}
catch (Exception e)
{
LoggerHelper.Error(e);
throw new Exception("Unable to fetch " + entity.GetTableName() + " list.", e);
}
finally
{
LoggerHelper.Info("End");
}
return(list);
}
/// <summary>
/// Returns the Page Configuration from the data base for the specified application name and pageId or pageName
/// </summary>
/// <param name="appName">The application name</param>
/// <param name="pageId">The id of the page</param>
/// <param name="pageName">The name of the page</param>
/// <returns>The Page</returns>
public virtual Page GetPageConfig(string appName, string pageId, string pageName)
{
if (string.IsNullOrEmpty(appName))
{
LoggerHelper.Warning("Page Application Name is not specified.");
throw new ArgumentNullException("Page Application Name is not specified.");
}
LoggerHelper.Info("Start");
Page _page = null;
try
{
StringBuilder query = new StringBuilder();
IList <DBParam> queryParams = new List <DBParam>();
query.Append("SELECT A.PageAppId, A.AppName, P.PageId, P.Name, P.Title, P.TableName, P.ConnName, T.TabId, T.TabName, T.TabOrder, T.Cols, F.FieldId, F.FieldName, ");
query.Append("F.Label, F.Type, F.Required, F.DropDownInfo, F.Exportable, F.IsId, F.FieldOrder, F.ControlType, F.JoinInfo, ");
query.Append("F.DBFieldName, F.Insertable, F.Updatable, F.ControlProps, ");
query.Append("G.ColumnId, G.Visible, G.Searchable, G.Width, G.ColumnName, G.ColumnLabel, G.ColumnOrder ");
query.Append("FROM Page P ");
query.Append("INNER JOIN PageApp A ON A.PageAppId = P.PageAppId ");
query.Append("LEFT OUTER JOIN PageTab T ON P.PageId = T.PageId ");
query.Append("LEFT OUTER JOIN PageField F ON T.TabId = F.TabId ");
query.Append("LEFT OUTER JOIN PageGridColumn G ON P.PageId = G.PageId AND F.FieldId = G.FieldId ");
query.Append("WHERE A.AppName = @p0 ");
queryParams.Add(new DBParam(queryParams, appName, DbType.String));
if (!String.IsNullOrEmpty(pageId))
{
query.Append("AND P.PageId = @p1 ");
queryParams.Add(new DBParam(queryParams, pageId, DbType.Int32));
}
else
{
query.Append("AND P.Name = @p1 ");
queryParams.Add(new DBParam(queryParams, pageName, DbType.String));
}
query.Append("ORDER BY T.TabOrder, F.FieldOrder, F.Label");
LoggerHelper.Debug(query.ToString());
StatementWrapper wrapper = new StatementWrapper(query, queryParams);
ResultSetHandler <Page> h = new PageInfoHandler <Page>(typeof(Page));
_page = GetQueryRunner().Query(GetConnection(), wrapper, h);
_page.Filter = GetPageFilter(_page.PageId);
}
catch (Exception e)
{
LoggerHelper.Error(e);
throw new Exception("Unable to get page config.", e);
}
finally
{
LoggerHelper.Info("End");
}
return(_page);
}
private void InitialiseStatement(Statement statement)
{
Statement = new StatementWrapper(statement);
Statement.PropertyChanged += (s, e) =>
{
if (!HasChanges)
{
HasChanges = _statementRepository.HasChanges();
}
if (e.PropertyName == nameof(Statement.HasErrors))
{
((DelegateCommand)SaveCommand).RaiseCanExecuteChanged();
}
};
((DelegateCommand)SaveCommand).RaiseCanExecuteChanged();
// Trick to get validations to work immediately - Statement only has no id if it hasn't been added to db yet
if (statement.Id == 0)
{
Statement.Bank = "";
Statement.NewBalance = 0.0;
Statement.PreviousBalance = 0.0;
Statement.StatementDate = DateTime.Now;
}
}
private string CreateStatementResponse(StatementWrapper stmt)
{
Dictionary <string, string> response = new Dictionary <string, string>();
response.Add("Statement", stmt.Query.ToString());
JavaScriptSerializer ser = new JavaScriptSerializer();
return(ser.Serialize(response));
}
private async Task HandleStatement(StatementWrapper statementWrapper)
{
try
{
var decisionStatement = await new PersonApplicationService().MakeDecisionAsync(statementWrapper.Data, _personRepository);
var decisionStatementWrapper = new StatementWrapper(statementWrapper.Subject, decisionStatement);
_decisionChannel.Next(decisionStatementWrapper);
}
catch (Exception exception)
{
_logger.LogError("Exception handling statement for person aggregate: " + exception.Message);
_logger.LogError(exception.StackTrace);
}
}
public FormStatementSelection(IObjectLabel label)
{
InitializeComponent();
ResourceService.Resources.LoadControlResources(this, Database.UI.Utils.ControlUtilites.Resources);
this.label = label;
provider = label.Object as StatementWrapper;
string[] d = DataSetFactoryChooser.Chooser.Names;
foreach (string name in d)
{
comboBoxDatabaseDriver.Items.Add(name);
}
UpdateFormUI();
setProvider();
}
/// <summary>
/// Returns the list of columns of the specified table.
/// </summary>
/// <param name="tableName">The table name</param>
/// <returns>The list of columns</returns>
public virtual IList <Entity> GetTableColumns(string tableName)
{
LoggerHelper.Info("Start");
IList <Entity> list = null;
try
{
string[] tableInfo = tableName.Split('.');
LoggerHelper.Debug("tableInfo,length = " + tableInfo.Length.ToString());
StringBuilder query = new StringBuilder();
IList <DBParam> queryParams = new List <DBParam>();
query.Append("SELECT COLUMN_NAME AS \"Name\", CASE WHEN NULLABLE = 'Y' THEN 'YES' ELSE 'NO' END AS \"Required\", \n");
query.Append("DATA_TYPE AS \"Type\", DATA_LENGTH AS \"MaxLength\", COLUMN_ID AS \"Order\" \n");
query.Append("FROM ALL_TAB_COLUMNS \n");
query.Append("WHERE OWNER = :p0 AND TABLE_NAME = :p1 \n");
query.Append("ORDER BY TABLE_NAME,COLUMN_ID ");
queryParams.Add(new DBParam(":p0", tableInfo[0], DbType.String));
queryParams.Add(new DBParam(":p1", tableInfo[1], DbType.String));
LoggerHelper.Debug(query.ToString());
//TODO : Create an entity object
Entity entity = new Entity("Columns");
entity.SetField(new Field("Name"));
entity.SetField(new Field("Required"));
entity.SetField(new Field("Type"));
entity.SetField(new Field("MaxLength"));
entity.SetField(new Field("Order"));
StatementWrapper wrapper = new StatementWrapper(query, queryParams);
ResultSetHandler <IList <Entity> > h = new EntityHandler <Entity>(entity);
list = GetQueryRunner().Query(GetConnection(), wrapper, h);
}
catch (Exception e)
{
LoggerHelper.Error(e);
throw new Exception("Unable to get table columns.", e);
}
finally
{
LoggerHelper.Info("End");
}
return(list);
}
/// <summary>
/// Returns a list of items related to the specified fieldname from a database.
/// </summary>
/// <param name="fieldName">The field name</param>
/// <param name="orderBy">The order in which the list will be returned</param>
/// <param name="orderType">The order type in which the list will be sorted</param>
/// <returns>The list of items in the specified order</returns>
public virtual IList <PageListItem> GetPageListItems(string fieldName, string orderBy, string orderType)
{
LoggerHelper.Info("Start");
IList <PageListItem> list = null;
try
{
StringBuilder query = new StringBuilder();
IList <DBParam> queryParams = new List <DBParam>();
query.Append("SELECT ItemId,FieldName,Value,Text,ShortText,Enable,Selected FROM PageListItem ");
query.Append("WHERE FieldName = @p0 AND Enable = 1 ");
if (String.IsNullOrEmpty(orderBy))
{
orderBy = "Text";
}
if (String.IsNullOrEmpty(orderType))
{
orderType = "ASC";
}
query.Append("ORDER BY ").Append(orderBy).Append(" ").Append(orderType);
LoggerHelper.Debug(query.ToString());
queryParams.Add(new DBParam(queryParams, fieldName, DbType.String));
StatementWrapper wrapper = new StatementWrapper(query, queryParams);
ResultSetHandler <IList <PageListItem> > h = new BeanListHandler <PageListItem>(typeof(PageListItem));
list = GetQueryRunner().Query(GetConnection(), wrapper, h);
}
catch (Exception e)
{
LoggerHelper.Error(e);
throw new Exception("Unable to fetch " + fieldName + " list Items.", e);
}
finally
{
LoggerHelper.Info("End");
}
return(list);
}
private List <EventGridEvent> CreateEventGridEventList(StatementWrapper statementWrapper)
{
if (statementWrapper == null)
{
return(new List <EventGridEvent>());
}
return(new List <EventGridEvent>
{
new EventGridEvent()
{
Data = statementWrapper.Data.ToJObject(),
DataVersion = statementWrapper.DataVersion,
EventTime = statementWrapper.EventTime,
EventType = statementWrapper.EventType,
Id = statementWrapper.Id,
Subject = statementWrapper.Subject,
}
});
}
public FormStatementWrapper(IObjectLabel label, bool show,
Action showNumber, Action <bool> showTable)
: this()
{
ResourceService.Resources.LoadControlResources(this, Database.UI.Utils.ControlUtilites.Resources);
this.label = label;
provider = label.Object as StatementWrapper;
this.showNumberDelegate = showNumber;
this.showTableDelegate = showTable;
this.show = show;
if (provider.GetType().Equals(typeof(StatementWrapper)))
{
udd = new UserControlControlDatabaseDriver();
udd.Dock = DockStyle.Fill;
ResourceService.Resources.LoadControlResources(udd, Database.UI.Utils.ControlUtilites.Resources);
udd.Wrapper = provider;
panelDriver.Controls.Add(udd);
}
UpdateFormUI();
setProvider();
}
/// <summary>
/// Returns a SELECT statement of the specified page.
/// <para>If the request user is not part of the App support team an exeception is returned.</para>
/// <para>The returned statement type depends on the specified page.connName provider</para>
/// </summary>
/// <param name="request">the request</param>
/// <returns>the statement</returns>
public virtual string GetSelectStatement(HttpRequest request)
{
LoggerHelper.Info("Start");
try
{
if (!CheckPermissions())
{
return(ErrorResponse("User don't have permissions to use this functionality."));
}
Page page = GetPage(request);
Entity entity = CreateEntity(request, page);
FilterInfo filterInfo = CreateFilter(request);
IQueryBuilder queryBuilder = GetQueryBuilder(page);
StatementWrapper stmt = null;
if (filterInfo == null)
{
stmt = queryBuilder.BuildFindEntitiesStatement(entity, filterInfo, GetSearchType(request));
}
else
{
stmt = queryBuilder.BuildSelectStatement(entity, filterInfo);
}
return(CreateStatementResponse(stmt));
}
catch (Exception e)
{
LoggerHelper.Error(e);
return(ErrorResponse(e));
}
finally
{
LoggerHelper.Info("End");
}
}
/// <summary>
/// Returns the page filter for the specified page
/// </summary>
/// <param name="pageId">the page id</param>
/// <returns>the page filter</returns>
public virtual PageFilter GetPageFilter(string pageId)
{
LoggerHelper.Info("Start");
PageFilter filter = null;
try
{
StringBuilder query = new StringBuilder();
IList <DBParam> queryParams = new List <DBParam>();
query.Append("SELECT PF.FilterId, PF.FilterText, PF.FilterCols, PF.ShowClear, PF.FilterProps, ");
query.Append("FF.FilterFieldId, FF.FieldId, FF.FilterOrder ");
query.Append("FROM PageFilter PF ");
query.Append("INNER JOIN Page P ON PF.PageId = P.PageId ");
query.Append("INNER JOIN PageFilterField FF ON PF.FilterId = FF.FilterId ");
query.Append("WHERE P.PageId = @p0 ");
query.Append("ORDER BY PF.FilterId, FF.FilterOrder");
LoggerHelper.Debug(query.ToString());
queryParams.Add(new DBParam(queryParams, pageId, DbType.Int32));
StatementWrapper wrapper = new StatementWrapper(query, queryParams);
ResultSetHandler <PageFilter> h = new PageFilterHandler <PageFilter>(typeof(PageFilter));
filter = GetQueryRunner().Query(GetConnection(), wrapper, h);
}
catch (Exception e)
{
LoggerHelper.Error("Unable to get page filter.", e);
//throw new Exception("Unable to get page filter.", e);
}
finally
{
LoggerHelper.Info("End");
}
return(filter);
}
/// <summary>
/// Deletes the page configuration from the database
/// </summary>
/// <param name="page">The page to be deleted</param>
public virtual void DeletePage(Page page)
{
LoggerHelper.Info("Start");
try
{
StringBuilder query = new StringBuilder();
IList <DBParam> queryParams = new List <DBParam>();
query.Append("DELETE FROM [PageGridColumn] WHERE [PageId] = @p0");
query.Append("\n DELETE FROM [PageFilterField] WHERE [FilterId] IN (SELECT [FilterId] FROM PageFilter WHERE [PageId] = @p1)");
query.Append("\n DELETE FROM [PageField] WHERE [TabId] IN (SELECT [TabId] FROM [PageTab] WHERE [PageId] = @p2 )");
query.Append("\n DELETE FROM [PageTab] WHERE [PageId] = @p3");
query.Append("\n DELETE FROM [PageFilter] WHERE [PageId] = @p4");
query.Append("\n DELETE FROM [Page] WHERE [PageId] = @p5");
queryParams.Add(new DBParam(queryParams, page.PageId, DbType.Int32));
queryParams.Add(new DBParam(queryParams, page.PageId, DbType.Int32));
queryParams.Add(new DBParam(queryParams, page.PageId, DbType.Int32));
queryParams.Add(new DBParam(queryParams, page.PageId, DbType.Int32));
queryParams.Add(new DBParam(queryParams, page.PageId, DbType.Int32));
queryParams.Add(new DBParam(queryParams, page.PageId, DbType.Int32));
LoggerHelper.Debug(query.ToString());
StatementWrapper wrapper = new StatementWrapper(query, queryParams);
GetQueryRunner().ExecuteNonQuery(GetConnection(), wrapper);
}
catch (Exception e)
{
LoggerHelper.Error(e);
throw new Exception("Unable to delete page.", e);
}
finally
{
LoggerHelper.Info("End");
}
}
public IObservable <T> Use <T>(Func <IStatement, CancellationToken, IEnumerable <T> > f)
{
Contract.Requires(f != null);
if (disposed)
{
throw new ObjectDisposedException(this.GetType().FullName);
}
return(Observable.Create((IObserver <T> observer, CancellationToken cancellationToken) =>
{
// Prevent calls to subscribe after the statement is disposed
if (this.disposed)
{
observer.OnError(new ObjectDisposedException(this.GetType().FullName));
return Task.FromResult(Unit.Default);
}
return conn.Use((_, ct) =>
{
ct.ThrowIfCancellationRequested();
// Note: Diposing the statement wrapper doesn't dispose the underlying statement
// The intent here is to prevent access to the underlying statement outside of the
// function call.
using (var stmt = new StatementWrapper(this.stmt))
{
foreach (var e in f(stmt, ct))
{
observer.OnNext(e);
cancellationToken.ThrowIfCancellationRequested();
}
}
}, cancellationToken);
}));
}
/// <summary>
/// Updates all entities that math the specified whereEntity properties in a SQL database.
/// </summary>
/// <param name="entity">The entity that contains the properties that will be updated.</param>
/// <param name="whereEntity">The entity that contains the properties used in the WHERE clause.</param>
public virtual void UpdateEntity(Entity entity, Entity whereEntity)
{
LoggerHelper.Info("Start");
try
{
if (whereEntity == null)
{
UpdateEntity(entity);
return;
}
StatementWrapper stmtWrapper = ((QueryBuilder)GetQueryBuilder()).BuildUpdateEntityStatement(entity, whereEntity);
LoggerHelper.Debug(stmtWrapper.Query.ToString());
GetQueryRunner().ExecuteNonQuery(GetConnection(), stmtWrapper);
}
catch (Exception e)
{
throw new Exception("Unable to update " + entity.GetTableName() + ".", e);
}
finally
{
LoggerHelper.Info("End");
}
}
/// <summary>
/// Saves the specified entity in a SQL database.
/// </summary>
/// <param name="entity">The entity to be saved</param>
public virtual void SaveEntity(Entity entity)
{
LoggerHelper.Info("Start");
try
{
if (string.IsNullOrEmpty(entity.GetEntityId())) // New entity Insert
{
StatementWrapper stmtWrapper = GetQueryBuilder().BuildInsertStatement(entity);
LoggerHelper.Debug(stmtWrapper.Query.ToString());
Int32 newId = GetQueryRunner().ExecuteScalar(GetConnection(), stmtWrapper);
entity.SetProperty(entity.GetFieldId().Name, newId.ToString());
}
else //update
{
StatementWrapper stmtWrapper = GetQueryBuilder().BuildUpdateStatement(entity);
LoggerHelper.Debug(stmtWrapper.Query.ToString());
GetQueryRunner().ExecuteNonQuery(GetConnection(), stmtWrapper);
}
}
catch (Exception e)
{
LoggerHelper.Error(e);
if (e.Message.Contains("UNIQUE KEY"))
{
throw new Exception(entity.GetTableName() + " already exists.");
}
else
{
throw new Exception("Unable to save " + entity.GetTableName() + ".", e);
}
}
finally
{
LoggerHelper.Info("End");
}
}
/// <summary>
/// Executes a transaction in a SQL database.
/// </summary>
/// <param name="operations">The list of operations to be executed.</param>
public virtual void ExecuteTransaction(List <TransOperation> operations)
{
LoggerHelper.Info("Start");
try
{
StringBuilder query = new StringBuilder();
IList <DBParam> queryParams = new List <DBParam>();
StringBuilder variables = new StringBuilder();
StringBuilder statementsQueries = new StringBuilder();
IDictionary <string, string> defaultVals = new Dictionary <string, string>();
IQueryBuilder queryBuilder = GetQueryBuilder();
foreach (TransOperation operation in operations)
{
Entity entity = operation.Entity;
if (operation.OperationType == TransOperation.OperType.Save)
{
if (string.IsNullOrEmpty(operation.Entity.GetEntityId())) // New entity Insert
{
if (operation.BindIds)
{
queryBuilder.BuildInsertStatement(entity, statementsQueries, queryParams, defaultVals);
}
else
{
queryBuilder.BuildInsertStatement(entity, statementsQueries, queryParams);
}
Field id = entity.GetFieldId();
string varName = GetVariableName(id);
if (!defaultVals.ContainsKey(id.Name))
{
defaultVals[id.Name] = varName;
variables.Append("DECLARE ").Append(varName).Append(" AS INT\n");
}
statementsQueries.Append("\nSET ").Append(varName).Append(" = scope_identity()\n");
}
else //update
{
queryBuilder.BuildUpdateStatement(entity, statementsQueries, queryParams);
}
}
else if (operation.OperationType == TransOperation.OperType.Update)
{
queryBuilder.BuildUpdateEntityStatement(entity, statementsQueries, queryParams);
}
else if (operation.OperationType == TransOperation.OperType.Delete)
{
queryBuilder.BuildDeleteStatement(entity, statementsQueries, queryParams);
}
else if (operation.OperationType == TransOperation.OperType.DeleteEntities)
{
queryBuilder.BuildDeleteEntitiesStatement(entity, statementsQueries, queryParams);
}
statementsQueries.Append("\n");
}
query.Append(variables).Append("\n");
query.Append(statementsQueries);
LoggerHelper.Debug(query.ToString());
StatementWrapper stmtWrapper = new StatementWrapper(query, queryParams);
IList <StatementWrapper> statements = new List <StatementWrapper>();
statements.Add(stmtWrapper);
GetQueryRunner().ExecuteTransaction(GetConnection(), statements);
}
catch (Exception e)
{
LoggerHelper.Error(e.Message);
if (e.Message.Contains("FK") || e.Message.Contains("REFERENCE"))
{
throw new Exception("Entity cannot be deleted, because is being used.");
}
else if (e.Message.Contains("UNIQUE KEY"))
{
throw new Exception("Entity already exists.");
}
else
{
throw new Exception("Unable to execute transactions.", e);
}
}
finally
{
LoggerHelper.Info("End");
}
}
public void Next(StatementWrapper statementWrapper)
{
_subject.OnNext(statementWrapper);
}
public void Perception(JObject payload)
{
var statementWrapper = new StatementWrapper(this.Context.ConnectionId, new Shared.Xapi.StatementExtension(payload));
_perceptionChannel.Next(statementWrapper);
}
