c# 获取FTP文件，并将数据导入ORACLE数据库

    如何从FTP上获取数据，很简单，使用控件edtFTPnet，可以到官网上下载，包含例子。

    如何将数据导入数据库中？在程序中使用insert语句？是一种办法，但如果获取的文件中的数据超过10万条，岂不是需要等很长时间？所以，将文本导入数据库使用ORACLE中的Sql Loader方法，即调用sqlldr.exe。关于SQL loader 的具体使用方法，Google可以Google到很多，就不详讲了。

 

    具体见程序：

 

一、连接FTP获取数据，首先引用控件edtFTPnet

 

  #region 连接FTP private static void Connect_Ftp() { XmlDocument xmlDoc = new XmlDocument(); xmlDoc.Load(AppDomain.CurrentDomain.BaseDirectory + "Config.xml");//连接配置文件，从配置文件中获取FTP、数据库信息 XmlNode xn = xmlDoc.SelectSingleNode("FTPConnection"); XmlNodeList xnf1 = xn.ChildNodes; string ConnectionString = "Data Source=" + xnf1[4].InnerText + ";user=" + xnf1[5].InnerText + ";password=" + xnf1[6].InnerText + ";"; //写连接串 string strFilePath = xnf1[7].InnerText;//文件存放路径 string strDataInfo = xnf1[5].InnerText + "/" + xnf1[6].InnerText + "@" + xnf1[4].InnerText + " ";//数据库信息 byte[] filebyte = null; string[] strArray = null; FTPClient ftp = new FTPClient(); try { //以下开始获取FTP数据 ftp.RemoteHost = xnf1[0].InnerText; ftp.ControlPort = Convert.ToInt32(xnf1[1].InnerText); ftp.Timeout = 0x2710; ftp.Connect(); ftp.Login(xnf1[2].InnerText, xnf1[3].InnerText); ftp.ConnectMode = FTPConnectMode.PASV; ftp.TransferType = FTPTransferType.ASCII; ftp.ChDir("/"); //文件夹路径 strArray = ftp.Dir(".", true); string strDay = DateTime.Now.AddDays(-1).ToString("yyyyMMdd"); foreach (string strFile in strArray) { if (strFile.Length >= 80) { string strfilename = strFile.Substring(56, 24).TrimStart(); if (strfilename == "jiangsu_kpi_" + strDay + ".txt") { if (CheckIsOver(ConnectionString, strfilename, strDay)) { if (CheckIsInserting(ConnectionString, strfilename, strDay)) { string strLogFile = "copy." + strfilename; if (File.Exists(strLogFile)) File.Delete(strLogFile); ftp.Get(strLogFile, strfilename); //以上为获取FTP数据，并将文件存放在本地硬盘中 Insert_Date(ConnectionString, null, strDay, strDataInfo, strFilePath + @"/" + strLogFile);//数据插入数据库 } } } } } if (ftp.Connected) { ftp.Quit(); } } catch (Exception ex) { if (ftp.Connected) { ftp.Quit(); } } } #endregion

 

 

二、使用sqlloader，将数据插入ORACLE数据库

  #region 使用sqlLoader 导入数据库 //建立CTL文件 string strCtlFile = file.Substring(0, file.Length - 3) + "ctl"; string strLogFile = file.Substring(0, file.Length - 3) + "log"; string strBadFile = file.Substring(0, file.Length - 3) + "bad"; if (File.Exists(strLogFile)) File.Delete(strLogFile); if (File.Exists(strCtlFile)) File.Delete(strCtlFile); if (File.Exists(strBadFile)) File.Delete(strBadFile); System.IO.FileStream sf = File.Create(strCtlFile); StringBuilder strCtlField = new StringBuilder(); strCtlField.Append("load data/r/n"); strCtlField.Append("infile '"); strCtlField.Append(file); strCtlField.Append("'/r/n"); strCtlField.Append("append into table "); strCtlField.Append("要导入的数据表名"); strCtlField.Append(@"(MSISDN char terminated by ',', RECORD_DAY char terminated by ',',PROVINCE_ID char terminated by ',',CITY_ID char terminated by ',', IS_WHOLE char terminated by ',',IS_WORD char terminated by ',',IS_SAPACK char terminated by ',', WHOLE_FEE char terminated by ',',WORD_FEE char terminated by ',',SAPACK_FEE char terminated by ',', IS_WAP char terminated by ',',IS_SOFT char terminated by ',', IS_HARD char terminated by whitespace)"); byte[] bytes = System.Text.Encoding.Default.GetBytes(strCtlField.ToString()); sf.Write(bytes, 0, bytes.Length); sf.Close(); //运行sqlldr，导入数据 System.Diagnostics.Process p = new System.Diagnostics.Process(); p.StartInfo = new System.Diagnostics.ProcessStartInfo(); p.StartInfo.FileName = "sqlldr"; p.StartInfo.Arguments = strDataInfo + " " + strCtlFile + " " + strLogFile; p.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden; p.StartInfo.RedirectStandardOutput = true; p.StartInfo.UseShellExecute = false; p.StartInfo.CreateNoWindow = true; p.Start(); while (p.StandardOutput.ReadLine() != null) //必须有输出，防止文件过大 p.WaitForExit(); 死掉 { System.Console.WriteLine(p.StandardOutput.ReadLine()); } p.WaitForExit(); cmd.CommandText = "UPDATE JIANGSU_KPI_DATA SET ADD_DATE=" + strDay + " WHERE ADD_DATE IS NULL"; cmd.ExecuteNonQuery(); if (File.Exists(strBadFile)) { throw new Exception("文件：" + file + "数据不符合规范！"); } #endregion