I Made Krisna's Times My Way to Write the Times

MySQL Query with C# ADO.NET

This post is using MySQL .NET Connector for connecting .NET Framework with MySQL Database Management System, so if you're looking for OBDC connection that's not here but you still can learn this because this much easier than ODBC Connection. Please look at my previous post for preparing your Visual Studio to be able connect with MySQL [How to Connect MySQL Database with C# ADO.NET]

Directly to the code, leave the default library using and add two additional lines below the defaults.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.Common;
using MySql.Data.MySqlClient;

Now the main part, creating ADO.NET connection for MySQL Database. I've found two ways that's very similar but the result for programme portability is very different. First way is using Database Factories and the second way is using directly MySQL Connection object from the reference which was added before (MySql.Data.dll)

Here is the example code using Database Factories

        static void connectUsingFactory()
        {
            DbProviderFactory factory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
            DbConnection connection = factory.CreateConnection();
            connection.ConnectionString = "server=10.151.34.31;User Id=adonet;password=adonet;Persist Security Info=True;database=adonet";
            connection.Open();

            DbCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM item";

            DbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader["itmid"]+"\t"+reader[1]+"\t"+reader[2]+"\t"+reader["price"]);
            }
            reader.Close();
            connection.Close();
        }

And the second way, using MySQL Connection object

        static void connectUsingReference()
        {
            DbConnection connection =
                new MySqlConnection("server=10.151.34.31;User Id=adonet;password=adonet;Persist Security Info=True;database=adonet");
            connection.Open();

            DbCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM item";

            DbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader["itmid"] + "\t" + reader[1] + "\t" + reader[2] + "\t" + reader["price"]);
            }
            reader.Close();
            connection.Close();
        }

And the last is the main method for testing the codes above...

        static void Main(string[] args)
        {
            connectUsingFactory();
            Console.WriteLine();
            connectUsingReference();
            Console.ReadKey();
        }

The first way, using Database Factories is easier if we didn't know the Database Connection Class or if we're using the connector that the default ADO.NET Driver already support it because we just insert the namespace for parameter. But the first way didn't work if you're using MySQL Database .NET Connector when the computer that you're use didn't installed by the connector or only using non-installer connector. So we must use second way to make the project portable.

Download the complete Project example here : MySQLNET Connection VS2008 Project