Click here to Skip to main content
15,883,883 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I am using below c# console application project code to export data from sql server into csv file which is working fine as expected but second portion of the code to upload that CSV to FTP server is not working. How do I accomplish this both actions in one code? Export and upload same CSV to FTP.
I am getting below error in my log file.
Message :Sequence contains no elements<br/>


What I have tried:

<pre> static void Main(string[] args)
        {
            string strConn = ConfigurationManager.ConnectionStrings["ExportRedwoodData.Properties.Settings.Setting"].ConnectionString;
            string ErrorLogfilePath = @"C:\Log\Error.txt";
            string ParamFilePath = @"C:\Params\LastSyncDate.xml";

            XmlDocument doc = new XmlDocument();
            doc.Load(ParamFilePath);
            XmlNode node = doc.DocumentElement.SelectSingleNode("/Date");
            string last_sync_date = node.InnerText;
            string sql = @"SQL Query";

            try
            {
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    SqlCommand comm = new SqlCommand(sql, conn);
                    comm.Parameters.Add(new SqlParameter("@last_sync_time", last_sync_date));
                    SqlDataAdapter da = new SqlDataAdapter(comm);
                    DataSet ds = new DataSet();
                    da.Fill(ds, "QB");
                    DataTable dt = ds.Tables["QB"];

                    StreamWriter sw = new StreamWriter(@"C:\Export\wt_shipments_data_" + DateTime.Now.ToString("MMddyyyyHHmmss") + ".csv", false);
                    int iColCount = dt.Columns.Count;

                    for (int i = 0; i < iColCount; i++)
                    {
                        sw.Write(dt.Columns[i]);
                        if (i < iColCount - 1)
                        {
                            sw.Write(",");
                        }
                    }
                    sw.Write(sw.NewLine);
                    foreach (DataRow dr in dt.Rows)
                    {
                        for (int i = 0; i < iColCount; i++)
                        {
                            if (dr[i].ToString().Contains(","))
                            {
                                sw.Write("\"{0}\",", dr[i].ToString());
                                //sw.Write(",");
                            }
                            else
                            {
                                sw.Write(dr[i].ToString());
                                sw.Write(",");
                            }    
                        }
                        sw.Write(sw.NewLine);
                    }

                    sw.Close();
                    DateTime NowDateTime = DateTime.Now;
                    node.InnerText = NowDateTime.ToString("yyyy-MM-dd HH:mm:ss.fff");
                    doc.Save(ParamFilePath);
                  
                    string filename = @"C:\Export\wt_shipments_data_123.csv";
  
                    FtpWebRequest request = (FtpWebRequest)FtpWebRequest.Create("ftp://"+ "ftpServerIPAddress" + "/test" + Path.GetFileName(filename));
                    request.Method = WebRequestMethods.Ftp.UploadFile;
                    request.Credentials = new NetworkCredential("username", "password");
                    request.UsePassive = true;
                    request.UseBinary = true;
                    request.KeepAlive = false;
                    FileStream stream = File.OpenRead(filename);
                    byte[] buffer = new byte[stream.Length];
                    stream.Read(buffer, 0, buffer.Length);
                    stream.Close();
                }
            }
            catch(Exception ex)
            {
                
                using (StreamWriter writer = new StreamWriter(ErrorLogfilePath, true))
                {
                    writer.WriteLine("Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
                       "" + Environment.NewLine + "Date :" + DateTime.Now.ToString() + Environment.NewLine);
                    writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
                }
            }
            
        }
Posted
Updated 6-Jan-20 11:13am
Comments
jimmson 2-Jan-20 9:38am    
On which line do you get the error? You need to get more information about the exception, it's the key to solve your problem.

Added right file path in web request create command and it resolved my issue
Thank you
 
Share this answer
 
Looking at your code there is no writing to the FTP... So I'm not much surprised the upload part does not work...
Follow this sample: How to: Upload files with FTP | Microsoft Docs[^]
 
Share this answer
 
Comments
RaviP17 3-Jan-20 15:20pm    
Hi,
I have changed in my code and now I am only using this code to upload my file to FTP.
FTP response says:226 Transfer Complete. But I don't see that file is uploaded on FTP.
I have another question: What exactly i need to put in Create request?

string filename = @"C:\Export\wt_shipments_data_123.csv";

FtpWebRequest request = (FtpWebRequest)FtpWebRequest.Create("ftp://ftp_server_IPaddress/test.htm");
request.Method = WebRequestMethods.Ftp.UploadFile;
request.Credentials = new NetworkCredential("username", "password");
request.UsePassive = true;
request.UseBinary = true;
request.KeepAlive = false;

byte[] fileContents;
using (StreamReader sourceStream = new StreamReader(filename))
{
fileContents = Encoding.UTF8.GetBytes(sourceStream.ReadToEnd());

}
request.ContentLength = fileContents.Length;
using (Stream requestStream = request.GetRequestStream())
{
requestStream.Write(fileContents, 0, fileContents.Length);

}
FtpWebResponse response = (FtpWebResponse)request.GetResponse();
MessageBox.Show(response.StatusDescription.ToString());

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900