sftp 连接 下载

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.Globalization;
using System.IO;
using MyFTPHelper;
using System.Diagnostics;
using System.Runtime.InteropServices;
using System.Threading;
using MESComponet;
using MESDBConnections;
using MESFORM;
using System.Data.OleDb;
using Oracle.ManagedDataAccess;
namespace TestMyFTP
{
    public partial class Form1 : Form
    {
        MESDBConnections.DBTools dbtools;
        MESComponet.ExecutionResult exeRes;

        public Form1()
        {
            dbtools = new MESDBConnections.DBTools();//数据u库访问工u具La
            exeRes = new MESComponet.ExecutionResult();//访问结果G
            InitializeComponent();
        }
        SFTPHelper sftp;
        string ftpWeb = "b2b-qa.sandisk.com:3080";
        string root = "//SSD_UPC_and_SCC_DrivesListing";
        string ftpUserName = "qss-sftp";
        string ftpPassWord = "zkBCdF91";
        string CUST_PN = "";
        string SSD_TODAY = "";
        string SSD_YESTERDAY = "";
        string CHANGE_FLAG = "";
        string UPC = "";
        string PACK_OUT = "";
        string SCC14_CODE = "";
        string sCUST_PN = "";
        string sSSD_TODAY = "";
        string sSSD_YESTERDAY = "";
        string sCHANGE_FLAG = "";
        string sUPC = "";
        string sPACK_OUT = "";
        string sSCC14_CODE = "";

        private delegate void ListBoxDelegate(ListBox control, string xstr);
        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        List<string> filenamelist = new List<string>();
        List<string> ltFile = new List<string>();

        public string remotepath = "";
        public string localpath = Application.StartupPath + "\\sftpfile";
        public string localpath1 = Application.StartupPath + "\\orifile";
        public string backpath = Application.StartupPath + "\\backfile";
        string LogAddress = Environment.CurrentDirectory + "\\Log\\" +
               DateTime.Now.Year + '-' +
               DateTime.Now.Month + '-' +
               DateTime.Now.Day + ".log";

     
   
        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
              sftp.Disconnect();
       
        }

