Sending Emails from .Net using Google's SMTP servers

The .Net framework has a few classes to handle mail.
The System.Net.Mail namespace will let you send an email message via the SMTP server configured in your web.config file or app.config file.
This works very well for many providers - but if you are using Gmail or google apps things get a little sticky since the only way to connect to the SMTP servers is using SSL which is not supported by system.net.mail.
I found a workaround this problem using an open source component called stunnel - stunnel allows you to create SSL tunnels to communicate with specific addresses and ports.
The way this would work is this:
1. we will tell our program to send email using SMTP to a non standard port (say 8025 instead of 25).
2. We will tell STunnel to listen on this port and forward all the traffic through an SSL tunnel to Google's SMTP server.

So here are the step by step directions:
1. Download the Stunnel installer from Stunnel.Org - http://www.stunnel.org/ - install this on your server
2. go to the the install directory for stunnel and change the stunnel.conf file to look like:

cert = stunnel.pem
socket = l:TCP_NODELAY=1
socket = r:TCP_NODELAY=1
;debug = 7

output = stunnel.log
; Use it for client mode
client = yes
[GmailSMTP]
accept = XXX.XXX.XXX.XXX:8025
connect = smtp.gmail.com:465

You will need to replace XXX.XXX.XXX.XXX with the IP address for the server you are running on - do not use localhost or 127.0.0.1 - they failed for me.
3. From the start menu do - stunnel service install and then stunnel servic start (you can change the start mode for the service to automatic also).
4. You can test your work - so far - open a command prompt window and type the following:
Telnet XXX.XXX.XXX.XXX:8025 (where XXX.XXX.XXX.XXX is the address above).
If everything works well you should see something like:
220 mx.google.com ESMTP q18sm6397127pog.5
Close the window.
5. In your application edit the System.Net configuration section of web.config or app.config as follows:
<system.net>
<mailsettings>
<smtp from="name@domain.com" >
<network host="XXX.XXX.XXX.XXX"
port="8025"
password="YOUR PASSWORD" userName="name@domain.com">
</smtp>
</mailsettings>
</system.net>

That should do it.

The inspiration for this was an article I read here:
http://quack.me/2007/02/gmail_via_pop3_on_exchange.php

Disable user interface during ajax postbacks on ASP.Net

One of the biggest issues I have with public facing websites I work on is double clicks on buttons or users interrupting the postback process. This happens a lot with the payment button of credit card transactions and other time consuming postbacks.
I have done some Javascript and CSS tweaks and tricks to handle specific controls and I was unable to get something I was truely happy with.
Until I met jQuery. jQuery is a javascript library that does a whole bunch of things, introduces shortend syntax and a pluggable API. It can be downloaded at http://jquery.com/.
Coupling JQuery with a plugin called BlockUI. Can be downloaded at http://malsup.com/jquery/block/
What I came out with is a fairly generic solution for ASP.Net that handles all the ajax postbacks for a specific page or website by wiring the application_beginRequest and application_endRequest on the client side.
So here is the recipe:
Ingrediants -
1. ASP.Net 3.5 website
2. An aspx webpage
3. A script manager with a reference to:

1. The JQeury library




2. Block UI plugin




3. A very short Javascipt file (enclosed below)

4. An update panel

Instructions:

1. Create your website - I use Visual Studio 2008

2. Add your page or your master page

3. Create a js directory and add the downloaded scripts from jQuery and JBlockUI

4. Add a new file to the js directory called wireRequestEvents.js

with the following code:

Sys.WebForms.PageRequestManager.getInstance().add_beginRequest(BeginRequestHandler);

Sys.WebForms.PageRequestManager.getInstance().add_endRequest(EndRequestHandler);
function BeginRequestHandler(sender, args)
{
$.blockUI();
}
function EndRequestHandler(sender, args)
{
$.unblockUI();
}

5. Drop a script manager in your page with the following syntax:

6. Add your update panels with contect templates and you are good to go.

To demonstrate the technique I created a simple page with a button and a label inside an update panel. Once clicked the button waits 4 seconds and then changes the label. This imulates a long transaction on the server.

Here is what the users will see:

After they click the button the UI is blocked by a semi transperant div

When the request returns the users will see the updated screen

Pretty simple!

The code for this is available at http://www.simplicityc.com/files/JQuerySample.zip

