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

Xml + Oracle + Sql + StringBuilder

둥둥다랑 부리부리 2022. 8. 3.

XML CLASS

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

namespace TEST
{
    class Xml_Class
    {
        private string id;
        private string pw;
        private string sv;
        private string ip;
        string[] Oracle_Data = new string[4];

        public void Xml_Create_Fn()
        {
            try
            {
                // Oracle Login Data
                id = "아이디";
                pw = "패스워드";
                sv = "서비스 이름";
                ip = "아이피 주소";

                // Xml 파일 생성
                XmlWriterSettings xml_set = new XmlWriterSettings();
                xml_set.Indent = true;
                xml_set.NewLineOnAttributes = true;

                XmlWriter xml_writer = XmlWriter.Create(Application.StartupPath + @"\Oracle_Login_Data.xml");
                xml_writer.WriteStartDocument();
                xml_writer.WriteStartElement("Oracle_Data");
                xml_writer.WriteElementString("ID", id);
                xml_writer.WriteElementString("PW", pw);
                xml_writer.WriteElementString("SV", sv);
                xml_writer.WriteElementString("IP", ip);
                xml_writer.WriteEndDocument();
                xml_writer.Flush();
                xml_writer.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Xml Create Error");
            }
        }

        public string[] Xml_Load_Fn()
        {
            try
            {
                // Xml 파일 불러오기
                if (File.Exists(Application.StartupPath + @"\Oracle_Login_Data.xml"))
                {
                    XmlDocument xml_doc = new XmlDocument();
                    string xml_file_url = Application.StartupPath + @"\Oracle_Login_Data.xml";
                    xml_doc.Load(xml_file_url);

                    XmlNodeList xml_node_list = xml_doc.SelectNodes("Oracle_Data");
                    foreach (XmlNode xml_node in xml_node_list)
                    {
                        Oracle_Data[0] = xml_node["ID"].InnerText;
                        Oracle_Data[1] = xml_node["PW"].InnerText;
                        Oracle_Data[2] = xml_node["SV"].InnerText;
                        Oracle_Data[3] = xml_node["IP"].InnerText;
                    }
                }
                return Oracle_Data;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Xml Load Error");
                return Oracle_Data;
            }
        }
    }
}

 

ORACLE CLASS

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

namespace TEST
{
    class Oracle_Class
    {
        Xml_Class xc = new Xml_Class();

        string id;
        string pw;
        string sv;
        string ip;
        OracleConnection conn;
        StringBuilder conn_str = new StringBuilder();

        public void Oracle_Conn_Fn()
        {
            try
            {
                if (!File.Exists(Application.StartupPath + @"\Oracle_Login_Data.xml"))
                {
                    xc.Xml_Create_Fn();
                }
                string[] Oracle_Data;
                Oracle_Data = xc.Xml_Load_Fn();

                id = Oracle_Data[0];
                pw = Oracle_Data[1];
                sv = Oracle_Data[2];
                ip = Oracle_Data[3];

                conn_str.Clear();
                conn_str.AppendFormat("data source = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)");
                conn_str.AppendFormat("(HOST = {0})(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = {1})));USER ID={2};PASSWORD={3};", ip, sv, id, pw);
                // conn_str = "data source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = " + ip + ")" +
                // "(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = " + sv + ")));USER ID=" + id + ";PASSWORD=" + pw + ";";

                conn = new OracleConnection(conn_str.ToString());
                conn.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("OracleConnection Error");
            }
        }

        public OracleConnection Get_Conn
        {
            get { return conn; }
        }
    }
}

 

BASIC 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;
using FarPoint.Win.Spread;

namespace TEST
{
    public partial class Basic_Form : Form
    {
        Oracle_Class oc = new Oracle_Class();
        StringBuilder sqlstr = new StringBuilder();
        OracleCommand cmd;
        DataSet ds;
        OracleDataAdapter oda;

        public Basic_Form()
        {
            InitializeComponent();
        }

