/// <summary> /// Executing a SQL query against current DB /// </summary> /// <param name="request"></param> /// <returns></returns> public SQLResult Execute(SQLRequest request) { //save executing query into history if (request.SaveToHistory) { _sqlCommandServices.SaveCommand(request); } //Prepare the result object based on basic information from request var result = new SQLResult { ConnectionString = _connection.ConnectionString, Query = request.Query, ReadOnly = request.ReadOnly, HtmlEncode = request.HtmlEncode }; var returnData = new List<DataResult>(); var startTime = DateTime.Now.Ticks; IDbTransaction transaction = null; bool openConnection = false; try { //open connection if needed if (_connection.State != ConnectionState.Open) { _connection.Open(); openConnection = true; } IDbCommand command = _connection.CreateCommand(); command.CommandText = request.Query; command.CommandType = CommandType.Text; //if executing in read only, we put all SQL statement into a DB transaction //then roll back after then if (request.ReadOnly) { transaction = _connection.BeginTransaction(); command.Transaction = transaction; } //Excuting and parse result IDataReader reader = command.ExecuteReader(); if (reader != null) { do { DataResult dataResult = getResult(reader); if (dataResult != null) { returnData.Add(dataResult); } } while (reader.NextResult()); result.RecordsAffected = reader.RecordsAffected; } } catch (Exception ex) { result.Error = ex; } finally { //Roll back transaction if needed if (transaction != null) { try { transaction.Rollback(); } catch { } } //Close connection if it was opened by us if (openConnection) { try { _connection.Close(); } catch { } } } //Other properties for resutl object result.Tables = GetTableNames(); long endTime = DateTime.Now.Ticks; result.ReturnData = returnData; result.ProcessTime = (long)new TimeSpan(endTime - startTime).TotalMilliseconds; var defaultHistoryLength = _settingServices.GetSetting<int>(SettingNames.DefaultHistoryLength); var defaultHistoryStart = _settingServices.GetSetting<int>(SettingNames.DefaultHistoryStart); result.Histories = _sqlCommandServices.GetHistories(defaultHistoryStart, defaultHistoryLength); return result; }
public ActionResult Index(SQLRequest request, bool exportToFile = false) { var executor = new SQLExecutor(); var result = executor.Execute(request); if (exportToFile) { return new FileContentResult(Encoding.UTF8.GetBytes(result.ToString()), "text/plain") { FileDownloadName = "QueryResult.txt" }; } return View(result); }
/// <summary> /// Save a SQL request into history for later use /// </summary> /// <param name="request"></param> public ResponseModel SaveCommand(SQLRequest request) { if (request.Query == null) { return new ResponseModel { Success = true }; } var last = GetLastCommand(); if (last != null && last.Query == request.Query) { return new ResponseModel { Success = true }; } var history = new SQLCommandHistoryModel { Query = request.Query }; var response = Insert(history); return response.SetMessage(response.Success ? _localizedResourceServices.T("AdminModule:::News:::Messages:::CreateSuccessfully:::Create news successfully.") : _localizedResourceServices.T("AdminModule:::News:::Messages:::CreateFailure:::Insert news failed. Please try again later.")); }