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".











1 comment: