예제 #1
0
        public bool ExecuteNonQueryWithParams(string query, List <SqlParameter> parameters)
        {
            string Function_Name = "ExecuteNonQueryWithParams";

            LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");

            LogHelper.Debug(CLASS_NAME, Function_Name, string.Format("Query = {0}", query));
            DatabaseSession session       = m_sessionFactory.GetDatabaseSession();
            bool            bLocalSession = false;
            int             recordUpdated = 0;
            bool            boolUpdated   = true;

            try
            {
                if (session != null)
                {
                    if (!session.IsConnectionOpen())
                    {
                        session.OpenConnection();
                        bLocalSession = true;
                    }
                    IDbCommand cmd = session.CreateCommand();
                    //Replace Parameter delimiter based on the type
                    query           = session.ReplaceParamelimiterString(query);
                    cmd.CommandText = query;
                    foreach (var item in parameters)
                    {
                        IDbDataParameter parameter = cmd.CreateParameter();
                        parameter.ParameterName = item.ParameterName;
                        parameter.DbType        = item.DbType;
                        parameter.Direction     = item.Direction;
                        parameter.Value         = item.Value;
                        cmd.Parameters.Add(parameter);
                        //cmd.Parameters.Add(item);
                    }
                    recordUpdated = cmd.ExecuteNonQuery();
                    boolUpdated   = false;
                }
            }
            catch (Exception localException)
            {
                LogHelper.Error(CLASS_NAME, Function_Name, localException.ToString());
            }

            if (bLocalSession)
            {
                session.CloseConnection();
            }
            LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");

//             if (recordUpdated <= 0)
//             {
//                 return false;
//             }
            return(!boolUpdated);
        }
예제 #2
0
        public IDataReader ExecuteQueryWithParams(string query, List <SqlParameter> parameters)
        {
            string Function_Name = "ExecuteQueryWithParams";

            LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");

            LogHelper.Debug(CLASS_NAME, Function_Name, string.Format("Query = {0}", query));

            DatabaseSession session       = m_sessionFactory.GetDatabaseSession();
            bool            bLocalSession = false;
            IDataReader     dr            = null;

            try
            {
                if (session != null)
                {
                    if (!session.IsConnectionOpen())
                    {
                        session.OpenConnection();
                        bLocalSession = true;
                    }
                    query = session.ReplaceParamelimiterString(query);
                    IDbCommand cmd = session.CreateCommand();
                    cmd.CommandText = query;
                    foreach (var item in parameters)
                    {
                        IDbDataParameter parameter = cmd.CreateParameter();
                        parameter.ParameterName = item.ParameterName;
                        parameter.DbType        = item.DbType;
                        parameter.Direction     = item.Direction;
                        parameter.Value         = item.Value;
                        cmd.Parameters.Add(parameter);
                        //cmd.Parameters.Add(item);
                    }
                    dr = cmd.ExecuteReader();


                    //LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
                    //return dr;
                }
            }
            catch (Exception localException)
            {
                LogHelper.Error(CLASS_NAME, Function_Name, localException.ToString());
            }


            /*if (bLocalSession)
             * {
             *  session.CloseConnection();
             * }*/

            LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
            return(dr);
        }
예제 #3
0
        public string GetStringFromQuery(string cmdQuery)
        {
            string Function_Name = "GetStringFromQuery";

            LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
            LogHelper.Debug(CLASS_NAME, Function_Name, string.Format("Query = {0}", cmdQuery));

            DatabaseSession session       = m_sessionFactory.GetDatabaseSession();
            bool            bLocalSession = false;
            string          tempString    = "";

            try
            {
                if (session != null)
                {
                    if (!session.IsConnectionOpen())
                    {
                        session.OpenConnection();
                        bLocalSession = true;
                    }
                    IDbCommand cmd = session.CreateCommand();
                    cmd.CommandText = cmdQuery;
                    IDataReader drOracle = null;
                    drOracle = cmd.ExecuteReader();
                    while (drOracle.Read())
                    {
                        if (!drOracle.IsDBNull(0))
                        {
                            tempString += drOracle.GetString(0) + ",";
                        }
                    }
                }
            }
            catch (Exception localException)
            {
                LogHelper.Error(CLASS_NAME, Function_Name, localException.ToString());
            }
            finally
            {
                if (bLocalSession)
                {
                    session.CloseConnection();
                }
            }

            if (tempString.Trim() != "" && tempString.LastIndexOf(",") + 1 == tempString.Length)
            {
                tempString = tempString.Substring(0, tempString.Length - 1);
            }
            LogHelper.Trace(CLASS_NAME, Function_Name, string.Format("Function_Exited with value {0}", tempString));
            return(tempString);
        }
