Example #1
0
        /// <summary>
        /// Execute Query with specific timeout and nRowsAffected as output
        /// </summary>
        /// <param name="sqlQuery"></param>
        /// <param name="nRowsAffected"></param>
        /// <param name="timeOut"></param>
        /// <returns></returns>
        public bool ExecuteDml(string sqlQuery, ref int nRowsAffected, int timeOut)
        {
            DbCommand cmd = null;
            bool bReturn = false;

            try
            {
                if (_adoConnection == null)
                {
                    _strLastError = ErrorMessage.DbConnectionNotOpened;
                    return false;
                }

                //Create Command Instance with respective SQLQuery or SPName
                CreateCmdInstance(sqlQuery, ref cmd);

                if (timeOut > 0)
                {
                    cmd.CommandTimeout = timeOut;
                }

                nRowsAffected = cmd.ExecuteNonQuery();
                bReturn = true;
            }
            catch (Exception ex)
            {
                _objCommon = new Common();

                _objCommon.WriteToFile(_objCommon.GetLogPath(),
                                        string.Format("{0} DBPool-Catch - {1} - {2} - 000",
                                        Convert.ToString(DateTime.Now), ex.Message,
                                        System.Net.Dns.GetHostName()), true);

                _strLastError = ex.Message;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                    cmd = null;
                }
                _objCommon = null;
            }

            return bReturn;
        }
Example #2
0
        /// <summary>
        /// Open Connection to Database
        /// </summary>
        /// <returns>bool</returns>
        private bool OpenConnection()
        {
            bool bReturn = false;

            try
            {
                if (GetConnection())
                {
                    bReturn = true;
                }
            }
            catch (Exception ex)
            {
                _objCommon = new Common();

                _objCommon.WriteToFile(_objCommon.GetLogPath(),
                                        string.Format("{0} DBPool-Catch - {1} - {2} - 000",
                                        Convert.ToString(DateTime.Now), ErrorMessage.ErrorWhileEstablishingConnection,
                                        System.Net.Dns.GetHostName()), true);

                _strLastError = ex.Message;
            }
            finally
            {
                _objCommon = null;
            }

            return bReturn;
        }
Example #3
0
        /// <summary>
        /// Close database Connection
        /// </summary>
        private void CloseConnection()
        {
            try
            {
                if (_adoConnection != null)
                {
                    _adoConnection.Close();
                }
            }
            catch (Exception ex)
            {
                _objCommon = new Common();

                _objCommon.WriteToFile(_objCommon.GetLogPath(),
                                        string.Format("{0} DBPool-Catch - {1} - {2} - 000",
                                        Convert.ToString(DateTime.Now), ErrorMessage.ErrorWhileClosingConnection,
                                        System.Net.Dns.GetHostName()), true);

                _strLastError = ex.Message;
            }
            finally
            {
                if (_adoConnection != null)
                {
                    _adoConnection.Dispose();
                    _adoConnection = null;
                }

                _objCommon = null;
            }
        }
