본문 바로가기
C# 개인 공부 기록용/개인 실습

XML + ORACLE DB + SQL QUERY

둥둥다랑 부리부리 2022. 7. 13.

TABLE

CREATE TABLE USER_TEST(
USER_IDX NUMBER NOT NULL PRIMARY KEY,
USER_NAME VARCHAR2(18),
USER_BIRTHDAY VARCHAR2(10),
USER_GENDER VARCHAR2(10),
USER_AGE NUMBER,
USER_CELLPHONE VARCHAR2(11),
USER_ADDR VARCHAR2(81)
);

CREATE SEQUENCE USER_IDX

 

XML CLASS (bin\Debug [ Application.StartupPath + @"\Oracle_Info.xml" ])

<?xml version="1.0" encoding="utf-8" ?>
<oracle_info>
	<ip>아이피 주소</ip>
	<service>서비스</service>
	<id>아이디</id>
	<pw>패스워드</pw>
</oracle_info>

Oracle_Info.xml

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms; // 추가
using System.Xml; // 추가

namespace XML_ORACLE_INFO____SQL_QUERY
{
    class Xml_Information
    {
        private string ip;
        private string service;
        private string id;
        private string pw;

        public void xml_fn()
        {
            try
            {
                XmlDocument xdoc = new XmlDocument();
                string url = Application.StartupPath + @"\Oracle_Info.xml";
                xdoc.Load(url);

                XmlNodeList root = xdoc.SelectNodes("oracle_info");

                foreach (XmlNode xn in root)
                {
                    this.ip = xn["ip"].InnerText;
                    this.service = xn["service"].InnerText;
                    this.id = xn["id"].InnerText;
                    this.pw = xn["pw"].InnerText;
                }
            }
            catch (Exception ex)
            {
                string Error = "Xml Error";
                MessageBox.Show(Error);
            }
        }

        // IP
        public string ip_xml
        {
            get
            {
                return ip;
            }
            set
            {
                ip = value;
            }
        }

        // SERVICE
        public string service_xml
        {
            get
            {
                return service;
            }
            set
            {
                service = value;
            }
        }

        // ID
        public string id_xml
        {
            get
            {
                return id;
            }
            set
            {
                id = value;
            }
        }

        // PASSWORD
        public string pw_xml
        {
            get
            {
                return pw;
            }
            set
            {
                pw = value;
            }
        }
    }
}

 

ORACLE CLASS (참조 추가(R)... → Oracle.DataAccess)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client; // 추가
using System.Windows.Forms; // 추가

namespace XML_ORACLE_INFO____SQL_QUERY
{
    class Oracle_Information
    {
        private OracleConnection conn;
        private Xml_Information xi = new Xml_Information();

        private string ip;
        private string service;
        private string id;
        private string pw;

        public void oracle_info()
        {
            xi.xml_fn();
            ip = xi.ip_xml;
            service = xi.service_xml;
            id = xi.id_xml;
            pw = xi.pw_xml;

            try
            {
                String connstr = "data source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = " + ip + ")" +
                    "(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = " + service + ")));USER ID=" + id + ";PASSWORD=" + pw + ";";

                conn = new OracleConnection(connstr);
                conn.Open();
            }
            catch (Exception ex)
            {
                string Error = "Oracle Error";
                MessageBox.Show(Error);
            }
        }

        // OracleConnection
        public OracleConnection oiconn
        {
            get
            {
                return conn;
            }
            set
            {
                conn = value;
            }
        }
    }
}

 

SELECT + SQL QUERY

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.DataAccess.Client; // Oracle 사용
using System.Xml; // Xml 사용

namespace XML_ORACLE_INFO____SQL_QUERY
{
    public partial class Xml_Oracle_Info : Form
    {
        private Oracle_Information oi = new Oracle_Information();
        private OracleCommand cmd;
        private DataSet ds;
        private OracleDataAdapter oda;

        public Xml_Oracle_Info()
        {
            InitializeComponent();
        }

