예제 #1
0
        /// <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);
        }
예제 #2
0
        /// <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);
        }
예제 #3
0
        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);
        }
예제 #4
0
 public SqlContactor()
 {
     _logger = new SoftLogger();
     _logger.FileCollector = "Connector.SqlProcessor.SqlContactor";
     _logger.addMessage    = new System.Collections.Specialized.NameValueCollection();
 }
예제 #5
0
        /// <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);
        }