VS.NET 2010 (and above) Users Click Here   

HOMECONTACT PRODUCTS DOWNLOADS PURCHASE TESTIMONIALS FORUMS COMPANY CONTACT
Home
Products
Downloads
Purchase
Licensing
Licensing FAQ
Software Updates
Support Forums
Testimonials
Feature Requests
Guarantee
About Us
Contact Us
Hosting Companies
Privacy Policy
   
Shopping Cart


Search

aspNetEmail: Adding Email Functionality to SQL Server with aspNetEmail

by Dave 6. March 2011 00:17

The following steps can be taken to use aspNetEmail from inside of SQL Server.

1)Make sure database Trustworth is set to on. This is found in the database properties. To set it call:

ALTER DATABASE "database name" SET TRUSTWORTHY ON;.

Here is a real example:

ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON;.

2) Because aspNetEmail depends upon System.Web, you will need to set a reference to it in SQL Server.
This can be done by executing:

CREATE ASSEMBLY SystemWeb from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'
with permission_set = unsafe
GO

Be sure the path to the System.Web.dll matches the path on your server machine.

3)To  import aspNetEmail into SQL server, expand the Programmability --> Assemblies folder (under your Database name).
Right-click the Assemblies folder, and select "New Assembly"
For perssion set, select "unrestricted"
For path, browse to the aspNetEmail filesystem location. By default, it is located under c:\program files\advancedintellect\aspNetEmail\
Set the permission set to Unrestricted

4)Verify your SQL Server is CLR enabled by running the following T-SQL statement:

sp_configure 'clr enabled',1

followed by either a server stop/re-start, or executing:

reconfigure

5)Open Visual Studio, and create a SQL database project

6)In the Visual Studio Solution Explorer pane,  right-click References, and select "Add Reference".

7)The Add References dialog box appears.

8)Under the SQL Server tab, select aspNetEmail.

9)Choose Project->Add User Defined Function, and name the .cs file anything you like, such as "EmailHelper.cs".

Add the SendEmail() method so your code looks something like:

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString SendEmail()
    {
        SqlString result = new SqlString("success");

        EmailMessage.LoadLicenseFile("c:\\aspNetEmail.xml.lic");
   
        EmailMessage msg = new EmailMessage("127.0.0.1");
        msg.From = "me@example.com";
        msg.To = "you@example.com";
        msg.Subject = "My test email from sql server set at: " + DateTime.Now.ToString("yyyy-MM-dd HH:mm");
        msg.Body = "put the contents of your email body here...";
        msg.Send();

        return result;
    }

};

Once that is complete, you can now use your new function like any other User Defined T-SQL function.  For example,

select dbo.SendEmail()

This method can easily be modified to accept various parameters (such as recipients) to send emails to different people.


 

Sending Email from different local IPs

by Dave 4. March 2011 06:05

I recently had a question about aspNetEmail (thanks Clay!).  He wanted to know how he could use aspNetEmail to choose different local IPs for sending emails.

A little background
aspNetEmail creates it's own TCP/IP sockets to send email. What it does, is open a local TCP/IP socket, and establish a remote endpoint with the server specified by the .Server property.

When that local TCP/IP socket is created, aspNetEmail lets the .NET framework randomly select a local IP and Port to use. We can override the behavior by using the .LocalEndpoint property found on the EmailMessage object.

Here is a short but complete code example that demonstrates this functionality.


C#

EmailMessage msg = new EmailMessage( "127.0.0.1" );

msg.FromAddress = "me@mycompany.com";
msg.To = "you@yourcompany.com";

msg.Subject = "Your Order Confirmation Number is 12345";
msg.Body = "put our body contents here...";

//local endpoint -- this opens a local TCP/IP socket on IP 192.168.1.18 and port 5555
msg.LocalEndPoint = new IPEndPoint( IPAddress.Parse( "192.168.1.18" ), 5555 );