        private void BTN_Execution_Click(object sender, EventArgs e)
        {
            try
            {
                oi.oracle_info();

                cmd = new OracleCommand();
                cmd.Connection = oi.oiconn;
                cmd.CommandText = SQL_Query_TB.Text; // SQL_Query 텍스트 박스
                string SQL_Q = cmd.CommandText;

                if (SQL_Q.Substring(0, 6) == "select" || SQL_Q.Substring(0, 6) == "SELECT")
                {
                    cmd.ExecuteReader();

                    ds = new DataSet();

                    oda = new OracleDataAdapter(SQL_Q, oi.oiconn);
                    oda.Fill(ds);

                    RESULT.DataSource = ds.Tables[0];
                }
                else
                {
                    cmd.ExecuteNonQuery();
                }

                oi.oiconn.Close();
            }
            catch
            {
                MessageBox.Show("Error");
                SQL_Query_TB.Clear();
            }
        }

        private void BTN_SELECT_Click(object sender, EventArgs e)
        {
            try
            {
                oi.oracle_info();

                cmd = new OracleCommand();
                cmd.Connection = oi.oiconn;
                cmd.CommandText = "SELECT * FROM USER_TEST ORDER BY USER_IDX ASC";
                cmd.ExecuteReader();

                ds = new DataSet();

                oda = new OracleDataAdapter(cmd.CommandText, oi.oiconn);
                oda.Fill(ds);

                RESULT.DataSource = ds.Tables[0];

                oi.oiconn.Close();
            }
            catch
            {
                MessageBox.Show("Error");
            }

        }

        private void BTN_INSERT_Click(object sender, EventArgs e)
        {
            this.Visible = false; // 현재 폼 안보이게 하기
            INSERT_FORM frm = new INSERT_FORM(); // 새 폼 생성
            frm.Owner = this; // 새 폼의 오너를 현재 폼으로
            frm.Show(); // 새폼 보여주기
        }

        private void BTN_UPDATE_DELETE_Click(object sender, EventArgs e)
        {
            this.Visible = false; // 현재 폼 안보이게 하기
            UPDATE_DELETE_FORM frm = new UPDATE_DELETE_FORM(); // 새 폼 생성
            frm.Owner = this; // 새 폼의 오너를 현재 폼으로
            frm.Show(); // 새폼 보여주기
        }
    }
}

실행 시 첫 화면
SELECT BUTTON
SQL QUERY + Execution BUTTON
RESULT : TEST가 삭제 되었다 (USER_IDX = 10)

 

INSERT FORM

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.DataAccess.Client; // Oracle 사용

namespace XML_ORACLE_INFO____SQL_QUERY
{
    public partial class INSERT_FORM : Form
    {
        private Oracle_Information oi = new Oracle_Information();
        private OracleCommand cmd;

        private string name;
        private string bd;
        private string gender;
        private int age;
        private string cp;
        private string addr;

        public INSERT_FORM()
        {
            InitializeComponent();
        }

        private void BTN_INSERT_Click(object sender, EventArgs e)
        {
            try
            {
                gender = string.Empty;

                if (M_RB.Checked)
                {
                    gender = "남자";
                }
                else if (W_RB.Checked)
                {
                    gender = "여자";
                }

                name = NAME_TB.Text;
                bd = BIRTHDAY_TB.Text;
                age = Int32.Parse(AGE_UD.Value.ToString());
                cp = CELLPHONE_TB.Text;
                addr = ADDRESS_TB.Text;

                if (name == string.Empty || bd == string.Empty || age == 0 || cp == string.Empty || addr == string.Empty || gender == string.Empty)
                {
                    MessageBox.Show("공백없이 입력해주세요");
                    return;
                }

                oi.oracle_info();

                cmd = new OracleCommand();
                cmd.Connection = oi.oiconn;
                cmd.CommandText = "INSERT INTO USER_TEST VALUES(USER_IDX.NEXTVAL,'" + name + "','" + bd + "','" + gender + "'," + age + ",'" + cp + "','" + addr + "')";
                cmd.ExecuteNonQuery();

                MessageBox.Show("입력되었습니다.");

                oi.oiconn.Close();

                NAME_TB.Clear();
                BIRTHDAY_TB.Clear();
                AGE_UD.Value = 0;
                CELLPHONE_TB.Clear();
                ADDRESS_TB.Clear();

            }
            catch (Exception ex)
            {
                MessageBox.Show("Error");
            }
        }

