Simple SQL proc to generate an enum class for a lookup table

by Heathesh 8. February 2012 01:40

Here's the proc I created and below is the explanation of how I went about doing this: http://heathesh.com/code/sql/GenerateEnumClass.txt

I'm sure every c# developer has come across a situation where they have a lookup table that they'd like to convert into an enum in code. I've actually had to do it a few times myself and each time I use some sort of cursor and t-sql to create the enum class code. So I figured why not create a procedure to do this that I could re-use?

To begin with, there's a few parameters I needed to pass into the procedure. These were:

  1. The table name
  2. The namespace I wanted of the code
  3. The DataContract namespace. Because most of the stuff I do now is WCF this was a necessity but you can obviously remove this if you want.
  4. The id field on the lookup table.
  5. The value field on the lookup table.

I've attached the proc for you to copy (http://heathesh.com/code/sql/GenerateEnumClass.txt). The basics of how it works is that I insert the id and value fields into a temporary table using sp_executesql, then loop through the values using a cursor and simply use print statements to output the relevant code.

To use the proc I created a simple table called Title with an Id and a Name column like so:

CREATE TABLE [dbo].[Title](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Title] PRIMARY KEY CLUSTERED 
(
[Id] 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

I then populated the table with values like so:

INSERT INTO [Title] ([Name]) VALUES ('Mr')
INSERT INTO [Title] ([Name]) VALUES ('Mrs')
INSERT INTO [Title] ([Name]) VALUES ('Miss')
INSERT INTO [Title] ([Name]) VALUES ('Dr')
INSERT INTO [Title] ([Name]) VALUES ('Prof')
INSERT INTO [Title] ([Name]) VALUES ('Sir')
INSERT INTO [Title] ([Name]) VALUES ('Madam')
INSERT INTO [Title] ([Name]) VALUES ('Mr And Mrs')

You'll notice one of the values has spaces in it. Since enum names cannot have spaces in it when you run the proc you'll notice in the output that it adds the Description attribute with the full name of the row, this is so you can still get the value of the string. If you need help with this check out a previous post of mine on reading the description attribute: Reading the DescriptionAttribute of enumerators ...

Then I simply ran my proc like so:

 [GenerateEnumClass] 
'Title', -- this is the name of the lookup table
'Heathesh.Com.Entity', -- this is the namespace I wanted to appear in the code
'http://heathesh.com/entities', -- this is the data contract namespace I wanted to appear in the code
'Id', -- this is the id field of the table
'Name' -- this is the name field of the table

And this produced my required output like so:

using System;
using System.Runtime.Serialization;
using System.ComponentModel;
 
namespace Heathesh.Com.Entity
{
/// <summary>
/// Title enum
/// </summary>
[Serializable]
[DataContract(Name = "Title", Namespace = "http://heathesh.com/entities")]
public enum Title
{
[EnumMember]
Mr = 1,
[EnumMember]
Mrs = 2,
[EnumMember]
Miss = 3,
[EnumMember]
Dr = 4,
[EnumMember]
Prof = 5,
[EnumMember]
Sir = 6,
[EnumMember]
Madam = 7,
[Description("Mr And Mrs")]
[EnumMember]
MrAndMrs = 8,
}
}

I simply then added a "Title.cs" class to my project, replaced all the code in the class with what was generated and that was it. An enum class generated using sql from a lookup table.

Please note: Don't forget to add a reference to "System.Runtime.Serialization" in your project for the WCF stuff (DataContract, DataMember, EnumMember etc.)

Happy generating!

 

 

Tags: , , ,

Development | .Net | SQL Server 2008

Kill all sql connections to a specific database

by Heathesh 18. March 2010 01:21

I wanted to figure out how (if possible) I could possibly kill all connections to a specific database and then take it offline. So what I did was create the following SQL script:

First I created a temporary table to use for storing the current connection info. The columns indicated in this table are the columns returned by the sp_who2 command.

CREATE TABLE #temp
(
    SPID1 int,
    [Status] varchar(500),
    [Login] varchar(500),
    HostName varchar(500),
    BlkBy varchar(500),
    DBName varchar(500),
    Command varchar(500),
    CPUTime int,
    DiskIO int,
    LastBatch varchar(500),
    ProgramName varchar(500),
    SPID2 int,
    REQURESTID int
)


Next I wanted to populate the #temp table I'd just created with the relevant connection data, so I ran:

INSERT INTO #temp
EXEC sp_who2


You can now select from the #temp table and you will see all the connection info at the time of the INSERT command above. So now to kill all the connections to a particular database, I created and ran the following script:

DECLARE
    @SPID int,
    @KillCmd varchar(150)

--change the My_Database_Name to your database name
DECLARE myCursor CURSOR FOR
SELECT SPID1 FROM #temp
WHERE DBName = 'My_Database_Name'

OPEN myCursor

FETCH NEXT FROM myCursor INTO @SPID

WHILE @@fetch_status <> -1
BEGIN
    SET @KillCmd = 'kill ' + CONVERT(varchar(50), @SPID)
    EXEC (@KillCmd)
    FETCH NEXT FROM myCursor INTO @SPID
END

CLOSE myCursor
DEALLOCATE myCursor


You will notice that I select the first SPID from the #temp table for my particular database. All you have to do is insert your particular database name in that portion of the script, run it and it will kill all the connections to that database. I have however notcied that some connections take a little time to die, not sure why this is, but I definitely found this script very useful.

Happy scripting!

Change the default path for database creation in SQL Server

by Heathesh 18. November 2009 20:58

Open SQL Server management studio, and right click on the instance name and select "Properties". In the properties window, select the "Database settings" page and you will see "Database default locations" on the window displayed. Change the folders, click "Ok".

It's that easy...

Tags: , , ,

SQL Server 2005 | SQL Server 2008



Powered by BlogEngine.NET 1.5.0.7 (with enhancements by Heathesh)
Theme by Mads Kristensen (with tweeks by Heathesh)

Certifications

Microsoft Certified Professional

Microsoft Certified Technology Specialist

Answer Questions

 

Tag cloud

Calendar

<<  July 2017  >>
MoTuWeThFrSaSu
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456

View posts in large calendar

http://heathesh.com