在 SQL Server 2005 中创建用户定义数据类型

引言

Microsoft SQL Server 2005 支持 Microsoft .NET Framework 2.0 CLR (Common Language Runtime) 集成，这意味着可以在 MS SQL Server 2005 中使用 .NET (托管代码) 进行编码。您可以使用 .NET 语言编写存储过程、函数、数据类型和触发器（我将以 C# 为例进行说明）。是的，您也可以使用非托管代码做到这一点，但我不会在这里介绍 :) - 我假设您已经熟悉 XPs (扩展存储过程)。我认为两者各有利弊。在这里，我将介绍如何使用 C# 创建用户定义数据类型。

让我们开始吧！

• 步骤 1 - 编写一些托管代码（包含方法、属性等的类集合），并将其编译成程序集。
• 步骤 2 - 将程序集加载到 SQL Server 数据库中，并注册程序集和数据类型。
• 步骤 3 - 无事可做，只需使用您的代码。

假设我们要为项目使用和存储复数，步骤很简单

当您需要一个复数字段时，只需创建两个列 x 和 y，并以 x + yi 的格式使用/存储它们。但如果您想以正常方式使用它们呢？只需插入 x + iy 并获取 x + iy？SQL Server 有解决方案。首先，我们将使用简单的方法。

1. 打开 Visual Studio，创建一个新项目。在 C# 下，选择数据库，然后从可用模板（通常只有一个模板）中，选择“SQL Server Project”，然后输入一个名称或接受默认名称。
2. 选择一个数据库或创建一个新连接，或者稍后处理（获取项目属性并选择数据库选项卡）。
3. 从项目菜单中，选择“添加用户定义类型”（或在解决方案资源管理器中右键单击项目，然后指向添加），并将其命名为“ComplexNumber”或其他您想要的名称 :)
4. 大部分工作已完成。现在已创建 Null 值以及 ToString() 和 Parse() 方法。

   让我们来看看

   [Serializable] 
   [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)] 
   public struct ComplexNumber : INullable 
   {
       //....
   }

   它应该是一个公共类或结构体，实现了 INullable 并具有 [SqlUserDefinedType] 属性。Null 值将这样定义：您的类型必须有一个 null 值，因此，请根据数据类型选择一个 null 值；例如，复数的合适 null 值可以是 (0, 0)。

   public bool IsNull 
   { 
       get { return m_Null; } 
   }
   public static ComplexNumber Null 
   { 
       get 
       {
           ComplexNumber h = new ComplexNumber();
           h.m_Null = true; return h;
       }
   }

   当您想显示字段值时，将调用 ToString() 方法，而 Parse() 方法接受值并使其可供使用。

   public static ComplexNumber Parse(SqlString s) 
   {
      if (s.IsNull) return Null;
      ComplexNumber u = new ComplexNumber(); 
      // Put your code here
      return u;  
   }
   public override string ToString() 
   {
       // Replace the following code with your code 
       return ""; 
   }

5. 删除 Method1()、Method2() 和 var1，然后编写复数代码。我们将保持尽可能简单。

   首先，成员

6. 有一个名为 m_Null 的 private bool，保留它并键入以下内容

   private double _x; //real part
   private double _y; //imaginary part

   属性

   public double RealPart
   {
       get { return _x; }
       set { _x = value; }
   }
       
   public double ImaginaryPart
   {
       get { return _y; }
       set { _y = value; }
   }

   构造函数

   public ComplexNumber(double x,double y)
   {
      _x = x;
      _y = y;
       m_Null = false;
   }

   结构体的构造函数不接受空参数，因此我使用了以下内容作为 null 构造函数

   public ComplexNumber(bool nothing)
   {   
       this._x = this._y = 0;
       this.m_Null = true;
   }

   现在，我们将处理 ToString() 方法

   public override string ToString()
   {
       return _x.ToString() + "+" + _y.ToString() + "i";
   }

   接下来是 Parse 方法。解析代码很糟糕，因为它不处理无效输入等。正则表达式会在这里做得很好，但为了简单起见，我在这里不涵盖它。

   public static ComplexNumber Parse(SqlString s)
   {
      string value = s.Value;
      if (s.IsNull || value.Trim()=="")
          return Null;
      string xstr = value.Substring(0, value.IndexOf('+'));
      string ystr = value.Substring(value.IndexOf('+') + 1, 
                    value.Length - xstr.Length - 2);
      double xx = double.Parse(xstr);
      double yy = double.Parse(ystr);
      return new ComplexNumber(xx,yy);
   }

   最后，一个执行某项操作的方法

   public static ComplexNumber Add(ComplexNumber c1,ComplexNumber c2)
   {
       return new ComplexNumber(c1._x + c2._x, c1._y + c2._y);
   }

