12/21

MySQL 실습

C#에서 사용 (NuGet 통해서 Mysql.data 사용)

Program.cs

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

namespace LearnDB
{
    class Program
    {
        static void Main(string[] args)
        {
            DBManager db = new DBManager();

            db.Connect("root", "1234");

            db.Run();
        }
    }
}

DBManager.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace LearnDB
{
    class DBManager
    {
        const string _serverName = "localhost";
        const string _dbName = "gamedb";
        const int _port = 3306;

        MySqlConnection _connect;
        string _userID;
        string _userPW;
        bool _isConnection;

        public void Connect(string id, string pw)
        {
            _userID = id;
            _userPW = pw;
            string connectionString = $"Server={_serverName};Port={_port};Database={_dbName};Uid={_userID};Pwd={_userPW}";

            using (_connect = new MySqlConnection(connectionString))
            {
                _isConnection = true;
            }
        }

        public void Run()
        {
            int selectMenu = 0;
            while(_isConnection)
            {
                Console.WriteLine("9.LevelUpdateTest");
                Console.WriteLine("999.종료");
                Console.Write("메뉴를 선택하세요. : ");
                if(int.TryParse(Console.ReadLine(), out selectMenu))
                {
                    switch (selectMenu)
                    {
                        case 9:
                            LevelUpdateTest();
                            break;
                        case 999:
                            _isConnection = false;
                            break;
                        default:
                            Console.WriteLine("잘못된 메뉴 번호입니다.");
                            break;
                    }
                }
                else
                {
                    Console.WriteLine("번호를 입력해주세요.");
                }
            }
        }

        #region [Query Building]
        private void UpdateSingle(string table, string col, string val, string id)
        {
            string query = $"UPDATE `{_dbName}`.`{table}` SET `{col}` =  '{val}' WHERE `id` = '{id}';";

            try
            {
                _connect.Open();
                MySqlCommand command = new MySqlCommand(query, _connect);
                if(command.ExecuteNonQuery() == 1)
                {
                    Console.WriteLine("UpdateSingle 성공");
                }
                else
                {
                    Console.WriteLine("UpdateSingle 실패");
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine($"UpdateSingle 실패 : {ex.ToString()}");
            }
            _connect.Close();
        }

        #endregion  [Query Building]

        #region [Sample]
        void LevelUpdateTest()
        {
            string input_id;
            int input_val;

            Console.Write("변경할 유저 아이디를 입력하세요. : ");
            input_id = Console.ReadLine();

            Console.Write("변경할 유저 레벨을 입력하세요. : ");
            while (true)
            {
                if(int.TryParse(Console.ReadLine(), out input_val))
                {
                    break;
                }
                else
                {
                    Console.WriteLine("숫자로 다시 입력해주세요.");
                }
            }

            UpdateSingle("user", "level", input_val.ToString(), input_id);
        }
        #endregion [Sample]
    }
}