Example #1
0
        public static SQLDBResult Execute(
            string procedureName,
            ArrayList procedureParameters, string strConnection)
        {
            SQLDBResult   result     = null;
            SqlConnection connection = null;

            try
            {
                // Get Connection and Procedure
                connection = new SqlConnection(strConnection);
                connection.Open();
                DbUtilProcedure procedure = Procedure(connection, procedureName, null);

                // Create Command
                SqlCommand command = procedure.Command(connection, procedureParameters);
                command.CommandTimeout = 0;
                result = new SQLDBResult();
                SqlDataAdapter adapter = new SqlDataAdapter(command);

                // Execute and Fill Results
                adapter.Fill(result.Contents);
                foreach (SqlParameter cparam in command.Parameters)
                {
                    switch (cparam.Direction)
                    {
                    case ParameterDirection.InputOutput:
                    case ParameterDirection.Output:
                    case ParameterDirection.ReturnValue:

                        result.Parameters[cparam.ParameterName] = cparam.Value;
                        break;

                    case ParameterDirection.Input:
                    default:

                        // do nothing
                        break;
                    }
                }
            }
            catch (System.Exception e)
            {
                throw new DbUtilException(e.Message, e);
            }
            finally
            {
                if ((connection != null) &&
                    (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }
            return(result);
        }
Example #2
0
        /// <summary>
        ///
        ///		Return the cached procedure.
        ///
        /// </summary>
        /// <param name="connection">
        ///
        ///		The connection to use to get the procedure detail
        ///
        /// </param>
        /// <param name="procedureName">
        ///
        ///		The procedure identifier.
        ///
        /// </param>
        /// <returns>
        ///
        ///		A procedure.
        ///
        /// </returns>

        private static DbUtilProcedure Procedure(
            SqlConnection connection,
            string procedureName, SqlTransaction oTransaction)
        {
            DbUtilProcedure procedure = null;

            if (!m_ProcedureCache.Contains(procedureName))
            {
                SqlDataReader reader = null;

                try
                {
                    m_ProcedureCacheLock.AcquireWriterLock(Timeout.Infinite);

                    if (!m_ProcedureCache.Contains(procedureName))
                    {
                        procedure = new DbUtilProcedure();

                        SqlCommand command = new SqlCommand("spUTL_GetProcedureMetaData", connection);
                        command.Parameters.AddWithValue("@objname", procedureName);
                        command.CommandType = CommandType.StoredProcedure;
                        command.Transaction = oTransaction;

                        reader = command.ExecuteReader();

                        procedure.ProcedureName = procedureName;

                        while (reader.Read())
                        {
                            DbUtilParameter parameter = new DbUtilParameter();

                            parameter.ParameterName   = reader.GetString(0);
                            parameter.ParameterLength = reader.GetInt32(2);
                            parameter.ParameterType   =
                                DbUtilParameter.StringToType(reader.GetString(1));
                            parameter.ParameterDirection =
                                DbUtilParameter.IntToDirection(reader.GetInt32(5));

                            procedure.ProcedureParameters.Add(parameter);
                        }
                        {
                            //Adding a Return Type as the SP can't return that value.
                            DbUtilParameter parameter = new DbUtilParameter();

                            parameter.ParameterName      = "@RETURN_VALUE";
                            parameter.ParameterLength    = 4;
                            parameter.ParameterType      = SqlDbType.Int;
                            parameter.ParameterDirection = ParameterDirection.ReturnValue;

                            procedure.ProcedureParameters.Add(parameter);
                        }

                        // Update Cache
                        m_ProcedureCache[procedureName] = procedure;
                    }
                }
                catch (System.Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    m_ProcedureCacheLock.ReleaseWriterLock();
                }
            }

            try
            {
                m_ProcedureCacheLock.AcquireReaderLock(Timeout.Infinite);
                procedure = (DbUtilProcedure)(m_ProcedureCache[procedureName]);
            }
            catch (System.Exception)
            {
                // do nothing
            }
            finally
            {
                m_ProcedureCacheLock.ReleaseReaderLock();
            }
            return(procedure);
        }
Example #3
0
        /// <summary>
        /// 
        ///		Return the cached procedure.
        ///		
        /// </summary>
        /// <param name="connection">
        /// 
        ///		The connection to use to get the procedure detail
        ///
        /// </param>
        /// <param name="procedureName">
        /// 
        ///		The procedure identifier.
        ///		
        /// </param>
        /// <returns>
        /// 
        ///		A procedure.
        ///		
        /// </returns>
        private static DbUtilProcedure Procedure(
            SqlConnection connection,
            string procedureName, SqlTransaction oTransaction)
        {
            DbUtilProcedure procedure = null;
            if (!m_ProcedureCache.Contains(procedureName))
            {

                SqlDataReader reader = null;

                try
                {

                    m_ProcedureCacheLock.AcquireWriterLock(Timeout.Infinite);

                    if (!m_ProcedureCache.Contains(procedureName))
                    {

                        procedure = new DbUtilProcedure();

                        SqlCommand command = new SqlCommand("spUTL_GetProcedureMetaData", connection);
                        command.Parameters.AddWithValue("@objname", procedureName);
                        command.CommandType = CommandType.StoredProcedure;
                        command.Transaction = oTransaction;

                        reader = command.ExecuteReader();

                        procedure.ProcedureName = procedureName;

                        while (reader.Read())
                        {

                            DbUtilParameter parameter = new DbUtilParameter();

                            parameter.ParameterName = reader.GetString(0);
                            parameter.ParameterLength = reader.GetInt32(2);
                            parameter.ParameterType =
                                DbUtilParameter.StringToType(reader.GetString(1));
                            parameter.ParameterDirection =
                                DbUtilParameter.IntToDirection(reader.GetInt32(5));

                            procedure.ProcedureParameters.Add(parameter);
                        }
                        {
                            //Adding a Return Type as the SP can't return that value.
                            DbUtilParameter parameter = new DbUtilParameter();

                            parameter.ParameterName = "@RETURN_VALUE";
                            parameter.ParameterLength = 4;
                            parameter.ParameterType = SqlDbType.Int;
                            parameter.ParameterDirection = ParameterDirection.ReturnValue;

                            procedure.ProcedureParameters.Add(parameter);
                        }

                        // Update Cache
                        m_ProcedureCache[procedureName] = procedure;
                    }
                }
                catch (System.Exception ex)
                {

                    throw ex;
                }
                finally
                {

                    if (reader != null)
                    {

                        reader.Close();
                    }
                    m_ProcedureCacheLock.ReleaseWriterLock();
                }
            }

            try
            {

                m_ProcedureCacheLock.AcquireReaderLock(Timeout.Infinite);
                procedure = (DbUtilProcedure)(m_ProcedureCache[procedureName]);
            }
            catch (System.Exception)
            {

                // do nothing
            }
            finally
            {

                m_ProcedureCacheLock.ReleaseReaderLock();
            }
            return procedure;
        }