Sunday, April 29, 2012

How to send mail automatically for every five minutes using C#?

This article explain how to send mail automatically using C# with window service.

Introduction

This article explain how to send mail automatically using window service, Concepts are involved for sending mail automatically are SMTP Server for sending mails, Window service used to mail automatically, Event log is used to see whether the window service is working, below explain detail.
1. Window Service
2. Event Log
3. SMTP Client
4. Timer Concepts
5. Steps to Create Automatic Mail Service
6. Create Windows Service Deployment project
7. Instal and Un-install the windows servie project
8. Start and Stop your service
9. View your log files

1.Window Service

 

Windows Service is used to create a application to run in a windows sessions. windows service can automatically run,  restart or paused when the system boots , we do not show any user interface for doing the window service.
In .Net framework support two type of windows service. window service can run in a single process to be create in Win32ownprocess and the service can share the process to be create in win32shareprocess. we can retrieve the information of the process using servicetype property.
They are two overrides method are used to Start and Stop your windows service.
1. Start  method
protected override void OnStart(string[] args) {    //Your coding
}
2. Stop Method
protected override void OnStart(string[] args) {    //Your coding
}

2.Event Log

Eventlog class used to create or accessing the windows event logs. Administration privileges need to write to an event log for every logs.EventLog,class method are used to read from existing logs, write entries to logs and create a logs  or delete event sources.

Check whether event log exisits in system using SourceExists method, if not created already create eventlog using CreateEventSource method and write to the event Source
Examples:
Create New EventLog
if (EventLog.SourceExists("AutoMail"))     {                      EventLog.CreateEventSource(                 "AutoMail","Mails");     }
Write the information in existing logs
    eventLog1.Source = "AutoMail";     eventLog1.Log = "Mails";

3.SMTP Server

The SMTP Server Class is used to Send Mail from a SMTP Server. .Net Framework 2.0 later supports the SMTP Server class from  System.Net.Mail namespace. 
It System.Net.Mail namespace supports three class
a) MailMessage
b) MailAddress.and
c) Attachments
Mail Message is used to describe the messages.
Mail Address is used to define theh sender and recipiants.
Attachments is used to attach the file along with the mail.
Examples:

MailMessage mailMsg = new MailMessage();
MailAddress mailAddress = null;
mailMsg.To.Add(tmuhilan@gmail.com); mailAddress =new MailAddress(Muhilan@maa.com.my); mailMsg.From = mailAddress;
mailMsg.Subject = "Automatic mailing";
mailMsg.Body = "Tomorrow Meeting at 6.30PM";
SmtpClient smtpClient = new SmtpClient("97.0.0.6", Convert.ToInt32(25));
System.Net.NetworkCredential credentials = new System.Net.NetworkCredential();
smtpClient.Credentials = credentials;
smtpClient.Send(mailMsg);
Here i have used  ipaddress and Port number is our SMTP server. So change the ip and port according to your SMTP Server configuration.

4. Timer Concepts

.Net providers timer component is a server-based timer allows you to specify interval and the elapsed event is raised in your application. This event to provide regular processing. while create a service that uses a timer to periodically , it will check the server is running or not.
While service could attempt to restart the server, timer component raises the elapsed event based on the value of the interval property.
Examples:
 System.Timers.Timer time = new System.Timers.Timer();

time.Start();

time.Interval = 300000;

time.Elapsed += time_elapsed;

     

public void time_elapsed(object sender, ElapsedEventArgs e)

{


}

5.   Steps


  1. Create New Project under Visual Studio -> File -> New -> Project -> Select Project Types -> Visual C# and choose windows service
  2. Change Service name as per your naming standard
  3. From Toolbox -> Components ->Select Eventlog component. Drag the Eventlog component and drop in design.
  4. Rightclick and choose view code , In constructor check whether the event log exists or not , if not exists create the event log.
  5. Examples:
if (!System.Diagnostics.EventLog.SourceExists("MailSend"))

{System.Diagnostics.EventLog.CreateEventSource(

"MailSend", "AutoMailLog");

}MyLogEvent.Source = "MailSend";

MyLogEvent.Log = "AutoMailLog";


    6.  Next onStart Override method
