/// <summary>
/// Unlocks tables locked in the current session.
/// </summary>
/// <param name="connection">MySQL Workbench connection to a MySQL server instance selected by users.</param>
public static void UnlockTablesInClientSession(this MySqlWorkbenchConnection connection)
{
try
{
const string sql = "UNLOCK TABLES";
MySqlHelper.ExecuteNonQuery(connection.GetConnectionStringBuilder().ConnectionString, sql);
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(Resources.UnableToUnlockTablesError, ex.Message);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
}
/// <summary>
/// Drops the given table from the connected schema.
/// </summary>
/// <param name="connection">MySQL Workbench connection to a MySQL server instance selected by users.</param>
/// <param name="tableName">The name of the table to drop.</param>
public static void DropTableIfExists(this MySqlWorkbenchConnection connection, string tableName)
{
if (connection == null || string.IsNullOrEmpty(connection.Schema) || string.IsNullOrEmpty(tableName))
{
return;
}
try
{
string sql = string.Format("DROP TABLE IF EXISTS `{0}`.`{1}`", connection.Schema, tableName);
MySqlHelper.ExecuteNonQuery(connection.GetConnectionStringBuilder().ConnectionString, sql);
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(string.Format(Resources.UnableToDropTableError, tableName), ex.Message);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
}
/// <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>
/// Gets the total number of rows contained in the corresponding MySQL object.
/// </summary>
/// <returns>The number of rows in a given table or view.</returns>
public long GetRowsCount()
{
if (Connection == null)
{
return(0);
}
object objCount = null;
try
{
string sql = string.Format("SELECT COUNT(*) FROM `{0}`.`{1}`", Connection.Schema, Name);
objCount = MySqlHelper.ExecuteScalar(Connection.GetConnectionStringBuilder().ConnectionString, sql);
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(string.Format(Resources.UnableToRetrieveData, this is DbTable ? "table" : "view", Name), ex.Message);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
return(objCount != null ? (long)objCount : 0);
}
/// <summary>
/// Creates an Excel table starting at the given cell containing the data in a <see cref="MySqlDataTable"/> instance.
/// </summary>
/// <param name="importDataAtCell">The top left Excel cell of the new <see cref="ExcelInterop.ListObject"/>.</param>
/// <param name="addSummaryRow">Flag indicating whether to include a row with summary fields at the end of the data rows.</param>
private void InitializeConnectionObjects(ExcelInterop.Range importDataAtCell, bool addSummaryRow)
{
if (importDataAtCell == null)
{
throw new ArgumentNullException("importDataAtCell");
}
var worksheet = Globals.Factory.GetVstoObject(importDataAtCell.Worksheet);
var workbook = worksheet.Parent as ExcelInterop.Workbook;
if (workbook == null)
{
throw new ParentWorkbookNullException(worksheet.Name);
}
string workbookGuid = workbook.GetOrCreateId();
try
{
// Create the Excel table needed to place the imported data into the Excel worksheet.
CreateExcelTableFromExternalSource(worksheet, importDataAtCell, addSummaryRow);
// Bind the MySqlDataTable already filled with data to the Excel table.
BindMySqlDataTable();
// Add this instance of the ImportConnectionInfo class if not present already in the global collection.
if (!Globals.ThisAddIn.StoredImportConnectionInfos.Exists(connectionInfo => connectionInfo.WorkbookGuid == workbookGuid && connectionInfo.MySqlTable == MySqlTable && string.Equals(connectionInfo.ExcelTableName, ExcelTable.Name, StringComparison.InvariantCultureIgnoreCase)))
{
Globals.ThisAddIn.StoredImportConnectionInfos.Add(this);
}
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(string.Format(Resources.ExcelTableCreationError, ExcelTable != null ? ExcelTable.Name : MySqlTable.ExcelTableName), ex.Message, true);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
}
/// <summary>
/// Checks if a table with the given name exists in the given schema.
/// </summary>
/// <param name="connection">MySQL Workbench connection to a MySQL server instance selected by users.</param>
/// <param name="schemaName">Name of the database schema where the table resides.</param>
/// <param name="tableName">Name of the table to look for.</param>
/// <returns><c>true</c> if the table exists, <c>false</c> otherwise.</returns>
public static bool TableExistsInSchema(this MySqlWorkbenchConnection connection, string schemaName, string tableName)
{
if (connection == null || string.IsNullOrEmpty(schemaName) || string.IsNullOrEmpty(tableName))
{
return(false);
}
object objCount = null;
try
{
string sql = string.Format("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '{0}' AND table_name = '{1}'", schemaName, tableName.EscapeDataValueString());
objCount = MySqlHelper.ExecuteScalar(connection.GetConnectionStringBuilder().ConnectionString, sql);
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(string.Format(Resources.UnableToRetrieveData, string.Format("`{0}`.", schemaName), tableName), ex.Message);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
long retCount = objCount != null ? (long)objCount : 0;
return(retCount > 0);
}
/// <summary>
/// Imports the data of this <see cref="DbView"/> to a <see cref="ExcelInterop.Worksheet"/>.
/// </summary>
/// <returns>A <see cref="Tuple"/> containing a <see cref="MySqlDataTable"/> filled with data for this <see cref="DbView"/> and either a <see cref="ExcelInterop.ListObject"/> or a <see cref="ExcelInterop.Range"/> where the data was imported to.</returns>
public Tuple <MySqlDataTable, object> ImportData()
{
Tuple <MySqlDataTable, object> retTuple;
var activeWorkbook = Globals.ThisAddIn.ActiveWorkbook;
try
{
// Create the MySqlDataTable that holds the data to be imported to Excel
var mySqlTable = GetMySqlDataTable();
object excelTableOrRange = null;
if (mySqlTable == null)
{
return(null);
}
if (!ImportParameters.ForEditDataOperation)
{
// Create a new Excel Worksheet and import the table/view data there
if (ImportParameters.IntoNewWorksheet)
{
var currentWorksheet = activeWorkbook.CreateWorksheet(mySqlTable.TableName, true);
if (currentWorksheet == null)
{
return(null);
}
}
else
{
// Check if the data being imported does not exceed the column available space
var exceedColumnsLimit = ExcelUtilities.CheckIfColumnsExceedWorksheetLimit(mySqlTable.Columns.Count);
var collides = DetectDataForImportPossibleCollisions(mySqlTable);
if (exceedColumnsLimit || collides)
{
var infoProperties = InfoDialogProperties.GetYesNoDialogProperties(
InfoDialog.InfoType.Warning,
Resources.ImportOverWorksheetColumnsLimitErrorTitle,
Resources.ImportOverWorksheetColumnsLimitErrorDetail,
Resources.ImportOverWorksheetColumnsLimitErrorSubDetail);
if (exceedColumnsLimit && InfoDialog.ShowDialog(infoProperties).DialogResult == DialogResult.No)
{
return(null);
}
infoProperties.TitleText = Resources.ImportOverExcelObjectErrorTitle;
infoProperties.DetailText = Resources.ImportOverExcelObjectErrorDetail;
infoProperties.DetailSubText = Resources.ImportOverExcelObjectErrorSubDetail;
if (collides && InfoDialog.ShowDialog(infoProperties).DialogResult == DialogResult.No)
{
return(null);
}
var newWorkSheet = activeWorkbook.CreateWorksheet(mySqlTable.TableName, true);
if (newWorkSheet == null)
{
return(null);
}
}
}
excelTableOrRange = Settings.Default.ImportCreateExcelTable
? mySqlTable.ImportDataIntoExcelTable(ImportParameters.CreatePivotTable, ImportParameters.PivotTablePosition, ImportParameters.AddSummaryRow)
: mySqlTable.ImportDataIntoExcelRange(ImportParameters.CreatePivotTable, ImportParameters.PivotTablePosition, ImportParameters.AddSummaryRow);
}
retTuple = new Tuple <MySqlDataTable, object>(mySqlTable, excelTableOrRange);
}
catch (Exception ex)
{
retTuple = null;
MiscUtilities.ShowCustomizedErrorDialog(string.Format(Resources.UnableToRetrieveData, this is DbTable ? "table" : "view", Name), ex.Message);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
return(retTuple);
}
/// <summary>
/// Gets the position of a column with the given name within the <see cref="TargetColumns"/> array.
/// </summary>
/// <param name="colName">Column name.</param>
/// <returns>Column name position.</returns>
public int GetTargetColumnIndex(string colName)
{
return(MiscUtilities.IndexOfStringInArray(TargetColumns, colName, true));
}
/// <summary>
/// Gets the position of the given source index within the <see cref="MappedSourceIndexes"/> array.
/// </summary>
/// <param name="sourceIndex">Source index.</param>
/// <returns>Source index position.</returns>
public int GetMappedSourceIndexIndex(int sourceIndex)
{
return(MiscUtilities.IndexOfIntInArray(MappedSourceIndexes, sourceIndex));
}
/// <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>
/// Prepares the procedure parameters needed to call the MySQL procedure.
/// </summary>
public void InitializeParameters()
{
var parametersTable = Connection.GetSchemaCollection("Procedure Parameters", null, Connection.Schema, Name);
if (parametersTable == null)
{
return;
}
try
{
var parametersCount = parametersTable.Rows.Count;
Parameters = new List <Tuple <string, MySqlParameter> >(parametersCount);
for (int paramIdx = 0; paramIdx < parametersCount; paramIdx++)
{
DataRow dr = parametersTable.Rows[paramIdx];
string dataType = dr["DATA_TYPE"].ToString().ToLowerInvariant();
string paramName = dr["PARAMETER_NAME"].ToString();
ParameterDirection paramDirection = ParameterDirection.Input;
int paramSize = dr["CHARACTER_MAXIMUM_LENGTH"] != null && dr["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value
? Convert.ToInt32(dr["CHARACTER_MAXIMUM_LENGTH"])
: 0;
byte paramPrecision = dr["NUMERIC_PRECISION"] != null && dr["NUMERIC_PRECISION"] != DBNull.Value
? Convert.ToByte(dr["NUMERIC_PRECISION"])
: (byte)0;
byte paramScale = dr["NUMERIC_SCALE"] != null && dr["NUMERIC_SCALE"] != DBNull.Value
? Convert.ToByte(dr["NUMERIC_SCALE"])
: (byte)0;
bool paramUnsigned = dr["DTD_IDENTIFIER"].ToString().Contains("unsigned", StringComparison.InvariantCultureIgnoreCase);
string paramDirectionStr = paramName != "RETURN_VALUE"
? dr["PARAMETER_MODE"].ToString().ToLowerInvariant()
: "return";
switch (paramDirectionStr)
{
case "in":
paramDirection = ParameterDirection.Input;
break;
case "out":
paramDirection = ParameterDirection.Output;
break;
case "inout":
paramDirection = ParameterDirection.InputOutput;
break;
case "return":
paramDirection = ParameterDirection.ReturnValue;
break;
}
var mySqlType = new MySqlDataType(dataType, true);
if (paramUnsigned)
{
mySqlType.Unsigned = true;
}
Parameters.Add(new Tuple <string, MySqlParameter>(dataType,
new MySqlParameter(paramName, mySqlType.MySqlDbType, paramSize, paramDirection, false, paramPrecision, paramScale, null, DataRowVersion.Current, mySqlType.TypeDefaultValue)));
}
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(Resources.ProcedureParametersInitializationError, ex.Message, true);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
}
/// <summary>
/// Imports the result sets of this stored procedure to a <see cref="ExcelInterop.Worksheet"/>.
/// </summary>
/// <param name="importType">The <see cref="ProcedureResultSetsImportType"/> defining what result sets are imported and how they are laid out in the Excel worksheet. </param>
/// <param name="selectedResultSetIndex">The index of the result set selected for import in case the <see cref="importType"/> is <see cref="ProcedureResultSetsImportType.SelectedResultSet"/>.</param>
/// <param name="resultSetsDataSet">The <see cref="DataSet"/> containing all result sets returned by the stored procedure's execution.</param>
/// <returns><c>true</c> if the import is successful, <c>false</c> otherwise.</returns>
public bool ImportData(ProcedureResultSetsImportType importType, int selectedResultSetIndex, DataSet resultSetsDataSet = null)
{
if (resultSetsDataSet == null)
{
resultSetsDataSet = Execute();
}
bool success = true;
try
{
var activeWorkbook = Globals.ThisAddIn.ActiveWorkbook;
// Check if the data being imported does not overlap with the data of an existing Excel table.
if (DetectDataForImportPossibleCollisions(importType, selectedResultSetIndex, resultSetsDataSet))
{
var infoProperties = InfoDialogProperties.GetYesNoDialogProperties(
InfoDialog.InfoType.Warning,
Resources.ImportOverExcelObjectErrorTitle,
Resources.ImportOverExcelObjectErrorDetail,
Resources.ImportOverExcelObjectErrorSubDetail);
if (InfoDialog.ShowDialog(infoProperties).DialogResult == DialogResult.No)
{
return(false);
}
var newWorkSheet = activeWorkbook.CreateWorksheet(Name, true);
if (newWorkSheet == null)
{
return(false);
}
}
int tableIdx = 0;
bool createPivotTable = ImportParameters.CreatePivotTable;
bool addSummaryRow = ImportParameters.AddSummaryRow;
ExcelInterop.Range nextTopLeftCell = Globals.ThisAddIn.Application.ActiveCell;
foreach (var mySqlTable in resultSetsDataSet.Tables.Cast <MySqlDataTable>().Where(mySqlTable => importType != ProcedureResultSetsImportType.SelectedResultSet || selectedResultSetIndex == tableIdx++))
{
Globals.ThisAddIn.Application.Goto(nextTopLeftCell, false);
mySqlTable.ImportColumnNames = ImportParameters.IncludeColumnNames;
mySqlTable.TableName = Name + "." + mySqlTable.TableName;
var excelObj = Settings.Default.ImportCreateExcelTable
? mySqlTable.ImportDataIntoExcelTable(createPivotTable, ExcelUtilities.PivotTablePosition.Right, addSummaryRow)
: mySqlTable.ImportDataIntoExcelRange(createPivotTable, ExcelUtilities.PivotTablePosition.Right, addSummaryRow);
if (excelObj == null)
{
continue;
}
var fillingRange = excelObj is ExcelInterop.ListObject
? (excelObj as ExcelInterop.ListObject).Range
: excelObj as ExcelInterop.Range;
nextTopLeftCell = fillingRange.GetNextResultSetTopLeftCell(importType, createPivotTable);
}
}
catch (Exception ex)
{
success = false;
MiscUtilities.ShowCustomizedErrorDialog(string.Format(Resources.UnableToRetrieveData, "procedure", Name), ex.Message);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
return(success);
}
