Function with parameter and layer MssqlFeatureLayer

Steph · March 22, 2016, 7:36am

Hi,

I have a question about function with parameter and a layer MssqlFeatureLayer.
I created a function Analyse.fn_Analyse2 with one parameter which is int type.

And I created a function Analyse.fn_Analyse1 with one parameter which is a list type.

fn_Analyse2 :
ALTER FUNCTION [Analyse].[fn_Analyse2]
(
@OccupationSolIds  INT
)
RETURNS TABLE
AS
RETURN
SELECT …

In c# code, I call the function like that :
mssql.TableName = “(SELECT * FROM analyse.fn_Analyse2(” + i + “)) AS TOTO” and i is the parameter
This works.

fn_Analyse1 :

ALTER FUNCTION [Analyse].[fn_Analyse1]

(
@OccupationSolIds  PhytAdmin.Liste_Id READONLY
)
RETURNS TABLE
AS
RETURN
SELECT  …

In sql server, when I tested this function fn_Analyse1 like that :

DECLARE @TableCulture [PhytAdmin].[Liste_Id]
INSERT INTO @TableCulture (id) VALUES
(910071)
,(910065)
,(910054)
,(910059)
,(910055)
,(910073)
,(910066)
,(910067)
,(910068)
,(910058)
,(910060)
,(910061)
,(910074)
,(910069)
,(910070)
,(910072)
,(910063)
,(910075)
,(3008)

SELECT * FROM analyse.fn_Analyse1(@TableCulture)
This works.

In c# code, I call the function like that :
List<int> idListIds = … ;
DataTable dt = DataHelpers.CreateIdsList(idListIds);
mssql.TableName = “(SELECT * FROM analyse.fn_Analyse1(” + dt + “)) AS TOTO” and dt is the parameter.
But obviously, this doesn’t work ;-(
Is there a way to pass a parameter in a MssqlFeatureLayer wich is a kind of list type ?

Have you ever had this type of problem ?

I hope my explanation is clear …
Thanks a lot.

Regards.

Steph.

Johnny · March 22, 2016, 10:01am

Hi Steph,

I guess I kinda got it. You are using the Sql Functions as the searching table name and pass some parameters into those functions, and now the issue is you can’t pass ids list into them.

If the above is correct, I got the below ideas:

Is it possible change the @OccupationSolIds type as nvarchar in function [Analyse].[fn_Analyse1] and then we split the parameters into ids in the function? If we define the function like this, then we can just pass the ids string(split by comma for example) to this function in c# codes.

Registering the executingSqlStatement event in MsSql2008FeatureLayer. The event will be triggered before communicating with the sql server and we can get and modify the T-SQL command string passed from this event.

Hope it helps.

Regards,

Troy

Johnny · March 22, 2016, 10:01am

Hi Steph,

I guess I kinda got it. You are using the Sql Functions as the searching table name and pass some parameters into those functions, and now the issue is you can’t pass ids list into them.

If the above is correct, I got the below ideas:

Is it possible change the @OccupationSolIds type as nvarchar in function [Analyse].[fn_Analyse1] and then we split the parameters into ids in the function? If we define the function like this, then we can just pass the ids string(split by comma for example) to this function in c# codes.

Registering the executingSqlStatement event in MsSql2008FeatureLayer. The event will be triggered before communicating with the sql server and we can get and modify the T-SQL command string passed from this event.

Hope it helps.

Regards,

Johnny

Steph · March 22, 2016, 10:01am

Hi johnny,

Thanks for your help, yes it’s the idea.
So I tested with string parameter but I have a problem with ’ and “.

I tested the two ways with ’ and " and i have an error.

1)
const string quote=’;
e.SqlStatement = “SELECT allColumns.name, allTypes.name, allTypes.max_length FROM sys.columns allColumns, sys.tables allTables, sys.types allTypes where allColumns.object_

