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); }