Show in body of HTML Query result data

tdignan87

Well-known member
Joined
Jul 8, 2019
Messages
62
Programming Experience
Beginner
Hi I am looking for some help if possible,
I have a query that writes off stock in our system which is older than 30 days. I would like the query results though to show in the body of the email it sends also so users know what stock has been written off. It doesn't need to be fancy, just a list of the query results.

Below is an example of my code
Thanks
C#:
   FbConnectionStringBuilder rfs = new FbConnectionStringBuilder();
                rfs.DataSource =
                    "localhost";
                rfs.Port = 3050;
                rfs.Database = @";
                rfs.UserID = "sysdba";
                rfs.Password = "masterkey";
                rfs.ServerType = FbServerType.Default;

                FbConnection db = new FbConnection(rfs.ToString());
                db.Open();

              //  String deletecommod = db.Query<String>("UPDATE STOCKDET SET STOCKDET.QTY = 0 WHERE STOCKDET.STOCKHDRID IN ( SELECT STOCKID FROM STOCKHDR WHERE STOCKHDR.RECEIVED <= " + date + ")" ).FirstOrDefault();

                String deletecommod = db.Query<String>("UPDATE STOCKDET SET STOCKDET.QTY = 0 WHERE STOCKDET.STOCKHDRID IN ( SELECT STOCKID FROM STOCKHDR WHERE STOCKHDR.RECEIVED <= " + date +
                    "AND NOT STOCKHDR.COMMODITYID = '3082' AND NOT STOCKHDR.COMMODITYID = '3081' AND NOT STOCKHDR.COMMODITYID = '894' AND NOT STOCKHDR.COMMODITYID = '981' )").FirstOrDefault();

                

                MailMessage mail = new MailMessage();
                SmtpClient SmtpServer = new SmtpClient(stevensSMTP);
                mail.From = new MailAddress(stevensNC);
                mail.To.Add(stevens);
                mail.Subject = "RFS Stock Clean Remnant Stock";
                mail.Body = "RFS Production Schedule Clean <br> Remnant Stock older than 30 days has been 0'd from the system. Please contact support if further assistance is required</br>";
                
                SmtpServer.Port = 25;
                SmtpServer.Credentials = new System.Net.NetworkCredential(stevensNC, stevensPW);
                SmtpServer.EnableSsl = true;
                mail.IsBodyHtml = true;

                SmtpServer.Send(mail);

                //   DateTime dateTime = new DateTime(2019, 6, 4);
                // double date = Convert.ToSingle(dateTime.ToOADate());
                // Console.WriteLine(date.ToString());
                //    Console.WriteLine("Successful");


            }
            catch (Exception ex)
            {
                Console.WriteLine("Not successful");
                MailMessage mail = new MailMessage();
                SmtpClient SmtpServer = new SmtpClient("smtp.office365.com");

                mail.From = new MailAddress("alerts@alertme.services");
                mail.To.Add("tom.dignan@burtonsbiscuits.com");
                mail.Subject = "RFS Stock Det Clean failed to update stock >30 days to 0";
                mail.Body = "Exception Details" + ex.ToString();

                SmtpServer.Port = 25;
                SmtpServer.Credentials = new System.Net.NetworkCredential("alerts@alertme.services", "");
                SmtpServer.EnableSsl = true;
                mail.IsBodyHtml = true;

                SmtpServer.Send(mail);
            }




        }
    }
}
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
646
Location
Virginia Beach, VA
Programming Experience
10+
So you need to do 2 queries. The first query would be a select that has the same where clause as your second (currently existing) update query. I don't see how this is difficult and could not make the obvious connection unless you don't understand what your current update query does.

Or is the issue that you are afraid that there maybe some intervening operation between the time that you select and the time that you update thereby making the email inaccurate? If so that is what transactions are for -- to make operations atomic. I don't know if FireBase supports transactions. Most modern databases support ACID operations, so I think you should be covered.
 

