Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-08-08 : 17:11:29
|
| Neil writes "It could be argued that this problem is more to do with poor structure design than SQL, but here's the problem...I have a number of C# objects that pull their data from SQL tables. For example I have a User object that defines First Name, Last Name, Email etc. The SQL database holds the data then the application populates the object with that data as required. All works fine (of course).In my C# code I use several enumerations, for example, for my User object I have an enumeration that defines the User profile (e.g. Anonymous, Member, SysAdmin). I hold the enum value as an integer in the SQL table and then cast it to the enum class when I load the data into the object - again all fine so far.Now, here's the problem: There are times when it would be useful to access the enumerated constants within my T-SQL procedures. The only way I can see of doing this is to hard code their integer values into the T-SQL script - this isn't great because maintenance/readability becomes an issue. I have also thought about creating a table of all enumeration values and then using a function to retieve the integer value associated with the enumeration name - again, it means maintaining the value in the C# code and the SQL table.Is there an elegant way I can 'provide' the Stored Procedure etc. with the (C#) enum name/value pairs?" |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-08-08 : 21:51:57
|
The domain values should be driven by the database, not the application code.The best would be to create a table for each of your enumerations,and create FK relationships from each of these domain tables.The application code would retrieve the information from the database;the numeric constant 1,2,3,,,and the name "Anonymous","Member","SysAdmin",,,You can create enumerations for the domains in the application code, that is ok, You can generate the enumerations in c# from the database (codegen).e.g. (unformatted example)select ' enum SysTypes{' as dunion all select char(9) + ',' + name from systypesunion all select '}'order by 1Using a code table for all the enumerations is also something that is sometimes done,It can help to retrieve the correct set of values for the client application,but You loose RI (Referential Integrity).And querying + maintenance the code table is more work.I don't recommend that approach.rockmoose |
 |
|
|
|
|
|
|
|
Defining Enumerations IN SQL Server