public Guid?GetEntityID(sysBpmsEntityDef entityDef, sysBpmsVariable variable, List <QueryModel> additionalParams)
{
string whereClause = VariableEngine.GenerateWhereClause(variable);
string orderClause = VariableEngine.GenerateOrderClause(variable);
//get sql query parameter from additionalParams
List <SqlParameter> queryParams = QueryModel.GetSqlParameter(base.GetAllQueryModels(additionalParams) ?? new List <QueryModel>()).ToList();
//add variable dependies to whereclause which have relation with current variable.
this.AddDependencyClause(variable, queryParams, null, ref whereClause, additionalParams);
string sqlQuery = $" select {entityDef.FormattedTableName}.ID from {entityDef.FormattedTableName} {whereClause} {orderClause} ";
this.AddDefaultParams(sqlQuery, queryParams);
this.AddVariableParameters(ref sqlQuery, queryParams);
queryParams = queryParams.Where(c => sqlQuery.Contains(c.ParameterName)).ToList();
DataTable dataTable = new DataBaseQueryService(this.UnitOfWork).GetBySqlQuery(sqlQuery, true, null, queryParams.ToArray());
if (dataTable.Rows.Count > 0)
{
return(Guid.Parse(dataTable.Rows[0][0].ToString()));
}
return(null);
}
/// <summary>
/// this method save entityDef after form post action.
/// </summary>
public Tuple <ResultOperation, Guid?> SaveIntoDataBase(sysBpmsEntityDef entityDef, sysBpmsVariable variable, DataModel dataModel, List <QueryModel> additionalParams, Dictionary <string, DataModel> allSavedEntities)
{
ResultOperation resultOperation = new ResultOperation();
List <SqlParameter> columnParams = this.GetColumsAsParams(entityDef, dataModel.ToList(), variable);
string whereClause = VariableEngine.GenerateWhereClause(variable);
string orderClause = VariableEngine.GenerateOrderClause(variable);
//get sql query parameter from additionalParams
List <SqlParameter> queryParams = QueryModel.GetSqlParameter(base.GetAllQueryModels(additionalParams) ?? new List <QueryModel>()).ToList();
//variable.VariableDependencies is null retrieve it from database
variable.DependentVariableDependencies = variable.DependentVariableDependencies ?? new VariableDependencyService(base.UnitOfWork).GetList(variable.ID, null);
//add variable dependies to whereclause which have relation with current variable.
this.AddDependencyClause(variable, queryParams, columnParams, ref whereClause, additionalParams, allSavedEntities);
string sqlQueryIsExist = $@"(select top(1) {entityDef.FormattedTableName}.ID from {entityDef.FormattedTableName} {whereClause} {orderClause})";
string sqlInsertQuery = $@"INSERT INTO {entityDef.FormattedTableName}
({(base.EngineSharedModel.CurrentThreadID.HasValue ? "ThreadID," : "")}{string.Join(",", columnParams.Select(c => c.ParameterName.TrimStart('@')))})
OUTPUT inserted.ID
VALUES
({(base.EngineSharedModel.CurrentThreadID.HasValue ? ("'" + base.EngineSharedModel.CurrentThreadID.Value + "',") : "")}{string.Join(",", columnParams.Select(c => c.ParameterName))})";
this.AddDefaultParams((sqlInsertQuery + sqlQueryIsExist), queryParams);
this.AddVariableParameters(ref sqlInsertQuery, queryParams);
this.AddVariableParameters(ref sqlQueryIsExist, queryParams);
queryParams = columnParams.Union(queryParams).Where(c => (sqlInsertQuery + sqlQueryIsExist).Contains(c.ParameterName)).GroupBy(c => c.ParameterName).Select(c => c.FirstOrDefault()).ToList();
DataTable dataTableIsExist = new DataBaseQueryService(base.UnitOfWork).GetBySqlQuery(sqlQueryIsExist, true, null, queryParams.ToArray());
Guid? entityIsExistId = dataTableIsExist != null && dataTableIsExist.Rows.Count > 0 ? dataTableIsExist.Rows[0][0].ToGuidObj() : (Guid?)null;
if (entityIsExistId.HasValue)
{
string sqlUpdateQuery = $@"update {entityDef.FormattedTableName} set {string.Join(",", columnParams.Select(c => c.ParameterName.TrimStart('@') + "=" + c.ParameterName))} where ID='{entityIsExistId.ToStringObj()}'";
//update entity
new DataBaseQueryService(base.UnitOfWork).ExecuteBySqlQuery(sqlUpdateQuery, true, queryParams.ToArray());
}
else
{
//insert entity
this.CheckMandatory(resultOperation, entityDef, columnParams);
if (resultOperation.IsSuccess)
{
entityIsExistId = new DataBaseQueryService(base.UnitOfWork).ExecuteScalar <Guid>(sqlInsertQuery, true, queryParams.ToArray()).ToGuidObjNull();
}
}
return(new Tuple <ResultOperation, Guid?>(resultOperation, entityIsExistId));
}
/// <summary>
/// this method save entityDef using dynamic code.
/// </summary>
public Tuple <ResultOperation, Guid?> SaveIntoDataBase(VariableModel variableModel)
{
ResultOperation resultOperation = new ResultOperation();
sysBpmsEntityDef entityDef = new EntityDefService(base.UnitOfWork).GetInfo(variableModel.Name);
List <SqlParameter> columnParams = this.GetColumsAsParams(entityDef, variableModel.Items.FirstOrDefault().ToList(), (base.EngineSharedModel != null ? new VariableService(base.UnitOfWork).GetInfo(base.EngineSharedModel.CurrentProcessID, base.EngineSharedModel.CurrentApplicationPageID, variableModel.Name) : null));
//get sql query parameter from additionalParams
List <SqlParameter> queryParams = QueryModel.GetSqlParameter(base.GetAllQueryModels(null) ?? new List <QueryModel>()).ToList();
string sqlQueryIsExist = $@"(select top(1) {entityDef.FormattedTableName}.ID from {entityDef.FormattedTableName} where ID='{variableModel["ID"]}' )";
string sqlInsertQuery = $@"INSERT INTO {entityDef.FormattedTableName}
({string.Join(",", columnParams.Select(c => c.ParameterName.TrimStart('@')))})
OUTPUT inserted.ID
VALUES
({string.Join(",", columnParams.Select(c => c.ParameterName))})";
this.AddDefaultParams((sqlInsertQuery + sqlQueryIsExist), queryParams);
this.AddVariableParameters(ref sqlInsertQuery, queryParams);
this.AddVariableParameters(ref sqlQueryIsExist, queryParams);
queryParams = columnParams.Union(queryParams).Where(c => (sqlInsertQuery + sqlQueryIsExist).Contains(c.ParameterName)).GroupBy(c => c.ParameterName).Select(c => c.FirstOrDefault()).ToList();
DataTable dataTableIsExist = new DataBaseQueryService(base.UnitOfWork).GetBySqlQuery(sqlQueryIsExist, true, null, queryParams.ToArray());
Guid? entityIsExistId = dataTableIsExist != null && dataTableIsExist.Rows.Count > 0 ? dataTableIsExist.Rows[0][0].ToGuidObj() : (Guid?)null;
if (entityIsExistId.HasValue)
{
string sqlUpdateQuery = $@"update {entityDef.FormattedTableName} set {string.Join(",", columnParams.Select(c => c.ParameterName.TrimStart('@') + "=" + c.ParameterName))} where ID='{entityIsExistId.ToStringObj()}' ";
//update entity
new DataBaseQueryService(base.UnitOfWork).ExecuteBySqlQuery(sqlUpdateQuery, true, queryParams.ToArray());
}
else
{
//insert entity
this.CheckMandatory(resultOperation, entityDef, columnParams);
if (resultOperation.IsSuccess)
{
entityIsExistId = new DataBaseQueryService(base.UnitOfWork).ExecuteScalar <Guid>(sqlInsertQuery, true, queryParams.ToArray()).ToGuidObjNull();
}
}
return(new Tuple <ResultOperation, Guid?>(resultOperation, entityIsExistId));
}
/// <summary>
/// publish a specific process by generating table with relations and properties.
/// </summary>
public ResultOperation DropTable(sysBpmsEntityDef entityDef)
{
DataBaseQueryService dataBaseQueryService = new DataBaseQueryService(base.UnitOfWork);
ResultOperation resultOperation = new ResultOperation();
List <string> executeAlterQueries = new List <string>();
foreach (EntityPropertyModel rModel in entityDef.Properties.Where(c => c.DbType == EntityPropertyModel.e_dbType.Entity))
{
executeAlterQueries.Add($@"ALTER TABLE {entityDef.FormattedTableName} DROP CONSTRAINT {rModel.RelationConstaintName}");
}
foreach (string query in executeAlterQueries)
{
dataBaseQueryService.ExecuteBySqlQuery(query, false, null);
}
string sqlQuery = $@"Drop TABLE [{entityDef.FormattedTableName}] ";
dataBaseQueryService.ExecuteBySqlQuery(sqlQuery, false, null);
return(resultOperation);
}
//It used used for built in upgrade system for now it is not in work.
public static void UpdatedSqlQuery(int portalId)
{
if (!IsUpdatedSqlQueryVesrion(portalId))
{
using (DataBaseQueryService dataBaseQueryService = new DataBaseQueryService())
{
List <string> queries = new List <string>();
string XslFile = System.Web.Hosting.HostingEnvironment.MapPath(BPMSResources.SqlDataProvider);
foreach (string fileName in Directory.GetFiles(XslFile, "*.SqlDataProvider").Select(Path.GetFileNameWithoutExtension))
{
if (fileName != "Uninstall.SqlDataProvider" && fileName.CompareTo(LastSqlVersionExecuted) == 1)
{
queries.AddRange(File.ReadAllText(XslFile + fileName + ".SqlDataProvider").Split(new string[1] {
"GO"
}, StringSplitOptions.None).Where(c => !string.IsNullOrWhiteSpace(c)).ToList());
}
}
dataBaseQueryService.UpdatedSqlQuery(queries);
using (ConfigurationService configurationService = new ConfigurationService())
{
sysBpmsConfiguration settingValue = configurationService.GetList("", sysBpmsConfiguration.e_NameType.LastSqlUpdatedVersion.ToString()).LastOrDefault();
if (settingValue == null)
{
settingValue = new sysBpmsConfiguration();
settingValue.LastUpdateOn = DateTime.Now;
settingValue.Value = CurrentBpmsVersion;
configurationService.Add(settingValue);
}
else
{
settingValue.LastUpdateOn = DateTime.Now;
settingValue.Value = CurrentBpmsVersion;
configurationService.Update(settingValue);
}
LastSqlVersionExecuted = CurrentBpmsVersion;
UrlUtility.NoSkinPath = configurationService.GetValue(sysBpmsConfiguration.e_NameType.NoSkinPath.ToString());
}
}
}
}
public ResultOperation CreateTable(sysBpmsEntityDef entityDef)
{
DataBaseQueryService dataBaseQueryService = new DataBaseQueryService(base.UnitOfWork);
ResultOperation resultOperation = new ResultOperation();
List <string> executeAlterQueries = new List <string>();
if (entityDef.IsActive && entityDef.Properties != null && entityDef.Properties.Any())
{
string paramsQuery = string.Empty;
foreach (EntityPropertyModel property in entityDef.Properties)
{
paramsQuery += $"[{property.Name}] {property.SqlTypeName} {(property.Required ? "NOT NULL" : "NULL")} ,";
}
//generate table create query.
string sqlQuery =
$@"CREATE TABLE [{entityDef.FormattedTableName}](
[ID][uniqueidentifier] NOT NULL DEFAULT newid() PRIMARY KEY,
[ThreadID][uniqueidentifier] NULL,
{paramsQuery.TrimEnd(',')}) ";
foreach (EntityPropertyModel property in entityDef.Properties.Where(c => c.DbType == EntityPropertyModel.e_dbType.Entity))
{
this.CreateConstraintQuery(property, entityDef, executeAlterQueries);
}
dataBaseQueryService.ExecuteBySqlQuery(sqlQuery, false, null);
}
foreach (EntityPropertyModel property in entityDef.Properties.Where(c => !string.IsNullOrWhiteSpace(c.DefaultValue)))
{
executeAlterQueries.Add($@" ALTER TABLE {entityDef.FormattedTableName} ADD CONSTRAINT def_{entityDef.FormattedTableName}_{property.Name} {property.SqlDefaultValue} FOR {property.Name} ;");
}
foreach (string query in executeAlterQueries)
{
dataBaseQueryService.ExecuteBySqlQuery(query, false, null);
}
return(resultOperation);
}
public ResultOperation UpdateTable(sysBpmsEntityDef newEntityDef)
{
DataBaseQueryService dataBaseQueryService = new DataBaseQueryService(base.UnitOfWork);
sysBpmsEntityDef currentEntityDef = this.GetInfo(newEntityDef.ID);
ResultOperation resultOperation = new ResultOperation();
List <string> addQuery = new List <string>();
if (newEntityDef.IsActive && newEntityDef.Properties != null && newEntityDef.Properties.Any())
{
foreach (EntityPropertyModel newProperty in newEntityDef.Properties)
{
//change Property
if (currentEntityDef.Properties.Any(c => c.ID == newProperty.ID))
{
EntityPropertyModel currentProperty = currentEntityDef.Properties.FirstOrDefault(c => c.ID == newProperty.ID);
//change property name.
if (currentProperty.Name != newProperty.Name)
{
addQuery.Add($" EXEC sp_rename '{currentEntityDef.FormattedTableName}.{currentProperty.Name}', '{newProperty.Name}', 'COLUMN'; {Environment.NewLine} ");
}
if (currentProperty.DbType != newProperty.DbType ||
currentProperty.SqlTypeName != newProperty.SqlTypeName ||
currentProperty.Required != newProperty.Required)
{
addQuery.Add($@" ALTER TABLE {currentEntityDef.FormattedTableName} ALTER COLUMN {newProperty.Name} {newProperty.SqlTypeName} {newProperty.SqlDefaultValue} {(newProperty.Required ? "NOT NULL" : "NULL")} ; ");
}
if (currentProperty.DefaultValue != newProperty.DefaultValue)
{
if (!string.IsNullOrWhiteSpace(currentProperty.DefaultValue))
{
addQuery.Add($@" ALTER TABLE {currentEntityDef.FormattedTableName} DROP CONSTRAINT def_{currentEntityDef.FormattedTableName}_{currentProperty.Name} ; ");
}
addQuery.Add($@" ALTER TABLE {currentEntityDef.FormattedTableName} ADD CONSTRAINT def_{currentEntityDef.FormattedTableName}_{newProperty.Name} {newProperty.SqlDefaultValue} FOR {newProperty.Name} ;");
}
//if property is no longer a entity type drop relation constraint
if (currentProperty.RelationToEntityID.HasValue && !newProperty.RelationToEntityID.HasValue)
{
addQuery.Add($@"ALTER TABLE {currentEntityDef.FormattedTableName} DROP CONSTRAINT {currentProperty.RelationConstaintName};");
}
else
{
if (currentProperty.RelationToEntityID != newProperty.RelationToEntityID)
{ //if it had relation add drop constraint relation query
if (currentProperty.RelationToEntityID.HasValue)
{
addQuery.Add($@"ALTER TABLE {currentEntityDef.FormattedTableName} DROP CONSTRAINT {currentProperty.RelationConstaintName};");
}
this.CreateConstraintQuery(newProperty, newEntityDef, addQuery);
}
}
}
else
{
addQuery.Add($@"ALTER TABLE {currentEntityDef.FormattedTableName} ADD {newProperty.Name} {newProperty.SqlTypeName} {newProperty.SqlDefaultValue} {(newProperty.Required ? "NOT NULL" : "NULL")} ;");
//Create Constraint for new properties
if (newProperty.RelationToEntityID.HasValue)
{
this.CreateConstraintQuery(newProperty, newEntityDef, addQuery);
}
}
}
//deleted properties
foreach (EntityPropertyModel currentProperty in currentEntityDef.Properties.Where(c => !newEntityDef.Properties.Any(d => d.ID == c.ID)))
{
//drop default CONSTRAINT
if (!string.IsNullOrWhiteSpace(currentProperty.DefaultValue))
{
addQuery.Add($@" ALTER TABLE {currentEntityDef.FormattedTableName} DROP CONSTRAINT def_{currentEntityDef.FormattedTableName}_{currentProperty.Name} ; ");
}
//if it has relation add drop constraint relation query
if (currentProperty.DbType == EntityPropertyModel.e_dbType.Entity)
{
addQuery.Add($@"ALTER TABLE {currentEntityDef.FormattedTableName} DROP CONSTRAINT {currentProperty.RelationConstaintName};");
}
//drop property query
addQuery.Add($@"ALTER TABLE {currentEntityDef.FormattedTableName} DROP COLUMN {currentProperty.Name};");
}
}
foreach (string query in addQuery)
{
dataBaseQueryService.ExecuteBySqlQuery(query, false, null);
}
return(resultOperation);
}
