C# Database Tutorial: Working with MySQL Database to Retrieve,Update,Insert and Delete Data – Habeeb Bioye

Hello guys! , in this tutorial, We will be working with database. And guess what, this tutorial entails or contains how to retrieve or show data, Insert data, Update record and also delete records. So if you need such tutorial in any application you are developing, then this tutorial is mainly for you. But for now we will be based on working with Show or retrieve records in a table.

To Download the pdf file for this tutorial, you can get it here

Working with other Unrelated Programmers

 Most of the people mostly working with database in their application mainly make use of MSSQL (Both the Compact and Express Edition) because it is easier to use since it is incorporated within. But perhaps, you are working with a person on an application which requires both parties working with a single DBMS and also perhaps the other programmer is not a C# programmer so he\she wants to make use of MySQL because of its popularity as a RDBMS, you will be prompted to also make use of MySQL. That is why this tutorial is made to give you better understanding on how to work with MySQL.

PDF Available here

Requirements

The applications or libraries required not elegant. We will need

•    Wamp/Xamp Server
•       MySql.Data.dll
•      Visual Studio (Any version)

Installation

For Wamp Server: Installing wamp server is as easy as installing normal application. It doesn’t require any external files for download (Simple and straight forward). But if you need guide for the download then search here .

For MySql.Data.dll: It is really essential to download this. Because without that our aim won’t be achieved.

For Visual Studio:  Any version will do. Just get it downloaded, but if you already have that IDE, then you don’t need to get it downloaded again.

Working With Wamp Server

Working with wamp server is really easy, you can either make use of the CLI or GUI(phpmyadmin) mode. You can get some tutorial on working with commands in MySQL. If you really need one, then you can comment in the comment section. Thanks!

Steps:

If you are familiar with this, then you can skip these procedures listed.

·        Run the Wamp Server   

·        Click on the wamp icon and select MySQL then MySQL Console  

·        Create Database By Using this Command    >>           Create Database databasename;

·        Use the database  >> Use databasename;

·        Create Table with this command    >>    Create table tablename (FirstName varchar(50), LastName varchar(50));

·        Insert some values to it using this command   >>   Insert into tablename VALUES (“values”, “values”);

Now we are done with the wamp server part! … We will move to the CSharp Program what you guys have been expecting

PROCEDURES:

*Get your Visual Studio working.

*Create a new project but this time we will be making use of Console Application. You can check out for that here

*Name your project and click ok

*Since your MySql.Data.dll is available on your system, at a known directory, You will add reference to that library file by navigating to your solution explorer. On the reference menu, right click and select add reference. As shown in the image below….

Since, we have included the file. We can work with our application now.

Mind you! I will comment in the code with explanation, but if you need extra explanation then you can comment below on what you don’t understand. I know you guys are doing great with this tutorial.

Also Check Out: Develop your coding skills with an online course from FutureLearn. Join for free.

The Code

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using MySql.Data.MySqlClient; //It is highly needed because without including it, Our code won't function as expected

namespace Working_with_my_sql

{

    class Program

    {

        static void Main(string[] args)

        {

            //RestartProgram();

            //Hanshaking the Connection. This is the type connection string are written for MySqlUnlike MSSQL. The default UID is root!

             MySqlConnection con = new MySqlConnection("Server=localhost; database=databasename; UID=root");

             //Note: My database server doesn't require any password.Because i didn't set it. In further classes we will talk about that!

            con.Open();

            //As Usual,we will open the connection. To gain access in to it

            //From Here.... An SQL command is needed to retrieve the data from the database. For those with better understanding of how MSSQL works, should know that.

                    MySqlCommand com = new MySqlCommand("Select * from devCTable", con);

           //Calling new instance of MySqlDataReader.

                    MySqlDataReader datareader;

            //Giving access to the datareader to retrieve data from the database by executing the SqlCommand

                    datareader = com.ExecuteReader();

            //This is not really required but for the alignment of the data, it is required!

                    Console.WriteLine("FirstName    LastName");

             //Since i am not reading just a single data, i can implicitly retrieve all the datas wi

                    while (datareader.Read())

                    {

                        //Then to display the data, This command is highly needed.Just like in MSSQL

                        Console.WriteLine(datareader[0].ToString() + "    " + datareader[1].ToString());

                    }                                   

        }

Result:

*** Don’t forget about the Console.ReadLine(); … So as to avoid the immediate close of the background

Final Note:

This marks the end of today’s tutorial. The Tutorial is also available for download. I think you all have seen a basic way to work with MySQL in our program at ease. In the upcoming tutorial we will work on Inserting, Updating and deletion of data. If you have a problem regarding this tutorial, simply comment below with your problem. Also don’t forget to share to your other friends which need the tutorial badly and we have some other e-books that can improve your skills with cool exercises.

Hint: In the future we will also be working with data online. In order not to miss that, You can simply subscribe to our sites.

We really need your review in the comment section. Thanks you.