        private void button1_Click(object sender, EventArgs e)
        {
          

            sftp = new SFTPHelper(ftpWeb, ftpUserName, ftpPassWord);
            if (!sftp.Connect())
            {
                MessageBox.Show("连接失败！");
            }
            else
            {

                var list = sftp.GetFileList(root);

                foreach (string files in list)
                {

                    string[] Fileslist = files.ToString().Split('\n');

                    for (int i = 0; i < Fileslist.Length; i++)
                    {
                        try
                        {
                            string[] arry = Fileslist[i].ToString().Split(' ');

                            int num = arry.Length - 1;
                            remotepath = root + "//" + arry[num].ToString().Trim();
                            if (arry[num].ToString().Trim() != null)
                            {
                                sftp.Get(remotepath, localpath);
                                sftp.Get(remotepath, localpath1);
                                filenamelist.Add(arry[num].ToString().Trim());

                                //listBox1.Items.Add("ORI:" + remotepath);
                            }

                        }

                        catch
                        {

                        }

                    }

                }

                Thread.Sleep(10000);

                //获取本地文件夹
                var downfile = Directory.GetFiles(localpath);

                foreach (var file in downfile)
                {
                    ltFile.Add(System.IO.Path.GetFileName(file).ToString());
                }
                for (int i = 0; i < ltFile.Count; i++)
                {
                    for (int j = 0; j < filenamelist.Count; j++)
                    {

                        if (ltFile[i].ToString() == filenamelist[j].ToString())
                        {

                            string remotepath1 = root + "//" + ltFile[i].ToString();

                            //var a = sftp.Delete(remotepath1);
                            var a = true;
                            if (a == true)
                            {
                                ListBox_InvokeRequired(listBox1, "删除文件" + ltFile[i].ToString() + "成功");
                            }
                            //listBox1.Items.Add("Total:" + remotepath1);
                        }
                    }

                }

                Thread.Sleep(50000);

                //读取本地文件夹文件
                for (int j = 0; j < ltFile.Count; j++)
                {

                    string targetPath = Environment.CurrentDirectory + "\\sftpfile\\" + ltFile[j].ToString();

                    DataTable dt = ReadExcelToDataTable(targetPath);
                    if (dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            CUST_PN = dt.Rows[i]["SKU"].ToString().Trim();
                            SSD_TODAY = dt.Rows[i]["SSD_Today"].ToString().Trim();
                            SSD_YESTERDAY = dt.Rows[i]["SSD_Yesterday"].ToString().Trim();
                            CHANGE_FLAG = dt.Rows[i]["Flag"].ToString().Trim();
                            UPC = dt.Rows[i]["UPC"].ToString().Trim();
                            PACK_OUT = dt.Rows[i]["Pack Out"].ToString().Trim();
                            SCC14_CODE = dt.Rows[i]["SCC-14"].ToString().Trim();

                            string sql = "select * from  SFC.R_REP_PN_T where CUST_PN='" + CUST_PN + "' ";
                            exeRes = this.dbtools.ExecuteQueryDS(sql);

                            DataSet ds = new DataSet();

                            ds = (DataSet)exeRes.Anything;

                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                sCUST_PN = ds.Tables[0].Rows[0]["CUST_PN"].ToString().Trim();
                                sSSD_TODAY = ds.Tables[0].Rows[0]["SSD_TODAY"].ToString().Trim();
                                sSSD_YESTERDAY = ds.Tables[0].Rows[0]["SSD_YESTERDAY"].ToString().Trim();
                                sCHANGE_FLAG = ds.Tables[0].Rows[0]["CHANGE_FLAG"].ToString().Trim();
                                sUPC = ds.Tables[0].Rows[0]["UPC"].ToString().Trim();
                                sPACK_OUT = ds.Tables[0].Rows[0]["PACK_OUT"].ToString().Trim();
                                sSCC14_CODE = ds.Tables[0].Rows[0]["SCC14_CODE"].ToString().Trim();

                                string isql = "insert into SFC.R_REP_PN_D_T (CUST_PN,SSD_TODAY,SSD_YESTERDAY,CHANGE_FLAG,UPC,PACK_OUT,SCC14_CODE,UPDATE_TIME) VALUES（'" + sCUST_PN + "','" + sSSD_TODAY + "','" + sSSD_YESTERDAY + "','" + sCHANGE_FLAG + "','" + sUPC + "','" + sPACK_OUT + "','" + sSCC14_CODE + "',sysdate）";
                                exeRes = this.dbtools.ExecuteUpdate(isql);
                                if (exeRes.Status)
                                {
                                    writelog(" 【" + sCUST_PN + "】" + " 【" + sSSD_TODAY + "】" + " 【" + sSSD_TODAY + "】" + "  【" + sSSD_YESTERDAY + "】" + "  【" + sCHANGE_FLAG + "】" + "  【" + sUPC + "】" + " 【" + sPACK_OUT + "】" + " 【" + sSCC14_CODE + "】" + "插入SFC.R_REP_PN_D_T成功");
                                    string usql = "update SFC.R_REP_PN_T  set  CUST_PN='" + CUST_PN + "',SSD_TODAY='" + SSD_TODAY + "',SSD_YESTERDAY='" + SSD_YESTERDAY + "',CHANGE_FLAG='" + CHANGE_FLAG + "',UPC='" + UPC + "',PACK_OUT='" + PACK_OUT + "',SCC14_CODE='" + SCC14_CODE + "',UPDATE_TIME=sysdate where CUST_PN='" + CUST_PN + "'";
                                    exeRes = this.dbtools.ExecuteUpdate(usql);

                                    if (exeRes.Status)
                                    {

                                        writelog(" 【" + CUST_PN + "】" + " 【" + SSD_TODAY + "】" + " 【" + SSD_TODAY + "】" + "  【" + SSD_YESTERDAY + "】" + "  【" + CHANGE_FLAG + "】" + "  【" + UPC + "】" + " 【" + PACK_OUT + "】" + " 【" + SCC14_CODE + "】" + "更新SFC.R_REP_PN_T成功");
                                        sb.Append(" 【" + CUST_PN + "】");
                                        sb.Append(" 【" + SSD_TODAY + "】");
                                        sb.Append("  【" + SSD_YESTERDAY + "】");
                                        sb.Append("  【" + CHANGE_FLAG + "】");
                                        sb.Append("  【" + UPC + "】");
                                        sb.Append(" 【" + PACK_OUT + "】");
                                        sb.Append(" 【" + SCC14_CODE + "】");
                                        sb.Append("   更新SFC.R_REP_PN_T表中资料");

                                    }

                                    else
                                    {
                                        writelog("插入失败");

                                    }
                                }

                            }

                            else
                            {

                                string isql = "insert into SFC.R_REP_PN_T (CUST_PN,SSD_TODAY,SSD_YESTERDAY,CHANGE_FLAG,UPC,PACK_OUT,SCC14_CODE,UPDATE_TIME) VALUES（'" + CUST_PN + "','" + SSD_TODAY + "','" + SSD_YESTERDAY + "','" + CHANGE_FLAG + "','" + UPC + "','" + PACK_OUT + "','" + SCC14_CODE + "',sysdate）";
                                exeRes = this.dbtools.ExecuteUpdate(isql);

                                if (exeRes.Status)
                                {

                                    writelog(" 【" + CUST_PN + "】" + " 【" + SSD_TODAY + "】" + " 【" + SSD_TODAY + "】" + "  【" + SSD_YESTERDAY + "】" + "  【" + CHANGE_FLAG + "】" + "  【" + UPC + "】" + " 【" + PACK_OUT + "】" + " 【" + SCC14_CODE + "】" + "插入SFC.R_REP_PN_T成功");
                                    sb.Append(" 【" + CUST_PN + "】");
                                    sb.Append(" 【" + SSD_TODAY + "】");
                                    sb.Append("  【" + SSD_YESTERDAY + "】");
                                    sb.Append("  【" + CHANGE_FLAG + "】");
                                    sb.Append("  【" + UPC + "】");
                                    sb.Append(" 【" + PACK_OUT + "】");
                                    sb.Append(" 【" + SCC14_CODE + "】");
                                    sb.Append("   资料成功写入SFC.R_REP_PN_T表中");

                                    ListBox_InvokeRequired(listBox1, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "    " + sb.ToString());
                                }
                                else
                                {
                                    writelog("插入失败");
                                    sb.Append(" 【" + CUST_PN + "】");
                                    sb.Append(" 【" + SSD_TODAY + "】");
                                    sb.Append("  【" + SSD_YESTERDAY + "】");
                                    sb.Append("  【" + CHANGE_FLAG + "】");
                                    sb.Append("  【" + UPC + "】");
                                    sb.Append(" 【" + PACK_OUT + "】");
                                    sb.Append(" 【" + SCC14_CODE + "】");
                                    sb.Append("   资料插入入SFC.R_REP_PN_T表失败");

                                }
                            }
                        }
                    }

                }