        private void BasicForm_Load(object sender, EventArgs e)
        {
            Basic_Load_Fn();
            fpSpread1.Sheets[0].OperationMode = FarPoint.Win.Spread.OperationMode.SingleSelect;
        }
        public void Basic_Load_Fn()
        {
            try
            {
                oc.Oracle_Conn_Fn();

                sqlstr.Clear(); // sqlstr = new StringBuilder();
                sqlstr.AppendFormat("SELECT * FROM ");
                sqlstr.AppendFormat("TEST_SUBWAY ");
                sqlstr.AppendFormat("ORDER BY ");
                sqlstr.AppendFormat("SUBWAY_NUMBER ");
                sqlstr.AppendFormat("ASC");

                cmd = new OracleCommand(sqlstr.ToString(), oc.Get_Conn);
                cmd.ExecuteReader();

                ds = new DataSet();

                oda = new OracleDataAdapter(sqlstr.ToString(), oc.Get_Conn);
                oda.Fill(ds);

                fpSpread1.DataSource = ds.Tables[0];

                fpSpread1_Sheet1.Columns[0, 2].Width = 365;
                for (int i = 0; i < fpSpread1.ActiveSheet.ColumnCount; i++)
                {
                    fpSpread1.ActiveSheet.Columns[i].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;
                    fpSpread1.ActiveSheet.Columns[i].VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;
                }

                oc.Get_Conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Basic Form Load Error");
            }
        }

