public ActionResult AddCheck(string appName, string tableName, FormCollection fc)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
foreach (string checkConstraint in table.GetCheckConstraints()) //constraint name control in table
{
if (checkConstraint == "CHK_" + appName + "_" + tableName + "_" + fc["checkName"])
{
TempData["message-error"] = "Check constraint with name " + checkConstraint + " is already exist.";
return RedirectToAction("Index", new { @appName = appName });
}
}
int i = 0;
Conditions con = new Conditions(new SqlQuery());
Condition_Operators ope = new Condition_Operators(con);
Condition_concat concat = new Condition_concat(con);
while (fc["column[" + i + "]"] != null)
{
DBColumn col = table.columns.SingleOrDefault(x => x.Name == fc["column[" + i + "]"]);
object val = table.ConvertValue(col, fc["value[" + i + "]"]);
con.column(col.Name);
con.isCheck = true;
if (i != 0) concat.and();
ope = table.GetConditionOperators(con, fc["conOperator[" + i + "]"], val);
i++;
}
table.AddCheck(fc["checkName"], con);
app.SaveChanges();
TempData["message-success"] = "Check constraint " + fc["checkName"] + " was successfully added into table " + tableName + ".";
return RedirectToAction("Index", new { @appName = appName });
}
public ActionResult InsertSelect(string appName, string tableName, List<string> columnsA, List<string> columnsB, string tableB, FormCollection fc)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
Conditions con = new Conditions(new SqlQuery());
if (fc["conOperator[0]"] != "")
{
Condition_Operators ope = new Condition_Operators(con);
Condition_concat concat = new Condition_concat(con);
int i = 0;
while (fc["column[" + i + "]"] != null)
{
DBColumn col = table.columns.SingleOrDefault(x => x.Name == fc["column[" + i + "]"]);
object val = table.ConvertValue(col, fc["value[" + i + "]"]);
con.column(col.Name);
if (i != 0) concat.and();
ope = table.GetConditionOperators(con, fc["conOperator[" + i + "]"], val);
i++;
}
}
table.InsertSelect(columnsA, tableB, columnsB, con.ToString());
app.SaveChanges();
TempData["message-success"] = "Row(s) was successfully inserted.";
return RedirectToAction("Data", new { @appName = appName, @tableName = tableName });
}
public ActionResult InsertRow(string appName, string tableName, FormCollection fc)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
bool isClusterIndex = false;
foreach (DBIndex index in table.indices) //looking for cluster index between all indeces
{
if (index.indexName == "index_" + appName + tableName)
{
isClusterIndex = true;
break;
}
}
if (!isClusterIndex)//condition for cluster, you can not insert/update/delete without cluster in Azure
{
TempData["message-error"] = "Row can not be inserted, because table does not have a cluster index. The cluster index is created when you first create a primary key.";
return RedirectToAction("Data", new { @appName = appName, @tableName = tableName });
}
DBItem row = new DBItem();
foreach (DBColumn c in table.columns) //converting to right data type
{
if (fc.Get("col" + c.Name) == "")
{
row.createProperty(c.ColumnId, c.Name, null);
}
else
{
row.createProperty(c.ColumnId, c.Name, table.ConvertValue(c, fc.Get("col" + c.Name)));
}
}
table.Add(row);
app.SaveChanges();
TempData["message-success"] = "Row was successfully inserted.";
return RedirectToAction("Data", new { @appName = appName, @tableName = tableName });
}
public ActionResult DropTable(string appName, string tableName)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
app.GetTable(tableName).Drop();
app.SaveChanges();
TempData["message-success"] = "Table " + tableName + " was successfully dropped.";
return RedirectToAction("Index", new { @appName = appName });
}
public ActionResult AddColumn(string appName, string tableName, DBColumn column)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
if (table.Select().ToList().Count != 0 && column.canBeNull == false)//condition for not null column, which can be created only in empty table
{
TempData["message-error"] = "Table " + tableName + " must be empty if you want to add NOT NULL column.";
return RedirectToAction("Details", new { @appName = appName, @tableName = tableName });
}
else if (table.columns.SingleOrDefault(x => x.Name == column.Name) != null)//condition for column name, column name can not be equal with other names in table
{
TempData["message-error"] = "Table " + tableName + " has already column with name " + column.Name + ".";
return RedirectToAction("Details", new { @appName = appName, @tableName = tableName });
}
table.columns.AddToDB(column);
app.SaveChanges();
TempData["message-success"] = "Column " + column.Name + " was successfully added.";
return RedirectToAction("Details", new { @appName = appName, @tableName = tableName });
}
public ActionResult DropConstraint(string tableName, string appName, string constraintName, bool? isPrimaryKey)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
if (isPrimaryKey == true)
{
if (table.primaryKeys.Count == 0) //is there some primary key to drop?
{
TempData["message-error"] = "Table has no primary key to drop.";
return RedirectToAction("Index", new { @appName = appName });
}
}
table.DropConstraint(constraintName, isPrimaryKey);
app.SaveChanges();
TempData["message-success"] = "Constraint was successfully dropped.";
return RedirectToAction("Index", new { @appName = appName });
}
public ActionResult AddIndex(string appName, string tableName, FormCollection fc, List<string> indexColumns)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
foreach (DBIndex index in table.indices)//constraint name control for in table
{
if (index.indexName == "index_" + appName + "_" + tableName + "_" + fc["indexName"])
{
TempData["message-error"] = "Index constraint with name " + index.indexName + " is already exist.";
return RedirectToAction("Index", new { @appName = appName });
}
}
table.indices.AddToDB(fc["indexName"], indexColumns);
app.SaveChanges();
TempData["message-success"] = "Index " + fc["indexname"] + " of table " + tableName + " was successfully created.";
return RedirectToAction("Index", new { @appName = appName });
}
public ActionResult UpdateRow(string appName, string tableName, FormCollection fc)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
DBItem changes = new DBItem();
DBItem oldVal = new DBItem();
foreach (DBColumn c in table.columns)//converting to right data type
{
changes.createProperty(c.ColumnId, c.Name, table.ConvertValue(c, fc.Get("col" + c.Name)));
oldVal.createProperty(c.ColumnId, c.Name, TempData[c.Name]);
}
table.Update(changes, oldVal);
app.SaveChanges();
TempData["message-success"] = "Row was successfully updated.";
return RedirectToAction("Data", new { @appName = appName, @tableName = tableName });
}
public ActionResult DeleteOrUpdate(string appName, string tableName, FormCollection fc)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
DBItem row = new DBItem();
foreach (DBColumn c in table.columns)//converting to right data type
{
row.createProperty(c.ColumnId, c.Name, table.ConvertValue(c, fc.Get("col" + c.Name)));
TempData.Remove(c.Name);
TempData.Add(c.Name, row[c.Name]);
}
if (fc.Get("Update") != null)
{
ViewBag.Row = row.getAllProperties();
return View("UpdateView", table);
}
else
{
table.Remove(row);
app.SaveChanges();
TempData["message-success"] = "Row was successfully deleted.";
return RedirectToAction("Data", new { @appName = appName, @tableName = tableName });
}
}
public ActionResult DeleteIndex(string appName, string tableName, string indexName)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
table.indices.DropFromDB(indexName);
app.SaveChanges();
TempData["message-success"] = "Index " + indexName + " of table " + tableName + " was successfully dropped.";
return RedirectToAction("Index", new { @appName = appName });
}
public ActionResult Create(string appName, DBTable model)
{
if (!string.IsNullOrWhiteSpace(model.tableName))
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
model.Application = app;
foreach (DBTable t in app.GetTables())//can not create table with name, which is already exist
{
if (t.tableName == model.tableName)
{
TempData["message-error"] = "Table " + model.tableName + " can not be created. Table name " +
model.tableName + " is already exist.";
return RedirectToAction("Index", new { @appName = appName });
}
}
foreach (DBColumn c in model.columns)//column name must be unique in table, int p is for situation, when column is compared with itself
{
int p = 0;
foreach (DBColumn d in model.columns)
{
if (c.Name == d.Name)
{
p = p + 1;
if (p > 1)
{
TempData["message-error"] = "Table " + model.tableName + " can not be created. Column name " +
c.Name + " is in table more then once.";
return RedirectToAction("Index", new { @appName = appName });
}
}
}
}
model.Create();
foreach (DBColumn c in model.columns)//every colum with isUnique=true add query for AddUniqueValue into queries
{
List<string> unique = new List<string>();
if (c.isUnique)
{
unique.Add(c.Name);
model.columns.AddUniqueValue(c.Name, unique);
}
}
app.SaveChanges();
TempData["message-success"] = "Table " + model.tableName + " was successfully created.";
return RedirectToAction("Index", new { @appName = appName });
}
return View(model);
}
public ActionResult AlterColumn(string appName, string tableName, DBColumn column)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
foreach (string s in TempData.Keys)//column names control with other names in table
{
if (s == column.Name)
{
TempData["message-error"] = "Table " + tableName + " has already column with name " + column.Name + ".";
ViewBag.ColName = column.Name;
return RedirectToAction("Details", new { @appName = appName, @tableName = tableName });
}
}
if (column.canBeNull != true)//column can not has NULL in definition, if it has null values
{
foreach (DBItem i in table.Select().ToList())
{
foreach (DBColumn c in table.columns)
{
if (c.Name == column.Name)
{
if (i.GetIdProperty(column.ColumnId).ToString() == null || i.GetIdProperty(column.ColumnId).ToString() == "")
{
TempData["message-error"] = "Column " + column.Name + " can not be null. It has null values.";
return RedirectToAction("Details", new { @appName = appName, @tableName = tableName });
}
}
}
}
}
if (column.Name != TempData["oldColumnName"].ToString()) //rename column is not part of ModifyInDB operation, that why is this condition used
{
table.columns.RenameInDB(TempData["oldColumnName"].ToString(), column.Name);
}
table.columns.ModifyInDB(column);
app.SaveChanges();
TempData["message-success"] = "Column " + column.Name + " was successfully altered.";
return RedirectToAction("Details", new { @appName = appName, @tableName = tableName });
}
public ActionResult AddUnique(string appName, string tableName, string uniqueName, List<string> uniqueColumns)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
foreach (string s in table.columns.GetUniqueConstrainst()) //constraint name control in table
{
if (s == "UN_" + appName + "_" + tableName + "_" + uniqueName)
{
TempData["message-error"] = "Unique constraint with name " + s + " is already exist.";
return RedirectToAction("Index", new { @appName = appName });
}
}
table.columns.AddUniqueValue(uniqueName, uniqueColumns);
app.SaveChanges();
TempData["message-success"] = "Column(s) " + string.Join(", ", uniqueColumns) + " is/are unique.";
return RedirectToAction("Index", new { @appName = appName });
}
public ActionResult AddPrimaryKey(string appName, string tableName, List<string> primaryKeys)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
table.AddPrimaryKey(primaryKeys);
app.SaveChanges();
TempData["message-success"] = "Primary key of table " + tableName + " was successfully created.";
return RedirectToAction("Index", new { @appName = appName });
}
public ActionResult RenameTable(string appName, DBTable model)
{
string tableName = TempData["oldTableName"].ToString();
if (!string.IsNullOrWhiteSpace(tableName))
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
table.Rename(model.tableName);
app.SaveChanges();
TempData["message-success"] = "Table " + tableName + " was successfully renamed to " + model.tableName + ".";
return RedirectToAction("Index", new { @appName = appName });
}
return View();
}
public ActionResult DisableOrEnableConstraint(string appName, string tableName, FormCollection fc, bool isDisable)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
string constraintName = (fc["all"] != null) ? "ALL" : fc["constraintName"];
if (isDisable)
{
table.DisableConstraint(constraintName);
TempData["message-success"] = "Constraint of table " + tableName + " was successfully disabled.";
}
else
{
table.EnableConstraint(constraintName);
TempData["message-success"] = "Constraint of table " + tableName + " was successfully enabled.";
}
app.SaveChanges();
return RedirectToAction("Index", new { @appName = appName });
}
public ActionResult TruncateTable(string appName, string tableName)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
table.Truncate();
app.SaveChanges();
return RedirectToAction("Data", new { @appName = appName, @tableName = tableName });
}
public ActionResult DropColumn(string appName, string tableName, string columnName)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
app.GetTable(tableName)
.columns.DropFromDB(columnName);
app.SaveChanges();
TempData["message-success"] = "Column " + columnName + " was successfully dropped.";
return RedirectToAction("Details", new { @appName = appName, @tableName = tableName });
}
public ActionResult AddDefault(string appName, string tableName, string value, string defaultColumn)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
DBColumn c = table.columns.SingleOrDefault(s => s.Name == defaultColumn);
object val = table.ConvertValue(c, value);
table.columns.AddDefaultValue(defaultColumn, val);
app.SaveChanges();
return RedirectToAction("Index", new { @appName = appName });
}
public ActionResult AddForeignKey(string appName, DBForeignKey model)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable sTable = app.GetTable(model.sourceTable.tableName);
foreach (DBForeignKey foreignKey in sTable.foreignKeys) //constraint name control in table
{
if (foreignKey.name == "FK_" + appName + "_" + model.sourceTable.tableName + "_" + model.name)
{
TempData["message-error"] = "Foreign key with name " + foreignKey.name + " is already exist.";
return RedirectToAction("Index", new { @appName = appName });
}
}
DBTable tTable = app.GetTable(model.targetTable.tableName);
DBColumn sColumn = sTable.columns.SingleOrDefault(x => x.Name == model.sourceColumn);
DBColumn tColumn = tTable.columns.SingleOrDefault(x => x.Name == model.targetColumn);
if (sColumn.type != tColumn.type) //columns must have equal data types
{
TempData["message-error"] = "Keys have different data types.";
return RedirectToAction("CreateForeignKey", new { @appName = appName, @tableName = sTable.tableName });
}
sTable.foreignKeys.AddToDB(model);
app.SaveChanges();
TempData["message-success"] = "Foreign key " + model.name + " of table " + sTable.tableName + " was successfully created.";
return RedirectToAction("Index", new { @appName = appName });
}