예제 #4
0
        public IDataReader ExecuteQueryWithSqls(SqlStatements queries)
        {
            string Function_Name = "ExecuteQuery";

            LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
            string          query;
            DatabaseSession session       = m_sessionFactory.GetDatabaseSession();
            bool            bLocalSession = false;
            IDataReader     dr            = null;

            if (session != null)
            {
                try
                {
                    if (!session.IsConnectionOpen())
                    {
                        session.OpenConnection();
                        bLocalSession = true;
                    }

                    IDbCommand cmd = session.CreateCommand();
                    if (session.GetDBType() == DBType.Oracle)
                    {
                        query = queries.OracleSql;
                    }
                    else
                    {
                        query = queries.MySqlStr;
                    }
                    LogHelper.Debug(CLASS_NAME, Function_Name, string.Format("Query = {0}", query));
                    cmd.CommandText = query;
                    dr = cmd.ExecuteReader();

                    // LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
                    // return dr;
                }
                catch (Exception localException)
                {
                    LogHelper.Error(CLASS_NAME, Function_Name, localException.ToString());
                    //todo
                    // throw exception
                }
            }

            /*if (bLocalSession)
             * {
             *  session.CloseConnection();
             * }*/
            LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
            return(dr);
        }
예제 #5
0
        public bool ExecuteNonQuery(string query)
        {
            string Function_Name = "ExecuteNonQuery";

            LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
            LogHelper.Debug(CLASS_NAME, Function_Name, string.Format("Query = {0}", query));
            // int connectionCounter = 0;
            bool            bLocalSession = false;
            DatabaseSession session       = m_sessionFactory.GetDatabaseSession();
            int             recordUpdated = 0;
            bool            boolUpdated   = true;

            try
            {
                if (session != null)
                {
                    if (!session.IsConnectionOpen())
                    {
                        session.OpenConnection();
                        bLocalSession = true;
                    }
                    IDbCommand cmd = session.CreateCommand();
                    cmd.CommandText = query;
                    recordUpdated   = cmd.ExecuteNonQuery();
                    boolUpdated     = false;
                }
            }
            catch (Exception localException)
            {
                LogHelper.Error(CLASS_NAME, Function_Name, localException.ToString());
            }
            if (bLocalSession)
            {
                session.CloseConnection();
            }

            LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");

//             if (recordUpdated <= 0)
//             {
//                 return false;
//             }

            return(!boolUpdated);
        }
예제 #6
0
        public IDataReader ExecuteQuery(string query)
        {
            string Function_Name = "ExecuteQuery";

            LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
            LogHelper.Debug(CLASS_NAME, Function_Name, string.Format("Query = {0}", query));
            DatabaseSession session = m_sessionFactory.GetDatabaseSession();

            IDataReader dr = null;

            if (session != null)
            {
                try
                {
                    if (!session.IsConnectionOpen())
                    {
                        session.OpenConnection();
                    }

                    IDbCommand cmd = session.CreateCommand();
                    cmd.CommandText = query;
                    dr = cmd.ExecuteReader();

                    // LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
                    // return dr;
                }
                catch (Exception localException)
                {
                    LogHelper.Error(CLASS_NAME, Function_Name, localException.ToString());
                    //todo
                    // throw exception
                }
            }

            /*if (bLocalSession)
             * {
             *  session.CloseConnection();
             * }*/
            LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
            return(dr);
        }
예제 #7
0
        public bool ExecuteEnqueneProcedure(List <SqlParameter> parameters)
        {
            string Function_Name = "ExecuteProcedure";

            LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");

            bool            bLocalSession = false;
            DatabaseSession session       = m_sessionFactory.GetDatabaseSession();
            bool            boolUpdated   = true;

            try
            {
                if (session != null)
                {
                    if (!session.IsConnectionOpen())
                    {
                        session.OpenConnection();
                        bLocalSession = true;
                    }
                    IDbCommand cmd = session.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    string procName;
                    if (session.GetDBType() == DBType.Oracle)
                    {
                        procName = DAOHelper.ORACLE_AUDIT_PROCEDURE_NAME;
                    }
                    else
                    {
                        procName = DAOHelper.MYSQL_AUDIT_PROCEDURE_NAME;
                    }

                    cmd.CommandText = procName;
                    LogHelper.Debug(CLASS_NAME, Function_Name, "SQL Parameters for Enquene procedure are : ");

                    foreach (var item in parameters)
                    {
                        IDbDataParameter parameter = cmd.CreateParameter();
                        parameter.ParameterName = item.ParameterName;
                        parameter.Direction     = item.Direction;
                        parameter.Value         = item.Value;
                        LogHelper.Debug(CLASS_NAME, Function_Name, String.Format("{0}\n", item.Value));
                        cmd.Parameters.Add(parameter);
                    }

                    LogHelper.Debug(CLASS_NAME, Function_Name, String.Format("Command Text: {0}", cmd.CommandText));

                    cmd.ExecuteNonQuery();
                    boolUpdated = false;
                }
            }
            catch (Exception localException)
            {
                LogHelper.Error(CLASS_NAME, Function_Name, localException.ToString());
            }
            if (bLocalSession)
            {
                session.CloseConnection();
            }

            LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");

            return(!boolUpdated);
        }