CLR-server와 통신하기

김종열

MS SQL Server MVP

 

Prologue

Procedure나 view를 통해 Database 내에 있는 정보를 Server로 주게 되고 Client에서 처리하게 되고 Client에서 처리한 결과들이 서버를 통해 Database까지 전달되면 Database에 저장하는 것이 통상적인 3-Tier의 처리 과정이라는 것은 모두들 잘 아시리라 생각합니다.

 

그런데 때로 Server에 있는 정보가 궁금했던 적은 없었나요?  악성 user를 Kickoff시키려면 어떤 과정을 거치나요? 마치 procedure를 실행시키듯 그 유저를 kickoff시키고 싶은 생각을 해 본적은 없나요? 악성 user는 몇 번 서버에 접속해 있는 상태이고 어떤 게임을 하고 있으며, 누구와 게임을 하고 있을까요?  때로는 게임 속 유저를 Trace해보고 싶지는 않나요? 실시간 랭킹을 내기 위해서는 어떻게 해야 할까요? …

 

이런 무수한 질문의 궁극은 Database에서 서버로 접속이 가능해지면 되는 것입니다. 즉 통신 프로그램을 database에 붙여 넣으면 된다는 간단한 결론입니다.

작업 (소스에 대한 설명은 하지 않겠습니다.)

1.       Echoserver 만들기

A.        역할 및 설명 : client에서 메시지를 주면 단순한 답을 주는 Demon

B.        UDP로 작성

C.        Winform_C# (visual studio 2008)

D.        .net Framework 3.5

E.         os : xp professional

F.         소스          

                                     i.    Project를 c# winform을 선택  다음과 같이 listbox를 올려놓고 objectname을 lbConnections라고 하십시오.

                                     ii.   Form1_load action에 다음과 같은 Thread를 시작하는 command를 주십시오.

private void Form1_Load(object sender, EventArgs e)

{

            thdUDPserver = new Thread(new ThreadStart(serverThread));            

            thdUDPserver.Start();

}

                                     iii.   Form1_close에서는 다음 서버의 종료를 알려주십시오.

private void Form1_FormClosed(object sender, FormClosedEventArgs e)

        {

            thdUDPserver.Abort();

            _udpClient.Close();

 

        }

                                     iv.   serverThread를 이제 작성해볼까요?                    

public void serverThread()

        {

            IPEndPoint ipep = new IPEndPoint(IPAddress.Parse("127.0.0.1"), 9999);

            _udpClient = new UdpClient(ipep);

            while (true)

            {

                IPEndPoint RemoteIpEndPoint = new IPEndPoint(IPAddress.Any, 0);

                Byte[] receiveBytes = _udpClient.Receive(ref RemoteIpEndPoint);

                string returnData = Encoding.Default.GetString(receiveBytes);

                lbConnections.Items.Add(RemoteIpEndPoint.Address.ToString() + ":" + returnData.ToString());

                if (returnData.ToString() == "Hi youly")

                {

                }

                else if (returnData.ToString() == "Ds" )

                {

 

                    string _strR = "a1^a2^a3|1^2^3|가^나^다|a1^a2^a3|1^2^3|가^나^다|a1^a2^a3|1^2^3|가^나^다";

 

 

                    receiveBytes = Encoding.Default.GetBytes(_strR);

                    _udpClient.Send(receiveBytes, receiveBytes.Length, RemoteIpEndPoint);

                   

 

 

 

 

                } else

 

                {

                    receiveBytes = Encoding.Default.GetBytes("SrvMsg " + returnData.ToString());

                    _udpClient.Send(receiveBytes, receiveBytes.Length, RemoteIpEndPoint);

 

                }

 

               

            }

        }

2.       database client만들기

A.        역할 및 설명 : echoserver에 접속하여 세가지형태의 command를 날려줌

B.        UDP로 작성

C.        Database Program with c# (visual studio 2008)

D.        .net Framework 3.5

E.         Database : mssql2008 (sp1)

F.         소스

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Net;

using System.Net.Sockets;

using System.Threading;

using System.Text;

 

 

 

