public static string DownloadTripBookingReferencesFileFromFtp(string countryCode) { try { string filename = GetTripBookingReferenceFileName(DateTime.Now, countryCode); if (!String.IsNullOrEmpty(filename)) { DownloadFtpFile(filename, false); } else { Emailer.SendEmail("*****@*****.**", countryCode + " TRIP Booking Reference File Name Came back blank", ""); } return(filename); } catch (Exception ex) { Emailer.SendEmail("*****@*****.**", "TRIP Booking Reference File FTP Import Failed" + DateTime.Now.ToString(), ex.InnerException.ToString()); return(""); } }
public void CsvExport() { try { FileInfo fileInfo = new FileInfo(m_pathAndFileName); FileInfo serverFileAndPath = new FileInfo(@"\\mssqldev\E$\SteveJ\" + fileInfo.Name); FileInfo tempFile = new FileInfo(@"\\mssqldev\E$\SteveJ\Temp.csv"); StringBuilder headers = new StringBuilder(); foreach (string header in m_selectStatementBuilder.GetOutputHeaders) { headers.Append(header + "|"); } headers.Remove(headers.Length - 1, 1); using (SqlConnection conn = new SqlConnection(DataConnection.SqlConnCoreData)) { SqlCommand sqlCommand = new SqlCommand(); sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandTimeout = 60 * 30; sqlCommand.Connection = conn; StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append(@"DECLARE @bcpCmd as VARCHAR(8000);"); sqlBuilder.Append(@"SET @bcpCmd= 'bcp ""SET NOCOUNT ON;"); sqlBuilder.Append(m_selectClause); sqlBuilder.Append(@";SET NOCOUNT OFF;"""); sqlBuilder.Append(" queryout "); sqlBuilder.Append(m_pathAndFileName); sqlBuilder.Append(" -t \"" + m_del + "\" -r(!!!!!!!!!!)"); sqlBuilder.Append(" -c -S MSSQLDEV -d CoreData -U CoreDataMaintain -P c0reMa1nta1n'; EXEC master..xp_cmdshell @bcpCmd;"); sqlBuilder.Replace("@LangId", m_selectStatementBuilder.LanguageId); string sql = sqlBuilder.ToString(); sqlCommand.CommandText = sql; conn.Open(); sqlCommand.ExecuteNonQuery(); if (File.Exists(serverFileAndPath.FullName)) { string line; if (File.Exists(tempFile.FullName)) { File.Delete(tempFile.FullName); } FileStream fs = new FileStream(serverFileAndPath.FullName, FileMode.Open); FileStream outStream = new FileStream(tempFile.FullName, FileMode.Append); StreamReader reader = new StreamReader(fs); StreamWriter writer = new StreamWriter(outStream); writer.WriteLine(headers); string[] stringSeperators = new string[] { "(!!!!!!!!!!)" }; string[] fieldSeperators = new string[] { "!|!" }; string lastEntry = ""; while ((line = reader.ReadLine()) != null) { string[] lines = line.Split(stringSeperators, StringSplitOptions.RemoveEmptyEntries);; if (lines.Length > 0) { for (int i = 0; i <= lines.Length - 1; i++) { if (lines[i].Split(fieldSeperators, StringSplitOptions.None).Length - 1 == headers.ToString().Split('|').Length - 1) { string row = lines[i].Replace("!|!", "|"); writer.WriteLine(row); } else { lastEntry = lastEntry + lines[i]; if (lastEntry.Split(fieldSeperators, StringSplitOptions.None).Length - 1 == headers.ToString().Split('|').Length - 1) { lastEntry = lastEntry.Replace("!|!", "|"); writer.WriteLine(lastEntry); lastEntry = ""; } } } } } writer.Flush(); reader.Close(); writer.Close(); File.Delete(serverFileAndPath.FullName); File.Move(tempFile.FullName, serverFileAndPath.FullName); File.Delete(tempFile.FullName); } } } catch (Exception exception) { Emailer.SendEmail("*****@*****.**", "CVS Data Exporter - Error : ", "CVSExport", exception); } }