public ActionResult CreateTable(string tableName, string columns)
{
var lst = new JavaScriptSerializer().Deserialize<List<Column>>(columns);
var saveTable = new Table { Name = tableName, IsIdentity = true, Columns = lst };
tableService.AddNewTable(saveTable);
return RedirectToAction("Detail", new { tableName });
}
public static string GetCreateTableStatement(Table table)
{
string columns = string.Empty;
foreach (var column in table.Columns)
{
string columnTemp = Column;
string dataType;
if (column.DataType == ColumnType.VarChar)
{
dataType = column.Size != null ? string.Format(Nvarchar, column.Size) : string.Format(Nvarchar, "max");
}
else
{
dataType = column.DataType.ToString();
}
columnTemp = columnTemp.Replace("{0}", column.Name);
columnTemp = columnTemp.Replace("{1}", dataType);
columnTemp = columnTemp.Replace("{2}", !column.Nullable ? "NOT NULL" : "");
columnTemp = column.IsPrimarykey ? columnTemp.Replace("{3}", table.IsIdentity ? "IDENTITY(1,1) PRIMARY KEY" : "PRIMARY KEY") : columnTemp.Replace("{3}", "");
columns += columnTemp;
}
return string.Format(Table, table.Name, columns.Substring(0, columns.Length - 1));
}
public void AddNewTable(Table table)
{
table.Columns.Add(new Column
{
Name = "Id",
Display = "Id",
DataType = ColumnType.Int,
IsPrimarykey = true
});
var tableScript = SqlHelper.GetCreateTableStatement(table);
tableRepository.Add(table);
unitOfWork.Context.Database.ExecuteSqlCommand(tableScript);
unitOfWork.Commit();
}
public static string GetInsertStatement(Table table)
{
string columns = string.Empty;
string values = string.Empty;
var listColums = table.IsIdentity ? table.Columns.Where(x => !x.IsPrimarykey).ToList() : table.Columns;
foreach (var column in listColums)
{
columns += column.Name + ",";
values += string.Format("@{0},", column.Name);
}
return string.Format(InsertStatement, table.Name, columns.Substring(0, columns.Length - 1), values.Substring(0, values.Length - 1));
}
public static string GetSelectTopOneStatement(Table table)
{
string columns = string.Empty;
string conditions = string.Empty;
foreach (var column in table.Columns)
{
columns += string.Format("{0},", column.Name);
}
var primaryColumns = table.Columns.Where(x => x.IsPrimarykey).ToList();
foreach (var column in primaryColumns)
{
conditions += string.Format("{0}=@{0},", column.Name);
}
return string.Format(SelectTopOneStatement, columns.Substring(0, columns.Length - 1), table.Name, conditions.Substring(0, conditions.Length - 1));
}
public static string GetUpdateStatement(Table table)
{
string columns = string.Empty;
string conditions = string.Empty;
var listColums = table.IsIdentity ? table.Columns.Where(x => !x.IsPrimarykey).ToList() : table.Columns;
foreach (var column in listColums)
{
columns += string.Format("{0}=@{0},", column.Name);
}
var primaryColumns = table.Columns.Where(x => x.IsPrimarykey).ToList();
foreach (var column in primaryColumns)
{
conditions += string.Format("{0}=@{0},", column.Name);
}
return string.Format(UpdateStatement, table.Name, columns.Substring(0, columns.Length - 1), conditions.Substring(0, conditions.Length - 1));
}