//if we only know the IP we want the socket open on, and not the port, we can 
//pass in 0 for the port, and the underlying OS will randomly pick an open port
//for example
//msg.LocalEndPoint = new IPEndPoint( IPAddress.Parse( "192.168.1.18" ), 0);

//any logging (not reqired) for troubleshooting
msg.LogPath = "c:\\temp\\email.log";
msg.Logging = true;

msg.Send();



VB.NET

Dim msg As New EmailMessage("127.0.0.1")

msg.FromAddress = "me@mycompany.com"
msg.To = "you@yourcompany.com"

msg.Subject = "Your Order Confirmation Number is 12345"
msg.Body = "put our body contents here..."

'local endpoint -- this opens a local TCP/IP socket on IP 192.168.1.18 and port 5555
msg.LocalEndPoint = New IPEndPoint(IPAddress.Parse("192.168.1.18"), 5555)

'if we only know the IP we want the socket open on, and not the port, we can 
'pass in 0 for the port, and the underlying OS will randomly pick an open port
'for example
'msg.LocalEndPoint = New IPEndPoint(IPAddress.Parse("192.168.1.18"), 0)

'any logging (not reqired) for troubleshooting
msg.LogPath = "c:\temp\email.log"
msg.Logging = True

msg.Send()

As always, if anyone has any questions, feel free to contact me over at Contact Us

Thanks!
Dave Wanta

 

Exchange 2010 and SMTP settings

by Dave 2. March 2011 04:43

Recently I had an email exchange with a customer about sending email through MS Exchange 2010, using aspNetEmail.

aspNetEmail had been running fine when they were emailing through Exchange 2007, however, when they upgraded, everything quit working, and started throwing 2 main errors.

The 2 errors were:

a) "504 5.7.4 Unrecognized authentication type"
And, once (a) was resolved, (b) happened:
b) "550 5.7.1 Client does not have permission to send as this sender"

Lets talk about these 2 errors.

504 5.7.4 Unrecognized authentication type
In this particular instance, the customer was using the AuthLogin authentication scheme in aspNetEmail. However, starting in Exchange 2010, the only authentication mechanism enabled is NTLM. On their Exchange 2007 server, they had other authentication schemes enabled. So, as soon as they upgraded to Exchange 2010, aspNetEmail was throwing an exception, basically saying that AuthLogin was no longer supported.

There are 2 ways to resolve this.
a) In the code, tell aspNetEmail to use the NTLM authentication scheme. In this instance, the customer didn't want to touch the code base. So they decided for option (b), which was:
b) Enable AuthLogin authenticaton on Exchange. To do this:
In the Exchange console under server configuration:
   Select hub transport.
   Right click  the client server and select properties.
   Select the authentication tab.
   Check the Basic Authentication checkbox.
   Uncheck the Offer Basic only after TLS

You may have to restart the Exchange services.


Once this was resolved, the next error was thrown.

550 5.7.1 Client does not have permission to send as this sender
This error was being thrown because the FROM address the customer was using, was different than the Exchange mailbox they were relaying through. Like the error message implies, this is a permissions issue. To resolve this, the following steps were taken.
a)From an Exchange Command prompt, run the following command:
Add-AdPermission -Identity "Default Receive Connector" -User "NT AUTHORITY\Authenticated Users" -ExtendedRights ms-Exch-SMTP-Accept-Any-Sender

c) On the user account, in Active Directory, under Security, under the SELF account, select the Manage Send As Permission option.

More info can be found here:
http://technet.microsoft.com/en-us/library/aa997170(EXCHG.140).aspx 
As always, if you are modifying settings on Exchange, talk these changes over with your Exchange admin.
 

Sending Email through Gmail using aspNetEmail

by Dave 2. March 2011 03:42

aspNetEmail can easily send email through Gmail. To do this, you must do the following:

