Receiving an auto-generated email is often unpleasant, but when deployed correctly, they can be useful for both customers and companies. C# and SQL make it easy to grab information from a database and send customized emails with little effort.
Step one: grab the data.
Before sending emails, information from the database, like email addresses, needs to be collected by a query. A SQL query is just a string that asks a database for a specific table, but users should be able to dynamically change it depending on their needs. As such, certain lines need to be flexible. In the past, I had used string formatting to insert text into the query, which looks like the following:
string text = "I ilke {0} more than {1}, honestly";
text = string.Format(text,"dogs",cats");
While this way to format a string is useful, it quickly gets confusing when there are multiple parameters. A SQL query might have lots of variables, and so that line of code would not be very easy to read. Also, the query would need to be manually filled with markers, which means porting back and forth between SQL Server Manager and Visual Studio would be a pain. An alternate way to format queries is to use a SqlParameter, which comes from a C# SQL module:
SqlCommand sqlComm = new SqlCommand(sqlQuery, sqlConn);
SqlParameter start = new SqlParameter;
start.ParameterName = "@startDate";
start.Value = "10/05/1991";
sqlComm.Parameters.Add(start); |
The SQL query needs to be prepared with undeclared local variables, like “@startdate”, in the query itself, like the simplified one below.
SELECT DISTINCT
emailAddress,
fullName
FROM contract c
WHERE c.expireson BETWEEN @startDate and @endDate
|
This method is more useful because variables in SQL are declared using “@” notation, and so the transition from the SQL testing world to C# is smoother. It is also more secure, as it reduces the chance of a SQL injection attack by separating the query from the user’s input by an extra layer. On the negative side, this technique requires four lines of code per variable, and is much longer, albeit better organized.
Step two: send the email.
Sending the email is straightforward if you know the name of the server to send from. At its core, a user needs only to instantiate a System.Web.Mail.MailMessage message, pass some intuitive arguments, and simply Send().
System.Web.Mail.MailMessage message = new System.Web.Mail.MailMessage();
message.To = "Goofy@Goober.com";
message.From = "RoboMail@Computers.com";
message.Body = "Don't hate me cuz I'm metal!";
message.Subject = "Robots never get emails back";
SmtpMail.SmtpServer = "EmailServer";
SmtpMail.Send(message); |
This produces an email with Courier New monospaced font, straight from the 80s. There is no way to make that look natural, organic, or anything close to user-friendly. Lucky for us, we can do better—we can make the email have a more flexible HTML body. By simply passing “message.BodyFormat = mailFormat.Html”, we can replace our plain text email with one that has HTML markup, like the one below. One needs a basic understanding of HTML, but a useful testing environment can be found at W3 Schools, which has an interactive HTML to plain text converter.
private string htmlTemplate =
@" <p> Hi {0}, </p></br> <p>Even though I'm an autogenerated email, please do not trash me!
</p> </br><p> Robomail </p> |
By default, an HTML email is size 12 Times New Roman, which is infinitely more readable than Courier. The above email template has a marker, the 0 enclosed in curly braces in the first line, for string formatting, marking where the recipient’s first name should go. There are a host of formatting options available for the inserted string, such as turning it into a date, and those options can be found here. And that’s it–happy spamming.
Follow