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!
