使用c＃枚举对象作为参数的CLR存储过程 [英] CLR stored procedure with a c# enumeration object as parameter

问题描述

我想知道是否可以在C＃中使用参数 enum 类型创建CRL存储过程？



如果是这样，一旦我在sql server management studio中执行它，我应该将什么值传递给此参数？



我的c＃代码将是这样的给你一个想法：

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:

enum Processtype 
{
  Employees = 1,
  TimeEntries = 2,
  AccrualBalances = 3
}

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

在SQL中，有什么价值我应该传递给我的mySP存储过程枚举参数吗？



SQL代码是这样的：

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

the SQL code is this:

EXEC mySP "What value goes here?", "mycompanyName"

推荐答案

不幸的是，存储过程永远不是CLR。根据定义，这是一个SQL过程。在CLR中执行的代码只能创建SP的代码，触发其执行，从SQL服务器获取其执行结果。



和SQL一样没有一般的枚举类型。 （一些基于SQL的系统确实拥有它，例如 MySQL 。）请参阅，例如：

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 [ ^ ]。



（答案上面引用的rs提出了一些类似于SQL的解决方法，但它对你来说几乎没什么帮助。）



所以，充其量，你可能代表你的.NET具有一些SQL整数类型的枚举类型，用于存储1,2,3个值。使用.NET枚举类型将需要类型转换。



另一种方法是在SQL中为一些枚举类型建模。它将有两个表：一个用于类型的表，另一个用于枚举成员的表（用于所有类型;每个枚举成员记录将通过外键链接到其枚举类型）。对于每个枚举成员，您可以存储其基础整数类型，名称（字符串）以及可选的有序数字。但是如何在这些表中识别.NET枚举类型和成员？按名字。 在.NET端，您可以使用反射获取所有必需的信息。 [结束编辑] 一些性能成本，毫无疑问，虽然与自己进行SQL查询的成本相比非常小。



我不是确定这两种方法都足以让人烦恼。在这两者中，问题是支持，需要同步更改.NET类型和SQL数据。实质上，您不仅要存储SQL表上的应用程序数据，还要存储其元数据的一部分，这会破坏数据库和客户端代码之间的某些隔离。不确定是否可以有更好的东西。也许你应该更好地回顾整个想法...

-SA

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. 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

这篇关于使用c＃枚举对象作为参数的CLR存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！