id=allTables.object_id AND allColumns.user_type_id=allTypes.user_type_id AND allTables.name='Analyse.fn_Analyse5(” + quote + sOcc + quote+ “)’;”;

The code error is :
L’exception System.Data.SqlClient.SqlException n’a pas été gérée
  Message=Syntaxe incorrecte vers ‘3008’.
Syntaxe incorrecte vers ‘3008’.
  Source=.Net SqlClient Data Provider
  ErrorCode=-2146232060
  Class=15
  LineNumber=1
  Number=102
  Procedure=”"
  Server=.\sqlexpress
  State=1
  StackTrace:
       à ThinkGeo.MapSuite.WpfDesktopEdition.Tile.<>c__DisplayClass6.<DrawException>b__4()
       à System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
       à MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(Object source, Delegate method, Object args, Int32 numArgs, Delegate catchHandler)
       à System.Windows.Threading.Dispatcher.WrappedInvoke(Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
       à System.Windows.Threading.DispatcherOperation.InvokeImpl()
       à System.Threading.ExecutionContext.runTryCode(Object userData)
       à System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
       à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       à System.Windows.Threading.DispatcherOperation.Invoke()
       à System.Windows.Threading.Dispatcher.ProcessQueue()
       à System.Windows.Threading.Dispatcher.WndProcHook(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
       à MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
       à MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
       à System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
       à MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(Object source, Delegate method, Object args, Int32 numArgs, Delegate catchHandler)
       à System.Windows.Threading.Dispatcher.WrappedInvoke(Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
       à System.Windows.Threading.Dispatcher.InvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
       à MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)
       à MS.Win32.UnsafeNativeMethods.DispatchMessage(MSG& msg)
       à System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame)
       à System.Windows.Application.RunInternal(Window window)
       à System.Windows.Application.Run()
       à … :ligne 51
       à System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       à Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       à System.Threading.ThreadHelper.ThreadStart()
  InnerException:

And with sql express profiler :
SELECT allColumns.name, allTypes.name, allTypes.max_length FROM sys.columns allColumns, sys.tables allTables, sys.types allTypes where allColumns.object_id=allTables.object_id

AND allColumns.user_type_id=allTypes.user_type_id AND allTables.name=‘Analyse.fn_Analyse5(‘3047,3008,140084,3012,140087,140088,3017,3018,140091,3020,140092,140093,140094,

3024,3025,140095,140096,140097,140098,140099,3031,3032,140100,140101,140102,140103,140104,140105,140106,3040,140107,140108,140109

,140110,140111,3046,3048,140112,140113,140114,140115,140116,3054,140117,140118,140119,140120,3059,140121,140122,140123,3063,3064,3065,140124,140125,140126,3069,140127,3071,3072,140128,140129,140130,3076’)’;
go

I think the problem is the quote in red.

So I changed and put " or ‘\u0022’

2)

e.SqlStatement = “SELECT allColumns.name, allTypes.name, allTypes.max_length FROM sys.columns allColumns, sys.tables allTables, sys.types allTypes where allColumns.object_id=allTables.object_id

