Word up, I as well got tired of writing out enumerations based on Id / Name db table columns, copying and pasting stuff from queries in SSMS.
Below is a super dirty stored procedure that takes as input a table name, the column name you want to use for the c# enumeration name, and the column name that you want to use for the c# enumeration value.
Most of theses table names I work with a) end with "s" b) have a [TABLENAME]Id column and c) have a [TABLENAME]Name column, so there are a couple if statements that will assume that structure, in which case, the column name parameters are not required.
A little context for these examples - "Stonk" here doesn't really mean "stock" but kinda, the way I'm using "stonk" it means "a thing that has some numbers associated to it for a time period" But that's not important, it's just an example of table with this Id / Name schema. It looks like this:
CREATE TABLE StonkTypes (
StonkTypeId TINYINT IDENTITY(1,1) PRIMARY KEY NOT NULL,
StonkTypeName VARCHAR(200) NOT NULL CONSTRAINT UQ_StonkTypes_StonkTypeName UNIQUE (StonkTypeName)
)
After I create the proc, this statement:
EXEC CreateCSharpEnum 'StonkTypes'
Selects this string:
public enum StonkTypes { Stonk = 1, Bond = 2, Index = 3, Fund = 4, Commodity = 5,
PutCallRatio = 6, }
Which I can copy and paste into a C# file.
I have a Stonks table and it has StonkId and StonkName columns so this exec:
EXEC CreateCSharpEnum 'Stonks'
Spits out:
public enum Stonks { SP500 = 1, DowJonesIndustrialAverage = 2, ..... }
But for that enum I want to use the "Symbol" column for the enum name values so this:
EXEC CreateCSharpEnum 'Stonks', 'Symbol'
Does the trick and renders:
public enum Stonks { SPY = 1, DIA = 2, ..... }
Without further ado, here is this dirty piece of craziness. Yeah, very dirty, but I'm kind of pleased with myself - it's SQL code that constructs SQL code that constructs C# code. Couple layers involved.
CREATE OR ALTER PROCEDURE CreateCSharpEnum
@TableName VARCHAR(MAX),
@EnumNameColumnName VARCHAR(MAX) = NULL,
@EnumValueColumnName VARCHAR(MAX) = NULL
AS
DECLARE @LastCharOfTableName VARCHAR(1)
SELECT @LastCharOfTableName = RIGHT(@TableName, 1)
PRINT 'Last char = [' + @LastCharOfTableName + ']'
DECLARE @TableNameWithoutS VARCHAR(MAX)
IF UPPER(@LastCharOfTableName) = 'S'
SET @TableNameWithoutS = LEFT(@TableName, LEN(@TableName) - 1)
ELSE
SET @TableNameWithoutS = @TableName
PRINT 'Table name without trailing s = [' + @TableNameWithoutS + ']'
IF @EnumNameColumnName IS NULL
BEGIN
SET @EnumNameColumnName = @TableNameWithoutS + 'Name'
END
PRINT 'name col name = [' + @EnumNameColumnName + ']'
IF @EnumValueColumnName IS NULL
SET @EnumValueColumnName = @TableNameWithoutS + 'Id'
PRINT 'value col name = [' + @EnumValueColumnName + ']'
-- replace spaces and punctuation
SET @EnumNameColumnName = 'REPLACE(' + @EnumNameColumnName + ', '' '', '''')'
SET @EnumNameColumnName = 'REPLACE(' + @EnumNameColumnName + ', ''&'', '''')'
SET @EnumNameColumnName = 'REPLACE(' + @EnumNameColumnName + ', ''.'', '''')'
SET @EnumNameColumnName = 'REPLACE(' + @EnumNameColumnName + ', ''('', '''')'
SET @EnumNameColumnName = 'REPLACE(' + @EnumNameColumnName + ', '')'', '''')'
PRINT 'name col name with replace sql = [' + @EnumNameColumnName + ']'
DECLARE @SqlStr VARCHAR(MAX) = 'SELECT ' + @EnumNameColumnName
+ ' + '' = '''
+ ' + LTRIM(RTRIM(STR(' + @EnumValueColumnName + '))) + '','' FROM ' + @TableName + ' ORDER BY ' + @EnumValueColumnName
PRINT 'sql that gets rows for enum body = [' + @SqlStr + ']'
CREATE TABLE #EnumRowsTemp (s VARCHAR(MAX))
INSERT
INTO #EnumRowsTemp
EXEC(@SqlStr)
--SELECT * FROM #EnumRowsTemp
DECLARE @csharpenumbody VARCHAR(MAX)
SELECT @csharpenumbody = COALESCE(@csharpenumbody + ' ', '') + s FROM #EnumRowsTemp
--PRINT @csharpenumbody
DECLARE @csharpenum VARCHAR(MAX) = 'public enum ' + @TableName + ' { ' + @csharpenumbody + ' }'
PRINT @csharpenum
SELECT @csharpenum
DROP TABLE #EnumRowsTemp
Please, be critical. One funky thing I didn't understand, how come I have to create and drop this #EnumRowsTemp table and not just "SELECT INTO #EnumRowsTemp" to create the temp table on the fly? I don't know the answer, I tried that and it didn't work. That's probably the least of the problems of this code...
As dirty as it may be... I hope this saves some of you fellow dorks a little bit of time.