Code Camp 2008.1 - t-Sql to CLR performance comparison testing and Cursor Avoidance Techniques

We had a great session at Code Camp. Thanks to Sebastian Meine who did most of the work for the session. I contributed by brainstorming with Sebastian, coding the CLR components of the project and walking through the code.
We concluded that there is no conclusion...CLR is faster for some things and T-SQL is faster for others. There are some very big limitations to the CLR integration in SQL server - the main one is the 8000 byte limit when serializing custom aggregates and the indifference to order in those functions.
We also played with some neat SQL-2005 enhancements like common table expressions and Cross apply which was new for some of the users.
I will try to get the code from Sebastian and post it here.

Troubleshooting SQL server database mail

I am putting database mail on SQL server 2005 into use more and more these days with my consutling projects and it is making things much easier on me.

It is very easy to trigger emails based on transactions that happen on the database - like payments posted, account goes below a certain balance etc.

It is also nice to use some of the included views to see what is the status of your email messages.

If a message that I send did not reach the recipient I can check:
SELECT * FROM msdb.dbo.sysmail_faileditems
to look for that message.

Once I find it there I can can look at the mailitem_id in the msdb.dbo.sysmail_event_log view.

Happy mailing!

Where did my SQL Server Memory go?

In managing one of my servers I had to make sure everything was running the way it should. I am working on fairly big databases (in the order of 10s of gigabytes) and I wanted to make sure SQL is using all the memory it has available.

The server I am working on is an x64 windows 2003 enterprise running SQL 2005 enterprise 32 bit. The server is also equipped with 12GB of ram.

Whenever I would look into the task manager or the performance monitor the only memory allocation I see for SQL server is around 202 MB which makes no sense.

If you look at the screen capture enclosed you can see the memory allocation for SQL server.

I did some research on the subject and found the answer. SQL has a dynamic management view called sys.dm_os_memory_clerks. This view described in http://technet.microsoft.com/en-us/library/ms175019.aspx has detailed memory allocation information for SQL server. When you examine this view you can see a column that details AWE memory allocations that are detailed in the awe_allocated_kb column, in my case this was summed to the 10GB I assigned to the server. There are other useful rows columns in the view and they are documented in the technet article mentioned here.

One useful query i run constantly:

SELECT
SUM(awe_allocated_kb)/1024 AS [AWE_MEM_MB],
SUM(virtual_memory_reserved_kb)/1024 AS [VM_RES_MB],
SUM(virtual_memory_committed_kb)/1024 AS [VM_COM_MB],
SUM(shared_memory_reserved_kb)/1024 AS [SM_RES_MB],
SUM(single_pages_kb )/1024 [SNG_PAGE_MB],
SUM(multi_pages_kb )/1024 [MLT_PAGE_MB]
FROM sys.dm_os_memory_clerks

This returns the memory allocation totals in megabytes.

Running C# and VB.Net Code on Microsoft SQL Server - Slides code and database

Thanks for coming to the November PSSUG Meeting at Microsoft.
All and all it went pretty well with a couple of Visual Studio crashes, SQL server acting funky but that comes with the territory.
As promised the code is available by clicking below:
Code, database and slides from Running C# and VB.Net Code on Microsoft SQL Server talk
I had to remove the ANTLR part of the demo since there are IP issues involved.
To get this running extract the zip file to drive c:\
it will create a folder c:\SQL_CLR
Attach the database in c:\SQL_CLR\db to sql server
You can look at a word document in c:\SQL_CLR\powerpoint\ for instructions to run the demo.
The slides are in c:\SQL_CLR\powerpoint\ also.
To run the solution open:
C:\SQL_CLR\SimpleFunctions\SimpleFunctions.sln
Good luck and let me know if you have any questions!
Sagi

Updated Code and Slides for the SQL CLR Integration Talk

I updated the code and the slide for the SQL Integration talk that I gave in Code Camp 2007.2 in Philadelphia.
The slides code and slides are available here:
http://www.simplicityc.com/CodeCamp2007_CLR_Integration.zip
I added a readme file that explains how to run these samples.
There are samples for:
-SQL Stored Procedure written in C#, it uses return parameters, the message channel, internal connections to the database.
-User defined Aggregate function in C#
-Table valued function in VB.Net
-Scalar Values functions in C# and VB.Net
And a couple more