public static void DropColumn(DbConnection cnn, FieldDAL field, bool rebuildViews = true)
{
var dbType = GetDbType(cnn);
var tableName = "content_" + field.ContentId;
var asyncTableName = tableName + "_async";
var sql = $"alter table {{0}} drop column {Escape(dbType, field.Name)}";
if (field.IndexFlag == 1)
{
DropIndex(cnn, tableName, field.Name);
DropIndex(cnn, asyncTableName, field.Name);
}
var contentId = (int)field.ContentId;
if (rebuildViews)
{
DropContentViews(cnn, contentId);
}
ExecuteSql(cnn, String.Format(sql, tableName));
ExecuteSql(cnn, String.Format(sql, asyncTableName));
if (rebuildViews)
{
CreateContentViews(cnn, contentId);
}
}
public static void DeleteField(string CompanyID, string ID, string FieldName, string TableType)
{
DataHelper.ExecuteQuerys("ALTER TABLE " + CompanyDAL.GetTableName(CompanyID, TableType) + " Drop column " + FieldName);
FieldDAL.DeleteField(ID);
string where = "CompanyID='{0}' and TableType ='{1}'";
DataSet ds = FieldDAL.GetFieldList(string.Format(where, CompanyID, TableType));
HttpContext.Current.Cache[Tools.GetCashName(CompanyID, TableType)] = ds;
}
private static void RecreateColumn(DbConnection cnn, FieldDAL oldField, FieldDAL newField)
{
var tableName = "content_" + newField.ContentId;
var asyncTableName = tableName + "_async";
DropColumn(cnn, oldField, false);
AddColumn(cnn, newField, false);
FillColumn(cnn, tableName, newField);
FillColumn(cnn, asyncTableName, newField);
}
public static void UpdateField(string ID, string FieldName, string companyID, string tableType)
{
FieldDAL.UpdateField(FieldName, ID);
DataSet fieldsDS = CompanyBLL.GetCacheFields(companyID, tableType);
DataRow[] drs = fieldsDS.Tables[0].Select("ID=" + ID);
if (drs.Length == 1)
{
drs[0]["FieldName"] = FieldName;
}
fieldsDS.AcceptChanges();
}
public static string InsertByDataSet(DataSet ds, string CompanyID, string tableType)
{
string message = FieldDAL.InsertByDataSet(ds, CompanyID, tableType);
if (message == "")
{
string where = "CompanyID='{0}' and TableType ='{1}'";
where = string.Format(where, CompanyID, tableType);
HttpContext.Current.Cache.Insert(Tools.GetCashName(CompanyID, tableType), FieldDAL.GetFieldList(where));
}
return(message);
}
public static void UpdateField(string ID, string FName, string Misk, string IsDispaly, string companyID, string tableType)
{
FieldDAL.UpdateField(FName, Misk, IsDispaly, ID);
DataSet fieldsDS = CompanyBLL.GetCacheFields(companyID, tableType);
DataRow[] drs = fieldsDS.Tables[0].Select("ID=" + ID);
if (drs.Length == 1)
{
drs[0]["FName"] = FName;
drs[0]["Misk"] = Misk;
drs[0]["IsDisplay"] = IsDispaly;
}
fieldsDS.AcceptChanges();
}
public static void InsertField(string FieldName, string FName, string CompanyID, string TableType, string FieldType, string FieldLength, string Misk, string IsDispaly)
{
if (FieldLength.Trim() == "")
{
FieldLength = "300";
}
FieldDAL.InsertField(FieldName, FName, CompanyID, TableType, FieldType, FieldLength, Misk, IsDispaly);
string tableName = CompanyDAL.GetTableName(CompanyID, TableType);
DataHelper.ExecuteQuerys("ALTER TABLE " + tableName + " ADD " + FieldName + " NVARCHAR(" + FieldLength + ") NULL");
string where = "CompanyID='{0}' and TableType ='{1}'";
DataSet ds = FieldDAL.GetFieldList(string.Format(where, CompanyID, TableType));
HttpContext.Current.Cache[Tools.GetCashName(CompanyID, TableType)] = ds;
}
public static DataSet GetCacheFields(string CompanyID, string tableType)
{
if (HttpContext.Current.Cache[Tools.GetCashName(CompanyID, tableType)] == null)
{
string where = "CompanyID='{0}' and TableType ='{1}'";
DataSet ds = FieldDAL.GetFieldList(string.Format(where, CompanyID, tableType));
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
HttpContext.Current.Cache[Tools.GetCashName(CompanyID, tableType)] = ds;
return(ds);
}
return(HttpContext.Current.Cache[Tools.GetCashName(CompanyID, tableType)] as DataSet);
}
private static void FillColumn(DbConnection cnn, string tableName, FieldDAL newField)
{
var dbType = GetDbType(cnn);
var typeExpr = dbType == DatabaseType.Postgres ? PgColumnType(newField.Type.DatabaseType) : "nvarchar(max)";
var sql = $@"
update {tableName} set {Escape(dbType, newField.Name)} = cast(coalesce(cd.blob_data, cd.data) as {typeExpr})
from content_data cd
where cd.attribute_id = {newField.Id} and cd.content_item_id = {tableName}.content_item_id
";
using (var cmd = DbCommandFactory.Create(sql, cnn))
{
cmd.ExecuteNonQuery();
}
}
public static void UpdateColumn(DbConnection connection, FieldDAL oldField, FieldDAL newField)
{
bool isIncompatibleChange = oldField.Type.DatabaseType != newField.Type.DatabaseType ||
oldField.Size != newField.Size && newField.Type.DatabaseType != "NTEXT";
if (isIncompatibleChange)
{
DropContentViews(connection, (int)newField.ContentId);
RecreateColumn(connection, oldField, newField);
CreateContentViews(connection, (int)newField.ContentId);;
return;
}
var tableName = "content_" + newField.ContentId;
var asyncTableName = tableName + "_async";
if (oldField.IndexFlag == 1 && newField.IndexFlag == 0)
{
DropIndex(connection, tableName, oldField.Name);
DropIndex(connection, asyncTableName, oldField.Name);
}
var dbType = GetDbType(connection);
if (oldField.Name != newField.Name)
{
DropContentViews(connection, (int)newField.ContentId);
RenameColumn(connection, tableName, oldField.Name, newField.Name);
RenameColumn(connection, asyncTableName, oldField.Name, newField.Name);
CreateContentViews(connection, (int)newField.ContentId);;
if (oldField.IndexFlag == 1 && newField.IndexFlag == 1)
{
RenameIndex(connection, tableName, oldField.Name, newField.Name);
RenameIndex(connection, asyncTableName, oldField.Name, newField.Name);
}
}
else if (oldField.IsLong != newField.IsLong)
{
RecreateNewViews(connection, (int)newField.ContentId);
}
if (oldField.IndexFlag == 0 && newField.IndexFlag == 1)
{
AddIndex(connection, tableName, newField.Name);
AddIndex(connection, asyncTableName, newField.Name);
}
}
private static void DropLinkWithCheck(DbConnection cnn, FieldDAL dal)
{
if (dal.LinkId.HasValue)
{
var anotherRealFieldsExists = QPContext.EFContext.FieldSet.Include(n => n.Content)
.Any(n => n.LinkId == dal.LinkId && n.Content.VirtualType == 0 && n.Id != dal.Id);
var link = QPContext.EFContext.ContentToContentSet.SingleOrDefault(n => n.LinkId == dal.LinkId.Value);
if (!anotherRealFieldsExists && link != null)
{
DefaultRepository.SimpleDelete(link);
Common.DropLinkView(cnn, link);
if (QPContext.DatabaseType == DatabaseType.Postgres)
{
Common.DropLinkTables(cnn, link);
}
}
}
}
public static void AddColumn(DbConnection cnn, FieldDAL field, bool rebuildViews = true)
{
var dbType = GetDbType(cnn);
var tableName = "content_" + field.ContentId;
var asyncTableName = tableName + "_async";
var columnType = (dbType == DatabaseType.SqlServer) ? field.Type.DatabaseType : PgColumnType(field.Type.DatabaseType);
var fieldDef = $@"{Escape(dbType, field.Name)} {columnType}";
if (field.Type.DatabaseType == "NVARCHAR")
{
fieldDef += $"({field.Size})";
}
else if (field.Type.DatabaseType == "NUMERIC")
{
var firstSize = (field.Type.Name == FieldTypeName.Numeric) ? 38 : 18;
fieldDef += $"({firstSize}, {field.Size})";
}
var sql = $"alter table {{0}} add {fieldDef}";
var contentId = (int)field.ContentId;
if (rebuildViews)
{
DropContentViews(cnn, contentId);
}
ExecuteSql(cnn, String.Format(sql, tableName));
ExecuteSql(cnn, String.Format(sql, asyncTableName));
if (rebuildViews)
{
CreateContentViews(cnn, contentId);
}
if (field.IndexFlag == 1)
{
AddIndex(cnn, tableName, field.Name);
AddIndex(cnn, asyncTableName, field.Name);
}
}
void IFieldRepository.Delete(int id)
{
using (var scope = new QPConnectionScope())
{
try
{
if (QPContext.DatabaseType == DatabaseType.SqlServer)
{
ChangeCleanEmptyLinksTriggerState(scope.DbConnection, false);
ChangeRemoveFieldTriggerState(scope.DbConnection, false);
ChangeReorderFieldsTriggerState(scope.DbConnection, false);
ChangeDeleteContentLinkTriggerState(scope.DbConnection, false);
}
var field = GetById(id);
field.ReorderContentFields(true);
var isVirtual = field.Content.IsVirtual;
FieldDAL dal = GetDal(field);
DefaultRepository.Delete <FieldDAL>(id);
if (!isVirtual)
{
Common.DropColumn(scope.DbConnection, dal);
DropLinkWithCheck(scope.DbConnection, dal);
}
}
finally
{
if (QPContext.DatabaseType == DatabaseType.SqlServer)
{
ChangeCleanEmptyLinksTriggerState(scope.DbConnection, true);
ChangeRemoveFieldTriggerState(scope.DbConnection, true);
ChangeReorderFieldsTriggerState(scope.DbConnection, true);
ChangeDeleteContentLinkTriggerState(scope.DbConnection, true);
}
}
}
}
public static DataSet GetFieldList()
{
return(FieldDAL.GetFieldList());
}
public static DataSet GetFieldByID(string id)
{
return(FieldDAL.GetFieldList("ID=" + id));
}
public static void DeleteField(string IDs)
{
FieldDAL.DeleteField(IDs);
}