/// <summary>
/// Call this function to Execute stored procedure specific Db that will return Single Value
/// </summary>
/// <param name="ConnectionStringName">Pass connection String Name Mention in Configuration</param>
/// <param name="StoredProcedureName">Pass stored Procedure Name That you have created</param>
/// <param name="Parameters">Pass List of Params if you have else you can skip</param>
/// <returns>Single Value if operation will successfull</returns>
public Object ExecuteScalar(string ConnectionStringName, string StoredProcedureName, Dictionary <string, object> Parameters = null)
{
DataTable dt = new DataTable();
SqlConnection objSqlConnection = null;
try
{
_logger.addMessage.Add("ExecuteScalar", "ExecuteScalar Method is goint to Execute");
SqlContactor objSqlContactor = new SqlContactor();
objSqlConnection = objSqlContactor.OpenConnection(ConnectionStringName);
_logger.addMessage.Add("ExecuteScalar", "SqlContactor Open Connection Successfully for " + ConnectionStringName);
using (SqlCommand objSqlCommand = new SqlCommand(StoredProcedureName, objSqlConnection))
{
objSqlCommand.CommandType = CommandType.StoredProcedure;
if (Parameters != null)
{
_logger.addMessage.Add("ExecuteScalar", "Parameters is going to Add");
foreach (KeyValuePair <string, object> item in Parameters)
{
if (item.Value != null)
{
_logger.addMessage.Add("ExecuteScalar", "Parameters Key:" + item.Key + "-Value:" + item.Value.ToString());
objSqlCommand.Parameters.AddWithValue("@" + item.Key, item.Value);
}
}
}
SqlDataAdapter da = new SqlDataAdapter(objSqlCommand);
objSqlConnection.Open();
_logger.addMessage.Add("ExecuteScalar", "Connection open Successfully");
DataSet ds = new DataSet();
da.Fill(ds);
_logger.addMessage.Add("ExecuteScalar", "Record fetached Successfully Successfully");
if (ds.Tables.Count > 0)
{
dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
return(dt.Rows[0][0]);
}
}
else
{
_logger.addMessage.Add("ExecuteScalar", "No Record found against this Login");
}
}
}
catch (Exception ex)
{
_logger.addMessage.Add("ExecuteScalar", "SqlContactor Error during Execution " + ex.ToString());
return(null);
}
finally
{
objSqlConnection.Close();
objSqlConnection.Dispose();
SoftLogger.WriteLogImmediateAsync(_logger);
}
return(null);
}
/// <summary>
/// Call this function to Insert Data into specific Db
/// </summary>
/// <param name="ConnectionStringName">Pass connection String Name Mention in Configuration</param>
/// <param name="StoredProcedureName">Pass stored Procedure Name That you have created</param>
/// <param name="Parameters">Pass List of Params if you have else you can skip</param>
/// <returns>True if operation will successfull</returns>
public bool ExecuteNonQuery(string ConnectionStringName, string StoredProcedureName, Dictionary <string, object> Parameters = null)
{
SqlConnection objSqlConnection = null;
try
{
_logger.addMessage.Add("ExecuteNonQuery", "ExecuteNonQuery Method is goint to Execute");
SqlContactor objSqlContactor = new SqlContactor();
objSqlConnection = objSqlContactor.OpenConnection(ConnectionStringName);
_logger.addMessage.Add("ExecuteNonQuery", "SqlContactor Open Connection Successfully for " + ConnectionStringName);
using (SqlCommand objSqlCommand = new SqlCommand(StoredProcedureName, objSqlConnection))
{
objSqlCommand.CommandType = CommandType.StoredProcedure;
if (Parameters != null)
{
_logger.addMessage.Add("ExecuteNonQuery", "Parameters is going to Add");
foreach (KeyValuePair <string, object> item in Parameters)
{
if (item.Value != null)
{
_logger.addMessage.Add("ExecuteNonQuery", "Parameters Key:" + item.Key + "-Value:" + item.Value.ToString());
objSqlCommand.Parameters.AddWithValue("@" + item.Key, item.Value);
}
}
}
objSqlConnection.Open();
_logger.addMessage.Add("ExecuteNonQuery", "Connection open Successfully");
objSqlCommand.ExecuteNonQuery();
_logger.addMessage.Add("ExecuteNonQuery", "Record Insetered Successfully");
}
}
catch (Exception ex)
{
_logger.addMessage.Add("ExecuteNonQuery", "SqlContactor Error during Execution " + ex.ToString());
return(false);
}
finally
{
SoftLogger.WriteLogImmediateAsync(_logger);
objSqlConnection.Close();
objSqlConnection.Dispose();
}
return(true);
}
internal SqlConnection OpenConnection(string connectionStringName)
{
SqlConnection objCon = null;
try
{
_logger.addMessage.Add("OpenConnection", "OpenConnection Method is goint to Execute");
objCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[connectionStringName].ToString());
_logger.addMessage.Add("OpenConnection", "Sql Connection Open Successfully Against" + connectionStringName);
}
catch (Exception ex)
{
_logger.addMessage.Add("OpenConnection", "Sql Connection Error Against" + connectionStringName + "Error:-" + ex.ToString());
return(null);
}
finally
{
SoftLogger.WriteLogImmediateAsync(_logger);
}
return(objCon);
}
public SqlContactor()
{
_logger = new SoftLogger();
_logger.FileCollector = "Connector.SqlProcessor.SqlContactor";
_logger.addMessage = new System.Collections.Specialized.NameValueCollection();
}
/// <summary>
/// This Function will called which fetch your Email Subject and body from DB
/// Written By:Amit Pratap Singh
/// written Date:28-OCT-2017
/// </summary>
/// <param name="mailto">Send Email To</param>
/// <param name="mailtype">Mail Template Name</param>
/// <returns>Mail Send Successfully or Not Boolean</returns>
public bool SendMailtoUser(string mailto, string mailtype, string CCAddress = "", bool bodyhtml = true)
{
string frommail = "", DisplayName = string.Empty;
string mailsubject = "";
string mailbody = "";
string smtp_user = "";
string smtp_password = "";
string App_name = "Softdebugger";
string mail_type = "Softdebugger";
string smtp_server = "";
int smtpport = 0;
bool isactive = false;
bool TLS_SSL = false;
mailtype = "ENQUIRY";
string BCCAddress = "";
//This contains the sender,retriever,subject and the body of the mail component
try
{
DataSet ds = null;
using (SqlConnection scon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultWebsite"].ToString()))
{
using (SqlCommand scmd = new SqlCommand("GET_SMTPDETAILS", scon))
{
scmd.CommandType = CommandType.StoredProcedure;
scmd.Parameters.AddWithValue("@Application_Name", App_name);
scmd.Parameters.AddWithValue("@Mail_Type", mail_type);
SqlDataAdapter da = new SqlDataAdapter(scmd);
ds = new DataSet();
da.Fill(ds);
}
}
//after getting data check for credential
if (ds != null && ds.Tables.Count > 0)
{
frommail = ds.Tables[0].Rows[0]["mail_from"].ToString();
smtp_user = ds.Tables[0].Rows[0]["smtp_user"].ToString();
smtp_password = ds.Tables[0].Rows[0]["smtp_password"].ToString();
smtp_server = ds.Tables[0].Rows[0]["smtp_server"].ToString();
smtpport = Convert.ToInt32(ds.Tables[0].Rows[0]["smtp_port"]);
TLS_SSL = Convert.ToBoolean(ds.Tables[0].Rows[0]["SSL_TLS"]);
isactive = Convert.ToBoolean(ds.Tables[0].Rows[0]["isActive"]);
DisplayName = ds.Tables[0].Rows[0]["DisplayName"].ToString();
}
else
{
//log no ecord found for smtp
}
using (SqlConnection scon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultWebsite"].ToString()))
{
using (SqlCommand scmd = new SqlCommand("GetMailTeplate", scon))
{
scmd.CommandType = CommandType.StoredProcedure;
scmd.Parameters.AddWithValue("@MailType", mailtype);
scmd.Parameters.AddWithValue("@ApplicationName", App_name);
SqlDataAdapter da = new SqlDataAdapter(scmd);
ds = new DataSet();
da.Fill(ds);
}
}
if (ds != null && ds.Tables.Count > 0)
{
mailbody = ds.Tables[0].Rows[0]["MAILBODY"].ToString();
mailsubject = ds.Tables[0].Rows[0]["MAILSUBJECT"].ToString();
}
else
{
//log no ecord found for smtp
}
if (isactive == true)
{
MailAddress FromAddress = new MailAddress(frommail, DisplayName);
MailAddress ToAddress = new MailAddress(mailto);
MailMessage objmsg = new MailMessage(FromAddress, ToAddress);
objmsg.Subject = mailsubject;
objmsg.Body = mailbody;
objmsg.IsBodyHtml = bodyhtml;
if (System.Configuration.ConfigurationSettings.AppSettings.AllKeys.Contains("BCCEMAILADDRESS"))
{
BCCAddress = System.Configuration.ConfigurationManager.AppSettings["BCCEMAILADDRESS"].ToString();
}
if (!string.IsNullOrEmpty(CCAddress))
{
string[] CCId = CCAddress.Split(';');
foreach (string CCEmail in CCId)
{
objmsg.CC.Add(new MailAddress(CCEmail)); //Adding Multiple CC email Id
}
}
if (!string.IsNullOrEmpty(BCCAddress))
{
string[] BCCId = BCCAddress.Split(';');
foreach (string BCCEmail in BCCId)
{
objmsg.Bcc.Add(new MailAddress(BCCEmail)); //Adding Multiple BCC email Id
}
}
//This part contains the smtp server details
SmtpClient Client_Server_Name = new SmtpClient(smtp_server);
//this part contains the port number of server
Client_Server_Name.Port = smtpport;
//this part contains the smtp login credentials
Client_Server_Name.Credentials = new System.Net.NetworkCredential(smtp_user, smtp_password);
//check tls/ssl value
Client_Server_Name.EnableSsl = TLS_SSL;
Client_Server_Name.Send(objmsg);
SoftLogger.WriteLogImmediate("Mail Status : " + "Mail Type :" + mailsubject + " ,Mail Sent Successfully to : " + mailto + ", CC TO :" + CCAddress + ", BCC TO :" + BCCAddress, "SendMail", AssemblyName);
}
else
{
SoftLogger.WriteLogImmediate(" //SMTP DETAILS IS NOT ACTIVE YET !!", "SendMail", AssemblyName);
}
}
catch (Exception ex)
{
SoftLogger.WriteLogImmediate(ex.ToString(), "SendMail", AssemblyName);
return(false);
}
return(true);
}