        private void Btn_Align_Click(object sender, EventArgs e)
        {
            fpSpread1.Sheets[0].OperationMode = FarPoint.Win.Spread.OperationMode.SingleSelect;

            try
            {
                string asc = "SELECT * FROM TEST_SUBWAY ORDER BY SUBWAY_NUMBER ASC";
                string desc = "SELECT * FROM TEST_SUBWAY ORDER BY SUBWAY_NUMBER DESC";


                if (sqlstr.ToString().Equals(asc))
                {
                    sqlstr.Clear();
                    sqlstr = sqlstr.AppendFormat(desc);
                }
                else if (sqlstr.ToString().Equals(desc))
                {
                    sqlstr.Clear();
                    sqlstr = sqlstr.AppendFormat(asc);
                }
                else
                {
                    sqlstr.Clear();
                    sqlstr = sqlstr.AppendFormat(asc);
                }

                oc.Oracle_Conn_Fn();

                cmd = new OracleCommand(sqlstr.ToString(), oc.Get_Conn);
                cmd.ExecuteReader();

                ds = new DataSet();

                oda = new OracleDataAdapter(sqlstr.ToString(), oc.Get_Conn);
                oda.Fill(ds);

                fpSpread1.DataSource = ds.Tables[0];

                fpSpread1_Sheet1.Columns[0, 2].Width = 365;
                for (int i = 0; i < fpSpread1.ActiveSheet.ColumnCount; i++)
                {
                    fpSpread1.ActiveSheet.Columns[i].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;
                    fpSpread1.ActiveSheet.Columns[i].VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Align Error");
            }
        }

        private void Btn_Search_Click(object sender, EventArgs e)
        {
            try
            {
                oc.Oracle_Conn_Fn();
                int number_Search = 0;

                if (Tb_Search.Text.Equals(string.Empty))
                {
                    MessageBox.Show("검색할 내용을 입력해주세요.");
                    return;
                }
                else if (int.TryParse(Tb_Search.Text, out number_Search))
                {
                    sqlstr.Clear();
                    sqlstr.AppendFormat("SELECT * FROM TEST_SUBWAY ");
                    sqlstr.AppendFormat("WHERE SUBWAY_NUMBER = {0}", Tb_Search.Text);

                    cmd = new OracleCommand(sqlstr.ToString(), oc.Get_Conn);
                    cmd.ExecuteReader();

                    ds = new DataSet();

                    oda = new OracleDataAdapter(sqlstr.ToString(), oc.Get_Conn);
                    oda.Fill(ds);

                    fpSpread1.DataSource = ds.Tables[0];

                    fpSpread1_Sheet1.Columns[0, 2].Width = 365;
                    for (int i = 0; i < fpSpread1.ActiveSheet.ColumnCount; i++)
                    {
                        fpSpread1.ActiveSheet.Columns[i].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;
                        fpSpread1.ActiveSheet.Columns[i].VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;
                    }
                }
                else
                {
                    sqlstr.Clear();
                    sqlstr.AppendFormat("SELECT * FROM TEST_SUBWAY ");
                    sqlstr.AppendFormat("WHERE SUBWAY_NAME = '{0}'", Tb_Search.Text);

                    cmd = new OracleCommand(sqlstr.ToString(), oc.Get_Conn);
                    cmd.ExecuteReader();

                    ds = new DataSet();

                    oda = new OracleDataAdapter(sqlstr.ToString(), oc.Get_Conn);
                    oda.Fill(ds);

                    fpSpread1.DataSource = ds.Tables[0];

                    fpSpread1_Sheet1.Columns[0, 2].Width = 365;
                    for (int i = 0; i < fpSpread1.ActiveSheet.ColumnCount; i++)
                    {
                        fpSpread1.ActiveSheet.Columns[i].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;
                        fpSpread1.ActiveSheet.Columns[i].VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;
                    }
                }

                oc.Get_Conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Search Error");
            }
        }

        private void Btn_Insert_Click(object sender, EventArgs e)
        {
            Insert_Update_Form iuf = new Insert_Update_Form();
            iuf.Owner = this;
            iuf.Form_Change = true;
            iuf.ShowDialog();
        }

        private void Btn_Update_Click(object sender, EventArgs e)
        {
            int row_num = fpSpread1.ActiveSheet.ActiveRowIndex;
            string selected_sub_num = fpSpread1.ActiveSheet.Cells[row_num, 0].Value.ToString();

            Insert_Update_Form iuf = new Insert_Update_Form();
            iuf.Owner = this;
            iuf.Form_Change = false;
            iuf.sub_num = selected_sub_num;
            iuf.ShowDialog();
        }

        private void Btn_Delete_Click(object sender, EventArgs e)
        {
            int row_num = fpSpread1.ActiveSheet.ActiveRowIndex;
            string selected_sub_num = fpSpread1.ActiveSheet.Cells[row_num, 0].Value.ToString();
            string selected_sub_name = fpSpread1.ActiveSheet.Cells[row_num, 1].Value.ToString();
            string selected_sub_line = fpSpread1.ActiveSheet.Cells[row_num, 2].Value.ToString();

            sqlstr.Clear();
            sqlstr.AppendFormat("Number : {0}\n", selected_sub_num);
            sqlstr.AppendFormat("Name : {0}\n", selected_sub_name);
            sqlstr.AppendFormat("Line : {0}\n", selected_sub_line);
            sqlstr.AppendFormat("해당 데이터를 삭제하시겠습니까?");

            if (MessageBox.Show(sqlstr.ToString(), "DELETE FORM", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                    oc.Oracle_Conn_Fn();

                    sqlstr.Clear();
                    sqlstr.AppendFormat("DELETE FROM TEST_SUBWAY ");
                    sqlstr.AppendFormat("WHERE SUBWAY_NUMBER = {0}", selected_sub_num);

                    cmd = new OracleCommand(sqlstr.ToString(), oc.Get_Conn);
                    cmd.ExecuteNonQuery();

                    MessageBox.Show("삭제되었습니다.");

                    oc.Get_Conn.Close();

                    Basic_Load_Fn();
            }
        }

        private void Btn_Excel_Save_Click(object sender, EventArgs e)
        {
            try
            {
                SaveFileDialog saveFile = new SaveFileDialog();
                saveFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);

                saveFile.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
                saveFile.FilterIndex = 1;

                if (saveFile.ShowDialog() == DialogResult.OK)
                {
                    fpSpread1.SaveExcel(saveFile.FileName, FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders);
                    MessageBox.Show("저장이 완료 되었습니다.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Excel Save Error");
            }
        }

        private void Btn_Excel_Load_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog openFile = new OpenFileDialog();
                openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);

                openFile.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
                openFile.FilterIndex = 1;

                if (openFile.ShowDialog() == DialogResult.OK)
                {
                    fpSpread1.OpenExcel(openFile.FileName);
                    MessageBox.Show("파일을 불러왔습니다.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Excel Load Error");
            }
        }
    }
}

 

INSERT & UPDATE 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;

namespace TEST
{
    public partial class Insert_Update_Form : Form
    {
        protected internal bool Form_Change;
        protected internal string sub_num;

        Oracle_Class oc = new Oracle_Class();
        StringBuilder sqlstr = new StringBuilder();
        OracleCommand cmd;
        OracleDataReader reader;

        string num;
        string name;
        string line;

        public Insert_Update_Form()
        {
            InitializeComponent();
        }

