public ActionResult Index()
{
var executor = new SQLExecutor();
var model = new SQLResult
{
ConnectionString = _sqlCommandServices.GetConnectionString(),
Histories = _sqlCommandServices.GetHistories(),
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)
{
_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;
}
