question

Dimples avatar image
Dimples asked

Convert C# code into a SP

This c# code will send reminders out to the Managers, to remind them of a clients birthday. How can I do this using cursor's or any other method in sql. Please help me convert this into a SP. Everthing ele is in a SP its just this part I need to add onto it, Thanks in advance. The c# code (Assume variable have been declared) For the Reminderdata can I use a #table while (rowIndex < ReminderData.Tables[0].Rows.Count) { (A) ManagerID = (int)Reminderdata.Tables[0].Rows[rowIndex]["ManagerID"]; While (ManagerID == (int)Reminderdata.Tables[0].Rows[rowIndex]["ManagerID"]) { if (EmployeeID != (int)Reminderdata.Tables[0].Rows[rowIndex]["EmployeeID"]) { EmployeeID = (int)Reminderdata.Tables[0].Rows[rowIndex]["EmployeeID"]; // Assign Email Address } if (new Guid(ReminderData.Tables[0].Rows[rowIndex]["ClientID"].ToString()) != ClientID) { string gender = (Convert.ToInt16(Reminaderdata.Tables[0].Rows[rowIndex]["GenderL"]) == 3) ? "she" : "he"; Remindermeassage += newLine + "It will be the birthday of " + ReminderData.Tables[0].Rows[rowIndex]["ClientName"].ToString() + " on " + Reminderdata.Tables[0].Rows[rowIndex]["BirthDate"].ToString() + ", " + gender + " is turning " + age + ". (contact number - " + Reminderdata.Tables[0].Rows[rowIndex]["TelNumber"].ToString() + ")"; } rowIndex++; } if (Manager == EmployeeEmail || EmployeeEmail == "@ Domian.co.za;") {EmployeeEmail = "";} Email.SendMail(EmployeeEmail, "Birthday reminder", Remindermeassage, EmployeeEmail, ""); reportBack(null, new ReportBackEventArgs(Remindermeassage + " sent to " + ManagerEmail + " cc to " + EmployeeEmail)); }
stored-procedures
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aRookieBIdev avatar image
aRookieBIdev answered
Even if it is a stored procedure , you will have to schedule it every day. To make it simpler you can try out SSIS packages where u can write some sql query(c# also supported) and configure the send mail task to achieve this.The package can then be scheduled using a Job.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image Pavel Pawlowski commented ·
+1 I support the idea of writing this kind of stuff in SSIS. You get the birthdays and email addresses etc. by a query and then process them and send appropriate emails. As even a stored proc is used with `sp_send_dbmail` it has to be scheduled. The SSIS approach is also more comfortable.
1 Like 1 ·
Dimples avatar image Dimples commented ·
Hey, Where can I get an e.g. of the ssis pacakages can you please send me a link that I can maybe use, to guide me on how to go about doing this.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
This should give you a start: DECLARE @ManagerEmail varchar(255), @EmployeeName varchar(255), @EmployeeAge tinyint, @EmployeeBirthdate datetime, @EmployeeGender bit, @EmployeeTelefon varchar(20), @Emailbody varchar(max) /* This is a cursor (a looping construct in SQL Server)*/ DECLARE EmailReminder CURSOR FAST_FORWARD READ_ONLY FOR /* You define a select statement to fill the cursor with data, you change this to fit your schema*/ SELECT ManagerEmail, EmployeeName, EmployeeAge, EmployeeBirthdate, EmployeeGender, EmployeeTelefon FROM MyBirthdayView WHERE Something = SomethingElse /* You then open the cursor and fill it with the first set of values*/ OPEN EmailReminder FETCH NEXT FROM EmailReminder INTO @ManagerEmail, @EmployeeName, @EmployeeAge, @EmployeeBirthdate, @EmployeeGender, @EmployeeTelefon /* If there is data, prepare it and send an email using the system sproc sp_send_dbmail (asynchronous email processor inside SQL Server)*/ WHILE @@FETCH_STATUS = 0 BEGIN SET @EmailBody = 'This is your message body, do what you need to do' EXEC msdb.dbo.sp_send_dbmail @recipients = @ManagerEmail, @subject = 'Birthday Reminder Mail', @body = @EmailBody /* Continue the loop */ FETCH NEXT FROM EmailReminder INTO @ManagerEmail, @EmployeeName, @EmployeeAge, @EmployeeBirthdate, @EmployeeGender, @EmployeeTelefon END /* Cleanup */ CLOSE EmailReminder DEALLOCATE EmailReminder Cursors should be used sparingly in SQL server, they are not as efficient as loops in languages like C#. If I were you, I'd leave the email stuff in C# if you already have it.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dimples avatar image Dimples commented ·
Thanks, for the Input, I loop in twice, firt to check for the data in Reminderdata, them The number of reminders send per manager(One email with multiple Names depending how mant clients belong to the manager are sharing a birthday), so I can have a nested cursor? E.g. 2 differnt clinets can share a birth day.
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
Sorry, you can nest loops you would define and run that cursor inside the first (where I started sending the email).
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.