a) Download the AdvancedIntellect.Ssl.dll from http://www.advancedintellect.com/download.aspx
b) Use aspNetEmail over SSL at port 587 to connect and send.

Lets discuss this below.

Download the AdvancedIntellect.Ssl.dll
To send email through Gmail, you need to do this over SSL. The AdvancedIntellect.Ssl.dll provides this functionality. It is a plugin that can be used with aspNetEmail.
You can download the dll from http://www.advancedintellect.com/download.aspx

Use aspNetEmail over SSL at port 587 to connect.
Once you've downloaded the dll, you can integrate it into your project.

If you are using VS.NET, import the dll into your application, and set a reference to it.

If you are not using VS.NET, you should be able to just copy the dll to your /bin directory.

Below is a simple code example that demonstrates sending the email through gmail.


C#

EmailMessage msg = new EmailMessage();

string userAccount = "MyAccount@gmail.com";
string password = "MyPassword";

//some basic properties
msg.Server = "smtp.gmail.com";
msg.Port = 587;
msg.Username = userAccount;
msg.Password = password;

msg.FromAddress = userAccount;
msg.To= "You@yourcompany.com";
msg.Subject = "This is my test email through gmail.";
msg.Body = "Put the body contents here...";

//any logging (not reqired) for troubleshooting
msg.LogPath = "c:\\temp\\email.log";
msg.Logging = true;

//create the ssl socket
AdvancedIntellect.Ssl.SslSocket ssl = new AdvancedIntellect.Ssl.SslSocket();
msg.LoadSslSocket( ssl, false);

//logging on the ssl socket
ssl.Logging = true;
ssl.LogPath = "c:\\ssl.log";

msg.Send();


VB.NET

Dim msg As New EmailMessage()

Dim userAccount As String = "MyAccount@gmail.com"
Dim password As String = "MyPassword"

'some basic properties
msg.Server = "smtp.gmail.com"
msg.Port = 587
msg.Username = userAccount
msg.Password = password

msg.FromAddress = userAccount
msg.To = "You@yourcompany.com"
msg.Subject = "This is my test email through gmail."
msg.Body = "Put the body contents here..."

'any logging (not reqired) for troubleshooting
msg.LogPath = "c:\temp\email.log"
msg.Logging = True

'create the ssl socket
Dim ssl As New AdvancedIntellect.Ssl.SslSocket()
msg.LoadSslSocket(ssl, False)

'logging on the ssl socket
ssl.Logging = True
ssl.LogPath = "c:\ssl.log"

msg.Send()

As always, if anyone has any questions, feel free to contact me over at the Contact Us web page.


Thanks,
Dave Wanta

 

Using Advanced Intellect's Products in VS2010/2012

by Dave 9. November 2010 01:46

** NOTE: ALL OF OUR PRODUCTS RUN ON ALL VERSIONS OF .NET. **

The instructions below are for people using VS2010 and beyond.

                                                        --------------------------

As more and more people are upgrading to VS2010, and beyond, I am getting more of the following emails:

aspNetEmail (or any of our other products) doesn't work in later versions of Visual Studio. I usually get one of the following errors:

"aspNetEmail is not declared, it may be inaccessible due to its protection level."

Or

"The referenced assembly "…" could not be resolved because it has a dependency upon System.Web (or some other internal .NET namespace).  Please remove references to assemblies not in the targeted framework or consider retargeting your project"

Usually these exceptions occur when the developer is building a client side application.

Starting in VS2010, VS tries to be too smart for it's own good.  When you build a client application (console.exe, winform, etc…) VS limits the number of namespaces you need access too, because it thinks you shouldn't need them.

To change this behavior, what you need to do, is change the target framework from a subset of namespaces, to all of them.

To change this, in VS.NET Solution Explorer, Right-Click on  your project, an select Properties.
 
On the Application tab, set the Target Framework to be ".NET Framework XX". By default it is set to ".NET Framework XX Client Profile".  Press Ctrl-S for save, and you are done.

