CODEWITHSUNDEEP
c#sqlsql-servert-sqlenums
Pawan Nogariya
Pawan Nogariya

Reputation: 9000

Exposing C# enums as views in database

Say if I have a column in database which can accept any one value of the enum that is defined in my C# code.

There is a usual way to make it be available to the database to store it in a database table and refer it from there.

But sometimes you don't want to store the enum in the database but just want to maintain it in the code, for these cases I have come up with the solution in my project to have a view which will return the values of enums so that we do not have to hard code it in the database, so having something like this in the database 

CREATE VIEW ENUM.ValidationFailReasonTypes
AS
SELECT 1 AS [FormLevel], 2 AS [GridLevel]

For enum 

public enum ValidationFailReasonTypes
{
    FormLevel = 1,
    GridLevel = 2
}

So just wanted to ask is it a good idea to do like this or is there some better way to handle this case when we don't want to store enum in the database?

Upvotes: 1

Views: 225

Answers (2)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

The entire reason for an enum is consistency, clarity, and ease of use when coding. I would think you would want that sort of clarity and consistency on the database side as well. If you persist an enum value to the database, you absolutely should have referential integrity on the column and a corresponding definition table. The view saves you nothing in my opinion.

Create a table and add referential integrity to the field even if it only has two rows for all eternity.

Upvotes: 0

Neville Kuyt
Neville Kuyt

Reputation: 29649

It all depends.

Old school database folk like me want you to build validity checks into your data model. See this question for how you would do that. You end up with a nice, self-documenting schema, which guarantees that your application can only contain valid entries for ValidationFailReasonTypes.

An alternative view is that the database is just the storage mechanism, and it's perfectly OK for the application to be the guarantor of data validity, especially if it has lots of unit tests. In this case, you use an ENUM in you C#, write unit tests to validate that the entries match your expectations, and accept the fact your database table has an integer column which could contain lots of values, but the application limits it to (1, 2).

I think it's important to pick one approach and stick to it. Your solution - if I understand it correctly - is a bit of both.

In general, I'd use an enum for values that are not going to change without a new release of your application, usually because you're enabling some new functionality.

I'd expect to use a database entry for something that can change in the business domain independently of your application's release. "Grid level" and "form level" feel like features in your application, so I think an enum is okay.

Upvotes: 3

Related Questions