Coding: Creating a Counter (MS SQL, C#) Samples

I probably create one of these for every one of my larger projects. I always have a need to track a counter of some sort.

My Standard Counter Table

USE [dbName]
GO

/****** Object:  Table [dbo].[__Counters]    Script Date: 05/01/2014 23:45:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[__Counters](
[CounterName] [varchar](50) NOT NULL,
[NextId] [int] NOT NULL,
[Notes] [varchar](max) NULL,
 CONSTRAINT [PK___Counters] PRIMARY KEY CLUSTERED
(
[CounterName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

My Standard Stored Procedure

USE [dbName]

GO

/****** Object:  StoredProcedure [dbo].[GetCounter]    Script Date: 05/01/2014 23:45:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Douglas Lee

-- Create date: 5/1/2014

-- Description: Get next id and update for next use

-- =============================================

CREATE PROCEDURE [dbo].[GetCounter]

-- Add the parameters for the stored procedure here

@CounterName varchar(50),

@NextId int OUTPUT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

    -- Insert statements for procedure here

--DECLARE @NextId int;

SET @NextId = 1;

SELECT @NextId = NextId FROM __Counters WHERE CounterName = @CounterName;

Update __Counters SET NextId = NextId + 1 WHERE CounterName = @CounterName;

RETURN

END

GO

My Standard C# Code

        public static int GetCounter(string CounterName)

        {

            SqlConnection conn = null;

            //SqlDataReader rdr = null;

            conn = new SqlConnection(c);

            conn.Open();

            // 1.  create a command object identifying the stored procedure

            SqlCommand cmd = new SqlCommand("GetCounter", conn);

            // 2. set the command object so it knows to execute a stored procedure

            cmd.CommandType = CommandType.StoredProcedure;

            // 3. add parameter to command, which will be passed to the stored procedure

            SqlParameter output = new SqlParameter("@NextId", SqlDbType.Int);

            output.Direction = ParameterDirection.Output;

            cmd.Parameters.Add(new SqlParameter("@CounterName", CounterName));

            cmd.Parameters.Add(output);

            cmd.ExecuteNonQuery();

            int retInt = (int)cmd.Parameters["@NextId"].Value;

            conn.Close();

            

            return retInt;

        }