Saving enum to Sql Server

[jhammer]

How do I save an Enum type in SqlServer?
Also, I know I can store an entire object in SqlServer 2005. Does someone know how?

[sameerteni]

Hello,

You can save Enum/ objec in Db using User defined type. Please refer following. I have defined CollectionsBE class and using it in SQL server as datatype.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Namespace MyProjects.CLR
<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, IsByteOrdered:=False, MaxByteSize:=8000)> _
Public Structure CollectionsBE
Implements INullable
Implements IBinarySerialize

Public Overrides Function ToString() As String
' Put your code here

Return Me.ToString
End Function

Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull
Get
' Put your code here
Return m_Null
End Get
End Property

Private Function ValidatePoint() As Boolean
Return True
End Function

Public Property Value() As String
Get
Return m_Value.ToString
End Get
Set(ByVal value As String)
m_Value = value
End Set
End Property

Public Property Description() As String
Get
Return m_Description.ToString
End Get
Set(ByVal value As String)
m_Description = value
End Set
End Property

Public Property Condition() As String
Get
Return m_Condition.ToString
End Get
Set(ByVal value As String)
m_Condition = value
End Set
End Property


Public Shared ReadOnly Property Null() As CollectionsBE
Get
Dim h As CollectionsBE = New CollectionsBE
h.m_Null = True
Return h
End Get
End Property

Public Shared Function Parse(ByVal s As SqlString) As CollectionsBE
If s.IsNull Then
Return Null()
End If

Dim u As CollectionsBE = New CollectionsBE
Dim sR() As String = Split(s.ToString, ";")
u.m_Value = sR(0).ToString
u.m_Description = sR(1).ToString()
u.m_Condition = IIf(sR(2).ToString() = "", "", sR(2)).ToString()
' Put your code here
Return u
End Function

' This is a place-holder method
Public Function Method1() As String
' Put your code here
Return "Hello - " + m_Value.ToString + " " + m_Description.ToString()
End Function

Private Sub Read(ByVal a As System.IO.BinaryReader) Implements IBinarySerialize.Read
m_Value = a.ReadString
m_Description = a.ReadString
m_Condition = a.ReadString
End Sub

Private Sub Write(ByVal b As System.IO.BinaryWriter) Implements IBinarySerialize.Write
b.Write(m_Value.ToString)
b.Write(m_Description.ToString)
b.Write(m_Condition.ToString)
End Sub

' Private member
Private m_Null As Boolean
Private m_Value As SqlString
Private m_Description As SqlString
Private m_Condition As SqlString
End Structure
End Namespace

Check the following SQL statements.

DECLARE @I_Collections CollectionsBE
SET @I_Collections = Convert(CollectionsBE, 'IND;SYSTEM;real')

INSERT INTO CUSTOMERS(IS_DELETED, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE, CUS_TYPE)
VALUES(0, @I_Collections.Description, GETDATE(), @I_Collections.Description, GETDATE(), @I_Collections.Value)

[jhammer]

Can I add the enum type in the management tool (graphically that is?)

[exterminator]

Is this what you are looking for - CREATE TYPE (http://msdn2.microsoft.com/en-us/library/ms175007.aspx)

One way to save into a varchar field would be serialization. You must be knowing more than me on that. The thing is you save whatever attributes your object has and that should define its valid state and you should be able to create an object with the same state reading that information in the db. Directly into the DB - may be there is a way but I am not aware of the new features of SQL Server 2005. However, take a look at this link: http://www.codeproject.com/vb/net/SqlDatabaseUtilities.asp

[Alsvha]

Otherwise....
I'd say the most easy method would simply be to cast your enum to and from its underlying type before storing it in the database.