Print Page | Close Window

Mapping Dynamic SQL with IdeaBlade

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=3218
Printed Date: 13-May-2026 at 8:27am


Topic: Mapping Dynamic SQL with IdeaBlade
Posted By: pponzano
Subject: Mapping Dynamic SQL with IdeaBlade
Date Posted: 17-Jan-2012 at 1:05am
Hello IdeaBlade,
I've got a problem with mapping an object... in a Master-Detail gridview I need to give the possibility to export particular calculated data for the whold grid... I've got a stored procedure that performs this calculation

SELECT <somefields>
INTO #tmp
FROM source

DECLARE @COLUMNS VARCHAR(8000)

SELECT @COLUMNS = COALESCE(@COLUMNS + ',[' + CAST([CODICE CAMPO] AS VARCHAR) + ']', '[' + cast([CODICE CAMPO] as varchar)+ ']')
FROM #tmp
GROUP BY [CODICE CAMPO]
ORDER BY [CODICE CAMPO]

DECLARE @QUERY VARCHAR(8000)

SET @QUERY = 'SELECT * FROM #tmp PIVOT (MAX([VALORE CAMPO]) FOR [CODICE CAMPO] IN (' + @COLUMNS + ')) AS P'

EXECUTE(@QUERY)

I got an exception that 'xxx' cannot be mapped....
any suggestion?


Replies:
Posted By: DenisK
Date Posted: 18-Jan-2012 at 11:35am
Hi Paolo,

I'm not sure I understand the exception and I'm not entirely convinced that DevForce is at play here. Have you tried mapping without using DevForce and see if that succeeds?

Posted By: pponzano
Date Posted: 18-Jan-2012 at 11:22pm
Hello Denisk,
it's a dynamic query with a pivot grid... for now I reverted to ADO.net and a generic object I map... just a question, it's wrong to create in a Devforce Application Framework a wcf service and handle in an old way the stuffs? I found no way for calling ADO .NET directly from IdeaBlade

Posted By: DenisK
Date Posted: 19-Jan-2012 at 2:49pm
Well, it's not "wrong". It's just not recommended. If you insist on calling the ObjectContext directly, use it sparingly since if you're using it too much, that'll defeat the purpose of having DevForce.

You can create your own ObjectContext by passing in the ClientEdmKey.ConnectionString to its constructor.

However, before you do so, I'm really curious as to the original problem. What exactly is the exception you're getting? Do you get the exception when you try to map the Dynamic SQL StoredProc to your edmx?

Posted By: stevef
Date Posted: 15-May-2012 at 2:04pm
Actually, I see a need to access ObjectContext as well, since it seems (or I simply can't find how to do it in DF) that DF is missing the ExecuteStoredCommand() and ExecuteStoreQuery() functions.  These would be required if you would like to do something like calling a stored proc that takes tabled-value parameter. 
 
Any ideas on how to make that happen?
 

Posted By: DenisK
Date Posted: 16-May-2012 at 1:19pm
Hi stevef,

You're correct that DF doesn't provide those 2 Execute* methods. As I mention above, you can create an ObjectContext by passing in the EdmKey.ConnectionString to the constructor. We recommend that you do this on the server via InvokeServerMethod.

You've probably already known this but EntityFramework (thus DevForce as well) doesn't currently support importing StoredProcs with TVP. ( http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/29bcd888-6d79-4f60-8858-3a11a650018b - http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/29bcd888-6d79-4f60-8858-3a11a650018b )


Print Page | Close Window