/// <summary>
/// Executes the given query and returns the result set in a <see cref="DataTable"/> object.
/// </summary>
/// <param name="connection">MySQL Workbench connection to a MySQL server instance selected by users.</param>
/// <param name="query">Select query to be sent to the MySQL Server.</param>
/// <param name="tableIndex">The index of the table in the <see cref="DataSet"/> to be returned.</param>
/// <returns>Table containing the results of the query.</returns>
public static DataTable GetDataFromSelectQuery(this MySqlWorkbenchConnection connection, string query, int tableIndex = 0)
{
if (connection == null)
{
return(null);
}
DataSet ds = null;
try
{
var connectionBuilder = connection.GetConnectionStringBuilder();
ds = MySqlHelper.ExecuteDataset(connectionBuilder.ConnectionString, query);
}
catch (Exception ex)
{
Logger.LogException(ex, true, string.Format(Resources.UnableToRetrieveData, "query: ", query));
}
var dataTable = ds == null || ds.Tables.Count <= 0 || tableIndex < 0 || tableIndex >= ds.Tables.Count
? null
: ds.Tables[tableIndex];
return(Settings.Default.ImportFloatingPointDataAsDecimal
? dataTable.ConvertApproximateFloatingPointDataTypeColumnsToExact()
: dataTable);
}
/// <summary>
/// Executes the given query and returns the result set in a <see cref="DataTable"/> object.
/// </summary>
/// <param name="connection">MySQL Workbench connection to a MySQL server instance selected by users.</param>
/// <param name="query">Select query to be sent to the MySQL Server.</param>
/// <param name="tableIndex">The index of the table in the <see cref="DataSet"/> to be returned.</param>
/// <returns>Table containing the results of the query.</returns>
public static DataTable GetDataFromSelectQuery(this MySqlWorkbenchConnection connection, string query, int tableIndex = 0)
{
if (connection == null)
{
return(null);
}
DataSet ds = null;
try
{
var connectionBuilder = connection.GetConnectionStringBuilder();
connectionBuilder.AllowUserVariables = true;
ds = MySqlHelper.ExecuteDataset(connectionBuilder.ConnectionString, query);
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(string.Format(Resources.UnableToRetrieveData, "from query: ", query), ex.Message);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
return(ds == null || ds.Tables.Count <= 0 || tableIndex < 0 || tableIndex >= ds.Tables.Count
? null
: ds.Tables[tableIndex]);
}
/// <summary>
/// Applies the SQL query by breaking it into stataments and executing one by one inside a transaction.
/// </summary>
public void ApplyScript()
{
ErroredOutDataRow = null;
ScriptResult = MySqlStatement.StatementResultType.NotApplied;
CreateActualStatementsList();
if (ActualStatementRowsList == null || ActualStatementRowsList.Count == 0)
{
return;
}
var connectionStringBuilder = _wbConnection.GetConnectionStringBuilder();
connectionStringBuilder.AllowUserVariables = true;
using (var conn = new MySqlConnection(connectionStringBuilder.ConnectionString))
{
conn.Open();
MySqlTransaction transaction = conn.BeginTransaction();
var command = new MySqlCommand(string.Empty, conn, transaction);
uint executionOrder = 1;
foreach (var mySqlRow in ActualStatementRowsList)
{
// Before attempting to execute the MySqlStatement object, check if the connection is still open.
if (conn.State != ConnectionState.Open)
{
ErroredOutDataRow = mySqlRow;
ErroredOutDataRow.RowError = Resources.ConnectionLostErrorText;
ScriptResult = MySqlStatement.StatementResultType.ConnectionLost;
break;
}
var rowStatement = mySqlRow.Statement;
rowStatement.Execute(command, executionOrder++, _useOptimisticUpdate);
ScriptResult = rowStatement.JoinResultTypes(ScriptResult);
if (ScriptResult.WithoutErrors())
{
continue;
}
ErroredOutDataRow = mySqlRow;
if (ScriptResult == MySqlStatement.StatementResultType.ErrorThrown)
{
// Check if the result was errored out because the connection was broken and if so, flip the ScriptResult to its proper value.
if (conn.State != ConnectionState.Open)
{
ErroredOutDataRow.RowError = Resources.ConnectionLostErrorText;
ScriptResult = MySqlStatement.StatementResultType.ConnectionLost;
break;
}
mySqlRow.ReflectError();
}
break;
}
PostApplyScript(transaction);
transaction.Dispose();
}
}
/// <summary>
/// Executes a routine and returns all result sets as tables within a <see cref="DataSet"/>.
/// </summary>
/// <param name="connection">MySQL Workbench connection to a MySQL server instance selected by users.</param>
/// <param name="routineName">Qualified routine name (i.e. Schema.Routine).</param>
/// <param name="routineParameters">Array of arguments passed to the routine parameters.</param>
/// <returns><see cref="DataSet"/> where each table within it represents each of the result sets returned by the routine.</returns>
public static DataSet ExecuteRoutine(this MySqlWorkbenchConnection connection, string routineName, params MySqlParameter[] routineParameters)
{
if (connection == null)
{
return(null);
}
// Create empty return DataSet
var ds = new DataSet();
// Create & open a SqlConnection, and dispose of it after we are done.
using (var baseConnection = new MySqlConnection(connection.GetConnectionStringBuilder().ConnectionString))
{
baseConnection.Open();
// Create a command and prepare it for execution
using (var cmd = new MySqlCommand
{
Connection = baseConnection,
CommandText = routineName,
CommandType = CommandType.StoredProcedure
})
{
if (routineParameters != null)
{
foreach (var p in routineParameters)
{
cmd.Parameters.Add(p);
}
}
using (var reader = cmd.ExecuteReader())
{
var resultSetTable = reader.ReadResultSet("ResultSet");
if (resultSetTable != null)
{
ds.Tables.Add(resultSetTable);
}
var resultSetIndex = 1;
while (reader.NextResult())
{
resultSetTable = reader.ReadResultSet("ResultSet" + resultSetIndex++);
if (resultSetTable != null)
{
ds.Tables.Add(resultSetTable);
}
}
}
// Detach the MySqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
}
// Return the data set
return(ds);
}
}
/// <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)
{
Logger.LogException(ex, true, Resources.UnableToUnlockTablesError);
}
}
/// <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
{
var sql = $"DROP TABLE IF EXISTS `{connection.Schema}`.`{tableName}`";
MySqlHelper.ExecuteNonQuery(connection.GetConnectionStringBuilder().ConnectionString, sql);
}
catch (Exception ex)
{
Logger.LogException(ex, true, string.Format(Resources.UnableToDropTableError, tableName));
}
}
/// <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>
/// Executes a routine and returns all result sets as tables within a dataset.
/// </summary>
/// <param name="connection">MySQL Workbench connection to a MySQL server instance selected by users.</param>
/// <param name="routineName">Qualified routine name (i.e. Schema.Routine).</param>
/// <param name="routineParameters">Array of arguments passed to the routine parameters.</param>
/// <returns><see cref="DataSet"/> where each table within it represents each of the result sets returned by the routine.</returns>
public static DataSet ExecuteRoutine(this MySqlWorkbenchConnection connection, string routineName, params MySqlParameter[] routineParameters)
{
if (connection == null)
{
return(null);
}
// Create & open a SqlConnection, and dispose of it after we are done.
using (MySqlConnection baseConnection = new MySqlConnection(connection.GetConnectionStringBuilder().ConnectionString))
{
baseConnection.Open();
// Create a command and prepare it for execution
MySqlCommand cmd = new MySqlCommand
{
Connection = baseConnection,
CommandText = routineName,
CommandType = CommandType.StoredProcedure
};
if (routineParameters != null)
{
foreach (MySqlParameter p in routineParameters)
{
cmd.Parameters.Add(p);
}
}
// Create the DataAdapter & DataSet
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
// Fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);
// Detach the MySqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
// Return the dataset
return(ds);
}
}
/// <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
{
var sql = $"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '{schemaName}' AND table_name = '{tableName.EscapeDataValueString()}'";
objCount = MySqlHelper.ExecuteScalar(connection.GetConnectionStringBuilder().ConnectionString, sql);
}
catch (Exception ex)
{
Logger.LogException(ex, true, string.Format(Resources.UnableToRetrieveData, $"`{schemaName}`.", tableName));
}
var retCount = (long?)objCount ?? 0;
return(retCount > 0);
}
/// <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);
}