private void B_BDD_SQL_execute_Click(object sender, EventArgs e)
{
//nettoit completement la DGV
clear_SQL_DGV(DGV_GestionBDD_SQL_resultat);
//remplie la DGV
string requete = (string)CB_BDD_SQL_command.SelectedItem + " " + TB_BDD_SQL_column.Text + " " + L_BDD_SQL_from.Text + " " + (string)CB_BDD_SQL_table.SelectedItem + " " + L_GestionBDD_SQL_set_values.Text + " " + TB_GestionBDD_SQL_set_values.Text;
if (TB_BDD_SQL_condition.Text != "")
{
requete += " " + L_BDD_SQL_where.Text + " " + TB_BDD_SQL_condition.Text;
}
requete = requete.Split(';')[0];
try
{
switch ((string)CB_BDD_SQL_command.SelectedItem)
{
case "SELECT": Variable_Compte.Connection.Open();
Lecture = new MySqlCommand(requete, Variable_Compte.Connection);
Resultat = Lecture.ExecuteReader();
DataTable schemaTable = Resultat.GetSchemaTable();
for (int C = 0; C < schemaTable.Rows.Count; C++)
{
int emplacement = DGV_GestionBDD_SQL_resultat.Columns.Add(C.ToString(), schemaTable.Rows[C].ItemArray[0].ToString());
DGV_GestionBDD_SQL_resultat.Columns[emplacement].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
}
string[] row = new string[DGV_GestionBDD_SQL_resultat.Columns.Count];
while (Resultat.Read())
{
for (int i = 0; i < DGV_GestionBDD_SQL_resultat.Columns.Count; i++)
{
row[i] = Resultat[DGV_GestionBDD_SQL_resultat.Columns[i].HeaderText].ToString();
}
DGV_GestionBDD_SQL_resultat.Rows.Add(row);
}
Variable_Compte.Connection.Close();
label_NB_Ligne_Requete.Visible = true;
label_NB_Ligne_Requete.Text = DGV_GestionBDD_SQL_resultat.RowCount.ToString() + " lignes trouvées";
break;
case "UPDATE": Variable_Compte.Connection.Open();
Lecture = new MySqlCommand(requete, Variable_Compte.Connection);
Resultat = Lecture.ExecuteReader();
Variable_Compte.Connection.Close();
label_NB_Ligne_Requete.Visible = false;
MessageBox.Show("Base de données mise à jour.", "Opération efectuée");
break;
case "DELETE": Variable_Compte.Connection.Open();
Lecture = new MySqlCommand(requete, Variable_Compte.Connection);
Resultat = Lecture.ExecuteReader();
Variable_Compte.Connection.Close();
label_NB_Ligne_Requete.Visible = false;
MessageBox.Show("Base de données mise à jour.", "Opération efectuée");
break;
case "INSERT": Variable_Compte.Connection.Open();
ecriture = new MySqlCommand(requete, Variable_Compte.Connection);
ecriture.ExecuteNonQuery();
Variable_Compte.Connection.Close();
label_NB_Ligne_Requete.Visible = false;
MessageBox.Show("Base de données mise à jour.", "Opération efectuée");
break;
default: MessageBox.Show("Veuillez selectionner un ordre à exécuter sur la base de données.", "Opération annulée");
break;
}
}
catch (Exception ex)
{
MessageBox.Show("Une erreur s'est produite, veuillez vérifier votre requête SQL." + Environment.NewLine + ex.Message, "Opération annulée");
Variable_Compte.Connection.Close();
}
// permet de resize la DGV en fonction du nombre de ligne
if (40 + (DGV_GestionBDD_SQL_resultat.RowCount) * 22 < 254)
{
DGV_GestionBDD_SQL_resultat.Height = 40 + (DGV_GestionBDD_SQL_resultat.RowCount) * 22;
}
else
{
DGV_GestionBDD_SQL_resultat.Height = 254;
}
DGV_GestionBDD_SQL_resultat.CurrentCell.Selected = false;
}
/// <summary>
/// MySqlDataReader转换成DataTable
/// </summary>
/// <param name="dataReader"></param>
/// <returns></returns>
public static DataTable GetNewDataTable(MySqlDataReader dataReader)
{
DataTable datatable = new DataTable();
DataTable schemaTable = dataReader.GetSchemaTable();
//动态添加列
try
{
foreach (DataRow myRow in schemaTable.Rows)
{
DataColumn myDataColumn = new DataColumn();
myDataColumn.DataType = myRow.GetType();
myDataColumn.ColumnName = myRow[0].ToString();
datatable.Columns.Add(myDataColumn);
}
//添加数据
while (dataReader.Read())
{
DataRow myDataRow = datatable.NewRow();
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
myDataRow[i] = dataReader[i].ToString();
}
datatable.Rows.Add(myDataRow);
myDataRow = null;
}
schemaTable = null;
dataReader.Close();
return datatable;
}
catch (Exception ex)
{
throw new Exception("转换出错出错!", ex);
}
}
/*
* fill _dbData from the specified MySqlDataReader position
* Fetch the schema for the current table if we don't already have it (we should have)
*/
private bool _readerFill(MySqlDataReader reader)
{
if (!(_dbSchemas.ContainsKey(this._dbTable))) {
_dbSchemas[this._dbTable] = reader.GetSchemaTable();
}
this._dbData.Clear ();
for (int i = 0; i < reader.FieldCount; i++) {
this._dbData.Add (reader.GetName (i), reader[reader.GetName (i)]);
}
return true;
}