CLR stored procedure with a c# enumeration object as parameter

Question

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 5-Aug-14 8:27am

Santiago Fabian

Add a Solution

1 solution

Add a Solution

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

Sergey Alexandrovich Kryukov · Accepted Answer · 2014-08-05T09:11:00

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

CLR stored procedure with a c# enumeration object as parameter

1 solution

Solution 1

Add your solution here

Preview 0

CLR stored procedure with a c# enumeration object as parameter

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us