Below are 2 pictures that display changing the target framework.

As always, if anyone has any questions, feel free to contact me.

Thanks!
Dave Wanta

 

C# Screenshot:

 

VB.NET Screenshot (this option is found under the Compile tab. Then, click the "Advanced Compile Option" button.

 

Using the ReversePath property

by Dave 13. October 2010 15:05

Today I received a request (Thanks Suraj!) about using a different email address during the SMTP process than what is seen in the email. Is this possible?

The short answer is "yes"!

aspNetEmail exposes a property called the ReversePath property. By default, this value is the FromAddress value.  The ReversePath value will be used during the SMTP process. Specifically is the value of the "MAIL FROM" command.

This is useful, because some mail servers will only allow a specific email address to be used, but you may want a different FROM value in the email seen by the end user. 

Here is a specific example.  Suppose you have a CRM application hosted by a provider that has a locked down mail server. The hosting provider may only allow email to be relayed through their mail server using a single MAIL FROM address. However, the CRM application needs to send out email from different users.  Using the ReversePath property allows this to happen.  The ReversePath property will be using as the MAIL FROM value, while the FromAddress value will be the actual value seen in the mail client. An added bonus to doing this is that all human replies can go back to the FromAddress mailbox, while NDR bounces can go back to the ReversePath mailbox.   You can then use something like ListNanny to process all of the bounces found in the ReversePath mailbox, without cluttering up someone's (FromAddress) personal mailbox.

As always, if you have any more questions about the ReversePath property, and how it works, please let me know.

Thanks!
Dave Wanta

Cancel or Update Metting Requests

by Dave 1. October 2010 09:36

One question I often receive about meeting requests is how to update or cancel them.

Can this be done with aspNetEmail?

The short answer is YES! However, there is one small gotcha, you need to keep track of the unique id of the meeting request.

According to the iCalendar specifications, each meeting request needs to have a unique id. By default, aspNetEmail uses a Guid.  aspNetEmail also allows you to over ride this unique id. Whether you create your own, or use aspNetEmail’s, you must keep track of this unique id.

Once you have the unique id of the originally sent iCalendar, you can update or cancel the request at any time in the future. 

==============================
Updating an iCalendar
==============================
To update a meeting request you will need to do 2 things:
 
UniqueId:
1) Use the same UniqueId as the original. By default, aspNetEmail simply uses a guid for a UniqueId. If you want to, you can just create your own guid, and then record it for later use. For example:
 
   string uniqueId = Guid.NewGuid().ToString().Replace( "-", string.Empty );
   ical.Event.UniqueId.Value = uniqueId;

 
Then, just store the uniqueId for any future updates.
 
Sequence number
2)Youo must also increment the sequence number by 1.
 
The newer version of aspNetEmail's iCalendar class  has a Sequence property, so you can simply increment it like:
 
ical.Event.Sequence.Index++;
or
ical.Event.Sequence.Index = 1;
or
ical.Event.Sequence.Index = 2;

(depending upon how many updates you have issued)


=================================
CANCEL an iCalender
=================================
To cancel a meeting request, you will again need to do 2 things.
 
1) You will need to still use the original UniqueId value (just like above)
 
2)You will need to change the Method of the iCal to CANCEL. Here is what the code looks like:
 
First Request
==================
string uid = Guid.NewGuid().ToString();
iCalendar ical = new iCalendar();
ical.Event.UniqueId.Value = uid;
//set other properties, add attendees, set organizer...
ical...
EmailMessage m = new EmailMessage();
m.AddCalendar( ical );
m....
m.Send();
 

Cancelled request
=================
//reset the uid
iCalendar ical2 = new iCalendar();
ical2.Event.UniqueId.Value = uid;
 
//change the method to CANCEL
ical2.Method.Value = "CANCEL";

All Day Events in an iCalendar

by Dave 1. October 2010 09:06