Example #4
0
        /// <summary>
        /// Execury Stored Procedure by passing parameters and getting return values as output
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="inParams"></param>
        /// <param name="retValues"></param>
        /// <param name="isNonQuery"></param>
        /// <returns>bool</returns>
        public bool SpQueryReturnValue(string spName, SqlParameter[] inParams, ref SqlParameter[] retValues, bool isNonQuery)
        {
            try
            {
                if (OpenConnection())
                {
                    bool bReturn = false;
                    SqlCommand cmd = null;
                    SqlParameter objParam = null;

                    try
                    {
                        objParam = default(SqlParameter);

                        if (_adoConnection == null)
                        {
                            _strLastError = ErrorMessage.DbConnectionNotOpened;
                            return false;
                        }

                        //Create Command Instance with respective SQLQuery or SPName
                        CreateCmdInstanceSql(spName, ref cmd);

                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandTimeout = intTimeOut; // increased the timeout for SQL Queries

                        if (inParams != null)
                        {
                            foreach (DbParameter dbPar in inParams)
                            {
                                objParam = ((SqlCommand)cmd).Parameters.Add((SqlParameter)dbPar);
                                objParam.Direction = ParameterDirection.Input;
                            }
                        }

                        if (retValues != null)
                        {
                            foreach (DbParameter dbPar in retValues)
                            {
                                objParam = ((SqlCommand)cmd).Parameters.Add((SqlParameter)dbPar);
                                objParam.Direction = ParameterDirection.ReturnValue;
                            }
                        }

                        if (isNonQuery)
                        {
                            cmd.ExecuteNonQuery();
                        }

                        bReturn = true;
                    }
                    catch (Exception ex)
                    {
                        _objCommon = new Common();

                        _objCommon.WriteToFile(_objCommon.GetLogPath(),
                                                string.Format("{0} DBPool-Catch - {1} - {2} - {3} - 000",
                                                Convert.ToString(DateTime.Now), ex.Message, spName,
                                                System.Net.Dns.GetHostName()), true);

                        _strLastError = ex.Message;
                    }
                    finally
                    {
                        cmd = null;
                        _objCommon = null;
                    }

                    return bReturn;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                CloseConnection();
            }
        }
Example #5
0
        /// <summary>
        /// Execury Stored Procedure by passing parameters and getting return values as output
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="inParams"></param>
        /// <param name="retValues"></param>
        /// <param name="isNonQuery"></param>
        /// <returns>bool</returns>
        public bool SPQueryDatasetNoParams( string strConn , string strSP , ref DataSet objDS )
        {
            try
            {
                //  if (OpenConnection("Password=ghost5;Persist Security Info=True;User ID=GClass_admin;Initial Catalog=CCT;Data Source=bdc-sqld023.na.pg.com\\devnt2322"))
                if ( OpenConnection () )
                {
                    bool bReturn = false;
                    SqlCommand cmd = null;

                    //bool bReturn = false;
                    //SqlCommand cmd = null;
                    SqlDataAdapter objDA = default ( SqlDataAdapter );
                    try
                    {
                        if ( ( _adoConnection == null ) )
                        {
                            _strLastError = ErrorMessage . DbConnectionNotOpened;
                            return false;
                        }

                        //Create Command Instance with respective SQLQuery or SPName
                        CreateCmdInstanceSql ( strSP , ref cmd );

                        cmd . CommandType = CommandType . StoredProcedure;
                        cmd . CommandTimeout = intTimeOut; // increased the timeout for SQL Queries

                        objDS = new DataSet ( );
                        objDA = new SqlDataAdapter ( ( SqlCommand ) cmd );
                        objDA . Fill ( objDS );
                        bReturn = true;
                    }
                    catch ( Exception ex )
                    {
                        _objCommon = new Common ( );

                        _objCommon . WriteToFile ( _objCommon . GetLogPath ( ) ,
                                                string . Format ( "{0} DBPool-Catch - {1} - {2} - {3} - 000" ,
                                                Convert . ToString ( DateTime . Now ) , ex . Message , strSP ,
                                                System . Net . Dns . GetHostName ( ) ) , true );

                        _strLastError = ex . Message;
                    }
                    finally
                    {
                        cmd = null;
                        _objCommon = null;
                    }

                    return bReturn;
                }
                else
                { return false; }
            }
            catch ( Exception )
            {

                throw;
            }
            finally
            {
                CloseConnection ( );
            }
        }
Example #6
0
        /// <summary>
        /// Execute Stored Procedure by getting resultSet as output 
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="resultSet"></param>
        /// <returns>bool</returns>
        public bool SpQueryDataset(string spName, ref DataSet resultSet)
        {
            try
            {
                if (OpenConnection())
                {
                    bool bReturn = false;
                    SqlCommand cmd = null;
                    SqlDataAdapter objDA = null;

                    try
                    {
                        objDA = default(SqlDataAdapter);

                        if (_adoConnection == null)
                        {
                            _strLastError = ErrorMessage.DbConnectionNotOpened;
                            return false;
                        }

                        //Create Command Instance with respective SQLQuery or SPName
                        CreateCmdInstanceSql(spName, ref cmd);

                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandTimeout = intTimeOut; // increased the timeout for SQL Queries

                        resultSet = new DataSet();
                        objDA = new SqlDataAdapter((SqlCommand)cmd);
                        objDA.Fill(resultSet);
                        bReturn = true;
                    }
                    catch (Exception ex)
                    {
                        _objCommon = new Common();

                        _objCommon.WriteToFile(_objCommon.GetLogPath(),
                                               string.Format("{0} DBPool-Catch - {1} - {2} - {3} - 000",
                                                             Convert.ToString(DateTime.Now), ex.Message, spName,
                                                             System.Net.Dns.GetHostName()), true);
                        _strLastError = ex.Message;
                    }
                    finally
                    {
                        objDA = null;
                        cmd = null;
                        _objCommon = null;
                    }

                    return bReturn;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                CloseConnection();
            }
        }
Example #7
0
        /// <summary>
        /// Execute Query with specific timeout and resultset as output
        /// </summary>
        /// <param name="sqlQuery"></param>
        /// <param name="resultSet"></param>
        /// <param name="timeOut"></param>
        /// <returns>bool</returns>
        public bool RsQuery(string sqlQuery, ref DataSet resultSet, int timeOut)
        {
            DbCommand cmd = null;
            DbDataAdapter da = null;
            bool bReturn = false;

            try
            {
                da = default(DbDataAdapter);

                if (_adoConnection == null)
                {
                    _strLastError = ErrorMessage.DbConnectionNotOpened;
                    return false;
                }

                //Create Command Instance with respective SQLQuery or SPName
                CreateCmdInstance(sqlQuery, ref cmd);

                if (timeOut > 0) cmd.CommandTimeout = timeOut;

                if (!_bOracle)
                {
                    da = new SqlDataAdapter((SqlCommand)cmd);
                }
                else
                {
                   // da = new OracleDataAdapter((OracleCommand)cmd);
                }

                resultSet = new DataSet();
                da.Fill(resultSet);
                bReturn = true;

            }
            catch (Exception ex)
            {
                _objCommon = new Common();

                _objCommon.WriteToFile(_objCommon.GetLogPath(),
                                        string.Format("{0} DBPool-Catch - {1} - {2} - 000",
                                        Convert.ToString(DateTime.Now), ex.Message,
                                        System.Net.Dns.GetHostName()), true);

                _strLastError = ex.Message;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                    cmd = null;
                }
                da = null;
                _objCommon = null;
            }

            return bReturn;
        }