public string DropColumn(DeleteColumnVm vm)
{
string dropQuery = $"DECLARE @ConstraintName nvarchar(200) " +
$"SELECT @ConstraintName = Name " +
$"FROM SYS.DEFAULT_CONSTRAINTS " +
$"WHERE PARENT_OBJECT_ID = OBJECT_ID('{vm.TableName}') " +
$"AND PARENT_COLUMN_ID = (SELECT column_id " +
$"FROM sys.columns " +
$"WHERE NAME = N'{vm.ColumnName}' AND object_id = OBJECT_ID(N'{vm.TableName}')) " +
$"IF @ConstraintName IS NOT NULL " +
$"EXEC('ALTER TABLE {vm.TableName} DROP CONSTRAINT ' + @ConstraintName) " +
$"ALTER TABLE {vm.TableName} DROP COLUMN {vm.ColumnName}";
return(DataProxy.DataService.ExecuteQuery(dropQuery, _dbInf.ConnectionString, _dbInf.DbmsType));
}
public ActionResult DeleteColumnConfirmed(DeleteColumnVm vm)
{
if (ModelState.IsValid)
{
IHelper helper = new SqlServerHelper(DataBaseInfo);
var result = helper.DropColumn(vm);
if (string.IsNullOrEmpty(result))
{
result = $"Колонка {vm.ColumnName} удалена успешно!";
}
TempData["StatusMessage"] = result;
return(RedirectToAction("Index", new { dbId = DbId, defaultTableName = vm.TableName }));
}
TempData["StatusMessage"] = "Были введены недопустимые данные.";
return(RedirectToAction("Index", new { dbId = DbId, defaultTableName = vm.TableName }));
}
public ActionResult DeleteColumn(string tablename)
{
DeleteColumnVm vm = new DeleteColumnVm {
TableName = tablename
};
var dt = Model;
foreach (DataColumn dataColumn in dt.Columns)
{
//Skip Id Row. We won't delete it
if (string.Compare(dataColumn.ColumnName, dt.Columns[GetIdOrdinalIndex()].ColumnName) == 0)
{
continue;
}
else
{
vm.AvailableColumns.Add(new SelectListItem {
Text = dataColumn.ColumnName, Value = dataColumn.ColumnName
});
}
}
return(PartialView(vm));
}
