public ActionResult Testing(SQLSettingVM vm)
{
if (!Func.SQLIsValid(vm.SQLStatement))
{
vm.SQLResult = "SQL語句不合法!";
}
else
{
using (DataAccess da = new DataAccess())
{
// 將 top(n) 帶入 SQL語句
int index = vm.SQLStatement.IndexOf("Select", StringComparison.OrdinalIgnoreCase); // 找出第一個select的位置
if (index < 0)
{
vm.SQLResult = "SQL語句不合法!";
}
else
{
string sql = Func.SqlPlusTop(vm.SQLStatement, vm.DataRow);
Tuple <bool, DataTable, string> result = da.TryExecuteDataTable(sql);
vm.SQLResultDataRow = result.Item2;
vm.SQLResult = result.Item3;
}
}
}
return(View("Edit", vm));
}
public ActionResult Save(SQLSettingVM vm)
{
if (!Func.SQLIsValid(vm.SQLStatement))
{
vm.SQLResult = "SQL語句不合法!";
return(View("Edit", vm));
}
else
{
using (DataAccess da = new DataAccess())
{
// 將 top(n) 帶入 SQL語句
//int index = vm.SQLStatement.IndexOf("Select", StringComparison.OrdinalIgnoreCase); // 找出第一個select的位置
//string sql = "SELECT TOP (" + vm.DataRow + ") " + vm.SQLStatement.Remove(index, 6);
string sql = Func.SqlPlusTop(vm.SQLStatement, vm.DataRow);
Tuple <bool, DataTable, string> result = da.TryExecuteDataTable(sql);
vm.SQLResultDataRow = result.Item2;
vm.SQLResult = result.Item3;
}
List <ColumnData> Columns = new List <ColumnData>();
for (int i = 0; i < vm.SQLResultDataRow.Columns.Count; i++)
{
Columns.Add(new ColumnData()
{
ColumnName = vm.SQLResultDataRow.Columns[i].ColumnName,
Idx = i
});
}
using (tblSQLSettingRepository setting = new tblSQLSettingRepository())
{
vm.SQLResult = setting.Save(vm.SQLName, vm.SQLStatement, vm.DataRow, vm.SQLType, Columns, userInfo.Account);
if (vm.SQLResult.Equals("ok"))
{
return(RedirectToAction("Index"));
}
else
{
return(View("Edit", vm));
}
}
}
}
