public RunningQueryViewModel(SqlQuery baseModel)
{
Id = baseModel.Id;
Sql = baseModel.Sql;
Databases = baseModel.Databases;
UserId = baseModel.UserId;
DateStart = baseModel.DateStart;
DateEnd = baseModel.DateEnd;
SingleTable = baseModel.SingleTable;
Done = DateEnd.HasValue && DateEnd > DateStart;
var runningTime = Done
? DateEnd.Value - DateStart
: DateTime.Now - DateStart;
RunningTime = runningTime.ToString(@"dd\.hh\:mm\:ss");
Results = new List <WorkerResult>();
using (var dbContext = new OctopusDbContext())
{
var tmpResults = dbContext
.QueryResults
.Where(qr => qr.QueryId == Id)
.ToList();
foreach (var tmpResult in tmpResults)
{
Results.Add(JsonConvert.DeserializeObject <WorkerResult>(tmpResult.Result));
}
}
}
/// <summary>
/// Internal thread execution method
/// </summary>
private void ExecutionThread()
{
_countdown = _databases.Count;
foreach (var db in _databases)
{
Thread databaseThread = new Thread(DatabaseThread);
databaseThread.Start(db);
}
while (_countdown > 0)
{
Thread.Sleep(500);
}
using (var dbContext = new OctopusDbContext())
{
var queryFromContext = dbContext
.Queries
.Where(q => q.Id == _query.Id)
.First();
queryFromContext.DateEnd = DateTime.Now;
dbContext.SaveChanges();
}
}
/// <summary>
/// Get script from script repository by id
/// </summary>
public static PreparedScript Get(int id)
{
var dbContext = new OctopusDbContext();
return(dbContext
.PreparedScripts
.Where(s => s.Id == id)
.FirstOrDefault());
}
// GET: History
public ActionResult Index()
{
var dbContext = new OctopusDbContext();
int userId = UserHelper.GetUserId(User.Identity);
return(View(dbContext
.Queries
.Where(q => q.UserId == userId)
.OrderByDescending(q => q.DateStart)
));
}
public static int GetUserId(IIdentity identity)
{
if (!identity.IsAuthenticated)
{
return(0);
}
else
{
using (var context = new OctopusDbContext())
return(context.Accounts.Where(a => a.Name == identity.Name).First().Id);
}
}
public static bool IsAdmin(IIdentity identity)
{
if (!identity.IsAuthenticated)
{
return(false);
}
else
{
using (var context = new OctopusDbContext())
return(context.Accounts.Where(a => a.Name == identity.Name).First().IsAdmin);
}
}
/// <summary>
/// A thread for executing query within each database
/// </summary>
private void DatabaseThread(object db)
{
var database = (WorkerDb)db;
var queryResult = new SqlQueryResult();
queryResult.QueryId = _query.Id;
queryResult.DatabaseId = database.Id;
try
{
var adapter = new SqlDataAdapter(_query.Sql, database.ConnectionString);
adapter.SelectCommand.CommandTimeout = 0;
var table = new DataTable();
adapter.Fill(table);
var result = new WorkerResult();
result.DatabaseId = database.Id;
foreach (DataColumn column in table.Columns)
{
result.AppendColumn(column.Caption);
}
foreach (DataRow row in table.Rows)
{
result.AppendRow(row.ItemArray);
}
_results.Add(result);
queryResult.Result = JsonConvert.SerializeObject(result);
}
catch (Exception ex)
{
var result = new WorkerResult();
result.DatabaseId = database.Id;
result.AppendColumn("error");
result.AppendRow(new object[] { ex.Message });
queryResult.Result = JsonConvert.SerializeObject(result);
}
finally
{
using (var dbContext = new OctopusDbContext())
{
dbContext.QueryResults.Add(queryResult);
dbContext.SaveChanges();
}
Interlocked.Decrement(ref _countdown);
}
}
public ActionResult Index(UserQueryViewModel model)
{
var dbContext = new OctopusDbContext();
var script = PreparedScriptHelper.Get(model.PreparedScriptId);
ModelState.Remove("Sql");
if (script != null)
{
model.Sql = script.Sql;
model.Params = PreparedScriptHelper.PrepareParams(script.Sql);
}
else
{
model.Sql = string.Empty;
}
return(View(model));
}
/// <summary>
/// Get prepared script for View
/// </summary>
/// <returns></returns>
public static List <SelectListItem> GetScripts()
{
var dbContext = new OctopusDbContext();
var result = new List <SelectListItem>();
result.Add(new SelectListItem()
{
Value = "0", Text = "( none )"
});
result.AddRange(dbContext
.PreparedScripts
.Select(s => new SelectListItem()
{
Text = s.Name,
Value = s.Id.ToString()
}));
return(result);
}
/// <summary>
/// A thread for executing query within each database
/// </summary>
private void DatabaseThread(object db)
{
var database = (WorkerDb)db;
var queryResult = new SqlQueryResult();
queryResult.QueryId = _query.Id;
queryResult.DatabaseId = database.Id;
try
{
var adapter = new SqlDataAdapter(_query.Sql, database.ConnectionString);
adapter.SelectCommand.CommandTimeout = 0;
var table = new DataTable();
adapter.Fill(table);
var result = new WorkerResult();
result.DatabaseId = database.Id;
foreach (DataColumn column in table.Columns)
result.AppendColumn(column.Caption);
foreach (DataRow row in table.Rows)
result.AppendRow(row.ItemArray);
_results.Add(result);
queryResult.Result = JsonConvert.SerializeObject(result);
}
catch (Exception ex)
{
var result = new WorkerResult();
result.DatabaseId = database.Id;
result.AppendColumn("error");
result.AppendRow(new object[] { ex.Message });
queryResult.Result = JsonConvert.SerializeObject(result);
}
finally
{
using (var dbContext = new OctopusDbContext())
{
dbContext.QueryResults.Add(queryResult);
dbContext.SaveChanges();
}
Interlocked.Decrement(ref _countdown);
}
}
/// <summary>
/// Internal thread execution method
/// </summary>
private void ExecutionThread()
{
_countdown = _databases.Count;
foreach (var db in _databases)
{
Thread databaseThread = new Thread(DatabaseThread);
databaseThread.Start(db);
}
while (_countdown > 0)
{
Thread.Sleep(500);
}
using (var dbContext = new OctopusDbContext())
{
var queryFromContext = dbContext
.Queries
.Where(q => q.Id == _query.Id)
.First();
queryFromContext.DateEnd = DateTime.Now;
dbContext.SaveChanges();
}
}
public AccountController()
{
_dbContext = new OctopusDbContext();
}
