Shimon Krokhmal's blog

For those who didn't read the previews post about Sequence Implementation In SQL Server 2000 , please read it before continuing.

basically the idea is quite simple but i tough to add this post to complete the sequence framework.
adding some handling functions to your DAL object :

        /// <summary>
        /// this procedure retrieves from the database the value of the desired sequence.
        /// this is a generic function that gets the stored procedure to get the data from and the sequence name itself
        /// </summary>
        /// <param name="seqName">the name of the desired sequence</param>
        /// <param name="storedProcedure">the stored prosedure to call</param>
        /// <param name="connection">the connection to the database</param>
        /// <returns>an integer that represent the value of the sequence</returns>
        internal virtual int GetSequenceID(string seqName,string storedProcedure, SqlConnection connection)
        {
            int ret = int.MinValue;
            
            // Create a command
            SqlCommand command = new SqlCommand(storedProcedure, connection);    
            command.CommandType = CommandType.StoredProcedure;

            // pass the parameter of the sequence name for the series sequence
            SqlParameter p1 = new SqlParameter("@seqName",SqlDbType.VarChar);
            p1.Value = seqName;
            p1.Direction = ParameterDirection.Input;
            command.Parameters.Add(p1);        

            // open connection or using it if already opened
            bool OpenedConnection = false;
            try
            {
                if(connection.State!=System.Data.ConnectionState.Open)
                {
                    OpenedConnection = true;
                    connection.Open();
                }            
                
                // Execute the command
                ret = Convert.ToInt32(command.ExecuteScalar());
            }
            finally
            {
                if ((OpenedConnection) && (connection.State != System.Data.ConnectionState.Closed))
                    connection.Close();
            }
            
            return ret;
        }

        /// <summary>
        /// this is an internal function that retrieves the sequence value
        /// generally, it wraps the other internal method that does the actual work
        /// </summary>
        /// <param name="seqName">the name of the sequence that we want</param>
        /// <param name="storedProcedure">the stored procedure that we address for</param>
        /// <returns>the value of the sequence</returns>
        internal int GetSequenceID(string seqName,string storedProcedure)
        {
            int ret = int.MinValue;

            // Use the connection
            using(SqlConnection connection = DalServices.Connection())
            {
                try
                {
                    // Open the connection
                    connection.Open();
                    
                    // Get the object throught the internal method
                    ret = GetSequenceID(seqName,storedProcedure,connection);
                }
                
                finally
                {
                    // Close the connection
                    if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
                }
            }
            
            return ret;
        }

and add some wrapping functions to extern the methods:

        /// <summary>
        /// The Next value of the series sequence
        /// </summary>
        /// <returns>the value of the sequence</returns>
        public int GetNextSeries()
        {
            // Calling the internall function to get the value
            return GetSequenceID("sq_series","sp_SequenceNextVal");
        }

        /// <summary>
        /// The current value of the series sequence
        /// </summary>
        /// <returns>the value of the sequence</returns>
        public int GetCurrentSeries()
        {
            // Calling the internall function to get the value
            return GetSequenceID("sq_series","sp_SequenceCurrVal");
        }