CLR - FTP download(FTP 2탄)

UDF_DOWNLOAD.CS

udf_download.sql

김종열

MS SQL Server MVP

엔트리브 소프트

FTP로 업로드를 했다면 다운로드도 가능해야 하지 않을까요?
그러면 역시 만들면 되죠 ^^
코드나 사용법은 udf_upload와 동일한 구조로 했습니다.

간단한 c#코드, sql파일을 첨부하겠습니다.

워낙 뜸했던 지라 clr에 관해 잘 모르겠다구요?
그렇다면 복습도 할겸 해서 다시 처음부터 하나씩 열어보시면서 기억을 상기해보세요.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Collections;

using System.Collections.Generic;

using System.IO;

using System.Net;

using System.Xml;

using System.Text;

 

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlInt32 UDF_DOWNLOAD(SqlString ftp_server

                                        , SqlString ftp_user

                                        , SqlString ftp_pwd

                                        , SqlString local_file

                                        , SqlString rmt_file

                                        , SqlInt32 ftp_mode)

    {

        /* Init Vairable */

        string ftpServer = Convert.ToString(ftp_server);

        string ftpUser = Convert.ToString(ftp_user);

        string ftpPwd = Convert.ToString(ftp_pwd);

        string localFile = Convert.ToString(local_file);

        string rmtFile = Convert.ToString(rmt_file);

        int ftpMode = Convert.ToInt32(Convert.ToString(ftp_mode)); /* asc(1), bin(else) */

 

        FtpWebRequest reqFtp;

        try

        {

            FileStream outputStream = new FileStream(localFile, FileMode.Create);

            reqFtp = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + ftpServer + rmtFile));

            reqFtp.Method = WebRequestMethods.Ftp.DownloadFile;

            if (ftpMode == 1)

            {

                reqFtp.UseBinary = false;

            }

            else

            {

                reqFtp.UseBinary = true;

            }

            reqFtp.Credentials = new NetworkCredential(ftpUser, ftpPwd);

            FtpWebResponse response = (FtpWebResponse)reqFtp.GetResponse();

            Stream ftpStream = response.GetResponseStream();

 

 

            int buffersize = 2048;

            int readCount;

            byte[] buffer = new byte[buffersize];

 

            readCount = ftpStream.Read(buffer, 0, buffersize);

            while (readCount > 0)

            {

                outputStream.Write(buffer, 0, readCount);

                readCount = ftpStream.Read(buffer, 0, buffersize);

 

            }

 

            ftpStream.Close();

            outputStream.Close();

            response.Close();

 

            return 1;

        }

        catch

        {

            return 0;

        }       

       

    }

};

 

 

실행예입니다.

DECLARE @RET_down INT

SELECT @RET_down = DBO.udf_Download ('server'

                                     , 'id'

                                     , 'pwd'

                                     , 'local_save_full_path'

                                     , 'download_file_full_path'

                                     , 1)

SELECT @RET_DOWN

 

참고파일 : udf_Download.cs
             udf_download.sql