        private void BTN_BACK_Click(object sender, EventArgs e)
        {
            this.Visible = false; // 현재 폼 안보이게 하기
            Xml_Oracle_Info frm = new Xml_Oracle_Info(); // 새 폼 생성
            frm.Owner = this; // 새 폼의 오너를 현재 폼으로
            frm.Show(); // 새폼 보여주기
        }
    }
}

INSERT BUTTON - INSERT FORM
공백이 있을 경우 INSERT 되지 않는다
공백없이 전부 입력하면 INSERT 된다
RESULT

 

UPDATE & DELETE

UPDATE

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.DataAccess.Client; // Oracle 사용

namespace XML_ORACLE_INFO____SQL_QUERY
{
    public partial class UPDATE_DELETE_FORM : Form
    {
        private Oracle_Information oi = new Oracle_Information();
        private OracleCommand cmd;
        private DataSet ds;
        private OracleDataAdapter oda;

        private string name;
        private string bd;
        private string gender;
        private int age;
        private string cp;
        private string addr;

        public UPDATE_DELETE_FORM()
        {
            InitializeComponent();
        }

        private void UPDATE_DELETE_FORM_Load(object sender, EventArgs e)
        {
            NAME_TB.Enabled = false;
            BIRTHDAY_TB.Enabled = false;
            M_RB.Enabled = false;
            W_RB.Enabled = false;
            AGE_UD.Enabled = false;
            CELLPHONE_TB.Enabled = false;
            ADDRESS_TB.Enabled = false;
        }

        private void BTN_SEARCH_Click(object sender, EventArgs e)
        {
            try
            {
                if (IDX_SEARCH_TB.Text == string.Empty)
                {
                    MessageBox.Show("IDX를 입력해주세요");
                    return;
                }

                BTN_UPDATE.Enabled = true;
                NAME_TB.Enabled = true;
                BIRTHDAY_TB.Enabled = true;
                M_RB.Enabled = true;
                W_RB.Enabled = true;
                AGE_UD.Enabled = true;
                CELLPHONE_TB.Enabled = true;
                ADDRESS_TB.Enabled = true;

                oi.oracle_info();

                string IDX_SEARCH_Q = "SELECT * FROM USER_TEST WHERE USER_IDX =" + IDX_SEARCH_TB.Text + "";

                IDX_SEARCH_TB.Enabled = false;
                BTN_SEARCH.Enabled = false;

                cmd = new OracleCommand(IDX_SEARCH_Q, oi.oiconn);
                OracleDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    NAME_TB.Text = reader.GetString(1);

                    BIRTHDAY_TB.Text = reader.GetString(2);

                    if (reader.GetString(3) == "여자")
                    {
                        W_RB.Checked = true;

                    }
                    else if (reader.GetString(3) == "남자")
                    {
                        M_RB.Checked = true;

                    }

                    AGE_UD.Value = reader.GetInt32(4);

                    CELLPHONE_TB.Text = reader.GetString(5);

                    ADDRESS_TB.Text = reader.GetString(6);
                }

                MessageBox.Show("검색이 완료되었습니다");
            }
            catch (Exception ex)
            {
                IDX_SEARCH_TB.Enabled = true;
                BTN_SEARCH.Enabled = true;
                NAME_TB.Enabled = false;
                BIRTHDAY_TB.Enabled = false;
                M_RB.Enabled = false;
                W_RB.Enabled = false;
                AGE_UD.Enabled = false;
                CELLPHONE_TB.Enabled = false;
                ADDRESS_TB.Enabled = false;
                BTN_UPDATE.Enabled = false;

                MessageBox.Show("Error");
            }
        }

