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