Display a Table using MySQL databases.

In this post, I'll show you an easy way of gathering data from a table using the C# programming language and
a MySQL database.
There are several ways of connecting to a database, I'll cover other ways in future posts.

What you will need for this example to work:

1-Microsoft Visual Studio (any version 2005,2008,2010,Express).

2-MySQL Connector (it's not distributed by Microsoft). It's opensource and free.
This will let you access a MySQL database, locally or remotely from your C# application.
download it from here mysql.data.msi 10MB then INSTALL IT!.

3-Create a Windows Console Application.
(the example below uses a console application for quicker demonstration purposes).

4-Add the refference: Go to Project > Add Refference...

5- After that add the line below to the top of your code.

using MySql.Data.MySqlClient;


The database I'm using is called "test".
The Table I'm using is called "people" and looks like this.

6- The code is self-explanatory.

Make sure you put the next lines inside your MainMethod.

           //Sets a wellformed connection string (domain;database name;user;EmtyPasswordInmyCase)
           string connectionString = @"Server=;
   //Creates the MySqlConnection Object
   MySqlConnection connection = new                  

   //Creates the MySqlCommand query
   MySqlCommand command = connection.CreateCommand();
   command.CommandText = "SELECT * FROM people";

   //Creates the DataAdapter "dataAdapter"
   MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
   DataSet dataSet = new DataSet();

   //Fill the MySqlDataAdapter object "dataAdapter"
   // with data from People.
   dataAdapter.Fill(dataSet, "People");

   //Creates the DataTable object  from the previous loaded DataAdapter object
   //to manipulate the data from the People table.
   DataTable builtTable = dataSet.Tables["People"];
   foreach (DataColumn dc in builtTable.Columns)
         Console.Write(dc+" | ");


   //Loop the People Table
   foreach (DataRow row in builtTable.Rows)
         foreach (var field in row.ItemArray)
               Console.Write(field + "  |");
  }//close try
  //catches any exception related to MySql and displays it
  catch (MySqlException me)


note: I used a console application for this example, But it is always a good practice to keep the logic away from the interface. In this case the console is my interface.
It is recommended to pass the table to the interface, a Windows Form GUI for example or the console.

If you found this example useful share it with others.

TIP: If you are looking for a free web host with MySQL capabilities.
1500 MB of space.
100 GB of bandwith traffic.
and a lot more great stuff...

You are supporting this blog by using their free service if you click and register below.
thanks :) click here.

If you want to learn more about it "check my 000webhost introductory post".

1 comment: