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!

Thanks dude, it's exactly what I've been looking for :cool:

awesome, glad i was able to help someone out.

I'm glad you got it helpful. Please do not resurrect old threads. If you have any questions please ask. You are welcome to start your own threads.

Thread Closed.

This article has been dead for over six months. Start a new discussion instead.