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