public partial class StoredProcedures

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void usp_echoSrv_Hi()

    {

        UdpClient _udpClient = new UdpClient();

        _udpClient.Connect("127.0.0.1", 9999);

        Byte[] _sendByte = Encoding.Default.GetBytes("Hi youly");

        _udpClient.Send(_sendByte, _sendByte.Length);

 

    }

 

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void usp_echoSrv_show(string msg)

    {

        UdpClient _udpClient = new UdpClient();

        _udpClient.Connect("127.0.0.1", 9999);

        IPEndPoint ipe = new IPEndPoint(IPAddress.Any, 0);

 

        Byte[] _sendByte = Encoding.ASCII.GetBytes(msg);

        _udpClient.Send(_sendByte, _sendByte.Length);

 

        _sendByte = _udpClient.Receive(ref ipe);

 

        string returnData = Encoding.Default.GetString(_sendByte);

 

 

        SqlConnection _sqlConn = new SqlConnection("Context Connection=True");

        try

        {

            _sqlConn.Open();

            string strsql = "select msg = '" + returnData.ToString() + "'";

            SqlCommand _sqlCmd = new SqlCommand(strsql, _sqlConn);

            SqlContext.Pipe.ExecuteAndSend(_sqlCmd);

 

 

           

        }

        catch (SqlException e)

        {

            SqlContext.Pipe.Send(e.Message.ToString());

        }

        finally

        {

            _sqlConn.Close();

        }

    }

 

 

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void usp_echoSrv_ds(string msg)

    {

        UdpClient _udpClient = new UdpClient();

        _udpClient.Connect("127.0.0.1", 9999);

        IPEndPoint ipe = new IPEndPoint(IPAddress.Any, 0);

 

        Byte[] _sendByte = Encoding.ASCII.GetBytes(msg);

        _udpClient.Send(_sendByte, _sendByte.Length);

 

        _sendByte = _udpClient.Receive(ref ipe);

 

        string returnData = Encoding.Default.GetString(_sendByte);

        string[] arr_1 = returnData.Split('|');

        string[] arr_2 = arr_1[0].Split('^');

 

        string strsql = "";

        string strQry = "";

 

        for (int i = 0; i <= arr_1.Length - 1; i++)

        {

            arr_2 = arr_1[i].Split('^');

            strsql = "";

            for (int j = 0; j <= arr_2.Length - 1; j++)

            {

                strsql += "a_" + j.ToString() + " = '" + arr_2[j].ToString() + "'";

                    if (j != arr_2.Length-1)

                    {

                        strsql += ",";

                    }

            }

            strQry  += " select " + strsql;

            if (i != arr_1.Length - 1)

            {

                strQry += " union all ";

 

            }           

        }

 

 

        SqlConnection _sqlConn = new SqlConnection("Context Connection=True");

        try

        {

            _sqlConn.Open();

            SqlCommand _sqlCmd = new SqlCommand(strQry, _sqlConn);

            SqlContext.Pipe.ExecuteAndSend(_sqlCmd);

 

        }

        catch (SqlException e)

        {

            SqlContext.Pipe.Send(e.Message.ToString());

        }

        finally

        {

            _sqlConn.Close();

        }

    }

};

좀 긴가요? 이 procedure는 세가지 command 구조를 실행하는 각각의 procedure 입니다.

 

배포를 하신 다음 echoServer를 실행하세요. SSMS를 실행한 다음 쿼리를 실행하면 만든 것들에 대한 결과를 볼 수 있습니다.

쿼리 및 실행결과	Echo server Capture
use youlydb go     exec usp_echoSrv_Hi /* 명령이완료되었습니다. */
use youlydb go   exec usp_echoSrv_show 'sqlleader, sqlleader' /* msg --------------------------- SrvMsg sqlleader, sqlleader   (1개행이영향을받음) */ --제가 두 번 실행을 해서 두번찍혀있네요
use youlydb go   exec usp_echoSrv_ds 'Ds' /* a_0  a_1  a_2 ---- ---- ---- a1   a2   a3 1    2    3 가   나   다 a1   a2   a3 1    2    3 가   나   다 a1   a2   a3 1    2    3 가   나   다   (9개행이영향을받음) */

Epilogue

말은 거창하게 한 거 같지만 너무 미미한 프로그램을 보여드린 것은 아닌지 모르겠습니다. 이런 접근이 Active한 서비스를 그리고 살아있는 따근한 정보를 다룰 수 있게 하는 계기가 되었으면 합니다.