Here , create timer component and assign the interval values and other properties

MyLogEvent.WriteEntry("In OnStart --- Sending Mail to" + Dt);

System.Timers.Timer time = new System.Timers.Timer();

time.Start();

time.Interval = 300000;

time.Elapsed += time_elapsed;

time_elapsed event to be call for every 5 minutes
public void time_elapsed(object sender, ElapsedEventArgs e)

{MyLogEvent.WriteEntry("Mail Sending on " + DateTime.Now.ToString());

SendEmail("Muhilan@maa.com.my", "tmuhilan@gmail.com", "Automatic Mail sending", "Successfully working contact tmuhilan@gmail.com");

} 
7.  Here SendEmail is my function for sending mail
public bool SendEmail(string strTo, string strFrom, string strSubject, string strBody)

{bool flag = false;

MailMessage mailMsg = new MailMessage();

MailAddress mailAddress = null;

try

{// To

mailMsg.To.Add(strTo);mailAddress = new MailAddress(strFrom);

mailMsg.From = mailAddress;mailMsg.Subject = strSubject;

mailMsg.Body = strBody;SmtpClient smtpClient = new SmtpClient("97.0.0.6", Convert.ToInt32(25));

System.Net.NetworkCredential credentials = new System.Net.NetworkCredential();

smtpClient.Credentials = credentials;

smtpClient.Send(mailMsg);flag = true;

MyLogEvent.WriteEntry("Mail Send Successfully");

}catch (Exception ex)

{

MyLogEvent.WriteEntry("Error occured");

//Response.Write(ex.Message);

}finally

{mailMsg = null;

mailAddress = null;

}return flag;

}
8. Once written coding , compile if no error occurs. Then
9. Rightclick in design mode ->Select Add installer -> it will create two components
a) ServiceProcessInstaller and b) ServiceInstaller
a) ServiceProcessInstaller is used to define the windows service work in which account.Here we can set the account type as LocalSystem , User,Network service. In my project i have used LocalSystem.
b)ServiceInstaller Set ServiceName as anything(AutoMail) and Starttype as Automatic
10. Once Set the property and Build. Now created web service.

6. Create windows service depolyment project

  1.  In solution explorer, Add new project -> select setup and deployment under other project types
  2. Right click the project -> Add -> Project Output
  3. In a project item for the primary output of your service is added to the setup project.
  4. Now to add a custom action to install the service.exe file.
    5.  In Solution Explorer, right-click the setup project -> point to View -> then choose Custom Actions.
        The Custom Actions editor appears.

  1. In the Custom Actions editor, right-click the Custom Actions node and choose Add Custom Action.The Select Item in Project dialog box appears.
  2. Double-click the Application Folder in the list box to open it, select Primary Output from Service (Active), and click OK.The primary output is added to all four nodes of the custom actions 
  3.  Install, Commit, Rollback, and Uninstall.
  4. In Solution Explorer, right-click the ServiceSetup project and choose Build.

7. To install the Windows Service

      To install service.exe, right-click the setup project in the Solution Explorer and select Install.If you want to unistall your service , right-click the sertup project in the solution explorer and select uninstall options.

8. Stop and Start your service

1. For Xp windows-> click Start, point to Programs-> My Computer -> rightclick-> then select Manage
2. Computer Management Consle windows will appear
3. Select the Service and Applications node , in the sub node select services , there
        4.  Select your service in the list, right-click it, and then click Start.
        5.   Right-click the service, and then click Stop.

SQL Drop Time in DateTime


Problem
i have a problem where my table stores the date as
2009-01-10 10:00:00.000
and I have another table which stores the date as
2009-01-10 12:00:00.000
I know they are not the same but the dates are, is there away in SQL to easily drop the time and keep just the dates for comparison?

Solution:
Run this
SELECT DATEADD(dd, DATEDIFF(d, 0, Getdate()), 0)
change Getdate() to your column name to strip the date
BTW if you are doing a comparison it is better to do >= and < since it will perform better

Wednesday, April 25, 2012

SQL server open connections exist in a database


