C# DataBase(Mysql, Maria) 접속, Select/Execute Query

Mysql(Maria) 데이터베이스 접속, 접속종료, 쿼리수행

• Select 쿼리 수행 후 DataTable로 결과 받기
• Insert, Update, Delete 쿼리 수행
• Insert, Update, Delete 쿼리 트랜잭션 수행(여러개 동시 수행)

 

MySql.Data 설치

• 도구 - NuGet 패키지 관리자 > 솔루션용 NuGet 패키지 관리... > mysql검색 후 설치

설치확인

 

MariaUtil.cs

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

namespace SrcTree.Util
{
    class MariaUtil
    {
        private MySqlConnection dbConnection;
        private static MariaUtil mariaUtil = new MariaUtil();
        public static MariaUtil instance {
            get {
                return mariaUtil;
            }
        }

        public MariaUtil() {
        }

        //open
        public int open() {
            if(isOpen()) {
                close();
            }

            int iret = 0;

            //각 서버의 설정에 맞게 수정
            String connectionString = "Server=mysqlserver.co.kr;Port=3306;Database=srctreeDB;Uid=adimn;Pwd=pass;Charset=utf8";
            dbConnection = new MySqlConnection(connectionString);
            try {
                iret = 1;
                dbConnection.Open();
                Console.Write("마리아DB 오픈완료");
            }
            catch (Exception e) {
                Console.Write("DB 오픈 : " + e.Message.ToString());
                close();
                iret = -1;

            }
            return iret;
        }

        //open여부
        public Boolean isOpen() {
            if (dbConnection != null) return true;
            else return false;
        }

        //close
        public int close() {
            int iret = 1;
            try {
                if (dbConnection != null) {
                    dbConnection.Close();                    
                    Console.WriteLine("DB 클로즈");
                }
            }
            catch(Exception e) {
                iret = -1;
            }
            dbConnection = null;
            return iret;
        }

        //insert, update, delete => 성공한 row개수 리턴(1: 성공)
        public int run(String sql) {
            int iret = 0;            
            try {
                Console.WriteLine("Maria DB 연결 중...");
                //connection.Open();
                MySqlCommand mySqlCommand = new MySqlCommand(sql, dbConnection);
                iret = mySqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex) {
                Console.WriteLine(ex.ToString());
            }
            return iret;
        }

        //insert, update, delete transaction => 성공한 row개수 리턴(1이상: 성공)
        public int runs(String[] sqls) {
            int iret = 0;

            MySqlCommand cmd = dbConnection.CreateCommand();
            MySqlTransaction tran = dbConnection.BeginTransaction();

            cmd.Connection = dbConnection;
            cmd.Transaction = tran;
            if (dbConnection == null || tran == null) iret = -2;
            else {
                try {
                    for (int i = 0; i < sqls.Length; i++)
                    {
                        cmd.CommandText = sqls[i];
                        iret += cmd.ExecuteNonQuery();//정상이면 1 리턴
                    }                    
                    tran.Commit();
                    Console.WriteLine("마리아DB 커밋 완료 :");
                }
                catch (Exception e)
                {
                    Console.WriteLine("DB 트랜잭션 : " + e.Message.ToString());
                    tran.Rollback();
                    iret = -1;
                }
            }

            return iret;
        }

        //select 쿼리 수행 -> DataTable 리턴
        public DataTable select(String sql) {
            var mySqlDataTable = new DataTable();
            try {
                MySqlCommand mySqlCommand = new MySqlCommand(sql, dbConnection);
                MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
                mySqlDataTable.Load(mySqlDataReader);

                StringBuilder output = new StringBuilder();
                foreach (DataColumn col in mySqlDataTable.Columns) {
                    output.AppendFormat("{0} ", col);
                }
                output.AppendLine();
                foreach (DataRow page in mySqlDataTable.Rows) {
                    foreach (DataColumn col in mySqlDataTable.Columns) {
                        output.AppendFormat("{0} ", page[col]);
                    }
                    output.AppendLine();

                }
                Console.WriteLine(output.ToString());

                mySqlDataReader.Close();
            }
            catch (Exception ex) {
                Console.WriteLine(ex.ToString());
            }
            return mySqlDataTable;
        }
    }
}

 

사용

//open
if(MariaUtil.instance.open() == 1) {
    MessageBoxEx.Show(this, "오픈 성공");
}
else {
    MessageBoxEx.Show(this, "오픈 실패");
}

//close
if (MariaUtil.instance.close() == 1) {
    MessageBoxEx.Show(this, "닫기 성공");
}
else {
    MessageBoxEx.Show(this, "닫기 실패");
}

//select
if (MariaUtil.instance.isOpen()) {
    String query = "select * from tbl_test";
    DataTable dt = MariaUtil.instance.select(query);
    dataGridView1.DataSource = dt;//DataGridView dataGridView1;
}
else {
    MessageBoxEx.Show(this, "데이터베이스가 연결되있지 않습니다.");
}

//insert
if (MariaUtil.instance.isOpen()) {
    String query = String.Format("INSERT INTO tbl_test (field1, field2, create_dt) VALUES ('{0}', '{1}', NOW())", textBox1.Text, textBox2.Text);
    if(MariaUtil.instance.run(query) == 1) {
        MessageBoxEx.Show(this, "Insert 성공");
    }
    else {
        MessageBoxEx.Show(this, "Insert 실패");
    }
}
else {
    MessageBoxEx.Show(this, "데이터베이스가 연결되있지 않습니다.");
}