C# 소켓 서버 클라이언트를 통한 DB 검색 (3)

DB에 name 과 tel 의 컬럼이 있고, Rows 이 저장되어 있음.

서버는 소켓 연결을 통해 DB와 클라이언트간의 통신을 이루게 해주고

클라이언트는 insert, update, select, delete를 간단하게 해볼수 있다.

update시 이름을 통해서만 각 컬럼 수정이 가능하다.

단순 테스트 용이므로 수많은 예외처리는 구현되어 있지 않다.

 

* Server

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Net;
using System.Net.Sockets;
using System.IO;
using System.Threading;
using ServerDll;

namespace ServerTest
{

    public partial class Form1 : Form
    {
        private Socket ServerSocket;
        private Socket ClientSocket;
        private NetworkStream stream;
        private StreamReader reader = null;
        private StreamWriter writer = null;
        private Thread ServerThread;

        private SqlCommand Comm;
        private string qry = "DELETE solever1 WHERE name = @name or tel = @tel";
        private Serverdll con;

        public Form1()
        {

            InitializeComponent();
            //Socket ServerSocket = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);
            con = new Serverdll();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {

                if (ServerSocket != null)
                {
                    MessageBox.Show("현재 서버 가동중");
                    return;
                }

                ServerThread = new Thread(new ThreadStart(thread_run));
                ServerThread.IsBackground = true;
                ServerThread.Start();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }

        public void thread_run()
        {
            ServerSocket = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);
            IPEndPoint ep = new IPEndPoint(IPAddress.Loopback, 35000);
            ServerSocket.Bind(ep);
            ServerSocket.Listen(10);

            Check();
        }

        public void Check()
        {
            try
            {
                ClientSocket = ServerSocket.Accept();

                stream = new NetworkStream(ClientSocket);
                reader = new StreamReader(stream);
                writer = new StreamWriter(stream);

                while (true)
                {
                    string readBuf = reader.ReadLine();
                    string[] infor = readBuf.Split('^');
                    string name = infor[0].ToString();
                    string tel = infor[1].ToString();
                    string what = infor[2].ToString();
                    string change = infor[3].ToString();
                    string sqr = infor[4].ToString();

                    if (readBuf != null && readBuf.Length > 0)
                    {
                        if (what == "select")
                        {
                            Comm = new SqlCommand("SELECT tel FROM solever1 WHERE name = @name", con.returnCon());
                            Comm.Parameters.Add("@name", SqlDbType.VarChar, 12);
                            Comm.Parameters["@name"].Value = name;

                            SqlDataReader sdr = Comm.ExecuteReader();
                           
                            while (sdr.Read())
                            {
                                writer.WriteLine(sdr.GetString(0));
                                writer.Flush();
                            }

                            sdr.Close();
                        }

                        else if (what == "delete")
                        {
                            Comm = new SqlCommand("DELETE solever1 WHERE name = @name or tel = @tel", con.returnCon());
                            Comm.Parameters.Add("@name", SqlDbType.VarChar, 12);
                            Comm.Parameters.Add("@tel", SqlDbType.VarChar, 15);
                            Comm.Parameters["@name"].Value = name;
                            Comm.Parameters["@tel"].Value = tel;

                            SqlDataReader sdr = Comm.ExecuteReader();

                            //while (sdr.Read())
                            //{
                            //    writer.WriteLine(sdr.GetString(0));
                            //    writer.Flush();
                            //}

                            sdr.Close();
                        }
                        else if (what == "insert")
                        {
                            Comm = new SqlCommand("INSERT INTO solever1 VALUES(@name, @tel)", con.returnCon());
                            Comm.Parameters.Add("@name", SqlDbType.VarChar, 12);
                            Comm.Parameters.Add("@tel", SqlDbType.VarChar, 15);
                            Comm.Parameters["@name"].Value = name;
                            Comm.Parameters["@tel"].Value = tel;

                            SqlDataReader sdr = Comm.ExecuteReader();

                            sdr.Close();
                        }
                        else if (what == "update")
                        {
                            Comm = new SqlCommand(sqr, con.returnCon());
                            Comm.Parameters.Add("@name", SqlDbType.VarChar, 12);
                            Comm.Parameters.Add("@upname", SqlDbType.VarChar, 12);
                            Comm.Parameters.Add("@uptel", SqlDbType.VarChar, 15);
                            Comm.Parameters["@name"].Value = name;
                            Comm.Parameters["@upname"].Value = change;
                            Comm.Parameters["@uptel"].Value = change;

                            SqlDataReader sdr = Comm.ExecuteReader();

                            sdr.Close();

                        }
                   
                    }
                    else
                    {
                        return;
                    }
                       
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (writer != null)
                writer.Close();
            if (reader != null)
                reader.Close();
            if (ClientSocket != null)
                ClientSocket.Close();
            if (ServerSocket != null)
            {
                ServerSocket.Close();
                ServerThread.Abort();
            }
              
        }

    }
}

 

* Client

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Net;
using System.Net.Sockets;
using System.IO;
using System.Threading;

namespace ClientTest
{
    public partial class Form1 : Form
    {
        private Socket ClientSocket;
        private Thread ClientThread;
        private NetworkStream stream;
        private StreamReader reader = null;
        private StreamWriter writer = null;
        private int portNum = 35000;

 

