public static async Task <List <SendDataMailAccount> > GetMailAccounts()
{
var sqlConStr = ConstantHelper.GetConnectionString();
List <SendDataMailAccount> sendDataMailAccountList = new List <SendDataMailAccount>();
try
{
using (SqlConnection connectionQueryData = new SqlConnection(sqlConStr))
{
if (connectionQueryData.State == System.Data.ConnectionState.Closed)
{
connectionQueryData.Open();
}
#region Execute Command
var selectQuery = @"
SELECT [MAILACCOUNTID]
,[MAILACCOUNTTITLE]
,[MAILSERVERNAME]
,[MAILACCOUNTNAME]
,[MAILACCOUNTPASSWORD]
,[MAILPOP3PORT]
,[MAILSMTPPORT]
,[FROMMAILADDRESS]
FROM [dbo].[PLG_SENDDATA_MAILACCOUNT]
WHERE ACTIVE = 1 AND ISDEFAULT = 1
";
using (SqlCommand command = new SqlCommand(selectQuery, connectionQueryData))
{
DataTable dataTable = new DataTable();
// create data adapter
SqlDataAdapter da = new SqlDataAdapter(command);
// this will query your database and return the result to your datatable
da.Fill(dataTable);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
var id = Int32.Parse(dataTable.Rows[i]["MAILACCOUNTID"].ToString());
var title = dataTable.Rows[i]["MAILACCOUNTTITLE"]?.ToString();
var serverName = dataTable.Rows[i]["MAILSERVERNAME"]?.ToString();
var accountName = dataTable.Rows[i]["MAILACCOUNTNAME"]?.ToString();
var accountPass = dataTable.Rows[i]["MAILACCOUNTPASSWORD"]?.ToString();
var pop3Port = Int32.Parse(dataTable.Rows[i]["MAILPOP3PORT"].ToString());
var smptpPort = Int32.Parse(dataTable.Rows[i]["MAILSMTPPORT"].ToString());
var fromAddress = dataTable.Rows[i]["FROMMAILADDRESS"]?.ToString();
var sendDataMailAccount = new SendDataMailAccount()
{
AccountId = id,
AccountName = accountName,
AccountPass = accountPass,
Active = 1,
FromMailAddress = fromAddress,
MailPop3Port = pop3Port,
MailSmtpPort = smptpPort,
ServerName = serverName,
Title = title
};
sendDataMailAccountList.Add(sendDataMailAccount);
}
}
#endregion
try
{
connectionQueryData.Close();
}
catch (Exception)
{
}
}
}
catch (Exception ex)
{
//TODO:Log
}
return(sendDataMailAccountList);
}
public static async Task <int> InsertSendDataItem(SendDataItem sendDataDetail)
{
try
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
var conStr = ConstantHelper.GetConnectionString();
using (SqlConnection connection = new SqlConnection(conStr))
{
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
#region commandStr
var insertCommand = @"
INSERT INTO [dbo].[PLG_SENDDATA_ITEMS]
([SCHED_NAME]
,[JOB_NAME]
,[JOB_GROUP]
,[TRIGGER_NAME]
,[TRIGGER_GROUP]
,[TYPE]
,[FROM]
,[RECIPIENTLIST]
,[CC]
,[BCC]
,[REPLYTO]
,[BODY]
,[SENTDATE]
,[ERRORMSG]
,[ACTIVE]
,[CREATEDDATE])
VALUES
(@SCHED_NAME
,@JOB_NAME
,@JOB_GROUP
,@TRIGGER_NAME
,@TRIGGER_GROUP
,@TYPE
,@FROM
,@RECIPIENTLIST
,@CC
,@BCC
,@REPLYTO
,@BODY
,@SENTDATE
,@ERRORMSG
,1
,@CREATEDDATE)
";
#endregion
#region Execute Command
using (SqlCommand command = new SqlCommand(insertCommand, connection))
{
command.Parameters.AddWithValue("@SCHED_NAME", sendDataDetail.ScheduleName);
command.Parameters.AddWithValue("@JOB_NAME", sendDataDetail.JobName);
command.Parameters.AddWithValue("@JOB_GROUP", sendDataDetail.JobGroup);
command.Parameters.AddWithValue("@TRIGGER_NAME", sendDataDetail.TriggerName);
command.Parameters.AddWithValue("@TRIGGER_GROUP", sendDataDetail.TriggerGroup);
command.Parameters.AddWithValue("@TYPE", sendDataDetail.Type);
command.Parameters.AddWithValue("@FROM", sendDataDetail.From);
command.Parameters.AddWithValue("@RECIPIENTLIST", sendDataDetail.Recipient);
command.Parameters.AddWithValue("@CC", sendDataDetail.Cc);
command.Parameters.AddWithValue("@BCC", sendDataDetail.Bcc);
command.Parameters.AddWithValue("@BODY", sendDataDetail.Body);
if (string.IsNullOrEmpty(sendDataDetail.ReplyTo) == false)
{
command.Parameters.AddWithValue("@REPLYTO", sendDataDetail.ReplyTo);
}
else
{
command.Parameters.AddWithValue("@REPLYTO", DBNull.Value);
}
if (sendDataDetail.SentDate.HasValue)
{
command.Parameters.AddWithValue("@SENTDATE", sendDataDetail.SentDate);
}
else
{
command.Parameters.AddWithValue("@SENTDATE", DBNull.Value);
}
if (string.IsNullOrEmpty(sendDataDetail.ErrorMsg) == false)
{
command.Parameters.AddWithValue("@ERRORMSG", sendDataDetail.ErrorMsg);
}
else
{
command.Parameters.AddWithValue("@ERRORMSG", DBNull.Value);
}
command.Parameters.AddWithValue("@CREATEDDATE", sendDataDetail.CreatedDate);
var res = await command.ExecuteNonQueryAsync();
stopwatch.Stop();
if (res < 1)
{
LoggerService.GetLogger(ConstantHelper.JobLog).Log(new LogItem()
{
LoggerName = ConstantHelper.JobLog,
Title = "InsertSendDataItem Executed",
Message = "InsertSendDataItem Executed",
LogItemProperties = new List <LogItemProperty>()
{
new LogItemProperty("ServiceName", ConstantHelper.JobLog),
new LogItemProperty("ActionName", "InsertSendDataItem"),
new LogItemProperty("FormData", sendDataDetail),
new LogItemProperty("ElapsedTimeAssn", stopwatch.Elapsed.TotalSeconds),
},
Exception = new ArgumentException("Insert Failed !"),
LogLevel = LogLevel.Error
});
}
else
{
LoggerService.GetLogger(ConstantHelper.JobLog).Log(new LogItem()
{
LoggerName = ConstantHelper.JobLog,
Title = "InsertSendDataItem Executed",
Message = "InsertSendDataItem Executed",
LogItemProperties = new List <LogItemProperty>()
{
new LogItemProperty("ServiceName", ConstantHelper.JobLog),
new LogItemProperty("ActionName", "InsertSendDataItem"),
new LogItemProperty("ElapsedTimeAssn", stopwatch.Elapsed.TotalSeconds),
},
LogLevel = LogLevel.Trace
});
}
return(res);
}
#endregion
}
}
catch (Exception ex)
{
LoggerService.GetLogger(ConstantHelper.JobLog).Log(new LogItem()
{
LoggerName = ConstantHelper.JobLog,
Title = "InsertSendDataItem Error",
Message = ex.Message,
LogItemProperties = new List <LogItemProperty>()
{
new LogItemProperty("ServiceName", ConstantHelper.JobLog),
new LogItemProperty("ActionName", "InsertSendDataItem"),
new LogItemProperty("FormData", sendDataDetail),
},
LogLevel = LogLevel.Error,
Exception = ex
});
return(-1);
}
}