                Thread.Sleep(60000);
                MoveFilefolder(localpath, backpath);

                sftp.Disconnect();
       

            }

        }

        public static void MoveFilefolder(string sourceFilePath, string targetFilePath)
        {

            //获取源文件夹中的所有非目录文件

            string[] files = Directory.GetFiles(sourceFilePath);

            string fileName;

            string destFile;

            //如果目标文件夹不存在，则新建目标文件夹

            if (!Directory.Exists(targetFilePath))
            {

                Directory.CreateDirectory(targetFilePath);

            }

            //将获取到的文件一个一个拷贝到目标文件夹中 

            foreach (string file in files)
            {

                fileName = System.DateTime.Now.ToString("yyyyMMddHHmmss") + Path.GetFileName(file);

                destFile = Path.Combine(targetFilePath, fileName);

                //File.Copy(s, destFile, true);
                File.Move(file, destFile);
            }

        }

        private void ListBox_InvokeRequired(ListBox control, string xstr)
        {
            if (control.InvokeRequired)
            {
                ListBoxDelegate mydelegate = new ListBoxDelegate(ListBox_InvokeRequired);
                control.Invoke(mydelegate, new object[] { control, xstr });
            }
            else
            {
                if (control.Items.Count > 100)
                {
                    control.Items.Clear();
                }
                control.Items.Add(xstr);
                control.SelectedIndex = control.Items.Count - 1;
                control.SelectedIndex = -1;
            }
        }

        public void writelog(string strerr)
        {
            FileStream sFile = new FileStream(LogAddress, FileMode.OpenOrCreate);
            StreamWriter sw = new StreamWriter(sFile);
            sw.BaseStream.Seek(0, SeekOrigin.End);
            sw.WriteLine(DateTime.Now + strerr);
            sw.Close();
            sw.Dispose();
            sFile.Close();
            sFile.Dispose();
        }

        public static DataTable ReadExcelToDataTable(string targetPath)
        {
            DataTable dt = new DataTable();

            string strConn = "";
            string fileName1 = targetPath.Substring(targetPath.Length - 4, 4);
            //if (fileName1 == ".CSV")
            //{
            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + targetPath + "';Extended Properties='Excel 12.0;HDR=YES'";
            //}
            //else
            //{
            //    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            //        "Data Source=" + targetPath + ";" +
            //        "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
            //}

            OleDbConnection conn = new OleDbConnection(strConn);

            conn.Open();
            string Sheet = "sheet1";
            OleDbDataAdapter da = new OleDbDataAdapter("SELECT *  FROM [" + Sheet + "$]", conn);
            DataSet ds = new DataSet();

            try
            {

                da.Fill(ds, Sheet);
                dt = ds.Tables[0];
                //this.dataGridView1.DataSource = dt;
                return dt;
            }
            catch (Exception err)
            {
                MessageBox.Show("操作失败！I" + err.ToString());
            }
            conn.Close();

            return dt;

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            timer1.Start();
            timer1.Interval = 1000;
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            string now = System.DateTime.Now.ToString("HHmmss");

            if (now=="080000")

            {

            sftp = new SFTPHelper(ftpWeb, ftpUserName, ftpPassWord);
            if (!sftp.Connect())
            {

                writelog("连接失败");
            }
            else
            {
                writelog("连接成功");
                var list = sftp.GetFileList(root);

                foreach (string files in list)
                {

                    string[] Fileslist = files.ToString().Split('\n');

                    for (int i = 0; i < Fileslist.Length; i++)
                    {
                        try
                        {
                            string[] arry = Fileslist[i].ToString().Split(' ');

                            int num = arry.Length - 1;
                            remotepath = root + "//" + arry[num].ToString().Trim();
                            if (arry[num].ToString().Trim() != null)
                            {
                                sftp.Get(remotepath, localpath);
                                sftp.Get(remotepath, localpath1);
                                filenamelist.Add(arry[num].ToString().Trim());

                                //listBox1.Items.Add("ORI:" + remotepath);
                            }

                        }

                        catch
                        {

                        }

                    }

                }

                Thread.Sleep(10000);

                //获取本地文件夹
                var downfile = Directory.GetFiles(localpath);

                foreach (var file in downfile)
                {
                    ltFile.Add(System.IO.Path.GetFileName(file).ToString());
                }
                for (int i = 0; i < ltFile.Count; i++)
                {
                    for (int j = 0; j < filenamelist.Count; j++)
                    {

                        if (ltFile[i].ToString() == filenamelist[j].ToString())
                        {

                            string remotepath1 = root + "//" + ltFile[i].ToString();

                            //var a = sftp.Delete(remotepath1);
                            var a = true;
                            if (a == true)
                            {
                                ListBox_InvokeRequired(listBox1, "删除文件" + ltFile[i].ToString() + "成功");
                            }
                            //listBox1.Items.Add("Total:" + remotepath1);
                        }
                    }

                }

                Thread.Sleep(50000);

                //读取本地文件夹文件
                for (int j = 0; j < ltFile.Count; j++)
                {

                    string targetPath = Environment.CurrentDirectory + "\\sftpfile\\" + ltFile[j].ToString();

                    DataTable dt = ReadExcelToDataTable(targetPath);
                    if (dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            CUST_PN = dt.Rows[i]["SKU"].ToString().Trim();
                            SSD_TODAY = dt.Rows[i]["SSD_Today"].ToString().Trim();
                            SSD_YESTERDAY = dt.Rows[i]["SSD_Yesterday"].ToString().Trim();
                            CHANGE_FLAG = dt.Rows[i]["Flag"].ToString().Trim();
                            UPC = dt.Rows[i]["UPC"].ToString().Trim();
                            PACK_OUT = dt.Rows[i]["Pack Out"].ToString().Trim();
                            SCC14_CODE = dt.Rows[i]["SCC-14"].ToString().Trim();

                            string sql = "select * from  SFC.R_REP_PN_T where CUST_PN='" + CUST_PN + "' ";
                            exeRes = this.dbtools.ExecuteQueryDS(sql);

                            DataSet ds = new DataSet();

                            ds = (DataSet)exeRes.Anything;

                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                sCUST_PN = ds.Tables[0].Rows[0]["CUST_PN"].ToString().Trim();
                                sSSD_TODAY = ds.Tables[0].Rows[0]["SSD_TODAY"].ToString().Trim();
                                sSSD_YESTERDAY = ds.Tables[0].Rows[0]["SSD_YESTERDAY"].ToString().Trim();
                                sCHANGE_FLAG = ds.Tables[0].Rows[0]["CHANGE_FLAG"].ToString().Trim();
                                sUPC = ds.Tables[0].Rows[0]["UPC"].ToString().Trim();
                                sPACK_OUT = ds.Tables[0].Rows[0]["PACK_OUT"].ToString().Trim();
                                sSCC14_CODE = ds.Tables[0].Rows[0]["SCC14_CODE"].ToString().Trim();

                                string isql = "insert into SFC.R_REP_PN_D_T (CUST_PN,SSD_TODAY,SSD_YESTERDAY,CHANGE_FLAG,UPC,PACK_OUT,SCC14_CODE,UPDATE_TIME) VALUES（'" + sCUST_PN + "','" + sSSD_TODAY + "','" + sSSD_YESTERDAY + "','" + sCHANGE_FLAG + "','" + sUPC + "','" + sPACK_OUT + "','" + sSCC14_CODE + "',sysdate）";
                                exeRes = this.dbtools.ExecuteUpdate(isql);
                                if (exeRes.Status)
                                {
                                    writelog(" 【" + sCUST_PN + "】" + " 【" + sSSD_TODAY + "】" + " 【" + sSSD_TODAY + "】" + "  【" + sSSD_YESTERDAY + "】" + "  【" + sCHANGE_FLAG + "】" + "  【" + sUPC + "】" + " 【" + sPACK_OUT + "】" + " 【" + sSCC14_CODE + "】" + "插入SFC.R_REP_PN_D_T成功");
                                    string usql = "update SFC.R_REP_PN_T  set  CUST_PN='" + CUST_PN + "',SSD_TODAY='" + SSD_TODAY + "',SSD_YESTERDAY='" + SSD_YESTERDAY + "',CHANGE_FLAG='" + CHANGE_FLAG + "',UPC='" + UPC + "',PACK_OUT='" + PACK_OUT + "',SCC14_CODE='" + SCC14_CODE + "',UPDATE_TIME=sysdate where CUST_PN='" + CUST_PN + "'";
                                    exeRes = this.dbtools.ExecuteUpdate(usql);

                                    if (exeRes.Status)
                                    {

                                        writelog(" 【" + CUST_PN + "】" + " 【" + SSD_TODAY + "】" + " 【" + SSD_TODAY + "】" + "  【" + SSD_YESTERDAY + "】" + "  【" + CHANGE_FLAG + "】" + "  【" + UPC + "】" + " 【" + PACK_OUT + "】" + " 【" + SCC14_CODE + "】" + "更新SFC.R_REP_PN_T成功");
                                        sb.Append(" 【" + CUST_PN + "】");
                                        sb.Append(" 【" + SSD_TODAY + "】");
                                        sb.Append("  【" + SSD_YESTERDAY + "】");
                                        sb.Append("  【" + CHANGE_FLAG + "】");
                                        sb.Append("  【" + UPC + "】");
                                        sb.Append(" 【" + PACK_OUT + "】");
                                        sb.Append(" 【" + SCC14_CODE + "】");
                                        sb.Append("   更新SFC.R_REP_PN_T表中资料");

                                    }

                                    else
                                    {
                                        writelog("插入失败");

                                    }
                                }

                            }

                            else
                            {

                                string isql = "insert into SFC.R_REP_PN_T (CUST_PN,SSD_TODAY,SSD_YESTERDAY,CHANGE_FLAG,UPC,PACK_OUT,SCC14_CODE,UPDATE_TIME) VALUES（'" + CUST_PN + "','" + SSD_TODAY + "','" + SSD_YESTERDAY + "','" + CHANGE_FLAG + "','" + UPC + "','" + PACK_OUT + "','" + SCC14_CODE + "',sysdate）";
                                exeRes = this.dbtools.ExecuteUpdate(isql);

                                if (exeRes.Status)
                                {

                                    writelog(" 【" + CUST_PN + "】" + " 【" + SSD_TODAY + "】" + " 【" + SSD_TODAY + "】" + "  【" + SSD_YESTERDAY + "】" + "  【" + CHANGE_FLAG + "】" + "  【" + UPC + "】" + " 【" + PACK_OUT + "】" + " 【" + SCC14_CODE + "】" + "插入SFC.R_REP_PN_T成功");
                                    sb.Append(" 【" + CUST_PN + "】");
                                    sb.Append(" 【" + SSD_TODAY + "】");
                                    sb.Append("  【" + SSD_YESTERDAY + "】");
                                    sb.Append("  【" + CHANGE_FLAG + "】");
                                    sb.Append("  【" + UPC + "】");
                                    sb.Append(" 【" + PACK_OUT + "】");
                                    sb.Append(" 【" + SCC14_CODE + "】");
                                    sb.Append("   资料成功写入SFC.R_REP_PN_T表中");

                                    ListBox_InvokeRequired(listBox1, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "    " + sb.ToString());
                                }
                                else
                                {
                                    writelog("插入失败");
                                    sb.Append(" 【" + CUST_PN + "】");
                                    sb.Append(" 【" + SSD_TODAY + "】");
                                    sb.Append("  【" + SSD_YESTERDAY + "】");
                                    sb.Append("  【" + CHANGE_FLAG + "】");
                                    sb.Append("  【" + UPC + "】");
                                    sb.Append(" 【" + PACK_OUT + "】");
                                    sb.Append(" 【" + SCC14_CODE + "】");
                                    sb.Append("   资料插入入SFC.R_REP_PN_T表失败");

                                }
                            }
                        }
                    }

                }

                Thread.Sleep(60000);
                MoveFilefolder(localpath, backpath);

                sftp.Disconnect();
       

            }

        }

        }

    
  

    }
}