        public Form1()
        {
            InitializeComponent();
        }

        private void connect()
        {
                ClientSocket = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);
                IPEndPoint ep = new IPEndPoint(IPAddress.Loopback, portNum);
                ClientSocket.Connect(ep);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                stream = new NetworkStream(ClientSocket);
                reader = new StreamReader(stream);
                writer = new StreamWriter(stream);

                string name = textBox1.Text;
                string tel = textBox2.Text;
                string select = "select";
                string hap = name + "^" + tel + "^" + select + "^" + " " + "^" + " ";
                string writeBuf = string.Concat(hap);

                writer.WriteLine(hap);
                writer.Flush();

                string readBuf = reader.ReadLine();
                textBox2.Text = readBuf;

                reader.Close();
                writer.Close();
                stream.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                if (ClientSocket != null)
                {
                    MessageBox.Show("현재 연결중");
                    return;
                }

                ClientThread = new Thread(new ThreadStart(connect));
                ClientThread.IsBackground = true;
                ClientThread.Start();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (writer != null)
                writer.Close();
            if (reader != null)
                reader.Close();
            if (ClientSocket != null)
                ClientSocket.Close();
            if (ClientSocket != null)
            {
                ClientSocket.Close();
                ClientThread.Abort();
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                stream = new NetworkStream(ClientSocket);
                reader = new StreamReader(stream);
                writer = new StreamWriter(stream);

                string name = textBox3.Text;
                string tel = textBox4.Text;
                string insert = "insert";
                string hap = name + "^" + tel + "^" + insert + "^" + " " + "^" + " ";
                string writeBuf = string.Concat(hap);

                writer.WriteLine(writeBuf);
                writer.Flush();
               
                reader.Close();
                writer.Close();
                stream.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            try
            {
                stream = new NetworkStream(ClientSocket);
                reader = new StreamReader(stream);
                writer = new StreamWriter(stream);

                string name = textBox5.Text;
                string tel = textBox6.Text;
                string delete = "delete";
                string hap = name + "^" + tel + "^" + delete + "^" + " " + "^" + " " ;
                string writeBuf = string.Concat(hap);

                writer.WriteLine(writeBuf);
                writer.Flush();

                reader.Close();
                writer.Close();
                stream.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            try
            {
                stream = new NetworkStream(ClientSocket);
                reader = new StreamReader(stream);
                writer = new StreamWriter(stream);

                string name = textBox7.Text;
                string tel = textBox8.Text;
                string upname = textBox9.Text;
                string uptel = textBox10.Text;
                string update = "update";

                if (radioButton1.Checked == true)
                {
                    string hap = name + "^" + tel + "^" + update + "^" + upname + "^" + "UPDATE solever1 SET name = @upname WHERE name = @name";
                    string writeBuf = string.Concat(hap);

                    writer.WriteLine(writeBuf);
                    writer.Flush();

                    reader.Close();
                    writer.Close();
                    stream.Close();

                }
                else if (radioButton2.Checked == true)
                {
                    string hap = name + "^" + tel + "^" + update + "^" + uptel + "^" + "UPDATE solever1 SET tel = @uptel WHERE name = @name";
                    string writeBuf = string.Concat(hap);

                    writer.WriteLine(writeBuf);
                    writer.Flush();

                    reader.Close();
                    writer.Close();
                    stream.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

    }
}