If you get a “timeout connection in the database because there are no connections available in the connection pool” error, the following queries will help you diagnose the problem:
  • To allow users to view current activity on the database:
    sp_who2
  • To give you the total number of connections per database on a database server:
    SELECT DB_NAME(dbid) as 'Database Name',
    COUNT(dbid) as 'Total Connections'
    FROM master.dbo.sysprocesses WITH (nolock)
    WHERE dbid > 0
    GROUP BY dbid
  • To get the dbid from database name
    SELECT DB_ID('MyDBName') as [Database ID]
  • To give you the process Ids of existing connections in the database (not necessarily open but existing):
    SELECT spid
    FROM master.dbo.sysprocesses WITH (nolock)
    WHERE dbid = (SELECT DB_ID('MyDBName') as [Database ID])
  • To give you information about the actual process id (replace 1018 with the spid):
    dbcc inputbuffer (1018)
  • To kill a process
    kill 1018

SQL server open connections

n order to solve performance issues or to check database usage, it can be quite useful to know how to get current connections to a SQL Server.

In SQL Server 2000 (if you still have one...), SQL Server 2005 and SQL Server 2008 the sp_who2 stored procedure returns information about current SQL Server 2000 users and processes. This function is unfortunately not very well documented.

As a general information, you must know that the connections returned by this function are denoted as SPID, or Server process Id. Running sp_who2 is easy, All that is required is to type sp_who2 and type F5.
Note that the first 50 results are system SPIDs (Generally these do not impact the performance of the system). So, Sp_who2 gives you this information:
  • SPID: System process id that requested the lock
  • STATUS: Background, sleeping or runnable
  • LOGIN: The login name that has requested the lock
  • HOSTNAME: The computer where the lock request has been initiated
  • BLKBY: The spid of the connection that is blocking the current connection
  • DBNAME: The database name where the lock request has been generated
  • COMMAND: General command type that requested the lock
  • CPUTIME: The number of milliseconds the request has used
  • DISKIO: Disk input / output that the command has used
  • LASTBATCH: Date and time of the last batch executed by the connection
  • PROGRAMNAME: The name of the application that issued the connection
For instance, you can run sp_who2 and get all the connections:

sp_who2


Or you can append the 'active' parameter and SQL will return only the active connections: 

sp_who2 active



An important thing to remember when runnign sp_who2 is that the user running the query must have the VIEW SERVER STATE permission on the server in order to see all executing sessions on the instance of SQL Server. Otherwise, the user will see just the current session.

As an alternative, which is still working even if deprecated under SQL 2008, the system table sys.sysprocesses contains connection information for each connection made to the SQL Server. You can query it like this:

select * FROM sys.sysprocesses


The result will mainly contain the following information:
  • SPID: SQL Server session ID.
  • KPID: Windows thread ID.
  • BLOCKED: ID of the session that is blocking the request. If this column is NULL, the request is not blocked
  • DBID: ID of the database currently being used by the process.
  • UID: ID of the user that executed the command.
  • CPU: Cumulative CPU time for the process.
  • PROGRAM_NAME: Name of the application program.
  • CMD: Command currently being executed.
  • NT_USERNAME: Windows user name for the process, if using Windows Authentication, or a trusted connection.
  • LOGINAME: Login name

But, IMHO, for a better understanding of your SQL Server usage, this queryis the best one:

SELECT db_name(dbid) as Database_Name, count(dbid) as Connections,
loginame as Login_Name
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
order by Connections desc

The output will be grouped by database ID and then by username, for a simpler reading.


If you are not sure of which SQL Server version you have, run this query:select SERVERPROPERTY('ProductVersion').n order to solve performance issues or to check database usage, it can be quite useful to know how to get current connections to a SQL Server.

In SQL Server 2000 (if you still have one...), SQL Server 2005 and SQL Server 2008 the sp_who2 stored procedure returns information about current SQL Server 2000 users and processes. This function is unfortunately not very well documented.

