HOWTO: Run a Query on a Database using ODBC and return all Results into a DGV Object

Okay, so currently i am working on a final project at my college, i have been tasked with creating a database administration tool.

One of my major hurdles was how to query the MySQL database and return all of the results into a DataGridView object with correct column names.

As of right now it will not use the correct data types, all data inputed into the cells will be assumed default textboxcell, but i am working on that and maybe my application will contain a future update that take into account the data type of the cell.

but here we go, this is only how to do it. i have not included any information on how to contact your server as i assume you have some prior C# knowledge of connecting to sql based servers.

please note that i am working in visual studio C# express and that i am using .NET Framework 2.0

/*
     a possible query to run on the database
*/
     //using System.Data.Odbc;
     OdbcCommand odbcCom = new OdbcCommand();
     odbcCom.Connection = <some odbcConnection to SQL server>;
     odbcCom.Command = "select distinct user, host, grant_priv from mysql.user where usere like '%foo%' and grant_priv = 'N';";
     OdbcDataReader odbcRead = odbcCom.ExecutReader();

dgvBuildMe.Columns.Clear();
/*
     Read the Query into the DGV
*/
     DataGridViewColumn colHead;//create the column
     DataGridTextBoxColumn colStyle = new DataGridTextBoxColumn();  //apply the default textbox column style
     DataGridViewCell cell = new DataGridViewTextBoxCell(); //apply the default cell style
     int intDgvCount = odbcRead.FieldCount; //set the counter to the number of fields
     int intDgvMin = 0; //starting point
     while (intDgvMin >= 0 && intDgvMin < intDgvCount)
     {
          colHead = new DataGridViewColumn();  //establish new column
          colHead.Name = odbcRead.GetName(intDgvMin) as string; //get the correct name for the column
          colHead.CellTemplate = cell; //apply the cell template to use
          dgvBuildMe.Columns.Add(colHead); //add the collumns
          intDgvMin++;
     }
     intDgvMin = 0; //reset counter
     string[] holder = new string[intDgvCount]; //establish a holder for the query
     while (odbcRead.Read())
     {
         while (intDgvMin >= 0 && intDgvMin < intDgvCount)
         {
              holder[intDgvMin] = odbcRead.GetString(intDgvMin); //build the row off of the current data.
              if (intDgvMin == (intDgvCount - 1))
              {
                   dgvBuildMe.Rows.Add(holder); //if it is the last item in the current row then go ahead and add the holder
              }
         intDgvMin++;
        }
        intDgvMin = 0;
     }

i really hope that this helps someone as much as it has helped me. this should return the results of a query no matter how large the query is. 100 columns or a 1000 rows it shouldnt matter.

i have not tested it on that scale yet but it should work!