public static QueryResult SendEmail(Connector.QueryParameter queryParameter)
{
QueryResult result = new QueryResult();
try
{
char splitMail = ';';
using (MailMessage mailMessage = new MailMessage())
{
AppHttpHandlerEmailConfig mailCfg = AppHttpHandler.AppHttpHandlerConfig.Email;
string server = mailCfg.Server;
int port = mailCfg.Port;
string username = mailCfg.Username;
string password = mailCfg.Password;
string defaultEmail = mailCfg.SenderAddress;
NetworkCredential credential = new NetworkCredential(username, password);
if (queryParameter["MAIL_FROM"] != null && !string.IsNullOrEmpty(queryParameter["MAIL_FROM"].ToString()))
{
defaultEmail = queryParameter["MAIL_FROM"].ToString();
}
mailMessage.From = new MailAddress(defaultEmail);
if (queryParameter["MAIL_TO"] != null && !string.IsNullOrEmpty(queryParameter["MAIL_TO"].ToString()))
{
string[] arrTo = queryParameter["MAIL_TO"].ToString().Split(splitMail);
arrTo.ToList <string>().ForEach(t => mailMessage.To.Add(new MailAddress(t)));
}
if (queryParameter["MAIL_CC"] != null && !string.IsNullOrEmpty(queryParameter["MAIL_CC"].ToString()))
{
string[] arrCC = queryParameter["MAIL_CC"].ToString().Split(splitMail);
arrCC.ToList <string>().ForEach(t => mailMessage.CC.Add(new MailAddress(t)));
}
if (queryParameter["MAIL_BCC"] != null && !string.IsNullOrEmpty(queryParameter["MAIL_BCC"].ToString()))
{
string[] arrBcc = queryParameter["MAIL_BCC"].ToString().Split(splitMail);
arrBcc.ToList <string>().ForEach(t => mailMessage.Bcc.Add(new MailAddress(t)));
}
if (queryParameter.Files != null && queryParameter.Files.Count > 0)
{
foreach (Connector.FileParameter fileParameter in queryParameter.Files)
{
mailMessage.Attachments.Add(new System.Net.Mail.Attachment(fileParameter.File.FullName));
}
}
mailMessage.Subject = queryParameter["MAIL_SUBJECT"].ToString();
mailMessage.SubjectEncoding = System.Text.Encoding.UTF8;
mailMessage.Body = queryParameter["MAIL_BODY"].ToString();
mailMessage.BodyEncoding = System.Text.Encoding.UTF8;
mailMessage.IsBodyHtml = true;
mailMessage.Priority = MailPriority.Normal;
using (SmtpClient smtpClient = new SmtpClient(server, port))
{
smtpClient.UseDefaultCredentials = false;
smtpClient.Credentials = credential;
smtpClient.EnableSsl = mailCfg.EnableSSL;
smtpClient.Send(mailMessage);
}
}
result.Success = true;
}
catch (SmtpFailedRecipientsException ex)
{
result.Success = false;
result.Message = ex.Message;
}
catch (Exception ex)
{
result.Success = false;
result.Message = ex.Message;
}
return(result);
}
public QueryResult ExecuteStoredProcedure(string storeName, QueryParameter queryParam)
{
StringBuilder logString = null;
QueryResult qResult = null;
IDbCommand dbCommand = null;
IDataReader dbReader = null;
IDbDataParameter dbParam = null;
//IDbTransaction dbTrans = null;
DataTable dt = null;
string paramName = string.Empty;
int cursorNum = 1;
try
{
logString = new StringBuilder();
logString.AppendLine(string.Format("\tAPP_DATA_PROCEDURE: {0}", storeName));
if (_dbConnection == null)
{
if (_exceptionTemporary != null)
{
throw new Exception(string.Format("ไม่สามารถโหลด assembly {0} ได้เนื่องจาก\r\n{1}", _assemblyName, _exceptionTemporary.GetBaseException().Message));
}
else
{
throw new Exception(string.Format("ไม่สามารถโหลด assembly {0} ได้", _assemblyName));
}
}
_dbConnection.ConnectionString = _connectionString;
if (_provider.Equals(ProviderFactory.MySQL))
{
throw new Exception("Provider ไม่รองรับการ Execute โดยใช้ StoredProcedure");
}
if (_dbConnection.State != ConnectionState.Open)
{
_dbConnection.Open();
}
qResult = new QueryResult();
dbCommand = CreateCommand(storeName, queryParam, ref logString);
//dbTrans = _dbConnection.BeginTransaction();
switch (_provider)
{
case ProviderFactory.MSSQL:
dbReader = dbCommand.ExecuteReader();
while (!dbReader.IsClosed)
{
dt = new DataTable();
dt.Load(dbReader);
if (cursorNum == 1)
{
qResult.DataTable = dt;
}
else
{
qResult.AddOutputParam("data" + cursorNum, ConnectorUtil.DataTableToDictionary(dt, _dateTimeFormat, _cultureInfo));
}
cursorNum++;
}
break;
default:
dbCommand.ExecuteNonQuery();
break;
}
for (int i = 0; i < dbCommand.Parameters.Count; i++)
{
dbParam = dbCommand.Parameters[i] as IDbDataParameter;
if (!dbParam.Direction.Equals(ParameterDirection.Output))
{
continue;
}
paramName = dbParam.ParameterName;
if (_paramNamePrefix.Length > 0)
{
paramName = paramName.Replace(_paramNamePrefix, string.Empty);
}
if (!_reservedOutputName.Contains(paramName.ToUpper()))
{
paramName = paramName.Replace(_outputReplace, string.Empty);
if (dbParam.Value == null || System.DBNull.Value.Equals(dbParam.Value))
{
qResult.AddOutputParam(paramName, dbParam.Value);
}
else if (dbParam.Value is DateTime)
{
qResult.AddOutputParam(paramName, ConnectorUtil.DateTimeToString((dbParam.Value as DateTime?)));
}
else if (dbParam.Value is IDataReader)
{
dt = new DataTable();
if (dbParam.Value != System.DBNull.Value)
{
dt.Load(dbParam.Value as IDataReader);
}
qResult.AddOutputParam(paramName.ToLower(), ConnectorUtil.DataTableToDictionary(dt, _dateTimeFormat, _cultureInfo));
}
else
{
if (paramName.IndexOf("NT_") == 0)
{
qResult.AddNTParam(paramName, dbParam.Value);
}
else
{
qResult.AddOutputParam(paramName, dbParam.Value);
}
}
}
else if (paramName.Equals("PO_DATA"))
{
dt = new DataTable();
if (dbParam.Value != System.DBNull.Value)
{
dt.Load(dbParam.Value as IDataReader);
}
qResult.DataTable = dt;
}
else if (paramName.Equals("PO_TOTAL"))
{
if (!string.IsNullOrEmpty(dbParam.Value.ToString()))
{
qResult.Total = int.Parse(dbParam.Value.ToString());
}
}
else if (paramName.Equals("PO_STATUS"))
{
if (dbParam.Value.ToString().Equals("1"))
{
qResult.Success = true;
}
else
{
qResult.Success = false;
}
}
else if (paramName.Equals("PO_STATUS_MSG"))
{
qResult.Message = dbParam.Value.ToString();
}
}
logString.AppendLine(string.Format("<LOGFUNCTION>"));
if (queryParam.Parameter.ContainsKey("FN_ID") == true)
{
logString.AppendLine(string.Format("{0}", queryParam.Parameter["FN_ID"]));
}
logString.AppendLine(string.Format("</LOGFUNCTION>"));
if (qResult.Success != true)
{
logString.AppendLine(string.Format("{0}", qResult.Message));
logString.AppendLine(string.Format("<LOGSESSION>"));
logString.Append(LogSession());
logString.AppendLine(string.Format("</LOGSESSION>"));
logString.AppendLine("!!! Error !!!");
ConnectorUtil.WriteLogError(logString.ToString());
}
else
{
logString.AppendLine(string.Format("<LOGSESSION>"));
logString.Append(LogSession());
logString.AppendLine(string.Format("</LOGSESSION>"));
logString.AppendLine("!!! Completed !!!");
ConnectorUtil.WriteLogInfo(logString.ToString());
//Util.WriteLogDebug(qResult.ToJson());
}
//try
//{
// dbTrans.Commit();
//}
//catch { }
}
catch (Exception ex)
{
//try
//{
// dbTrans.Rollback();
//}
//catch { }
qResult = new QueryResult(ex);
logString.AppendLine(string.Format("{0}", qResult.Message));
logString.AppendLine(string.Format("<LOGFUNCTION>"));
if (queryParam.Parameter.ContainsKey("FN_ID") == true)
{
logString.AppendLine(string.Format("{0}", queryParam.Parameter["FN_ID"]));
}
logString.AppendLine(string.Format("</LOGFUNCTION>"));
logString.AppendLine(string.Format("<LOGSESSION>"));
logString.Append(LogSession());
logString.AppendLine(string.Format("</LOGSESSION>"));
logString.AppendLine("!!! Error !!!");
ConnectorUtil.WriteLogError(logString.ToString());
}
finally
{
//try
//{
// dbTrans.Dispose();
// dbTrans = null;
//}
//catch { }
if (_dbConnection != null && _dbConnection.State == ConnectionState.Open)
{
_dbConnection.Close();
_dbConnection.Dispose();
}
}
return(qResult);
}
public QueryResult ExecuteStatement(string statement, QueryParameter queryParam)
{
StringBuilder logString = null;
QueryResult qResult = null;
IDbCommand dbCommand = null;
IDataReader dbReader = null;
IDbDataParameter dbParam = null;
DataTable dt = null;
string paramName = string.Empty;
int cursorNum = 1;
try
{
logString = new StringBuilder();
if (_dbConnection == null)
{
throw new Exception(string.Format("ไม่สามารถโหลด assembly {0} ได้เนื่องจาก\r\n{1}", _assemblyName, _exceptionTemporary.GetBaseException().Message));
}
_dbConnection.ConnectionString = _connectionString;
qResult = new QueryResult();
if (_dbConnection.State != ConnectionState.Open)
{
_dbConnection.Open();
}
dbCommand = CreateCommand(statement);
if (queryParam != null)
{
for (int i = 0; i < dbCommand.Parameters.Count; i++)
{
if (queryParam.Parameter.Count >= i)
{
dbParam = dbCommand.Parameters[i] as IDbDataParameter;
dbParam.Value = queryParam.Parameter[queryParam.Parameter.Keys.ElementAt(i)];
}
else
{
break;
}
}
}
dbReader = dbCommand.ExecuteReader();
while (!dbReader.IsClosed)
{
dt = new DataTable();
dt.Load(dbReader);
if (cursorNum == 1)
{
qResult.DataTable = dt;
}
else
{
qResult.AddOutputParam("data" + cursorNum, ConnectorUtil.DataTableToDictionary(dt, _dateTimeFormat, _cultureInfo));
}
cursorNum++;
}
for (int i = 0; i < dbCommand.Parameters.Count; i++)
{
dbParam = dbCommand.Parameters[i] as IDbDataParameter;
if (!dbParam.Direction.Equals(ParameterDirection.Output))
{
continue;
}
paramName = dbParam.ParameterName;
if (_paramNamePrefix.Length > 0)
{
paramName = paramName.Replace(_paramNamePrefix, string.Empty);
}
if (!_reservedOutputName.Contains(paramName.ToUpper()))
{
paramName = paramName.Replace(_outputReplace, string.Empty);
if (dbParam.Value == null || System.DBNull.Value.Equals(dbParam.Value))
{
qResult.AddOutputParam(paramName, dbParam.Value);
}
else if (dbParam.Value is DateTime)
{
qResult.AddOutputParam(paramName, ConnectorUtil.DateTimeToString((dbParam.Value as DateTime?)));
}
else if (dbParam.Value is IDataReader)
{
dt = new DataTable();
dt.Load(dbParam.Value as IDataReader);
qResult.AddOutputParam(paramName.ToLower(), ConnectorUtil.DataTableToDictionary(dt, _dateTimeFormat, _cultureInfo));
}
else
{
qResult.AddOutputParam(paramName, dbParam.Value);
}
}
else if (paramName.Equals("PO_DATA"))
{
dt = new DataTable();
dt.Load(dbParam.Value as IDataReader);
qResult.DataTable = dt;
}
else if (paramName.Equals("PO_TOTAL"))
{
if (!string.IsNullOrEmpty(dbParam.Value.ToString()))
{
qResult.Total = int.Parse(dbParam.Value.ToString());
}
}
else if (paramName.Equals("PO_STATUS"))
{
if (dbParam.Value.ToString().Equals("1"))
{
qResult.Success = true;
}
else
{
qResult.Success = false;
}
}
else if (paramName.Equals("PO_STATUS_MSG"))
{
qResult.Message = dbParam.Value.ToString();
}
}
logString.AppendLine(string.Format("<LOGFUNCTION>"));
if (queryParam.Parameter.ContainsKey("FN_ID") == true)
{
logString.AppendLine(string.Format("{0}", queryParam.Parameter["FN_ID"]));
}
logString.AppendLine(string.Format("</LOGFUNCTION>"));
if (qResult.Success != true)
{
logString.AppendLine(string.Format("{0}", qResult.Message));
logString.AppendLine(string.Format("<LOGSESSION>"));
logString.Append(LogSession());
logString.AppendLine(string.Format("</LOGSESSION>"));
logString.AppendLine("!!! Error !!!");
ConnectorUtil.WriteLogError(logString.ToString());
}
else
{
logString.AppendLine(string.Format("<LOGSESSION>"));
logString.Append(LogSession());
logString.AppendLine(string.Format("</LOGSESSION>"));
logString.AppendLine("!!! Completed !!!");
ConnectorUtil.WriteLogInfo(logString.ToString());
//Util.WriteLogDebug(qResult.ToJson());
}
}
catch (Exception ex)
{
qResult = new QueryResult(ex);
logString.AppendLine(string.Format("{0}", qResult.Message));
logString.AppendLine(string.Format("<LOGFUNCTION>"));
if (queryParam.Parameter.ContainsKey("FN_ID") == true)
{
logString.AppendLine(string.Format("{0}", queryParam.Parameter["FN_ID"]));
}
logString.AppendLine(string.Format("</LOGFUNCTION>"));
logString.AppendLine(string.Format("<LOGSESSION>"));
logString.Append(LogSession());
logString.AppendLine(string.Format("</LOGSESSION>"));
logString.AppendLine("!!! Error !!!");
ConnectorUtil.WriteLogError(logString.ToString(), ex);
}
finally
{
if (_dbConnection != null && _dbConnection.State == ConnectionState.Open)
{
_dbConnection.Close();
}
}
return(qResult);
}
protected IDbCommand CreateCommand(string storeName, QueryParameter queryParam, ref StringBuilder logString)
{
IDbDataParameter dbParam = null;
//List<IDbDataParameter> paramList = null;
IDbCommand dbCommand = null;
string[] logHiddenParams = null;
try
{
//paramList = new List<IDbDataParameter>();
if (_dbConnection.State != ConnectionState.Open)
{
_dbConnection.Open();
}
dbCommand = _dbConnection.CreateCommand();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = storeName;
if (_provider.Equals(ProviderFactory.Oracle))
{
var oracleCommandBindByName = _commandType.GetProperty("BindByName");
oracleCommandBindByName.SetValue(dbCommand, true, null);
}
logHiddenParams = AppHttpHandler.AppHttpHandlerConfig.Logs.HideFields.ToArray();
_commandBuilderType.GetMethod("DeriveParameters").Invoke(null, new object[] { dbCommand });
for (int i = 0; i < dbCommand.Parameters.Count; i++)
{
string paramName = string.Empty;
object paramValue = null;
dbParam = dbCommand.Parameters[i] as IDbDataParameter;
switch (dbParam.Direction)
{
case ParameterDirection.Input:
if (queryParam != null && queryParam.Parameter != null)
{
if (dbParam.ParameterName.IndexOf(_inputReplace) != -1)
{
paramName = dbParam.ParameterName.Substring(dbParam.ParameterName.IndexOf(_inputReplace) + _inputReplace.Length);
}
else
{
paramName = dbParam.ParameterName;
}
if (queryParam.Parameter.ContainsKey(paramName.ToUpper()))
{
paramValue = queryParam.Parameter[paramName];
}
if (paramValue != null && ConnectorUtil.DBTypeMap(dbParam.DbType).Equals(typeof(DateTime)) || dbParam.Value is DateTime)
{
if (paramValue is string)
{
long milliseconds;
bool parseResult = long.TryParse((string)paramValue, out milliseconds);
if (parseResult)
{
dbParam.Value = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(milliseconds).ToLocalTime();
}
else
{
dbParam.Value = ConnectorUtil.StringToDateTime(paramValue.ToString());
}
}
else if (paramValue is int)
{
dbParam.Value = ConnectorUtil.UnixTimeStampToDateTime(int.Parse(paramValue.ToString())).AddMinutes(TimeZoneOffset);
}
else if (paramValue is long)
{
dbParam.Value = ConnectorUtil.UnixTimeStampToDateTime(long.Parse(paramValue.ToString())).AddMinutes(TimeZoneOffset);
}
if (_provider == ProviderFactory.MSSQL)
{
logString.AppendLine(string.Format("{0}: PARSE('{1}' as DATETIME USING 'th-TH')", dbParam.ParameterName, ConnectorUtil.DateTimeToString(dbParam.Value as DateTime?)));
}
else if (_provider == ProviderFactory.Oracle)
{
logString.AppendLine(string.Format("{0}: TO_DATE('{1}','DD/MM/YYYY HH24:MI:SS','NLS_DATE_LANGUAGE=THAI')", dbParam.ParameterName, ConnectorUtil.DateTimeToString(dbParam.Value as DateTime?)));
}
break;
}
else if (paramValue != null)
{
if (dbParam.DbType != DbType.String && !string.IsNullOrEmpty(paramValue.ToString()))
{
dbParam.Value = paramValue;
}
else if (dbParam.DbType == DbType.String)
{
dbParam.Value = paramValue;
}
else
{
}
}
else
{
dbParam.Value = System.DBNull.Value;
}
}
object logValue = dbParam.Value;
if (logHiddenParams != null && logHiddenParams.Contains(dbParam.ParameterName.Replace(_inputReplace, "").ToUpper()))
{
logValue = "-- Hidden Field --";
}
logString.AppendLine(string.Format("{0}: {1}", dbParam.ParameterName, logValue));
break;
case ParameterDirection.InputOutput:
case ParameterDirection.Output:
dbParam.Direction = ParameterDirection.Output;
break;
//paramName = dbParam.ParameterName.Substring(dbParam.ParameterName.IndexOf(_outputReplace) + _outputReplace.Length);
//break;
default:
continue;
}
//paramList.Add(dbParam);
}
return(dbCommand);
}
catch (Exception ex)
{
throw ex;
}
}