As a general information, you must know that the connections returned by this function are denoted as SPID, or Server process Id. Running sp_who2 is easy, All that is required is to type sp_who2 and type F5.
Note that the first 50 results are system SPIDs (Generally these do not impact the performance of the system). So, Sp_who2 gives you this information:
  • SPID: System process id that requested the lock
  • STATUS: Background, sleeping or runnable
  • LOGIN: The login name that has requested the lock
  • HOSTNAME: The computer where the lock request has been initiated
  • BLKBY: The spid of the connection that is blocking the current connection
  • DBNAME: The database name where the lock request has been generated
  • COMMAND: General command type that requested the lock
  • CPUTIME: The number of milliseconds the request has used
  • DISKIO: Disk input / output that the command has used
  • LASTBATCH: Date and time of the last batch executed by the connection
  • PROGRAMNAME: The name of the application that issued the connection
For instance, you can run sp_who2 and get all the connections:

sp_who2


Or you can append the 'active' parameter and SQL will return only the active connections: 

sp_who2 active



An important thing to remember when runnign sp_who2 is that the user running the query must have the VIEW SERVER STATE permission on the server in order to see all executing sessions on the instance of SQL Server. Otherwise, the user will see just the current session.

As an alternative, which is still working even if deprecated under SQL 2008, the system table sys.sysprocesses contains connection information for each connection made to the SQL Server. You can query it like this:

select * FROM sys.sysprocesses


The result will mainly contain the following information:
  • SPID: SQL Server session ID.
  • KPID: Windows thread ID.
  • BLOCKED: ID of the session that is blocking the request. If this column is NULL, the request is not blocked
  • DBID: ID of the database currently being used by the process.
  • UID: ID of the user that executed the command.
  • CPU: Cumulative CPU time for the process.
  • PROGRAM_NAME: Name of the application program.
  • CMD: Command currently being executed.
  • NT_USERNAME: Windows user name for the process, if using Windows Authentication, or a trusted connection.
  • LOGINAME: Login name

But, IMHO, for a better understanding of your SQL Server usage, this queryis the best one:

SELECT db_name(dbid) as Database_Name, count(dbid) as Connections,
loginame as Login_Name
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
order by Connections desc

The output will be grouped by database ID and then by username, for a simpler reading.


If you are not sure of which SQL Server version you have, run this query:select SERVERPROPERTY('ProductVersion').

Tuesday, April 24, 2012

Configure SMTP E-Mail in IIS 7


Introduction

To send e-mail from a PHP application using the System.Net.Mail API, you must configure Simple Mail Transfer Protocol (SMTP) e-mail. Configuring e-mail services tells the System.Net.Mail application programming interface (API) to which SMTP server to deliver the e-mail generated by your application. Mail can be delivered immediately, or it can be delivered to a file location on disk where it can be retrieved for delivery later. For example, a company can provide an e-mail link for sending feedback messages or for requesting information.
The procedures for configuring SMTP e-mail can be performed at the following levels in Internet Information Services 7 (IIS 7):
  • Web server
  • Site
  • Application
  • Physical and virtual directories
  • File (URL)

Install SMTP

The SMTP server is not installed by default.
1. Open Server Manager by right-clicking on My Computer, and selecting Manage. (Alternately, open Control Panel, click on Programs and Features, and then select Turn Windows features on or off.)
2. Under Features, select Add Features.
3. Select the SMTP Server check box.
4. Click Add Required Role Services. If there are any missing roles required for the SMTP installation, Windows Server® 2008 R2 or Windows Server®2008 installs them. Click Next.
5. You need to step through the entire wizard again, even though IIS is already installed (IIS 6 Management Compatibility and the IIS 6 Management Console must be installed for SMTP to work).
Figure 1: Confirm Installation Selections
6. Press Close when installation is complete.

Configure SMTP E-Mail for a Web Application

Once you add SMTP, you can configure it for your PHP applications. This can be done by using the user interface (UI), by running Appcmd.exe commands from a command prompt, by editing configuration files directly, or by writing Windows® Management Instrumentation (WMI) scripts. Note that you must have IIS 7 installed before enabling SMTP.

  

Use the User Interface