AND allColumns.user_type_id=allTypes.user_type_id AND allTables.name='Analyse.fn_Analyse5(” + ‘\u0022’ + sOcc + ‘\u0022’+")’;";
The code error is
L’exception System.Data.SqlClient.SqlException n’a pas été gérée
  Message=Le identificateur qui commence par ‘3008,140084,3012,140087,140088,3017,3018,140091,3020,140092,140093,140094,3024,3025,140095,140096,140097,140098,140099,3031,3032’ est trop long. La longueur maximale est 128.
  Source=.Net SqlClient Data Provider
  ErrorCode=-2146232060
  Class=15
  LineNumber=1
  Number=103
  Procedure=""
  Server=.\sqlexpress
  State=4
  StackTrace:
       à ThinkGeo.MapSuite.WpfDesktopEdition.Tile.<>c__DisplayClass6.<DrawException>b__4()
       à System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
       à MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(Object source, Delegate method, Object args, Int32 numArgs, Delegate catchHandler)
       à System.Windows.Threading.Dispatcher.WrappedInvoke(Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
       à System.Windows.Threading.DispatcherOperation.InvokeImpl()
       à System.Threading.ExecutionContext.runTryCode(Object userData)
       à System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
       à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       à System.Windows.Threading.DispatcherOperation.Invoke()
       à System.Windows.Threading.Dispatcher.ProcessQueue()
       à System.Windows.Threading.Dispatcher.WndProcHook(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
       à MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
       à MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
       à System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
       à MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(Object source, Delegate method, Object args, Int32 numArgs, Delegate catchHandler)
       à System.Windows.Threading.Dispatcher.WrappedInvoke(Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
       à System.Windows.Threading.Dispatcher.InvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
       à MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)
       à MS.Win32.UnsafeNativeMethods.DispatchMessage(MSG& msg)
       à System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame)
       à System.Windows.Application.RunInternal(Window window)
       à System.Windows.Application.Run()
       à …
       à System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       à Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       à System.Threading.ThreadHelper.ThreadStart()
  InnerException:

And with sql express profiler :

SELECT allColumns.name, allTypes.name, allTypes.max_length FROM sys.columns allColumns, sys.tables allTables, sys.types allTypes where allColumns.object_id=allTables.object_id

AND allColumns.user_type_id=allTypes.user_type_id AND allTables.name=‘Analyse.fn_Analyse5(“3047,3008,140084,3012,140087,140088,3017,3018,140091,3020,140092,140093,

140094,3024,3025,140095,140096,140097,140098,140099,3031,3032,140100,140101,140102,140103,140104,140105,140106,3040,140107,140108,

140109,140110,140111,3046,3048,140112,140113,140114,140115,140116,3054,140117,140118,140119,140120,3059,140121,140122,

140123,3063,3064,3065,140124,140125,140126,3069,140127,3071,3072,140128,140129,140130,3076”)’;
go

select * from Analyse.fn_analyse5(“3008,140084,3012,140087,140088,3017,3018,140091,3020,140092,140093,140094,3024,3025,140095,140096,140097,

140098,140099,3031,3032,140100,140101,140102,140103,140104,140105,140106,3040,140107,140108,140109,140110,140111,3046,3047

,3048,140112,140113,140114,140115,140116,3054,140117,140118,140119,140120,3059,140121,140122,140123,3063,3064,3065,140124,

140125,140126,3069,140127,3071,3072,140128,140129,140130,3076”) where 1=0;

go

I think that the first intruction  (SELECT allColumns.name, allTypes.na …) works because i have the second one but the problem is the second one with the ". When I execute this in sql server, I have an error.

Do you have the same issue ? If yes, have you solved ?

Thanks again for your help.

Regards.

Steph.

Johnny · March 22, 2016, 10:01am

Hi Steph,

I noticed there is a “single quote” before the function name and it shouldn’t be there, the single quote should be used to wrap the varchar parameter like the ids here, please try the below codes in your executingSqlStatement event:

e.SqlStatement =“SELECT allColumns.name, allTypes.name, allTypes.max_length FROM sys.columns allColumns, sys.tables allTables, sys.types allTypes where allColumns.object_

id=allTables.object_id AND allColumns.user_type_id=allTypes.user_type_id AND allTables.name=Analyse.fn_Analyse5(’” + sOcc + “’)’;”;

Also, here is the test codes which works for me

Create FUNCTION [dbo].[FuntionTest1]
(
    @OccupationSolIds  nvarchar(100)
)
RETURNS int
AS
begin
    – Add the SELECT statement with parameter references here
    declare @ret int
    SELECT @ret = dbo.Articles.ArticleId from dbo.Articles where ArticleId = substring(@OccupationSolIds,1,1)
    return @ret;
end

select dbo.FuntionTest1(‘1,2’)

Hope it helps.

Any questions, don’t hesitate to let us know.

Troy