7. 现在，您已准备好测试您的工作。生成项目，然后从生成菜单中选择部署。完成。

   注意：CLR 集成功能在 Microsoft SQL Server 中默认是关闭的，必须使用以下命令启用

   sp_configure 'clr enabled', 1
   GO
   RECONFIGURE
   GO

   在查询窗口中执行上述代码。

8. 打开“SQL Server Management Studio”，展开您的数据库（我使用的是 AdventureWorks），然后选择可编程性 > 类型 > 用户定义类型。您的数据类型应该在那里，如果没有，请刷新列表。
9. 添加一个新表并命名为“test”或其他名称，并在表中添加一个列并命名为“complexField”（再次，随便起个名字）。
10. 保存后，右键单击表并选择“打开表”，然后输入一个复数，例如“253.85+10i”。现在，我们通过查询来完成。

    在“SQL Server Management Studio”中，打开一个新的查询窗口（Ctrl+N）并键入以下内容。请确保选择了正确的数据库（您的表名和字段名可能与我的不同）。

    --insert command & etc
    insert into test (complexField) values('25+52i')

11. 在查询窗口中使用类型的该方法

    -- using add method of object
    insert into test values(ComplexNumber::[Add]( 
           convert(complexnumber,'12+25i' ),'25+12i'))

    您已经完成了。

我告诉过您，这是简单的方法，对吧？好的，让我们尝试另一种方法

1. 我们需要的是一个具有 [SqlUserDefinedType] 属性并实现 INullable 的公共类或结构体。现在，用您想要的任何方式编译它，例如，使用 csc 命令。
2. 现在，我们必须像这样在 SQL Server 中注册我们的 DLL（程序集）

     CREATE ASSEMBLY ComplexNumber
     FROM '\\PersianGulf\Hamed\complexNumber.dll'  --an example path
     WITH PERMISSION_SET = SAFE;

   哎呀……我忘了权限。权限有三种类型

   • Safe：计算、访问本地数据
   • External Access：文件、注册表、网络
   • Unsafe：完全信任、非托管代码、验证

   另外一点是，您可以使用文件中的字节流（而不是像前面的代码那样使用路径）或 varbinary 变量。

3. 下一步是像这样创建类型

   CREATE TYPE dbo.ComplexNumber 
   EXTERNAL NAME ComplexNumber .[ComplexNumber ];

   EXTERNAL NAME 使用 AssemblyName.UDTName 的两部分命名语法指定。

   注意：DROP 适用于类型和程序集，您可以 ALTER 您的类型的程序集或对其进行更改。

   DROP TYPE dbo.ComplexNumber ;
   DROP ASSEMBLY ComplexNumber ;
   
   ALTER ASSEMBLY ComplexNumber 
   FROM '\\PersianGulf\Hamed\complexNumber.dll'
   
   ALTER ASSEMBLY ComplexNumber 
   ADD FILE FROM '\\PersianGulf\Hamed\complexNumber.cs' AS something;

   下一步就是使用它

   CREATE TABLE test
   complexField ComplexNumber)
   
   /*....and so on...*/

   您将在 MSDN 中找到关于此主题的出色代码示例，甚至还有一个复数示例。

历史

• 第一个版本 - 在高峰时段完成。

参考文献

• 强大而伟大的 MSDN。
• 一个 PPT 文件：SQL Server 2005 您需要了解什么？- Randy Holloway - Microsoft Corporation。
• 一个 PPT 文件：SQL Server 2005 CLR 集成 - Matthew Roche