/// <summary>
/// Initializes a new instance of the <see cref="DbObjectSelectionPanel"/> class.
/// </summary>
public DbObjectSelectionPanel()
{
_excelSelectionContainsData = false;
_wbConnection = null;
Filter = string.Empty;
LoadedProcedures = new List <DbProcedure>();
LoadedTables = new List <DbTable>();
LoadedViews = new List <DbView>();
InitializeComponent();
ConnectionNameLabel.Paint += Label_Paint;
UserIPLabel.Paint += Label_Paint;
SchemaLabel.Paint += Label_Paint;
InheritFontToControlsExceptionList.AddRange(new[]
{
ExportToNewTableHotLabel.Name,
SelectDatabaseObjectHotLabel.Name,
ImportDataHotLabel.Name,
EditDataHotLabel.Name,
AppendDataHotLabel.Name,
ImportMultiHotLabel.Name
});
DBObjectList.AddHeaderNode("Tables");
DBObjectList.AddHeaderNode("Views");
DBObjectList.AddHeaderNode("Procedures");
}
public object OrderStats(EFCoreDB <NorthwindContext> db)
{
SQL sql = @"select o.ProductID,d.OrderDate,(o.UnitPrice*o.Quantity) Amount,p.ProductName from 'Order Details' o inner join orders d on o.OrderID=d.OrderID
inner join Products p on p.ProductID = o.ProductID order by o.OrderID asc";
DBObjectList <ExpandoObject> items = (db.DBContext, sql, new EFCore.Extension.Region(0, 10000));
var productOrderGroup = (from a in items.Cast <dynamic>()
group a by new { a.ProductID, a.ProductName } into g
select new { g.Key.ProductID, g.Key.ProductName, Amount = g.Sum(i => (double)i.Amount) }).OrderByDescending(t => t.Amount).Take(10);
var timeGroup = from a in items.Cast <dynamic>()
group a by DateTime.Parse(a.OrderDate).ToString("yyyy-MM")
into g
select new { Date = g.Key, Amount = g.Sum(i => (double)i.Amount) };
var yearMonth = from a in timeGroup select a.Date;
Dictionary <string, IEnumerable> result = new Dictionary <string, IEnumerable>();
result["All"] = timeGroup;
foreach (var item in productOrderGroup)
{
var pgroup = from a in items.Cast <dynamic>()
where a.ProductID == item.ProductID
group a by DateTime.Parse(a.OrderDate).ToString("yyyy-MM")
into g
select new { Date = g.Key, Amount = g.Sum(i => (double)i.Amount) };
result[item.ProductName] = pgroup;
}
List <object> stats = new List <object>();
List <string> products = new List <string>();
foreach (var item in result)
{
var statItem = new List <double>();
products.Add(item.Key);
foreach (var month in yearMonth)
{
var data = item.Value.Cast <dynamic>().FirstOrDefault(p => p.Date == month);
if (data != null)
{
statItem.Add(data.Amount);
}
else
{
statItem.Add(0);
}
}
stats.Add(statItem);
}
return(new { Months = yearMonth, Products = products, Items = stats });
}
public void AutoExecute()
{
CodeTrackFactory.Level = CodeTrackLevel.All;
using (CodeTrackFactory.TrackReport("AutoExecute", CodeTrackLevel.Bussiness, null, "EFCore", "BeetleX"))
{
using (NorthwindContext db = new NorthwindContext())
{
DBValueList <string> values = (db, "select customerid from customers");
DBObjectList <CustomerName> items = (db, "select CustomerID,CompanyName from customers");
DBExecute <string> id = (db, "select CompanyName from customers where CustomerID='ALFKI'");
DBExecute execute = (db, "delete from customers", " delete from orders");
}
}
this.Console.WriteLine(CodeTrackFactory.Activity?.GetReport());
}
/// <summary>
/// Refreshes the DB objects list control with current objects in the connected schema.
/// </summary>
/// <param name="reloadFromServer">Flag indicating whether DB objects must be reloaded from the connected MySQL server.</param>
private bool RefreshDbObjectsList(bool reloadFromServer)
{
if (DBObjectList.HeaderNodes.Count < 3)
{
return(false);
}
bool success = true;
try
{
// Avoids flickering of DB Objects lists while adding the items to it.
DBObjectList.BeginUpdate();
DBObjectList.ClearChildNodes();
if (reloadFromServer)
{
LoadTables();
LoadViews();
LoadProcedures();
}
// Refresh Tables
foreach (var dbTable in LoadedTables.Where(table => string.IsNullOrEmpty(Filter) || table.Name.ToUpper().Contains(Filter)))
{
var node = DBObjectList.AddDbObjectNode(DBObjectList.HeaderNodes[0], dbTable);
dbTable.Selected = false;
node.ImageIndex = 0;
}
// Refresh Views
foreach (var dbView in LoadedViews.Where(view => string.IsNullOrEmpty(Filter) || view.Name.ToUpper().Contains(Filter)))
{
var node = DBObjectList.AddDbObjectNode(DBObjectList.HeaderNodes[1], dbView);
dbView.Selected = false;
node.ImageIndex = 1;
}
// Refresh Procedures
foreach (var dbProcedure in LoadedProcedures.Where(procedure => string.IsNullOrEmpty(Filter) || procedure.Name.ToUpper().Contains(Filter)))
{
var node = DBObjectList.AddDbObjectNode(DBObjectList.HeaderNodes[2], dbProcedure);
dbProcedure.Selected = false;
node.ImageIndex = 2;
}
DBObjectList.ExpandAll();
DBObjectList.Nodes[0].EnsureVisible();
// Avoids flickering of DB Objects lists while adding the items to it.
DBObjectList.EndUpdate();
DBObjectList_AfterSelect(null, null);
}
catch (Exception ex)
{
success = false;
MiscUtilities.ShowCustomizedErrorDialog(Resources.RefreshDBObjectsErrorTitle, ex.Message, true);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
return(success);
}
