/// <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."));
}