1. Open Internet Information Services (IIS) Manager, and then navigate to the level you want to manage.
2. In Features View, double-click SMTP E-mail.
3. On the SMTP E-mail page, type the e-mail address of the sender in the E-mail address text box.
4. On the SMTP E-mail page, select one of the following delivery methods:
a. Deliver e-mail to SMTP server: to deliver e-mail messages immediately. This requires an operational SMTP server for which the user has credentials.
b. Store e-mail in pickup directory: to store e-mails in a file location on disk for later delivery by the PHP application (or by a Microsoft® ASP.NET application or by a user).
5. If Deliver e-mail to SMTP server is selected, do the following:
a. Type the unique name of your SMTP server in the SMTP Server text box, or select the Use localhost check box to set the name to LocalHost. Setting the name to LocalHost means that ASP.NET uses an SMTP server on the local computer. Typically, this is the default SMTP virtual server.
b. Enter a TCP port in the Port text box. Port 25 is the SMTP standard TCP port and is the default setting. More than one virtual server can use the same TCP port if all servers are configured by using different IP addresses.
c. Under Authentication Settings, specify the authentication mode and credentials if your SMTP server requires these.
6. If Store e-mail in pickup directory is selected, type the batch e-mail location in the Store e-mail in pickup directory text box.
7. Click Apply in the Actions pane.

  

Use the Command Line

You can configure IIS so that e-mail is delivered immediately or stored for later delivery.
Deliver e-mail messages immediately
To configure SMTP e-mail to deliver e-mail messages immediately, use the following syntax:
C:\%windir%\system32\inetsrv\appcmd set config /commit:WEBROOT /section:smtp /from: string /deliveryMethod:network /network.port:int /network.defaultCredentials:True|False /network.host:string /network.userName:string /network.password:string
The variable from string is the e-mail address of the sender. The variable /deliveryMethod:network configures IIS to deliver e-mail messages immediately. The variable /network.port int sets the TCP port that is used by IIS to deliver e-mail messages. The variable /network.host string specifies the host used for SMTP transactions. The variable network.defaultCredentials:True|False enables or disables authentication using the default network credentials. If defaultCredentials is set to True, Kerberos or NTLM are used if the server supports these protocols. The variablesnetwork.userName:string and network. password:string set a basic authentication user name and password.
Store e-mails for later delivery
To configure SMTP e-mail to store e-mails in a file location on disk for later delivery by an application, such as an ASP.NET application, or by a user, such as an administrator, use the following syntax:
C:\%windir%\system32\inetsrv\appcmd set config /commit:WEBROOT /section:smtp /from: string /deliveryMethod:PickupDirectoryFromIis|SpecifiedPickupDirectory /SpecifiedPickupDirectory:string
The variable from string is the e-mail address of the sender. The variable/deliveryMethod:PickupDirectoryFromIis|SpecifiedPickupDirectory string configures IIS to store e-mails in a file location on disk for later delivery. The variable /SpecifiedPickupDirectory string sets the file location on disk in which to store the e-mail messages for later delivery.
Note that when you use Appcmd.exe to configure the <mailSettings> element at the global level in IIS 7, you must specify /commit:WEBROOT in the command so that configuration changes are made to the root Web.config file instead of the ApplicationHost.config file.

  

Edit the Configuration Files

1. First, configure the Php.ini file. Open the Php.ini file, and find the entry:
[mail function]
2. Set the following values:
SMTP=localhost
sendmail_from = string
smtp_port=25
Note that the default value is localhost, so there is no need to change this if SMTP is installed locally.
a. Save and close the Php.ini file.
3.Enable Relay for localhost:
a. Open the Internet Information Services (IIS) 6.0 Manager.
b. Right-click on Default SMTP Virtual Server.
4. On the Properties page, open the Access tab, and then click on Connection. You can see which server or IP addresses are allowed to connect to the server.
5. If the IP address 127.0.0.1 is not listed, add it using the Add button. You may also add your server IP address to the list.
Figure 2: Enable Relay for localhost
6. Click OK to accept the information.
7. Click the Relay button on the Access tab, and set up the relay options for the server. Ensure that localhost and the IP address 127.0.0.1 are granted permission to relay mail.
8. Click OK to accept the options, and then click Apply and OK to return to the main page of the IIS Manager.
9. Click Restart IIS.

Test E-Mail from PHP

Create a test script to see whether you can send e-mail using PHP. Copy the following text and save it as email_test.php at your server root.
<?php
if(mail(‘user@mydomain.com’,’test message’)){
     echo(‘ok’);
     }
else{
Run the file at mydomain.com/email_test.php.