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); } }