        private void BTN_UPDATE_Click(object sender, EventArgs e)
        {
            try
            {
                gender = string.Empty;

                if (M_RB.Checked)
                {
                    gender = "남자";
                }
                else if (W_RB.Checked)
                {
                    gender = "여자";
                }

                name = NAME_TB.Text;
                bd = BIRTHDAY_TB.Text;
                age = Int32.Parse(AGE_UD.Value.ToString());
                cp = CELLPHONE_TB.Text;
                addr = ADDRESS_TB.Text;

                oi.oracle_info();

                string UPDATE_Q = "UPDATE USER_TEST SET " +
                    "USER_NAME = '" + name + "',USER_BIRTHDAY = '" + bd + "',USER_GENDER = '" + gender + "', " +
                    "USER_AGE =" + age + ",USER_CELLPHONE ='" + cp + "', USER_ADDR = '" + addr + "' WHERE USER_IDX =" + IDX_SEARCH_TB.Text + "";

                cmd = new OracleCommand(UPDATE_Q, oi.oiconn);
                cmd.ExecuteNonQuery();

                MessageBox.Show("수정이 완료되었습니다");

                oi.oiconn.Close();

                IDX_SEARCH_TB.Enabled = true;
                BTN_SEARCH.Enabled = true;

                IDX_SEARCH_TB.Clear();
                NAME_TB.Clear();
                BIRTHDAY_TB.Clear();
                AGE_UD.Value = 0;
                CELLPHONE_TB.Clear();
                ADDRESS_TB.Clear();

                NAME_TB.Enabled = false;
                BIRTHDAY_TB.Enabled = false;
                M_RB.Enabled = false;
                W_RB.Enabled = false;
                AGE_UD.Enabled = false;
                CELLPHONE_TB.Enabled = false;
                ADDRESS_TB.Enabled = false;
                BTN_UPDATE.Enabled = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error");
            }
        }

        private void BTN_DELETE_Click(object sender, EventArgs e)
        {
            try
            {
                oi.oracle_info();

                string DELETE_Q = "DELETE FROM USER_TEST WHERE USER_IDX =" + DELETE_TB.Text + "";

                cmd = new OracleCommand(DELETE_Q, oi.oiconn);
                cmd.ExecuteNonQuery();

                MessageBox.Show("IDX " + DELETE_TB.Text + "의 정보가 삭제되었습니다");

                oi.oiconn.Close();

                DELETE_TB.Clear();
            }
            catch (Exception ex)
            {
                DELETE_TB.Clear();
                MessageBox.Show("Error");
            }
        }

        private void BTN_BACK_Click(object sender, EventArgs e)
        {
            this.Visible = false; // 현재 폼 안보이게 하기
            Xml_Oracle_Info frm = new Xml_Oracle_Info(); // 새 폼 생성
            frm.Owner = this; // 새 폼의 오너를 현재 폼으로
            frm.Show(); // 새폼 보여주기
        }

    }
}

UPDATE & DELETE BUTTON -&nbsp;UPDATE & DELETE FORM
IDX Search = 18 - SEARCH BUTTON
NAME = 자바 / ADDRESS = Oracle 변경
UPDATE BUTTON
RESULT

 

DELETE

IDX DELETE = 18
DELETE BUTTON
RESULT

저작자표시 비영리 변경금지

'C# 개인 공부 기록용 > 개인 실습' 카테고리의 다른 글

fpSpread Practice (SpreadSheet) + Xml + Oracle  (0) 2022.07.21
DataGridView + Selected (Cell, Row)  (0) 2022.07.18
Oracle DB Login Form + SQL QUERY (OracleConnection + SQL QUERY)  (0) 2022.07.11
Oracle DB Login Form (OracleConnection)  (0) 2022.07.08
숫자 퀴즈 게임 (Number Quiz Game)  (0) 2022.07.03

'C# 개인 공부 기록용/개인 실습' 관련글

티스토리툴바