c# - Only one attachment is fired instead of many records

Question

I have a code for which there are 79 records coming from the SP. I want to send the attachment irrespective of their data to their users. but only 79th record is send to every user as an attachment. I don't know why.

private void Form1_Load(object sender, EventArgs e)
    {
        Cls_Email_Sql ce = new Cls_Email_Sql();
        List<string> ls_attach = new List<string>();
        using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(SqlConn))
        {
            using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
            {
                cmd.CommandText = "GET_INWARD_REMINDER_REPORT";
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                conn.Close();
                DataTable tb_RA = ds.Tables[0];
                DataTable tb_User = ds.Tables[1];

                string strcolorHead = "#C0C0C0";
                string strcolorDet = "#C0C0C0";

                var groups = tb_RA.AsEnumerable().GroupBy(r => r.Field<string>("RAName"));

                foreach (var group in groups)   // RA Table
                {
                    sbodyMail = "Dear " + group.Key + ", <br /><br />  " +

                    "As per the details available in the system, below are the summary  "+
                    "of number of documents lying with your reportees for more than five days. "+
                    "This is for your information and necessary action ";

                    sbodyMail += "<table style='width: 400px;font-size:12px;font-family: Arial, Helvetica, sans-serif;' " +
                                "border='0'><tr><td style='width: 100%;'></b><td></tr></table> " +

                                "<table style='width: 470px;font-size:12px; font-family: Arial, Helvetica, sans-serif;height: 53px' border='1'><tr> " +
                                "<td style='width: 30px; height: 14px;color:black;background-color:" + strcolorHead + " ;white-space:nowrap'><strong>SR No</strong></td> " +
                                    "<td style='width: 300px; height: 14px;color:black;background-color:" + strcolorHead + " ;white-space:nowrap'><strong>UserName</strong></td> " +
                                "<td style='width: 120px; height: 14px;color:black;background-color:" + strcolorHead + " ;white-space:nowrap'><strong>Document type</strong></td> " +
                                    "<td style='width: 20px; height: 14px;color:black;background-color:" + strcolorHead + " ;white-space:nowrap'><strong><div>No. of docs working </div><div> for more than five days</div></strong></td> ";

                    foreach (var row in group)
                    {
                        sbodyMail += "<tr>" +
                            "<td style='width: 30px; height: 14px;background-color:" + strcolorDet + "'>" + row["SR_No"].ToString() + " </td> " +
                            "<td style='width: 100px; height: 14px;background-color:" + strcolorDet + "'>" + row["userName"].ToString() + " </td> " +
                            "<td style='width: 100px; height: 14px;background-color:" + strcolorDet + "'>" + row["Document_Type"].ToString() + " </td> " +
                            "<td style='width: 100px; height: 14px;background-color:" + strcolorDet + "'>" + row["CountofDocNo"].ToString() + " </td> " +
                            "</tr>";
                    }
                    sbodyMail += "</table><br>" + //close of header

                     "<b>THIS IS A SYSTEM GENERATED MAIL. PLEASE DO NOT REPLY </b>";
                    string startupPath = "";
                    List<string> ls_attach1 = new List<string>();

                    MailMessage mail = new MailMessage();
                    startupPath = Environment.CurrentDirectory;
                    ls_attach1.Add(startupPath + "\Attachment\Reminder_Sheet.xls");

                    string strExp = "";
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        strExp = "RAName = '" + ds.Tables[0].Rows[i]["RAName"].ToString() + "'";
                        DataRow[] dr = ds.Tables[0].Select(strExp);
                        ds.Tables[0].AcceptChanges(); 
                        DataTable dtNew = ds.Tables[0].Select(strExp).CopyToDataTable();
                        DataSet dsNew = new System.Data.DataSet();
                        dsNew.Tables.Add(dtNew);
                        ExcelLibrary.DataSetHelper.CreateWorkbook(startupPath + "\Attachment\Reminder_Sheet.xls", dsNew);
                    }
                    foreach (var attach in ls_attach1)
                    {
                        mail.Attachments.Add(new Attachment(attach));
                    }
                    foreach (Attachment attachments in mail.Attachments)
                    {
                        attachments.Dispose();
                    }
                    ce.SendEmail("test@test.com", "", "", "Information on documents for processing", sbodyMail,"AUTOSQL", "Powersoft", ls_attach1, "ConnectionString");
                }
            }
        }
    }

at

ds.Tables[0].Rows.Count 

I get 79 records. but the attachment is going only for the 79th record only.

Answer 1

You should normally produce a simpler example of your issue without the business logic if you want people to read through it. That said, your issue is this line:

ExcelLibrary.DataSetHelper.CreateWorkbook(startupPath + "\Attachment\Reminder_Sheet.xls", dsNew);

Looking at the source for the library you're using, it saves the dataset you provide to a workbook using Microsoft's Workbook.Save(). If you call it for each DataSet you want (which I think you are, from the code) you will save them all to the same path, overwriting each one.

This means, after you loop through your 79 reports, you overwrite each one and are left with the last one in the file. You should either find a way to load them all onto seperate worksheets in your book, or save them to different files.