        private void Insert_Update_Form_Load(object sender, EventArgs e)
        {
            try
            {
                if (Form_Change)
                {
                    Lb_Title.Text = "INSERT FORM";

                    Tb_Number.Clear();
                    Tb_Name.Clear();
                    Tb_Line.Clear();
                }
                else if (!Form_Change)
                {
                    Lb_Title.Text = "UPDATE FORM";
                    Tb_Number.Enabled = false;

                    oc.Oracle_Conn_Fn();

                    sqlstr.AppendFormat("SELECT * FROM ");
                    sqlstr.AppendFormat("TEST_SUBWAY ");
                    sqlstr.AppendFormat("WHERE SUBWAY_NUMBER = {0}", sub_num);

                    cmd = new OracleCommand(sqlstr.ToString(), oc.Get_Conn);
                    reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        Tb_Number.Text = reader.GetInt32(0).ToString();
                        Tb_Name.Text = reader.GetString(1);
                        Tb_Line.Text = reader.GetString(2);
                    }

                    oc.Get_Conn.Close();
                }
            }
            catch (Exception ex)
            {
                if (Form_Change)
                {
                    MessageBox.Show("Insert Form Load Error");
                }
                else if (!Form_Change)
                {
                    MessageBox.Show("Update Form Load Error");
                }
            }
        }

            private void Btn_Save_Click(object sender, EventArgs e)
            {
                num = Tb_Number.Text;
                name = Tb_Name.Text;
                line = Tb_Line.Text;

                try
                {
                    if (Form_Change)
                    {
                        if (num.Equals(string.Empty) || name.Equals(string.Empty) || line.Equals(string.Empty))
                        {
                            MessageBox.Show("빈칸 없이 입력해주세요.");
                            return;
                        }

                        oc.Oracle_Conn_Fn();

                        sqlstr.Clear();
                        sqlstr.AppendFormat("SELECT * FROM TEST_SUBWAY ");
                        sqlstr.AppendFormat("WHERE SUBWAY_NUMBER = {0}", num);

                        cmd = new OracleCommand(sqlstr.ToString(), oc.Get_Conn);
                        reader = cmd.ExecuteReader();

                        if (reader.Read())
                        {
                            MessageBox.Show("이미 존재하는 역사번호입니다.\n다른 번호를 입력해주세요.");
                            Tb_Number.Clear();
                            return;
                        }

                        sqlstr.Clear();
                        sqlstr.AppendFormat("INSERT INTO TEST_SUBWAY VALUES ");
                        sqlstr.AppendFormat("({0}, '{1}', '{2}')", num, name, line);

                        cmd = new OracleCommand(sqlstr.ToString(), oc.Get_Conn);
                        cmd.ExecuteNonQuery();

                        MessageBox.Show("등록되었습니다!");

                        ((Basic_Form)this.Owner).Basic_Load_Fn();

                        Tb_Number.Clear();
                        Tb_Name.Clear();
                        Tb_Line.Clear();

                        oc.Get_Conn.Close();
                    }
                    else if (!Form_Change)
                    {
                        oc.Oracle_Conn_Fn();

                        sqlstr.Clear();
                        sqlstr.AppendFormat("UPDATE TEST_SUBWAY SET ");
                        sqlstr.AppendFormat("SUBWAY_NUMBER = {0},", num);
                        sqlstr.AppendFormat("SUBWAY_NAME = '{0}',", name);
                        sqlstr.AppendFormat("SUBWAY_LINE = '{0}'", line);
                        sqlstr.AppendFormat("WHERE SUBWAY_NUMBER = {0}", num);

                        cmd = new OracleCommand(sqlstr.ToString(), oc.Get_Conn);
                        cmd.ExecuteNonQuery();

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

                        ((Basic_Form)this.Owner).Basic_Load_Fn();

                        oc.Get_Conn.Close();

                        Insert_Update_Form.ActiveForm.Close();
                    }
                }
                catch (Exception ex)
                {
                    if (Form_Change)
                    {
                        MessageBox.Show("Insert Form Error");
                    }
                    else if (!Form_Change)
                    {
                        MessageBox.Show("Update Form Error");
                    }
                }
            }
        }
    }

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

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

Login Form(Feat. Button)  (0) 2022.08.18
Preference (Settings) + Xml (아이디 저장? 데이터 저장?)  (0) 2022.08.02
SpreadSheet + Excel + Oracle + Xml (Synthesis Test)  (0) 2022.07.26
fpSpread Practice (SpreadSheet) + Xml + Oracle  (0) 2022.07.21
DataGridView + Selected (Cell, Row)  (0) 2022.07.18

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

티스토리툴바