📚mysql

http://www.csharpstudy.com/Practical/Prac-mysql.aspx

Setting

Mysql 사용하기 위해선 해당 dll을 참조해야 함.

C:\Program Files (x86)\MySQL\Connector NET 8.0\Assemblies\net6.0

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

namespace test
{
    class Program
    {
        static void Main(string[] args)
        {
            string strConn = "Server=localhost;Database=sakila;Uid=root;Pwd=1234;";
            MySqlConnection conn = null;
            DataSet ds = new DataSet();

            using (conn = new MySqlConnection(strConn))
            {
                // connected.
            }

            conn.Close();
        }
    }
}

Read Data

Connection Oriented

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


namespace test
{
    class Program
    {
        static void Main(string[] args)   
        {
            string strConn = "Server=localhost;Database=sakila;Uid=root;Pwd=1234;";
            MySqlConnection conn = null;

            using (conn = new MySqlConnection(strConn))
            {
                conn.Open();
                string sql = "SELECT * FROM actor limit 10";
                
                // using MySqlDataReader for connection mode
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                // cmd.CommandText(sql);
                MySqlDataReader dr = cmd.ExecuteReader();
                
                while (dr.Read())
                {
                    Console.WriteLine(
                        string.Format("{0, -15} {1, -15} {2, -15} {3, -15}"
                            , dr["actor_id"]
                            , dr["first_name"]
                            , dr["last_name"]
                            , dr["last_update"]));
                }
                dr.Close();
            }
            conn.Close();
        }
    }
}

• 순차접근 / 한번만 순회.

• 메모리 효율 좋음.

• MysqlDataReader close() 로 마무리 해줘야 함.

Non-Connection Oriented

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


namespace test
{
    class Program
    {
        static void Main(string[] args)
        {
            string strConn = "Server=localhost;Database=sakila;Uid=root;Pwd=1234;";
            MySqlConnection conn = null;
            DataSet ds = new DataSet();

            using (conn = new MySqlConnection(strConn))
            {
                string sql = "SELECT * FROM actor limit 10";
                
                // using MySqlDataAdapter for non-connection mode
                MySqlDataAdapter adpt = new MySqlDataAdapter(sql, conn);
                adpt.Fill(ds, "actor");

                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    Console.WriteLine(
                    string.Format("{0, -15} {1, -15} {2, -15} {3, -15}"
                            , r["actor_id"]
                            , r["first_name"]
                            , r["last_name"]
                            , r["last_update"]));
                }
            }

            conn.Close();
        }
    }
}

• 쿼리에 의한 데이트를 통으로 들고옴 -> 메모리 효율(많은량의 메모리 사) 떨어짐.

• 랜덤접근 가능.

• 데이터베이스로 다시 업데이트 가능.

Result

결과는 동일.

1               PENELOPE        GUINESS         2006-02-15 오전 4:34:33
2               NICK            WAHLBERG        2006-02-15 오전 4:34:33
3               ED              CHASE           2006-02-15 오전 4:34:33
4               JENNIFER        DAVIS           2006-02-15 오전 4:34:33
5               JOHNNY          LOLLOBRIGIDA    2006-02-15 오전 4:34:33
6               BETTE           NICHOLSON       2006-02-15 오전 4:34:33
7               GRACE           MOSTEL          2006-02-15 오전 4:34:33
8               MATTHEW         JOHANSSON       2006-02-15 오전 4:34:33
9               JOE             SWANK           2006-02-15 오전 4:34:33
10              CHRISTIAN       GABLE           2006-02-15 오전 4:34:33

Insert Data

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

namespace test
{
    class Program
    {
        static void Main(string[] args)
        {
            string strConn = "Server=localhost;Database=sakila;Uid=root;Pwd=1234;";
            MySqlConnection conn = null;

            using (conn = new MySqlConnection(strConn))
            {
                conn.Open();
                string sql = "insert into actor (first_name, last_name, last_update) values ('aaa', 'bbb', now())";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.ExecuteNonQuery(); 
            }
            conn.Close();
        }
    }
}

Update Data

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

namespace test
{
    class Program
    {
        static void Main(string[] args)
        {
            string strConn = "Server=localhost;Database=sakila;Uid=root;Pwd=1234;";
            MySqlConnection conn = null;

            using (conn = new MySqlConnection(strConn))
            {
                conn.Open();
                string sql = "update actor set first_name='ccc' where actor_id=201";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }

            conn.Close();
        }
    }
}

Delete Data

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

namespace test
{
    class Program
    {
        static void Main(string[] args)
        {
            string strConn = "Server=localhost;Database=sakila;Uid=root;Pwd=1234;";
            MySqlConnection conn = null;

            using (conn = new MySqlConnection(strConn))
            {
                conn.Open();
                string sql = "delete from actor where actor_id=202";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
            
            conn.Close();
        }
    }
}