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;

note:

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.

try

       {

           //Sets a wellformed connection string (domain;database name;user;EmtyPasswordInmyCase)

           string connectionString = @"Server=127.0.0.1;

                                            Database=test;

                                            Uid=root;

                                            Pwd=;

                                            ";

   //Creates the MySqlConnection Object

   MySqlConnection connection = new                  
   MySqlConnection(connectionString);

   //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"];

               

   //PRINTS THE COLUMNS TITLES

   foreach (DataColumn dc in builtTable.Columns)

         Console.Write(dc+" | ");

   //A NEW LINE

   Console.WriteLine("");

   //Loop the People Table

   foreach (DataRow row in builtTable.Rows)

    {

         foreach (var field in row.ItemArray)

           {

               Console.Write(field + "  |");

           }

          Console.WriteLine("");

    }

  }//close try

  //catches any exception related to MySql and displays it

  catch (MySqlException me)

      {

         Console.WriteLine(me.Message);

      }

           

      //end

      Console.WriteLine("FINISHED.");

      Console.ReadKey();

FINISHED

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.
MySQL + PHP
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".