Thursday, May 1, 2014

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;
        }