Sending Email Using C#

I thought I would change things up a bit today and actually talk about a little bit of C#.  I currently use Visual C# at work for some projects and some of those include sending email.  First, you need to make sure to add the System.Net.Mail namespace to your C# project.  This namespace gives you a list of different classes you can use to send email.  Once the namespace has been added, here is the code I use to create a method to send email.  It allows you to provide the body, subject and an attachment to the method:

public static string SendEmail(String body, String subject, Attachment attachment)
        {
            SmtpClient smtp = new SmtpClient("mail.companyname.com");

            string fromEmail = "my.email@companyname.com";

            MailMessage objEmail = new MailMessage();

            objEmail.IsBodyHtml = true;

            objEmail.To.Add("to.email@companyname.com");

            objEmail.From = new MailAddress(fromEmail, "My Name");

            objEmail.Subject = subject;

            objEmail.Body = body;

            objEmail.Attachments.Add(attachment);

            try
            {
             
                smtp.Send(objEmail);

                return "true";

            }
         
            catch (Exception exc)
         
            {
                return exc.Message;
             }

        }

If you wanted to create a method that sends an email, but doesn't include an attachment, use the following:

public static string SendEmail(String body, String subject)
        {
            SmtpClient smtp = new SmtpClient("mail.companyname.com");

            string fromEmail = "my.email@companyname.com";

            MailMessage objEmail = new MailMessage();

            objEmail.IsBodyHtml = true;

            objEmail.To.Add("to.email@companyname.com");

            objEmail.From = new MailAddress(fromEmail, "My Name");

            objEmail.Subject = subject;

            objEmail.Body = body;

            try
            {
             
                smtp.Send(objEmail);

                return "true";

            }
         
            catch (Exception exc)
         
            {
                return exc.Message;
             }

        }

These methods are pretty basic, since the email addresses are hard-coded.  You will want to create your own method if your "to" email is dynamic or you want to pass the email values into the method.

SQL CHOOSE

SQL Server has a list of logical functions that can be used on data.  The first of these logical functions is the CHOOSE() function.  This function will return the value identified at the specified index.  Here is what the syntax for the CHOOSE() function looks like:

CHOOSE(index value, value 1, value 2, value 3, etc.)

A pretty simple example of the CHOOSE() function looks like this:

SELECT CHOOSE(3, 'Executive', 'Senior Management', 'Vice President')

The above would return 'Vice President', since the index for lookup is 3 and the third value is 'Vice President'.

So how would a function like this be useful?  Well, we could use it to list out the months of the year for an employee's birthday, like so:

SELECT CHOOSE(Month(Birthday), 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December')
FROM Employee

How does this differ from what you might see using the CASE() function?  There isn't a difference.  CHOOSE() is just a short hand version of CASE().  It definitely looks neater than CASE(), since you don't have to do so much coding.  Using the example above, CASE() would look something like this:

SELECT CASE Month(Birthday)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
FROM Employee

As you can see, much mode coding needs to be done, while also adding the value you are looking for.  CHOOSE() already knows the value you are looking for and selects it based on the order in the provided value list.  Any value searched for that is not contained in the value list (such as 13 for our months) will return a NULL value.

How do you think you could use the CHOOSE() function at your organization?

To read up more on SQL CHOOSE(), go here: SQL CHOOSE

SQL SUBSTRING

There may be reasons why you may only need part of the text and not the whole thing.  For example, if you wanted to organize your customer list by first initial of their company name.  How would you do this?  SQL Server has a function called SUBSTRING().  This function allows for you to specify your starting position and the number of digits you want to take.  If you want the first digit of a customer's company name, you would write your query like this:

SELECT SUBSTRING(CompanyName, 1, 1), *
from CustomerList

You could also use it to find out the Area Code of your customers, by getting the first 3 digits of their phone number:


SELECT SUBSTRING(PhoneNumber, 1, 3), *
from CustomerList


Unlike other programming languages, SQL's SUBSTRING() function is 1-based, while other programming languages are 0-based.  This means that if you want the first digit in SQL, you use 1.  With C#, for example, if you wanted the first digit, you would use 0.

You can also run a query to see how many customers you have for each letter in the alphabet.  This could be done by combining the SUBSTRING() function with a group by, like so:


SELECT SUBSTRING(CompanyName, 1, 1), count(*)
from CustomerList
group by SUBSTRING(CompanyName, 1, 1)
order by SUBSTRING(CompanyName, 1, 1)


SQL SUBSTRING()

What data could you see using the SUBSTRING() function on?

Question of the Day

A question I put together has been featured as the "Question of the Day" at one of the sites I regularly visit (sqlservercentral.com).  Go check it out and see if you can get the answer right (you will have to register):

CHARINDEX

Here's some help if you need it:

SQL CHARINDEX

SQL SUBSTRING

Using EXCEPT in SQL

EXCEPT is an Operator in SQL that will return any distinct values found in the "left" table that is not found in the "right" table.  How can this be useful?  Let's say that one day you notice you have some data corruption in your database.  Your Orders table has some orders that are not found in the OrdersDetails table.  How do we easily figure out which orders are missing?  Our initial query could look something like this:

SELECT distinct OrderNumber
from Orders o
LEFT OUTER JOIN OrdersDetails d
on o.OrderNumber = d.OrderNumber
WHERE d.OrderNumber is null

This gets the job done, but doesn't look very organized.  We have to make sure we:

• Have the correct join (LEFT or RIGHT, depending on which table has the rows we want)
• Make sure the NULL is on the correct table (NULL would return where rows don't exist).

An easier and more efficient way to do this is to use the EXIST Operator in SQL:


SELECT OrderNumber
from Orders
EXISTS
select OrderNumber
from OrdersDetails

You will want to make sure that your data types for comparing are the same.  If not, it is possible you will not get expected results.

Where do you think you could use EXCEPT in your organization?

To read more on EXCEPT, go here: SQL EXCEPT

SQL Having Clause

Let's say you have a query that you want to run, where you summarize customer information (order totals, number of calls, etc.).  With your query, though, you only want to include customers that have over a certain value for what you are summarizing.  The SQL WHERE clause doesn't allow for aggregate functions, so what do you do?

To exclude customers under a certain value, you would have to use the SQL HAVING Clause.  There are a few things you need to keep in mind when using the HAVING Clause.

• The HAVING Clause occurs after your GROUP BY.
• To use the HAVING Clause, you must have a GROUP BY because you are aggregating data together
• You do not need a WHERE Clause

So, how does the HAVING Clause work?  Let's say that we want to see the total dollar value of a customer's order.  The statement would look something like this:

SELECT LastName, SUM(Total) Total

from OrderDetails

Group By LastName

Now, we want the query to return customers whose sum total is greater than $100.  That query would look like this:

SELECT LastName, SUM(Total) Total

from OrderDetails

Group By LastName

Having SUM(Total) > 100

We can also throw a WHERE Clause in there if we just want it returned for customers that have a certain last name:

SELECT LastName, SUM(Total) Total

from OrderDetails

WHERE LastName = 'Smith'

Group By LastName

Having SUM(Total) > 100

The HAVING Clause can also be used for non-aggregate functions, but at that point, it is better to use the WHERE Clause.  This is because the WHERE Clause removes the data before the result set is returned, while the HAVING Clause is applied to the rows after the result set has been returned.

Read more on SQL HAVING Clause here: SQL Having Clause

Using SQL Mail Part 2

Yesterday we talked about setting up SQL Mail.  Today we are going to talk about how we can take advantage of SQL Mail.  To do so, we need to be familiar with the sp_send_dbmail stored procedure.  This stored procedure allows for sending emails to a given list of recipients and many other options:

The format for the stored procedure looks something like this:

exec msdb.dbo.sp_send_dbmail @profile_name = 'profile_name'
    ,@recipients = 'recipients'
    ,@copy_recipients = 'copy_recipient'
    ,@blind_copy_recipients = 'blind_copy_recipient'
    ,@from_address = 'from_address'
    ,@reply_to = 'reply_to'
    ,@subject = 'subject'
    ,@body = 'body'
    ,@body_format = 'body_format'
    ,@importance = 'importance'
    ,@sensitivity = 'sensitivity'
    ,@file_attachments = 'attachment'
    ,@query = 'query'
    ,@execute_query_database = 'execute_query_database'
    ,@attach_query_result_as_file = attach_query_result_as_file
    ,@query_attachment_filename = query_attachment_filename
    ,@query_result_header = query_result_header
    ,@query_result_width = query_result_width
    ,@query_result_separator = 'query_result_separator'
    ,@exclude_query_output = exclude_query_output
    ,@append_query_error = append_query_error
    ,@query_no_truncate = query_no_truncate
    ,@query_result_no_padding = @query_result_no_padding
    ,@mailitem_id = mailitem_id OUTPUT

As you can see, there are many different options available when using this stored procedure.  Here are some items of note on the variables:

• @profile_name is the Profile Name used in the previous post.  The email will not go out if the profile_name is not spelled correctly.
• @recipients is a comma separated list of recipients.
• @body_format is the format to use for the body of the email.  The options are "Text" and "HTML".  The HTML options allows you to use HTML formatting for the email (which includes line breaks, etc.)
• @file_attachment is a comma separated list of files to attach to the email.
• @query (query to execute), @execute_query_database (database to execute the query on), @attach_query_result_as_file (selected whether to put the results as an attachment or in the body of the email, and @query_attachment_filename (the name for the attached query results) can all work together to give you different options when sending your email.

Once you have decided on your different options, you can then format your email message and include the SQL stored procedure when scheduling jobs or directly into stored procedures you have written.  You can then attach any results you want into the email.

As an example, let's say that I set up a stored procedure that emails customers once they have made a purchase.  I have variables that I would use in the stored procedure to perform this.  Those variables could include: @customernumber (customers account number), @ordernumber (order number), @customeremail (customers email address).  Using these variables, I could then send out an email on order like so:

declare @query1 varchar(4000) = 'select * from CustomerOrder where custombernumber = ' + @customernumber

exec msdb.dbo.sp_send_dbmail @profile_name = 'Test Profile'
    ,@recipients = @customeremail
    ,@from_address = 'myemail@gmail.com'
    ,@reply_to = 'myemail@gmail.com'
    ,@subject = 'Customer Order'
    ,@body = 'Attached is the information for your order'
    ,@body_format = 'HTML'
    ,@query = @query1
    ,@execute_query_database = 'MyDB'
    ,@attach_query_result_as_file = 1

To read more on SQL Mail, visit the following: SQL Mail

Using SQL Mail Part 1

You may have reports for your organization that you would like to mail out each day, but you don't have the time to run them manually.  So, what are your options?  They are as follows:

• Create an SSIS (SQL Server Integration Services) package and within it create a Mail process.
• Configure SQL Mail and use sp_send_dbmail stored procedure to send your reports.

The first choice (SSIS) is the simplest option and allows for a very minimal knowledge of T-SQL using system stored procedures.  The second choice allows for more options, but is also more complicated to set up.  The article will mostly focus on using the second option.

First, you have to configure Database Mail.  To do this, go in SSMS (SQL Server Management Studio), expand Management and then right click Database Mail (as seen below).  Then, select Configure Database Mail.

A window will then open.  In this new window, select Management Database Mail account and profile and click Next.

Then, select Create a new account and click Next.

In this new window, you will want to enter your mail settings.  Get it an account name and description.  The description should be one that you can use easily to identify the account later.  Then, enter your outgoing mail settings, which include e-mail address, display name, reply e-mail and server name/port number.

It should be noted that you can have a different Reply e-mail address from the E-mail address.  The e-mail address is used to send the email, while the reply e-mail is used when the person receiving e-mail hits reply.  This is useful if you need to send an e-mail from your address, but want any replies sent to a different e-mail address.

You will also need to enter your SMTP authentication, if need be.  Refer to your IT department to get these settings.

Once all information has been entered, click Next.

SQL will then give you all the steps that will be taken when the wizard finishes.  Click Finish.

If there were any errors, they will be shown in this screen.  Otherwise, click Close to finish the wizard.

Next, you will need to set up an e-mail profile.  Go back to the Configuration wizard and this time select Set up Database Mail by performing the following tasks.  Then click Next.

In this new window, enter your profile name and description.  From here, you will select which accounts you want assigned to the profile.  Now, why would you select more than one profile?  Redundancy, in the case one profile fails, it will go to the next profile.  Click Add.

Select your account from the Account Name drop down list and click OK.  If you want to add more than one account, click on the Add button again.

Your accounts will then be added to the list.  Click Next.

If you want your profile to be available for all databases, make it a public profile.  Click Next.

You can then change system parameters for your e-mails.  For example, most mail servers can handle files between 5MB and 10MB in size.  You can set your database mail to allow file sizes greater or lower using the Maximum File Size option.  Once you have made necessary changes, click Next.

You will then see a list of changes that will be made.  Click Finish.

SQL will then make the appropriate changes.  Click Close when done.

Tomorrow we will cover how we can then take advantage of SQL Mail in our processes.

Using DATEDIFF() in SQL

Let's say that you wanted to know the difference between two dates.  Do you want to know the difference in months, days, years, minutes, or seconds?  SQL has a built-in function that allows you to do this.  The name of this functions is DATEDIFF (or date difference).  The function works as follows:

DATEDIFF ( DatePart, StartDate, EndDate)

DatePart is the part of the date that you want to see the difference on.  For example, to see the difference in years, your DatePart would be yy or yyyy.  Some other examples as below:

• Month = mm or m
• Hour = hh or h
• Minute = n or mi
• Second = s or ss
• Day = d or dd

So, where can a function like this be valuable?  I work in the telecom industry and sometimes we have to calculate how long a customer was on a call using the raw records.  In the records, there is a start time and an end time.  I could use DATEDIFF() to find out how many seconds they were on the call, as follows:

SELECT DATEDIFF(ss, StartTime, EndTime) 

FROM CDRTable

As another example, let's say that you are an online sales company and you want to see how many days it took for a package to get to a customer:

SELECT DATEDIFF(dd, ShipDate, ReceivedDate) 

FROM ShippingTable

Make sure that you have the correct date in the correct columns.  If we reversed our date columns (like below), we would end up with a negative number:

SELECT DATEDIFF(dd, ReceivedDate, ShipDate) 

FROM ShippingTable

Where do you think you could use this in your organization?

To read up more on the DATEDIFF function, go here: DATEDIFF()

SQL Stored Procedures

Yesterday I talked about SQL Functions and their benefits.  Today I wanted to talk about SQL Stored Procedure.  Much like a SQL Function, a Stored Procedure takes in parameters and performs certain actions.  A Stored Procedure can do more, though, which would include the following:

• Update data within the database
• Return more than one value
• Call (run) other stored procedures
• Return status flag on whether or not the process completed successfully or failed

So why would someone have need for a SQL Stored Procedure?  Let's take the situation, for example, that you are an online company that takes sales orders, much like Amazon.com or eBay.  Every time someone completes an order, you have two choices.

1. Have the web interface (website) run certain insert statements into your database so the order is tracked.
2. Have the web interface (website) run one or multiple Stored Procedures to insert the order into your database

Both options are perfectly viable, but for option #1, anyone who understands HTML code can easily capture your statements and use it to hack into your database.

The second option, on the other hand, not only obfuscates (hides) data about your database, but you can also more easily control what happens if an error occurs at any moment in the insert.  This could include trying to run the stored procedure again if it fails, or show a pop-up window to a customer on failure.  These stored procedures can also be set up to fulfill your company's business rules.  And then, if for whatever reason your business rules change, you just have to update your stored procedure, instead of your website.

The syntax to create a stored procedure looks like the following:


CREATE PROCEDURE  
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> = , 
<@Param2, sysname, @p2> =
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END

If I wanted to create a stored procedure that returned my name, it would look like this:

CREATE PROCEDURE myName

AS

BEGIN

SELECT 'Bill Lantz'

END

Of course, stored procedure can be much more complicated than this.  For instance, if you wanted to allow someone to provide a customer first name, last name, and phone number and then insert it into your customer table, it would look something like this:


CREATE PROCEDURE insertCustomer
(
@LastName varchar(100),
@FistName varchar(100),
@PhoneNumber varchar(10)
)
AS
BEGIN

Insert into Customer
Values (@LastName, @FirstName, @PhoneNumber)

END

How do you think SQL Stored Procedures would work at your organization?

SQL Functions

Let's say that in SQL you want to add values in a single column for all rows returned in your query.  Maybe you want to count the number of rows that are returned from your query.  Or maybe you want to find the average of the values for all rows using the same column.  It is possible to do every one of these situations using something called SQL Functions.

What is a SQL Function? A function is a routine that takes the parameters you provide, performs a certain set of actions, and then returns the results from those actions.  For example, using the SUM(), COUNT() and AVG() functions, you can accomplish the situations above.

Now, there are two types of functions in SQL Server.  They are as follows:

• Built-in functions
• Created by SQL Server
• User defined functions
• Created by the user

Built-in functions include those mentioned above, as well as many others, such as

• UPPER()
• Shows all letters in uppercase
• LOWER()
• Shows all letters in lowercase
• MAX()
• Maximum value in a column
• MIN()
• Minimum value in a column
• And many more

A user can also create a function for his or her needs.  The syntax looks like this:

--Transact-SQL Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH  [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN scalar_expression
    END
[ ; ]

For example, let's say that I need a function that calculates the square footage.  My function would look like this:

CREATE FUNCTION sqFeet
(@SideOne decimal(4,1), @SideTwo decimal(4,1))

RETURNS decimal(14,3)

AS

BEGIN

RETURN (@SideOne * @SideTwo)

END

To call your function, you would then run the following query:

SELECT sqFeet(2, 2)

Which would return the result: 4

Now you might be asking: what are the benefits of using SQL Functions instead of using something else, like SQL Stored Procedures? Well, SQL Functions allow you to return results inline, instead of having to execute a stored procedure and returning the results as an output parameter.  Also, a SQL Function can be used at different parts within a T-SQL statement, whether in the SELECT statement, WHERE or HAVING clauses.

Where can you see using a SQL Function in your organization?

Non-SARGable Queries

As I've been doing some research online, I've come across a term that I am not familiar with.  This term is SARGable and is used in reference to the WHERE clause of select statements.  SARG actually stands for Search ARGument able.  Basically, if you have an index or multiple indexes on a table, a SARGable WHERE clause can take advantage of your indexes.  On the other hand, a non-SARGable WHERE clause cannot take full advantage of the indexes and will cause your query to take longer to run.

Keep in mind that this only applies to WHERE clauses.  If these statement are used in your SELECT statement, it will not affect performance.

So, what makes a query non-SARGable?  Here are some examples:

• Using LIKE with a leading %
• SELECT...... WHERE LastName like '%mith'
• Using a field within a function in the WHERE clause
• SELECT...... WHERE RIGHT(LastName, 5) like 'mith'

Each of these queries skips the index and uses a table scan, causing your query to run longer and resources to be tied up.  So what can we do instead?

• Use =, >, <, >=, <=, BETWEEN, LIKE without a leading %
• SELECT....... WHERE LastName like 'Smith%'
• Use IN, OR, NOT IN, NOT EXISTS, NOT LIKE
• SELECT....... WHERE LastName not in ('Smith')

Using these clues can increase your performance and should be used at all times when possible.

Wikipedia on "SARGable"

REPLACE Function

Let's say that you have a customer database and you need to update all of your customers' addresses.  You need to change every instance of "Street" to "St."  How would you go about easily doing this using a T-SQL query?

Microsoft SQL Server has a nifty function called REPLACE().  Following is the basic syntax for the REPLACE function:

REPLACE ( string_expression , string_pattern , string_replacement )

In our example above, we would put the field of our address in the "string_expression", the value "Street" in the "string_pattern" and "St." in the "string_replacement:.  You would have a query much like this:

update customer
set address1 = REPLACE(address1, 'Street', 'St.')
where address1 like '%Street%'

You can also use REPLACE() to reference a value in one field to update another field.  For example, instead of using strings in "string_pattern" and "string_replacement", you can use fields in your table.  If the customer table contains both a phone number and an extension for your customer, you could use this to update the customer's call records.  A query would look something like this:

update RatedCall
set CallingNumber = REPLACE(CallingNumber, ANI, Customer.Extension)
from RatedCall, Customer
where RatedCall.CustomerNumber = Customer.CustomerNumber

Where ANI is the customer's phone number and extension is their extension (CallingNumber is the ANI is the call record).  This would only update instances where CallingNumber is the customer's phone number.

In what cases at your organization could you see using the REPLACE() function?

To read up more on SQL REPLACE(), go here: SQL REPLACE()

SQL Indexes and Their Benefits

As the data in your database grows, you may notice that your querying becomes slower.  This occurs because SQL Server needs to search through each record until it finds all the records you are looking for.  So, when this happens to your database, how do we approach the problem?

The answer to this lies in something called indexes.  An index is described as a structure on a table or view that speeds up the retrieval of data.  In SQL Server, there are two major types of indexes:

1. Clustered Index
• Clustered indexes sort and store the data based on key values (specified by you).
• Each table can only contain one clustered index.
2. Nonclustered index
• Nonclustered indexes do not sort and store the data like clustered indexes do.  They basically have a pointer to the data based on the key values selected.
• The pointer is actually called a row locator.
• Depending on your version of SQL Server, you can have quite a few nonclustered indexes.

When performing a search on table, SQL Server will use the least labor intensive method.  Indexes will be the least labor intensive (finds storage location for the data) than a non-indexed table (table scan), which needs to search through each row to find a match and can be resource intensive.

Now, it is very important to put in time considering what should be included in your indexes.  For example, clustered indexes should be fields that have unique data (primary key).  Nonclustered indexes could be created on other columns that you search on frequently and multiple columns that are searched together could be included on the same nonclustered index.  Be careful, though, because adding too many indexes (one of every column) will cause too much overhead.

Don't be afraid to try out different indexes on your data.  Put an index in place, query the table, and see if your querying improves.  If not, try a different index and keep trying until you feel like you have a couple that fit your needs.

Clustered and Nonclustered Indexes Described

Database Files, Recovery Models and Backups Part 2

Yesterday we talked about the .MDF and .LDF files and what their purpose is.  We also talked about the different recovery models available in Microsoft SQL Server and which model to select for your database.

Now, I want to cover database backups.

First, two questions you might ask are:

1. What kind of backups should I do?
2. How often should I do them?

There are three major types of backups in SQL Server:

• Full Backup
• A data backup that contains all data within the given database.  It also contains enough log backup to allow for recovering all data
• Differential Backup
• A backup that contains all data that has changed since the last full or differential backup
• With differential backups, you will be required to restore each backup in order for the data to be in sync.
• Log Backup
• A backup of the transaction log since the last log backup.
• Transaciton log (.LDF file) can be shrunk once a log backup has been performed.  If you find your .LDF file is growing, run a transaction log backup and then shrink your .LDF file (Shrinking the Transaction Log)

For more information, review this Backup Overview

Deciding which backup (or combination of backups) should be selected, ask yourself this: how much data am I willing to lose?  Can you go a full 24 without backing up your data?  Do you need to have as much real time data as you can get?

Most applications will need a backup at least every 24 hours (whether full or differential).  If you can afford to lose 30 minutes to an hour of data, a full backup each day accompanied by a differential backup every 30 minutes might suffice what you need.  If you need a solution that is more real-time then thirty minutes, there are other options that we will not cover in this article (non-Microsoft applications, Replication, and Mirroring [which has been deprecated]).

To read more about backup and restore strategies, go here: Backup and Restore Strategies