By default, the RFC standard for the iCalendar specification does not support all day events.

However, both Outlook and Lotus Notes support this capability. Both do it a bit differently.

Below is a code example of how to use the iCalendar functionality of aspNetEmail to create all day events in both Lotus Notes and Outlook. 

static void AllDayEvent()
{
	//create the email message object
	EmailMessage m = new EmailMessage( "192.168.1.106" );
	m.From = "me@example.com";
	m.To = "you@blah.com";
	
	//create an all day event for next week
	DateTime eventDay = DateTime.Now.AddDays(7);

	m.Subject = "all day event for " + eventDay.ToShortDateString();

	//uncomment to create an iCal for Lotus Notes
	//iCalendar iCal= AllDayEventForLotus( eventDay );

	//create an iCal for Outlook
	iCalendar iCal = AllDayEventForOutlook( eventDay );

	//set some event properties
	//set the properties
	iCal.Event.Summary.Text = "Sales Meeting"; 
	iCal.Event.Location.Text = "100 main conf room"; 


	//when we add it to the EmailMessage object, all EmailMessage recipients will
	//be added to the iCalendar *if* the iCalendar does not have any recipients
	//if we wanted to add additional attendees we could use
	//ic.Event.Attendees.Add( ... )
	m.AddCalendar(iCal); 

	m.Logging = true;
	m.LogPath = "c:\\email.log";
	m.LogOverwrite = true;

	m.SaveToFile( "c:\\temp\\" );
	m.Send();


}
static iCalendar AllDayEventForLotus(DateTime eventDay)
{
	iCalendar iCal = new iCalendar(); 

	DateTime start = new DateTime( eventDay.Year, eventDay.Year, eventDay.Day, 4, 0, 0 );
	DateTime end = new DateTime( eventDay.Year, eventDay.Year, eventDay.Day, 20, 0, 0 );

	iCal.Event.DateStart.Date = start;
	iCal.Event.DateEnd.Date = end;
	iCal.Event.Priority.PriorityType = PriorityType.Normal; 

	return iCal;
}

static iCalendar AllDayEventForOutlook( DateTime eventDay)
{
	DateTime dayStart = eventDay;
	DateTime dayEnd = eventDay.AddDays( 1 );

	iCalendar iCal = new iCalendar(); 

	iCal.TimeZone.Format = TimeZoneFormat.SuppressTimeZoneRules;

	iCal.Event.DateStart.AddParameter( "VALUE", "DATE:" + dayStart.ToString("yyyyMMdd")  );
	iCal.Event.DateEnd.AddParameter( "VALUE", "DATE:" + dayEnd.ToString( "yyyyMMdd" ) );
	iCal.Event.TimeTransparency.TransparencyType = TransparencyType.Transparent; 

	iCal.Event.Priority.PriorityType = PriorityType.Normal; 

	//set various Outlook X-Headers, so Outlook will recognize it as an All Day Event.
	iCal.Event.Properties.Add( new CustomCalendarProperty( "X-MICROSOFT-CDO-BUSYSTATUS", "FREE" ) );
	iCal.Event.Properties.Add( new CustomCalendarProperty( "X-MICROSOFT-CDO-INSTTYPE", "0" ) );
	iCal.Event.Properties.Add( new CustomCalendarProperty( "X-MICROSOFT-CDO-INTENDEDSTATUS", "FREE" ) );
	iCal.Event.Properties.Add( new CustomCalendarProperty( "X-MICROSOFT-CDO-ALLDAYEVENT", "TRUE" ) );

	return iCal;
}

 

 

Testimonial

Thank you again for all of your help, and your great products!!! "

Mark | Industry Pro

Read more testimonials
ListNanny aspNetDNS aspNetEmail aspNetPOP3 aspNetMX IPMuncher aspNetMIME aspNetPING aspNetTraceRoute aspNetIMAP aspNetMHT