Berikut merupakan cara membuat windows service yang berfungsi untuk menarik email exchange ke database sql server anda.
Buat projek baru yaitu windows service
Tambahkan referance pada projek anda, reference yang digunakan adalah "Microsoft.Exchange.Webservice" anda dapat menemukan reference ini di "manage Nuget Packages"
Buat database untuk menyimpan data exchange anda di sql server
CREATE TABLE [dbo].[EMAIL_LOG](
[param1] [varchar](50) NULL,
[from] [varchar](max) NULL,
[body] [varchar](max) NULL,
[cc] [varchar](max) NULL,
[subject] [varchar](max) NULL,
[created_datetime] [datetime] NULL,
[type] [varchar](max) NULL,
[to] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Buat Store Procedure
CREATE procedure [dbo].[spinsert_email_log] @message_id varchar(50),@to varchar(512), @from varchar(256), @cc varchar(256)=null,@subject varchar(256), @body varchar(max), @received_time datetime
as
begin
IF NOT EXISTS( SELECT 1 from EMAIL_LOG where param1 = @message_id )
insert into EMAIL_LOG( created_datetime, type, [from], [to], cc, [subject], body, param1 )
values( @received_time, 'receive_supportmail', @from, @to, @cc, @subject, @body, @message_id)
end
GO
Name Space
using Microsoft.Exchange.WebServices.Data;
using System.Data.SqlClient;
using System.IO;
using System.Timers;
Complete Script
Input script ini di Service1.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using Microsoft.Exchange.WebServices.Data;
using System.Data.SqlClient;
using System.IO;
using System.Timers;
namespace ExchangeToSQL
{
public partial class Service1 : ServiceBase
{
Timer timer = new Timer();
private void onElapsedTime(object source, System.Timers.ElapsedEventArgs e)
{
AddToFile("Send/Receive " + DateTime.Now.ToLongTimeString() + " " + DateTime.Now.ToLongDateString());
Receive();
}
// function untuk menginput log services
private void AddToFile(string contents)
{
FileStream fs = new FileStream(@"d:/logsServices.txt", FileMode.OpenOrCreate, FileAccess.Write);
StreamWriter sw = new StreamWriter(fs);
sw.BaseStream.Seek(0, SeekOrigin.End);
sw.WriteLine(contents);
sw.Flush();
sw.Close();
}
protected override void OnStart(string[] args)
{
AddToFile("Starting Service " + DateTime.Now.ToLongTimeString() + " " + DateTime.Now.ToLongDateString());
timer.Elapsed += new ElapsedEventHandler(onElapsedTime);
timer.Interval = 300000; // service dijalankan 3 menit sekali
timer.Enabled = true;
}
protected override void OnStop()
{
timer.Enabled = false;
AddToFile("Stopping Service " + DateTime.Now.ToLongTimeString() + " " + DateTime.Now.ToLongDateString());
}
private void Receive()
{
ExchangeService _service;
try
{
AddToFile("Registering Exchange connection " + DateTime.Now.ToLongTimeString() + " " + DateTime.Now.ToLongDateString());
_service = new ExchangeService
{
Credentials = new WebCredentials("xxx@emailexhangeanda", "password Email Anda")
};
}
catch
{
AddToFile("new ExchangeService failed " + DateTime.Now.ToLongTimeString() + " " + DateTime.Now.ToLongDateString());
Console.ReadKey();
return;
}
_service.Url = new Uri("https://outlook.office365.com/EWS/Exchange.asmx");
// Prepare seperate class for writing email to the database
try
{
Write2DB db = new Write2DB();
AddToFile("Reading mail " + DateTime.Now.ToLongTimeString() + " " + DateTime.Now.ToLongDateString());
// Baca 10 Email
foreach (EmailMessage email in _service.FindItems(WellKnownFolderName.Inbox, new ItemView(10)))
{
if (email.Subject.ToUpper().Substring(0, 2) == "EX") // membaca email dengan subject yang memiliki string
{
db.Save(email);
AddToFile("Data Saved " + email.Subject);
}
}
foreach (EmailMessage email in _service.FindItems(WellKnownFolderName.Inbox, new ItemView(10)))
{
// delete email after insert.
if (email.Subject.ToUpper().Substring(0, 2) == "EX")
{
email.Delete(DeleteMode.SoftDelete);
Console.WriteLine("Data Deleted");
}
}
AddToFile("new ExchangeService Success " + DateTime.Now.ToLongTimeString() + " " + DateTime.Now.ToLongDateString());
}
catch (Exception e)
{
AddToFile("An error has occured. \n:" + e.Message + DateTime.Now.ToLongTimeString() + " " + DateTime.Now.ToLongDateString());
}
}
}
class Write2DB
{
SqlConnection conn = null;
public Write2DB()
{
Console.WriteLine("Connecting to SQL Server");
try
{
conn = new SqlConnection("Server=.\\SQLEXPRESS;DataBase=Webdesk;uid=sa;pwd=anjf99788$");
conn.Open();
// Console.WriteLine("Connected");
}
catch (System.Data.SqlClient.SqlException e)
{
throw (e);
}
}
public void Save(EmailMessage email)
{
email.Load(new PropertySet(BasePropertySet.FirstClassProperties, ItemSchema.TextBody));
string filename = "";
// melakukan pengecekan bila email memiliki attachment
if (email.HasAttachments && email.Attachments[0] is FileAttachment)
{
FileAttachment fileAttachment = email.Attachments[0] as FileAttachment;
// menyimpan email ke dalam folder yang di tentukan
fileAttachment.Load("D:\\folder\\folder1\\folder2\\" + fileAttachment.Name);
filename = fileAttachment.FileName.ToString();
}
// Menjalankan Store Procedure untuk menginput data Exchange ke sql server
SqlCommand cmd = new SqlCommand("dbo.usp_servicedesk_savemail", conn)
{
CommandType = System.Data.CommandType.StoredProcedure,
CommandTimeout = 1500
};
string recipients = "";
// mengambil parameter yang terdapat di Email dan melemparnya ke STORE PROCEDURE
foreach (EmailAddress emailAddress in email.CcRecipients)
{
recipients += ";" + emailAddress.Address.ToString();
}
cmd.Parameters.AddWithValue("@message_id", email.InternetMessageId);
cmd.Parameters.AddWithValue("@from", email.From.Address);
cmd.Parameters.AddWithValue("@body", email.TextBody.ToString());
cmd.Parameters.AddWithValue("@cc", recipients);
cmd.Parameters.AddWithValue("@subject", email.Subject);
cmd.Parameters.AddWithValue("@filependukung", filename);
cmd.Parameters.AddWithValue("@received_time", email.DateTimeReceived.ToUniversalTime().ToString());
recipients = "";
foreach (EmailAddress emailAddress in email.ToRecipients)
{
recipients += ";" + emailAddress.Address.ToString();
}
cmd.Parameters.AddWithValue("@to", recipients);
// Execute the procedure
cmd.ExecuteNonQuery();
}
~Write2DB()
{
// Console.WriteLine("Disconnecting from SQLServer");
}
}
}
Setelah aplikasi sudah dibuat selanjutnya kita perlu melakukan deploy aplikasi, cara melakukan deploy aplikasi.
Deploy windows service manual
https://www.youtube.com/watch?v=EwTYYPRcWj0
Gagal Mendeploy ? Baca juga
https://archieananda.blogspot.com/2018/11/gagal-melakukan-deploy-windows-services.html
Membuat installasi windows service
https://www.youtube.com/watch?v=cp2aFNtcZfk&t=138s
sumber :
https://blog.sqltreeo.com/read-emails-from-exchange-online-mailbox-office-365-into-sql-server/
