public ActionResult Index()
{
var executor = new SQLExecutor();
var model = new SQLResult
{
ConnectionString = _sqlCommandService.GetConnectionString(),
Histories = _sqlCommandService.GetHistories(EzCMSContants.DefaultHistoryStart, EzCMSContants.DefaultHistoryLength),
ReadOnly = true,
Tables = executor.GetTableNames()
};
return(View(model));
}
/// <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)
{
_isqlCommandService.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.UtcNow.Ticks;
IDbTransaction transaction = null;
var openConnection = false;
try
{
//open connection if needed
if (_connection.State != ConnectionState.Open)
{
_connection.Open();
openConnection = true;
}
var 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
var reader = command.ExecuteReader();
do
{
var 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
{
//Do nothing
}
}
//Close connection if it was opened by us
if (openConnection)
{
try
{
_connection.Close();
}
catch
{
//Do nothing
}
}
}
//Other properties for resutl object
result.Tables = GetTableNames();
var endTime = DateTime.UtcNow.Ticks;
result.ReturnData = returnData;
result.ProcessTime = (long)new TimeSpan(endTime - startTime).TotalMilliseconds;
result.Histories = _isqlCommandService.GetHistories(EzCMSContants.DefaultHistoryStart,
EzCMSContants.DefaultHistoryLength);
return(result);
}