C# - 닷넷 응용 프로그램에서 MySQL(MySqlConnector) 사용

예전에 PHP를 통해 MySQL을 사용하는 방법을 소개했었는데요.

.NET 개발자가 처음 해보는 PHP + MySQL 연동
; https://www.sysnet.pe.kr/2/0/1058

DB(Schema): mytestdb
테이블: mytable
        칼럼:  id int NOT NULL
               name varchar(50)
               age int
               description varchar(150) 

Download Connector/Net
; http://dev.mysql.com/downloads/connector/net/

// https://www.nuget.org/packages/MySqlConnector/

Install-Package MySqlConnector

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

namespace ConsoleApplication1
{
    class Program
    {
        private void CreateMySqlTable(MySqlConnection connection)
        {
            string query = "CREATE TABLE IF NOT EXISTS mytable(";
            query += " id INT NOT NULL, ";
            query += " NAME VARCHAR(50) NOT NULL PRIMARY KEY, ";
            query += " age INT NOT NULL, ";
            query += " DESCRIPTION VARCHAR(150) NOT NULL)";

            MySqlCommand createCommand = new MySqlCommand();
            createCommand.Connection = connection;
            createCommand.CommandText = query;

            createCommand.ExecuteNonQuery();
        }

        static void Main(string[] args)
        {
            using (MySqlConnection myConnection =
                new MySqlConnection(@"Data Source=192.168.0.11;Database=mytestdb;User Id=mytest;Password=mytest@2008"))
            {
                myConnection.Open();

                CreateMySqlTable(myConnection);

                // Create
                MySqlCommand insertCommand = new MySqlCommand();
                insertCommand.Connection = myConnection;
                insertCommand.CommandText = "INSERT INTO mytable(id, NAME, age, DESCRIPTION) VALUES (@id, @NAME, @age, @DESCRIPTION)";

                insertCommand.Parameters.Add("@id", MySqlDbType.Int32, 50);
                insertCommand.Parameters.Add("@NAME", MySqlDbType.VarChar, 50);
                insertCommand.Parameters.Add("@age", MySqlDbType.Int32, 50);
                insertCommand.Parameters.Add("@DESCRIPTION", MySqlDbType.VarChar, 150);

                string nameValue = "Name" + Guid.NewGuid().ToString();
                insertCommand.Parameters[0].Value = 0;
                insertCommand.Parameters[1].Value = nameValue;
                insertCommand.Parameters[2].Value = 10;
                insertCommand.Parameters[3].Value = nameValue + "_Description";

                int affected = insertCommand.ExecuteNonQuery();
                Console.WriteLine("# of affected row: " + affected);

                // Update
                MySqlCommand updateCommand = new MySqlCommand();
                updateCommand.Connection = myConnection;
                updateCommand.CommandText = "UPDATE mytable SET DESCRIPTION=@DESCRIPTION WHERE NAME=@NAME";

                updateCommand.Parameters.Add("@NAME", MySqlDbType.VarChar, 50);
                updateCommand.Parameters.Add("@DESCRIPTION", MySqlDbType.VarChar, 150);

                updateCommand.Parameters[0].Value = nameValue;
                updateCommand.Parameters[1].Value = nameValue + "_Description2";

                affected = updateCommand.ExecuteNonQuery();
                Console.WriteLine("# of affected row: " + affected);

                // Select - ExecuteScalar
                MySqlCommand selectCommand = new MySqlCommand();
                selectCommand.Connection = myConnection;
                selectCommand.CommandText = "SELECT count(*) FROM mytable";

                object result = selectCommand.ExecuteScalar();
                Console.WriteLine("# of records: " + result);

                // Select - DataTable
                DataSet ds = new DataSet();
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM mytable", myConnection);
                da.Fill(ds, "mytable");

                DataTable dt = ds.Tables["mytable"];
                foreach (DataRow dr in dt.Rows)
                {
                    Console.WriteLine(string.Format("Name = {0}, Desc = {1}", dr["NAME"], dr["DESCRIPTION"]));
                }

                // Delete
                MySqlCommand deleteCommand = new MySqlCommand();
                deleteCommand.Connection = myConnection;
                deleteCommand.CommandText = "DELETE FROM mytable WHERE NAME=@NAME";

                deleteCommand.Parameters.Add("@NAME", MySqlDbType.VarChar, 50);
                deleteCommand.Parameters[0].Value = nameValue;

                affected = deleteCommand.ExecuteNonQuery();
                Console.WriteLine("# of affected row: " + affected);
            }
        }
    }
}

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql
ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -u root -p
Enter password: