Good Night!
Please I'm trying to do a Clr function wiht null values but I have an error. What am I doing wrong?
My Clr is like this:
using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Collections; using System.Text; public partial class UserDefinedFunctions { private class Function { public SqlInt16 First; public SqlString Second; public Function ( SqlInt16 first, SqlString second ) { First = first; Second = second; } } [SqlFunction ( DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read, FillRowMethodName = "Fill", TableDefinition = "[first] [SMALLINT] NULL," + "\r\n" + "[second] [NVARCHAR](4000) NULL" + "\r\n" ) ] public static IEnumerable function() { ArrayList resultSet = new ArrayList(); SqlCommand Command = new SqlCommand(); Command.CommandType = CommandType.Text; StringBuilder sb = new StringBuilder(); sb.AppendLine("SELECT NULL, N'A';"); Command.CommandText = sb.ToString(); using (SqlConnection conn = new SqlConnection("context connection=true")) { Command.Connection = conn; conn.Open(); using (SqlDataReader Read = Command.ExecuteReader()) { while (Read.Read()) { SqlInt16 first = Read.GetSqlInt16(0); SqlString second = Read.GetSqlString(1); resultSet.Add ( new Function ( first, second ) ); } } } return resultSet; } public static void Fill ( object functionObj, out SqlInt16 first, out SqlString second ) { Function function = (Function)functionObj; if (function.First.IsNull) { first = SqlInt16.Null; } else { first = function.First; } second = function.Second; } };
I implement and execute it it in Sql like this:
CREATE ASSEMBLY [function] FROM N'X:\Directory\function.dll' WITH PERMISSION_SET = SAFE GO CREATE FUNCTION [dbo].[function]() RETURNS TABLE ( [first] [SMALLINT] NULL, [second] [NVARCHAR](4000) NULL ) AS EXTERNAL NAME [function].[UserDefinedFunctions].[function] GO SELECT * FROM [dbo].[function]() GO
And I have the next error message:
Mens. 6522, Nivel 16, Estado 1, Lnea 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "function":
System.InvalidCastException: La conversin especificada no es vlida.
System.InvalidCastException:
en Microsoft.SqlServer.Server.ValueUtilsSmi.GetSqlInt16(SmiEventSink_Default sink, ITypedGettersV3 getters, Int32 ordinal, SmiMetaData metaData)
en UserDefinedFunctions.function()
May anybody tell me what I'm doing wrong?
Thanks a lot for your valuable help. I'm trying to learn .Net in Sql and those are my basic steps.
Regards
Ja