Click here to Skip to main content
15,065,783 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to know if its possible to create a CRL stored procedure in C# with a parameter of enum type?

If so, what value should I pass to this parameter once I execute it in sql server management studio?

My c# code would be something like this to give you an idea:
C#
enum Processtype 
{
  Employees = 1,
  TimeEntries = 2,
  AccrualBalances = 3
}

[Microsoft.SqlServer.Server.SqlProcedure]
public static void mySP(Processtype pt, int companyName)
{
     //All my code here
}


in SQL, what value should I pass to my "mySP" stored procedure enum parameter?

the SQL code is this:
SQL
EXEC mySP "What value goes here?", "mycompanyName"
Posted

1 solution

Unfortunately, "stored procedure" is never CLR. By definition, this is a SQL procedure. The code being executed in CLR can only create the code of the SP, trigger its execution, pick up the results of its execution from the SQL server.

And SQL does not have general enumeration types. (Some SQL-based systems do have it, such as MySQL.) Please see, for example:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c7abe3c1-cd48-4ddf-9ce2-b28d0ffb25f6/enum-in-sql-server?forum=transactsql[^],
http://stackoverflow.com/questions/1434298/sql-server-equivalent-to-mysql-enum-data-type[^].

(The answers referenced above suggest some work-around approach similar to SQL, but it will hardly help you much.)

So, at best, you probably can represent your .NET enumeration types with some SQL integer type, to store your 1, 2, 3 values. Working with your .NET enumeration types will require type casting.

Another approach would be modeling some enumeration types in SQL. It will have two tables: one table for types, another table for enumeration members (for all types together; each enumeration member record will be linked to its enumeration type by a foreign key). For each enumeration member, you can store its underlying integer type, name (string) and, optionally, ordered number. But how to identify .NET enumeration type and member in those tables? By name. [EDIT] On the .NET side, you can get all required information using reflection. [END EDIT] Some performance cost, no doubt, albeit very minor compared to the cost of doing SQL queries themselves.

I am not sure that both approaches are good enough to bother. In both, the problem is support, the need to change .NET types and SQL data in synch. Essentially, you have to store not only the application data on SQL tables, but also part of its metadata, which breaks certain isolation between the database and client code. Not sure if there can be something better. Maybe you should better review the whole idea…

—SA
   
v2
Comments
Santiago Fabian 6-Aug-14 10:08am
   
Thanks for taking your time to give me a response. None of those approaches fit my needs so as you said I reviewed my idea in order to change it. Basically in the the SQL procedure(mySP), I will use an .Net Int(representing the enum value) as parameter instead of a enum object, then created a .NET function with the enum parameter and Cast the int value as you mentioned with Enum.GetName(ProcessType,IntValue) an pass this result to my new .NET function
   
Well, this is because the SQL-based technology has intrinsic problems collectively called the problems of "mapping code". I expected that my suggestions could be considered as not worth implementing (they both can work, but won't really improve the application design) and outlined their problems myself. Hope it can just serve as some food for thought.

Thank you.
—SA
Santiago Fabian 6-Aug-14 10:27am
   
Thanks Sergey, I'll take into account your suggestions.
   
You are very welcome.
Good luck, call again.
—SA

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900