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; }