MySqlTable GetShops(List <XListing> list)
{
MySqlTable table = new MySqlTable
{
cols = new string[] {
"Shop_id", "SiteName", "ShopName",
"Location", "Credit", "CreatedAt", "LastUpdatedAt",
"taobaoId", "ContactUrl", "IsTmall", "Siteid"
}
};
table.data = new object[list.Count, table.cols.Length];
for (int i = 0; i < list.Count; i++)
{
var l = list[i];
table.data[i, 0] = l.BotShopID;
table.data[i, 1] = l.SiteName;
table.data[i, 2] = l.ShopName;
table.data[i, 3] = l.ShopLocation;
table.data[i, 4] = l.ShopCredit;
table.data[i, 5] = DateTime.Now;
table.data[i, 6] = DateTime.Now;
table.data[i, 7] = l.ShopID;
table.data[i, 8] = l.ShopContactUrl;
table.data[i, 9] = l.ShopIsTmall;
table.data[i, 10] = l.SiteID;
}
return(table);
}
public async Task <bool> UpdateAsync(MySqlTable data)
{
_dataContext.MySqlTable.Update(data);
var updated = await _dataContext.SaveChangesAsync();
return(updated > 0);
}
public async Task <bool> CreateAsync(MySqlTable data)
{
await _dataContext.MySqlTable.AddAsync(data);
var created = await _dataContext.SaveChangesAsync();
return(created > 0);
}
MySqlTable GetListings(List <XListing> list)
{
MySqlTable table = new MySqlTable
{
cols = new string[] {
"ItemId", "ShopID",
"ShopName", "ItemName", "CreatedAt", "LastUpdatedAt",
"TotalBotPackages", "taobaoID", "Recent30DaysSoldNum", "Price",
"DetailUrl", "SiteName", "LastBotAt", "QuantityInStock",
"BotStatus", "TotalComments", "BadComments", "Reviews", "BotBrand",
"Recent30DaysOrderedNum", "IsTmall", "ItemGoodCommentPercent",
"pic_url", "taskId", "taskName", "SiteId"
}
};
table.data = new object[list.Count, table.cols.Length];
for (int i = 0; i < list.Count; i++)
{
var l = list[i];
table.data[i, 0] = l.BotItemID;
table.data[i, 1] = l.BotShopID;
table.data[i, 2] = l.ShopName;
table.data[i, 3] = l.ItemName;
table.data[i, 4] = DateTime.Now;
table.data[i, 5] = DateTime.Now;
table.data[i, 6] = null;
table.data[i, 7] = l.ItemID;
table.data[i, 8] = l.ItemSold30Days;
table.data[i, 9] = l.ItemPrice;
table.data[i, 10] = l.ItemDetailUrl;
table.data[i, 11] = l.SiteName;
table.data[i, 12] = DateTime.Now;
table.data[i, 13] = 0;
table.data[i, 14] = l.ItemBotStatus;
table.data[i, 15] = l.ItemTotalCommentCount;
table.data[i, 16] = 0;
table.data[i, 17] = l.ItemReviews;
table.data[i, 18] = "";
table.data[i, 19] = l.Item30DaysOrderedNum;
table.data[i, 20] = l.ShopIsTmall;
table.data[i, 21] = l.ItemGoodCommentPercent;
table.data[i, 22] = l.Itempic;
table.data[i, 23] = l.taskid;
table.data[i, 24] = l.taskName;
table.data[i, 25] = l.SiteID;
}
return(table);
}
/// <summary>
/// 为某个指定的仓库对象构造一个 DbTable
/// </summary>
/// <param name="repo"></param>
/// <returns></returns>
internal static RdbTable CreateORMTable(IRepositoryInternal repo)
{
RdbTable table = null;
var provider = RdbDataProvider.Get(repo).DbSetting.ProviderName;
switch (provider)
{
case DbSetting.Provider_SqlClient:
table = new SqlServerTable(repo);
break;
case DbSetting.Provider_SqlCe:
table = new SqlCeTable(repo);
break;
case DbSetting.Provider_MySql:
table = new MySqlTable(repo);
break;
default:
if (DbConnectionSchema.IsOracleProvider(provider))
{
table = new OracleTable(repo);
break;
}
throw new NotSupportedException();
}
table.IdentifierProvider = DbMigrationProviderFactory.GetIdentifierProvider(provider);
table.DbTypeConverter = DbMigrationProviderFactory.GetDbTypeConverter(provider);
var em = repo.EntityMeta;
foreach (var columnInfo in table.Info.Columns)
{
var epm = em.Property(columnInfo.Property);
if (epm == null)
{
throw new ArgumentNullException(string.Format("{0}.{1} 属性需要使用托管属性进行编写。", table.Info.Class.FullName, columnInfo.Property.Name));
}
var column = table.CreateColumn(columnInfo);
table.Add(column);
}
return(table);
}
/// <summary>
/// Unbinds the <see cref="ToolsExcelTable"/>, refreshes the data on the <see cref="MySqlTable"/> and binds it again to the <see cref="ToolsExcelTable"/>.
/// </summary>
public void Refresh()
{
if (MySqlTable == null || ToolsExcelTable == null)
{
return;
}
// Test the connection before attempting the data refresh.
if (!TestConnection())
{
if (ConnectionInfoError != ConnectionInfoErrorType.WorkbenchConnectionDoesNotExist)
{
return;
}
// If the Workbench connection does not exist anymore, log a message to the log, remove this object from the global connections collection and exit.
MySqlSourceTrace.WriteToLog(string.Format(Resources.ImportConnectionInfoRemovedConnectionText, WorkbookName, WorksheetName, ExcelTableName), SourceLevels.Warning);
Globals.ThisAddIn.StoredImportConnectionInfos.Remove(this);
return;
}
try
{
// In case the table is bound (it should not be) then disconnect it.
if (ToolsExcelTable.IsBinding)
{
ToolsExcelTable.Disconnect();
}
// Refresh the data on the MySqlDataTable and bind it so the Excel table is refreshed.
MySqlTable.RefreshData();
// Bind the table again after it was refreshed.
BindMySqlDataTable();
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(string.Format(Resources.ImportDataRefreshError, _excelTableName), ex.GetFormattedMessage(), true);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
}
/// <summary>
/// Releases all resources used by the <see cref="ImportConnectionInfo"/> class
/// </summary>
/// <param name="disposing">If true this is called by Dispose(), otherwise it is called by the finalizer</param>
protected virtual void Dispose(bool disposing)
{
if (_disposed)
{
return;
}
// Free managed resources
if (disposing)
{
if (ToolsExcelTable != null)
{
if (ToolsExcelTable.IsBinding)
{
ToolsExcelTable.Disconnect();
}
ToolsExcelTable.DeleteSafely(false);
}
if (MySqlTable != null)
{
MySqlTable.Dispose();
}
// Set variables to null so this object does not hold references to them and the GC disposes of them sooner.
_connection = null;
MySqlTable = null;
ExcelTable = null;
ToolsExcelTable = null;
}
// Add class finalizer if unmanaged resources are added to the class
// Free unmanaged resources if there are any
_disposed = true;
}
public static string SqlSelecionarTodosDadosTabela(MySqlTable table)
{
return(string.Format("SELECT * FROM `{0}`;", table.Nome));
}
public static MySqlDataTableScope <TEntity> CreateScope(MySqlDataBase dataBase, MySqlTable <TEntity> table)
{
return(new MySqlDataTableScope <TEntity>(dataBase, table));
}
private MySqlDataTableScope(MySqlDataBase dataBase, MySqlTable <TEntity> table)
: base(dataBase)
{
Table = table;
}
/// <summary>
/// Binds the <see cref="MySqlTable"/> to the <see cref="ToolsExcelTable" /> so its data can be refreshed.
/// </summary>
public void BindMySqlDataTable()
{
if (MySqlTable == null || ToolsExcelTable == null)
{
return;
}
try
{
// In case the table is bound (it should not be) then disconnect it.
if (ToolsExcelTable.IsBinding)
{
ToolsExcelTable.Disconnect();
}
// Skip Worksheet events
Globals.ThisAddIn.SkipWorksheetChangeEvent = true;
Globals.ThisAddIn.SkipSelectedDataContentsDetection = true;
// Resize the ExcelTools.ListObject by giving it an ExcelInterop.Range calculated with the refreshed MySqlDataTable dimensions.
// Detection of a collision with another Excel object must be performed first and if any then shift rows and columns to fix the collision.
const int headerRows = 1;
int summaryRows = ExcelTable.ShowTotals ? 1 : 0;
ExcelInterop.Range newRange = ToolsExcelTable.Range.Cells[1, 1];
newRange = newRange.SafeResize(MySqlTable.Rows.Count + headerRows + summaryRows, MySqlTable.Columns.Count);
var intersectingRange = newRange.GetIntersectingRangeWithAnyExcelObject(true, true, true, _excelTable.Comment);
if (intersectingRange != null && intersectingRange.CountLarge != 0)
{
ExcelInterop.Range bottomRightCell = newRange.Cells[newRange.Rows.Count, newRange.Columns.Count];
// Determine if the collision is avoided by inserting either new columns or new rows.
if (intersectingRange.Columns.Count < intersectingRange.Rows.Count)
{
for (int colIdx = 0; colIdx <= intersectingRange.Columns.Count; colIdx++)
{
bottomRightCell.EntireColumn.Insert(ExcelInterop.XlInsertShiftDirection.xlShiftToRight, Type.Missing);
}
}
else
{
for (int rowIdx = 0; rowIdx <= intersectingRange.Rows.Count; rowIdx++)
{
bottomRightCell.EntireRow.Insert(ExcelInterop.XlInsertShiftDirection.xlShiftDown, Type.Missing);
}
}
// Redimension the new range. This is needed since the new rows or columns inserted are not present in the previously calculated one.
newRange = ToolsExcelTable.Range.Cells[1, 1];
newRange = newRange.SafeResize(MySqlTable.Rows.Count + headerRows + summaryRows, MySqlTable.Columns.Count);
}
// Redimension the ExcelTools.ListObject's range
ToolsExcelTable.Resize(newRange);
// Re-format the importing range
ExcelInterop.Range dataOnlyRange = newRange.Offset[headerRows];
dataOnlyRange = dataOnlyRange.Resize[newRange.Rows.Count - headerRows - summaryRows];
MySqlTable.FormatImportExcelRange(dataOnlyRange, true);
// Bind the redimensioned ExcelTools.ListObject to the MySqlDataTable
ToolsExcelTable.SetDataBinding(MySqlTable);
if (MySqlTable.ImportColumnNames)
{
foreach (MySqlDataColumn col in MySqlTable.Columns)
{
ToolsExcelTable.ListColumns[col.Ordinal + 1].Name = col.DisplayName;
}
}
ToolsExcelTable.Range.Columns.AutoFit();
// Disconnect the table so users can freely modify the data imported to the Excel table's range.
ToolsExcelTable.Disconnect();
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(string.Format(Resources.ImportDataBindError, _excelTableName), ex.GetFormattedMessage(), true);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
finally
{
Globals.ThisAddIn.SkipWorksheetChangeEvent = false;
Globals.ThisAddIn.SkipSelectedDataContentsDetection = false;
}
}
/// <summary>
/// Refreshes the row's data and reflects the changes on the <see cref="ExcelRow"/>.
/// </summary>
/// <param name="acceptChanges">Flag indicating whether the refreshed data is committed immediately to the row.</param>
public void RefreshData(bool acceptChanges)
{
if (MySqlTable == null)
{
return;
}
if ((RowState != DataRowState.Added && RowState != DataRowState.Modified))
{
return;
}
bool refreshSuccessful = true;
try
{
var refreshQuery = GetSqlForRefreshingRow();
if (string.IsNullOrEmpty(refreshQuery))
{
return;
}
var refreshTable = MySqlTable.WbConnection.GetDataFromSelectQuery(refreshQuery);
if (refreshTable == null || refreshTable.Rows.Count == 0)
{
return;
}
var refreshedRow = refreshTable.Rows[0];
var rowValues = refreshedRow.ItemArray;
MySqlTable.PrepareCopyingItemArray(ref rowValues, MySqlTable.EscapeFormulaTexts);
_refreshingData = true;
ItemArray = rowValues;
if (ExcelRange == null)
{
return;
}
Globals.ThisAddIn.SkipWorksheetChangeEvent = true;
for (int columnIndex = 1; columnIndex <= ExcelRange.Columns.Count; columnIndex++)
{
ExcelRange.Cells[1, columnIndex] = rowValues[columnIndex - 1];
}
}
catch (Exception ex)
{
MySqlSourceTrace.WriteAppErrorToLog(ex);
refreshSuccessful = false;
}
finally
{
_refreshingData = false;
Globals.ThisAddIn.SkipWorksheetChangeEvent = false;
}
if (acceptChanges && refreshSuccessful)
{
AcceptChanges();
}
}
