Send Mass Emails using Database Queue in ASP.NET, VB.NET

In previous section, I introduced how to send email EASendMail Service Queue. In this section, I will introduce how to send mass emails with advanced database queue in ASP.NET/VB.NET.

Introduction

Although EASendMail service provides a faster way to send email in background, but there are thousands of emails in a task, the SendMailToQueue will be invoked for thousands times, obviously it is not effect way.

Therefore, EASendMail service provides a more effective way to send mass emails. In short, you just need to submit your database connection and record set once, EASendMail service will pick up the record set in background and send email to each record one by one. It is very useful to send mass emails in ASP.NET web application.

send email using database queue in VB.NET/ASP.NET

To better understand how to use database queue, we need to create three tables in your SQL database like this:

CREATE TABLE [dbo].[rcpts](
    [uid] [bigint] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
    [email] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_rcpts] PRIMARY KEY CLUSTERED
(
    [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[errorlog](
    [uid] [bigint] IDENTITY(1,1) NOT NULL,
    [email] [nvarchar](128) NULL,
    [server] [nvarchar](50) NULL,
    [errorcode] [nvarchar](50) NULL,
    [errordescription] [nvarchar](255) NULL,
 CONSTRAINT [PK_errorlog] PRIMARY KEY CLUSTERED
(
    [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[sentlog](
    [uid] [bigint] IDENTITY(1,1) NOT NULL,
    [server] [nvarchar](50) NULL,
    [email] [nvarchar](128) NULL,
 CONSTRAINT [PK_sentlog] PRIMARY KEY CLUSTERED
(
    [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Then insert two records in table ‘rcpts’ like this:

SQL table sample

Note

Remarks: All of samples in this section are based on first section: Send email in a simple VB.NET project. To compile and run the following example codes successfully, please click here to learn how to create the test project and add reference of EASendMail to your project.

[VB.NET/ASP.NET - Send Mass Emails with EASendMail Service Database Queue - Example]

The following example codes demonstrate how to send mass emails using email queue + database in VB.NET/ASP.NET.

Note

To get the full sample projects, please refer to Samples section.

' Add EASendMail namespace
Imports EASendMail

Module Module1

    Sub Main()

        Try
            Dim oMail As New SmtpMail("TryIt")

            ' Set sender email address, please change it to yours
            oMail.From = "test@emailarchitect.net"
            ' Set email subject
            oMail.Subject = "test email from VB.NET project"

            ' If you want EASendMail service to send the email after 10 minutes,
            ' use the following code.
            ' oMail.Date = System.DateTime.Now.AddMinutes( 10 );

            ' change it to your sql server address, database, user and password.
            ' The server/instance name syntax used in the server option is the same for all SQL Server connection strings.
            ' e.g.: Server=serveraddress\instancename;
            ' EASendMail will use the following connection to connect to the database,
            ' the syntax is same as ADO connection object.

            ' MS SQL 2005
            oMail.Headers.ReplaceHeader("X-Data-Connection", _
            "Driver={SQL Native Client};" & _
            "Server=serveraddress;Database=database;Uid=user;Pwd=password;")

            ' MS SQL Server 2005 Native provider
            ' oMail.Headers.ReplaceHeader("X-Data-Connection", _
            '  "Provider=SQLNCLI;Server=serveraddress;Database=database;Uid=user;Pwd=password;")

            ' MS SQL Server 2008
            ' oMail.Headers.ReplaceHeader("X-Data-Connection", _
            '  "Driver={SQL Server Native Client 10.0};Server=serveraddress;Database=database;Uid=myUsername;Pwd=myPassword;")

            ' MS SQL Server 2008 Native provider
            ' oMail.Headers.ReplaceHeader("X-Data-Connection", _
            '  "Provider=SQLNCLI10;Server=serveraddress;Database=database;Uid=user;Pwd=password;" )

            ' MS SQL Server 2012
            ' oMail.Headers.ReplaceHeader("X-Data-Connection", _
            '  "Driver={SQL Server Native Client 11.0};Server=serveraddress;Database=database;Uid=myUsername;Pwd=myPassword;")

            ' MS SQL Server 2012 Native provider
            ' oMail.Headers.ReplaceHeader("X-Data-Connection", _
            '  "Provider=SQLNCLI11;Server=serveraddress;Database=database;Uid=user;Pwd=password;")

            ' EASendMail will select the fields by the following sql statement
            ' before sending email,
            ' then pick the recipient address from specified field.
            oMail.Headers.ReplaceHeader("X-Sql-Select", "SELECT uid, name, email FROM Recipients")

            ' pick "name" field as the recipient name and "email" field as
            ' the recipient address.
            ' you can also use {$var_srecord:fieldname} to pick any field
            ' in X-Sql-Select statement and put it to subject, bodytext, then EASendMail will
            ' replace it automatially.
            oMail.Headers.ReplaceHeader("To", """{$var_srecord:name}"" <{$var_srecord:email}>")
            oMail.Headers.ReplaceHeader("X-Rcpt-To", "{$var_srecord:email}")

            ' EASendMail service will execute the following sql statement on
            ' every email was sent successfully.
            oMail.Headers.ReplaceHeader("X-Sql-OnSentSuccess", _
            "INSERT INTO sentlog ( server, email ) VALUES('{$var_server}', '{$var_rcptaddr}')")

            ' EASendMail service will execute the following sql statement on
            ' every email could not be sent.
            oMail.Headers.ReplaceHeader("X-Sql-OnSentError", _
            "INSERT INTO errorlog( email, server, errorcode, errordescription )" & _
            " VALUES( '{$var_rcptaddr}', '{$var_server}', '{$var_errcode}', '{$var_errdesc}' )")

            Dim s As String = "Hi {$var_srecord:name}, " & vbCrLf
            s += "this sample demonstrates how to send email using email queue." & vbCrLf & vbCrLf
            s += "Your id in database is {$var_srecord:uid}." & vbCrLf & vbCrLf
            s += "No matter how many recipients there are, EASendMail "
            s += "service will send the email in background." & vbCrLf
            oMail.TextBody = s

            ' {$var_srecord:uid} {$var_srecord:name} {$var_srecord:email} in
            ' body text will be replaced by EASendMail automatically.

            ' Your SMTP server address
            Dim oServer As New SmtpServer("smtp.emailarchitect.net")

            ' User and password for ESMTP authentication
            oServer.User = "test@emailarchitect.net"
            oServer.Password = "testpassword"

            ' Most mordern SMTP servers require SSL/TLS connection now.
            ' ConnectTryTLS means if server supports SSL/TLS, SSL/TLS will be used automatically.
            oServer.ConnectType = SmtpConnectType.ConnectTryTLS

            ' If your SMTP server uses 587 port
            ' oServer.Port = 587

            ' If your SMTP server requires SSL/TLS connection on 25/587/465 port
            ' oServer.Port = 25 ' 25 or 587 or 465
            ' oServer.ConnectType = SmtpConnectType.ConnectSSLAuto

            Console.WriteLine("start to send email ...")

            Dim oSmtp As New SmtpClient()
            ' You just need to change SendMail method to SendMailToQueue method in
            ' your ASP.NET web application, then EASendMail uses queue to send email.
            ' SendMailToQueue can be used in windows application as well.
            oSmtp.SendMailToQueue(oServer, oMail)

            ' If you want to use SMTP server setting in EASendMail Service Manager, use
            ' oSmtp.SendMailToQueue(Nothing, oMail)

            Console.WriteLine("email was sent queue successfully!")
        Catch ep As Exception
            Console.WriteLine("failed to send email with the following error:")
            Console.WriteLine(ep.Message)
        End Try

    End Sub

End Module

With above codes, no matter how many records in your table, SendMailToQueue is only invoked once and EASendMail service will pick up records in background and send the email based on each record to recipient one by one. It also inserts the results back to “errorlog” and “sentlog” tables automatically. You can open EASendMail Service Manager to monitor the queue and view Journal -> System Error to check if there is error with your database connection and SQL statement.

Database Server Driver

In X-Data-Connection header, you should specify a database driver to connect database. You can open “Control Panel” -> “Administrative Tools” - > “ODBC Data Sources” - “Drivers” to check current installed database drivers.

odbc drivers

Common SQL Driver Download

If SQL Server is installed on a remote server, and you don’t have SQL driver installed on local machine, then you need to download and install corresponding driver on local machine.

MS Access Database x64 Driver Download

Microsoft Access Database Engine 2010 Redistributable

See Also

Send Email in SQL Server Stored Procedure - Tutorial

Next Section

At next section I will introduce how to send email using Exchange Web Service (EWS).

Appendix

Comments

If you have any comments or questions about above example codes, please click here to add your comments.