(SQL) SQL (2014 год)

Сommon template of table value SQL CLR Assembly.

Main point of SQL server is extensibility, opportunities of SQL Server is not restricted by four main command SELECT, INSERT, DELETE, UPDATE and not only more than 1000 additional function and operators Transact-SQL Reference for SQL 2000. Main benefits of MS SQL Server is extensebility, so existing function of SQL server can extend by any direction and it very often is used in practice. In my site I have some description of my own various SQL CLR Assembly, for example:


And below I show step-by-step new simple SQL CRL Assembly to call WMI in new reality VS2015. At common there are three type of SQL CLR function - Scalar-Valued, Table-Valued and User-Defined Aggregates, below I will create table value function. If you don't understand what is this, read first simple MS article CLR Table-Valued Functions and Mapping CLR Parameter Data.

So. in first step I show you common pattern how to create table value SQL CLR function in VS2015. Create as first step Database project.



Next step need to set right property of project - version of .NET framework and Languages. SQL Server 2005, 2008, and 2008 R2 require CLR version 2.0 which handles .NET Framework versions 2.0, 3.0, and 3.5, SQL Server 2012 and 2014 are require to CLR version 4.0, which handles .NET Framework versions 4.0, 4.5.x, 4.6.x, etc.

Also we set instead stupid language C# more comfortable, usable and human orientied language VB.NET and sign assembly.



After that need to add common project template. All templates of SQL CLR we may see in my page New SQL Server project templates for VS2015.



Afer that need to type first test of table value SQL CLR function:


   1:  CREATE FUNCTION [dbo].[ReadWmi]
   2:  (
   3:      @WmiNamespace Nvarchar(250),
   4:      @WmiClass Nvarchar(50)
   5:  )
   6:  RETURNS TABLE
   7:  (
   8:      Property Varchar(250),
   9:      Val Varchar(1000)
  10:  )
  11:  AS   
  12:  EXTERNAL NAME  WmiAsm.[Wmi].GetWmiInfo

   1:  Imports System
   2:  Imports System.Data
   3:  Imports System.Data.SqlClient
   4:  Imports System.Data.SqlTypes
   5:  Imports Microsoft.SqlServer.Server
   6:  Partial Public Class Wmi
   7:   
   8:      <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read,
   9:                                              Name:="GetWmiInfo",
  10:                                              FillRowMethodName:="NextRow",
  11:                                              TableDefinition:="Prop Nvarchar(250), Val Nvarchar(1000)")>
  12:      Public Shared Function GetWmiInfo(WmiNamespace As SqlString, WmiClass As SqlString) As Collections.IEnumerable
  13:          Dim X As New Data.DataTable
  14:          X.Columns.Add("Prop", GetType(SqlString))
  15:          X.Columns.Add("Val", GetType(SqlString))
  16:          X.Rows.Add("A", "B")
  17:          X.Rows.Add("C", "D")
  18:          Return X.Rows
  19:      End Function
  20:   
  21:      Public Shared Sub NextRow(Obj As Object,
  22:                               <Runtime.InteropServices.Out()> ByRef Prop As SqlString,
  23:                               <Runtime.InteropServices.Out()> ByRef Val As SqlString)
  24:          Try
  25:              Dim X As Data.DataRow = TryCast(Obj, DataRow)
  26:              Prop = X("Prop").ToString
  27:              Val = X("Val").ToString
  28:          Catch ex As Exception
  29:              Prop = "Error"
  30:              Val = ex.Message
  31:          End Try
  32:      End Sub
  33:  End Class

This is my common workable pattern for table value SQL CLR function.



Next step need to prepare SQL Server to permit it working with assembly extension.


   1:  EXEC sp_configure 'clr enabled', 1
   2:  go
   3:  RECONFIGURE
   4:  go
   5:  EXEC sp_configure 'clr enabled'
   6:  go
   7:  ALTER DATABASE [tst1] SET TRUSTWORTHY ON
   8:  go

In deploy assembly has a trick, because VS2015 not can deploy Assembly automatically, it generates more unexpected and unresolved error. We are leave VS2015 with its problem and going directly to MS SQL Server. In obj\Debug folder there VS2015 generating deploy script. We are take up it and execute it directly in SQL Server.

In this test deploy script is showing as below, we will execute it directly in SQL.


   1:  --------------------------------------------------------------------------------
   2:  --     This code was generated by a tool.
   3:  --
   4:  --     Changes to this file may cause incorrect behavior and will be lost if
   5:  --     the code is regenerated.
   6:  --------------------------------------------------------------------------------
   7:  CREATE FUNCTION [dbo].[GetWmiInfo] (@WmiNamespace [nvarchar](MAX), @WmiClass [nvarchar](MAX))
   8:  RETURNS TABLE (Prop Nvarchar(250), Val Nvarchar(1000))
   9:  AS EXTERNAL NAME [WmiAsm].[WmiAsm.Wmi].[GetWmiInfo];
  10:   
  11:  GO
  12:   
  13:  CREATE ASSEMBLY [WmiAsm] AUTHORIZATION [dbo]
  14:  FROM 0x4D5A90000300000004000000FFFF0000B8......0000000
  15:  WITH PERMISSION_SET = UNSAFE
  16:   
  17:  GO
  18:   
  19:  ALTER ASSEMBLY [WmiAsm]
  20:  ADD FILE FROM 0x4D6963726F736F667420432F4......0000000
  21:  AS N'WmiAsm.pdb'
  22:   
  23:  GO

Great, this prototype of real function is working.






Comments ( )
Link to this page: //www.vb-net.com/TableValueSQLCLR/index.htm
< THANKS ME>