/// <summary>
/// Mysql Command with parameters, including Insert, Update and Delet statements.
/// </summary>
/// <param name="cmd">Type of Command</param>
/// <param name="parameters">Parameters to be added</param>
/// <returns>Returns mysql cmd</returns>
public void MySQL_AdvancedQueryCommand(Constants.Constants.ComandSelect cmd, Dictionary <string, string> parameters)
{
//Set Loading ON
this.isLoading = true;
//Open Conn
this.mysql_connect_Open();
#region Propriedades
//Setting Proprienties
_cmd.CommandText = this.query;
_cmd.Connection = _conn;
#endregion
#region Parameters
foreach (var param in parameters)
{
_cmd.Parameters.AddWithValue(param.Key, param.Value);
}
#endregion
//Execute Command
try
{
//Select Statements
if (cmd == Constants.Constants.ComandSelect.Select)
{
_cmd.ExecuteNonQueryAsync();
}
//Insert Statmensts
if (cmd == Constants.Constants.ComandSelect.Insert)
{
_cmd.ExecuteNonQuery();
}
}
//Get Error
catch (MySqlException ex)
{
Console.WriteLine(ex);
}
finally
{
//Close Command
_cmd.Dispose();
//Close Conn
this.mysql_connect_Close();
//Set False
this.isLoading = false;
}
//Return MysqlCommand
//return _cmd;
}
/// <summary>
/// Method contaning mysql reader results
/// </summary>
/// <param name="_type_command">Command constant for querry</param>
/// <returns>return a IDatareader</returns>
public IDataReader MySQL_DataReader(Constants.Constants.ComandSelect _type_command)
{
//Set Loading True
this.isLoading = true;
#region Propriedades
//Setting Proprienties
_cmd.CommandText = this.query;
_cmd.Connection = _conn;
_cmd.Parameters.Clear();
#endregion
//Execute Command
try
{
this.mysql_connect_Open();
if (_type_command == Constants.Constants.ComandSelect.Select)
{
_dr = _cmd.ExecuteReader();
}
if (_type_command == Constants.Constants.ComandSelect.Update)
{
_dr = _cmd.ExecuteReader();
}
if (_type_command == Constants.Constants.ComandSelect.Insert)
{
_dr = _cmd.ExecuteReader();
}
if (_type_command == Constants.Constants.ComandSelect.Delete)
{
_dr = _cmd.ExecuteReader();
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex);
}
finally
{
this.mysql_connect_Close();
//Set False
this.isLoading = false;
}
//Return Temp MysqlDataReader
return(_dr);
}
/// <summary>
/// Select and Retrive Results from a SQL querry
/// </summary>
/// <param name="cmd">Enum to choose kind of command type operation</param>
/// <param name="parameters">Dictonary for paramters including</param>
public void MySQL_ExecuteNonQuerySingleCommandWithParameters(Constants.Constants.ComandSelect cmd, Dictionary <string, string> parameters)
{
//Open Conn
this.mysql_connect_Open();
//Set Loading param
this.isLoading = true;
#region Propriedades
//Setting Proprienties
_cmd.CommandText = this.query;
_cmd.Connection = _conn;
_cmd.Parameters.Clear();
#endregion
#region Parameters
foreach (var param in parameters)
{
_cmd.Parameters.AddWithValue(param.Key, param.Value);
}
#endregion
//Execute Command
try
{
if (cmd == Constants.Constants.ComandSelect.Select)
{
_cmd.ExecuteNonQuery();
}
if (cmd == Constants.Constants.ComandSelect.Insert)
{
_cmd.ExecuteNonQuery();
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex);
throw;
}
finally
{
//Close Command
_cmd.Dispose();
//Close Conn
this.mysql_connect_Close();
//Set False
this.isLoading = false;
}
}
/// <summary>
/// Run a query in mysql and return a string with results
/// </summary>
/// <param name="cmd">type of command</param>
/// <returns>Return a string array with results</returns>
public string[] MySQL_ExecuteSingleCommand(Constants.Constants.ComandSelect cmd)
{
//Set Loading param
this.isLoading = true;
//Strings
this._cmd.CommandText = query;
//Instancing Connection
this._cmd.Connection = _conn;
try
{
if (cmd == Constants.Constants.ComandSelect.Select)
{
//Instancing DR
_dr = _cmd.ExecuteReader();
while (_dr.Read())
{
_s = new string[_dr.FieldCount];
for (int i = 0; i < _dr.FieldCount; i++)
{
_s[i] = _dr.GetString(i);
}
}
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
this.mysql_connect_Close();
//Set False
this.isLoading = false;
//DataReader Check
this._dr.Close();
}
return(_s);
}
/// <summary>
/// Execute and Retrive Results from a SQL query
/// </summary>
/// <param name="cmd">query to be executed</param>
public void MySQL_ExecuteNonQuerySingleCommand(Constants.Constants.ComandSelect cmd)
{
//Open Conn
this.mysql_connect_Open();
//Set Loading param
this.isLoading = true;
#region Propriedades
//Setting Proprienties
_cmd.CommandText = this.query;
_cmd.Connection = _conn;
_cmd.Parameters.Clear();
#endregion
//Execute Command
try
{
if (cmd == Constants.Constants.ComandSelect.Select)
{
_cmd.ExecuteNonQuery();
}
if (cmd == Constants.Constants.ComandSelect.Insert)
{
_cmd.ExecuteNonQuery();
}
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex);
}
finally
{
//Close Command
_cmd.Dispose();
//Close Conn
this.mysql_connect_Close();
//Set False
this.isLoading = false;
}
}
/// <summary>
/// Retrive asynchronius a Dataset with Populate data
/// </summary>
/// <param name="_type_command"></param>
/// <returns></returns>
public async Task <DataSet> MySQL_DataReaderAsync(Constants.Constants.ComandSelect _type_command)
{
//Open Conn
this.mysql_connect_Open();
//Set Loading param
this.isLoading = true;
//Instancing DataSet
this._ds = new DataSet();
//Try
try
{
//Strings
this._cmd.CommandText = query;
//Instancing Connection
this._cmd.Connection = _conn;
if (_type_command == Constants.Constants.ComandSelect.Select)
{
this._da.SelectCommand = _cmd; //Select CMD
//Try to fill data source, throw ex
try
{
this._da.Fill(_ds);
}
catch (MySqlException mysql_ex)
{
Mainstream.NativeMethods.MessageBox(new IntPtr(0), "The excption " + mysql_ex.ToString() + "has been triggred", "A error has been trigged ", 0);
}
}
if (_type_command == Constants.Constants.ComandSelect.Update)
{
this._da.UpdateCommand = _cmd;
try
{
this._da.Update(_ds);
}
catch (MySqlException mysql_ex)
{
Mainstream.NativeMethods.MessageBox(new IntPtr(0), "The excption " + mysql_ex.ToString() + "has been triggred", "A error has been trigged ", 0);
}
}
if (_type_command == Constants.Constants.ComandSelect.Insert)
{
this._da.InsertCommand = _cmd;
}
if (_type_command == Constants.Constants.ComandSelect.Delete)
{
this._da.DeleteCommand = _cmd;
}
// this._da.Fill(_ds); //Fill
}
catch (MySqlException ex)
{
Mainstream.NativeMethods.MessageBox(new IntPtr(0), "Error", ex.ToString(), 0);
}
return(_ds);
}
/// <summary>
/// Retrive a DataSet from a mysql query with command.
/// </summary>
/// <param name="_type_command">Type of command</param>
/// <param name="parameters"> Parammeter for inserting into query</param>
/// <returns>Returns a dataset populed table </returns>
public DataSet MySQL_DataReader_With_Param(Constants.Constants.ComandSelect _type_command, Dictionary <string, string> parameters)
{
//Open Conn
this.mysql_connect_Open();
//Set Loading param
this.isLoading = true;
#region Propriedades
//Setting Proprienties
_cmd.CommandText = this.query;
_cmd.Connection = _conn;
_cmd.Parameters.Clear();
#endregion
#region Parameters
foreach (var param in parameters)
{
_cmd.Parameters.AddWithValue(param.Key, param.Value);
}
#endregion
//Try
try
{
//Strings
this._cmd.CommandText = query;
//Instancing Connection
this._cmd.Connection = _conn;
if (_type_command == Constants.Constants.ComandSelect.Select)
{
this._da.SelectCommand = _cmd; //Select CMD
this._da.Fill(_ds);
}
if (_type_command == Constants.Constants.ComandSelect.Update)
{
this._da.UpdateCommand = _cmd;
this._da.Update(_ds); //Update
}
if (_type_command == Constants.Constants.ComandSelect.Insert)
{
this._da.InsertCommand = _cmd;
}
if (_type_command == Constants.Constants.ComandSelect.Delete)
{
this._da.DeleteCommand = _cmd;
}
// this._da.Fill(_ds); //Fill
}
catch (MySqlException ex)
{
Console.WriteLine(ex);
}
finally
{
this.mysql_connect_Close();
this.isLoading = false;
this._da.Dispose();
//DeallocDataSet
this._DeallocTemporaryDataSet();
}
//Return DataSet
return(_ds);
}
/// <summary>
/// Execute a query and store on a Dataset, no parametizaded.
/// </summary>
/// <param name="cmd">type of Command</param>
/// <returns>Returns the dataset results of query</returns>
public DataSet MySQL_DataSet(Constants.Constants.ComandSelect cmd)
{
//Open Conn
this.mysql_connect_Open();
//Set Loading param
this.isLoading = true;
//Instancing DataSet
this._ds = new DataSet();
//Try
try
{
//Strings
this._cmd.CommandText = query;
//Instancing Connection
this._cmd.Connection = _conn;
if (cmd == Constants.Constants.ComandSelect.Select)
{
this._da.SelectCommand = _cmd; //Select CMD
//Try to fill data source, throw ex
try
{
this._da.Fill(_ds);
}
catch (MySqlException mysql_ex)
{
Mainstream.NativeMethods.MessageBox(new IntPtr(0), "The excption " + mysql_ex.ToString() + "has been triggred", "A error has been trigged ", 0);
}
}
if (cmd == Constants.Constants.ComandSelect.Update)
{
this._da.UpdateCommand = _cmd;
try
{
this._da.Update(_ds);
}
catch (MySqlException mysql_ex)
{
Mainstream.NativeMethods.MessageBox(new IntPtr(0), "The excption " + mysql_ex.ToString() + "has been triggred", "A error has been trigged ", 0);
}
}
if (cmd == Constants.Constants.ComandSelect.Insert)
{
this._da.InsertCommand = _cmd;
}
if (cmd == Constants.Constants.ComandSelect.Delete)
{
this._da.DeleteCommand = _cmd;
}
// this._da.Fill(_ds); //Fill
}
catch (MySqlException ex)
{
Console.WriteLine(ex);
}
finally
{
this.mysql_connect_Close();
this.isLoading = false;
this._da.Dispose();
//DeallocDataSet
this._DeallocTemporaryDataSet();
}
//Return DataSet
return(_ds);
}
/// <summary>
/// Execute a single command, to get a array of data
/// </summary>
/// <param name="parameters">The query parameters for sql instruciton</param>
/// <param name="cmd">Enumerator for Type of command</param>
/// <returns>Returns a string array from Data Reader results</returns>
public string[] MySQL_ExecuteSingleCommandWithParameters(Dictionary <string, string> parameters, Constants.Constants.ComandSelect cmd)
{
//Set Loading param
this.isLoading = true;
//Open Connection
this.mysql_connect_Open();
#region Propriedades
//Setting Proprienties
this._cmd.CommandText = this.query;
this._cmd.Connection = _conn;
this._cmd.Parameters.Clear();
#endregion
#region Parameters
foreach (var param in parameters)
{
_cmd.Parameters.AddWithValue(param.Key, param.Value);
}
#endregion
//Run Cmd and create array with loop
try
{
if (cmd == Constants.Constants.ComandSelect.Select)
{
//Instancing DR
_dr = _cmd.ExecuteReader();
//Creating string for storage data
_s = new string[_dr.FieldCount];
//Loop from DataReader
while (_dr.Read())
{
for (int i = 0; i < _dr.FieldCount; i++)
{
_s[i] = _dr.GetString(i);
}
}
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.ToString());
throw;
}
finally
{
this.mysql_connect_Close();
//Set False
this.isLoading = false;
//DataReader Check
this._dr.Close();
}
return(_s);
}