tdignan87

Well-known member
Joined
Jul 8, 2019
Messages
62
Programming Experience
Beginner
Yeah im fine with doing a select query first to pull in records older than 30 days old and above 0 stock, but how to add the database data that matches the select criteria into the body of a mail message? As you can see at the minute I am just telling the recipients that stock has been written off but not telling them what records were written off. Cheers
I can just run the UPDATE query immediately after the email is sent.
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
646
Location
Virginia Beach, VA
Programming Experience
10+
When you do a select query, it will return a set of rows. You would iterate over the rows and build up your message body using that row data.
 

tdignan87

Well-known member
Joined
Jul 8, 2019
Messages
62
Programming Experience
Beginner
i am a bit stuck trying to get the console to show me the query results ( getting no records in email). Thanks


C#:
                    FbDataAdapter sqlDA = new FbDataAdapter("Select PRODUCEDH.BATCHID,COMMODITIES.CODE AS PRODUCT,PRODUCEDH.STATUS,PRODUCEDH.QTY AS CASES,COMMODITIES1.CODE AS MIX,STOCKHDR.RECEIVEDWEIGHT,PRODUCEDD.QTY, PRODUCEDD.QTY / STOCKHDR.RECEIVEDWEIGHT As YIELD From PRODUCEDH Inner Join " +
                        "COMMODITIES On PRODUCEDH.COMMODITYID = COMMODITIES.COMMODITYID Inner Join PRODUCEDD On PRODUCEDH.BATCHID = PRODUCEDD.BATCHID Inner Join STOCKDET On PRODUCEDD.SRCSTOCKLOTID = STOCKDET.STOCKID Inner Join " +
                        "STOCKHDR On STOCKDET.STOCKHDRID = STOCKHDR.STOCKID Inner Join COMMODITIES COMMODITIES1 On STOCKHDR.COMMODITYID = COMMODITIES1.COMMODITYID Where PRODUCEDH.CREATETIME  <" + date + " and (COMMODITIES.CODE Like 'F%' Or COMMODITIES.CODE Like 'X%' Or " +
                        "COMMODITIES.CODE Like 'B%' Or COMMODITIES.CODE Like 'W%') And PRODUCEDH.STATUS = 'C' And COMMODITIES1.CODE Like 'M%' And COMMODITIES1.CODE Not Like 'MIX-SUB'", db);


                    DataTable dtbl = new DataTable();
                    sqlDA.Fill(dtbl);

                    //   batchesDGV.ItemsSource = dtbl.AsDataView();
                   Console.WriteLine(dtbl.ToString());
                    Console.ReadLine();

                    db.Close();






                    MailMessage mail = new MailMessage();
                    SmtpClient SmtpServer = new SmtpClient(stevensSMTP);
                    mail.From = new MailAddress(stevensNC);
                    mail.To.Add(stevens);
                    mail.Subject = "RFS Stock Clean Remnant Stock";
                    mail.Body = dtbl.ToString();
Also console not showing any records at the writeline area. i was trying to get the records first to show on the console.
Thanks
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
2,511
Location
Sydney, Australia
Programming Experience
10+
You can't just call ToString on a DataTable and expect it to provide a formatted String comprised of the entire contents of the table. Most types simply return the name of the type when you call ToString and DataTable is one of them. If you want to see the contents then you have to actually extract the contents. You've been told that you need to loop through the Rows collection to get the data out so that's what you need to do. You'll need to loop through the columns of each row to build an HTML table but, to simply view the data, you can call String.Join and pass the ItemArray for simplicitly.
C#:
foreach (DataRow row in table.Rows)
{
    Console.WriteLine(string.Join(", ", row.ItemArray));
}
 

tdignan87

Well-known member
Joined
Jul 8, 2019
Messages
62
Programming Experience
Beginner
Brilliant thanks, got it working now.
Appreciate your help.
 
Top Bottom