How can I use a ContextManager to retrieve a list of objects?

How can I use a ContextManager to retrieve a list of objects?

Old forum URL: forums.lhotka.net/forums/t/8929.aspx


lazaroms posted on Thursday, May 13, 2010

Hello:

I'm starting with the CSLA 3.5, so pray for me!!!

I´m "coding" my classes and I've been trying to code the Fetch Method with no good results.

I need to get a list of objects and I wrote a stored procedure for it.

Words from Mr. Lhotka in the book: "So, I typically compromise, using LINQ to SQL to query for data and using stored procedures (called through LINQ to SQL) for insert, update, and delete operations."

The problem is that I want to query for data using store procedures (called through LINQ to SQL). My stored procedure returns a sets or list of objects because it has a "Select...." instruction.

But (always bitting the eternal BUT)

When I write:

Dim data = ctx.DataContext.spMyStoredProcedure(criteria.Name, criteria.Active,criteria.Date1, criteria.Date2)

and try to make:

Me.AddRange(data)

Referring to "data", VS says that:

Value of type 'Integer' cannot be converted to 'System.Collections.Generic.IEnumerable(Of GoldenTitanium.Library.InfoMyObject)

I hope somebody can help and I apologize for my english, I'm not english-spoken people.

Thaks,

LazaroMS

RockfordLhotka replied on Thursday, May 13, 2010

It sounds like your stored procedure is returning a single Integer result, not a list of data.

You need to get the stored procedure call working first, then get the data loaded into your business object.

lazaroms replied on Friday, May 14, 2010

Sorry Mr. Lhotka but that is the main problem.

The following is my stored procedure:

ALTER PROCEDURE [dbo].[spPrueba]

AS
 
CREATE TABLE #tmp_ListaPolos(polo_Id UniqueIdentifier, polo_Nomb varchar(50))
 
INSERT INTO #tmp_ListaPolos

SELECT trsx_Polo.polo_Id, trsx_Polo.polo_Nomb FROM trsx_Polo ORDER BY trsx_Polo.polo_Id

return SELECT * FROM #tmp_ListaPolos

DROP TABLE #tmp_ListaPolos

When I drag % drop to the DBML, it automatically generates a function as integer.

I checked into the PTracker.designer.vb and it has some Partial Classes defined to use them as Auto-Generated Types.

I noted that those Auto-Generated types are used in the functions that need another type but integer.

I don't know how to make that my DBML generates/creates automatically those Partial Classes and I noted that those partial classes are a representacion of the info an object, like a member of a readonly list of objects.

I hope you can help me.

 

Lazaro

RockfordLhotka replied on Friday, May 14, 2010

I really can't provide support for Microsoft's LINQ to SQL technology, sorry. But maybe someone here has encountered that issue and can help - or you can try the MSDN forums, as I'm sure there's a L2S forum where L2S experts might answer.

chrisghardwick replied on Friday, May 14, 2010

Not really knowing that your procedure is trying to do, but taking a wild guess:

Change:

  return SELECT * FROM #tmp_ListaPolos

To:

SELECT * FROM #tmp_ListaPolos
 

That will have your procedure return the data from #tmp_ListaPolos rather than as the status of the execution of the procedure. From books online:

When used with a stored procedure, RETURN cannot return a null value. If a procedure tries to return a null value (for example, using RETURN @status when @status is NULL), a warning message is generated and a value of 0 is returned.

USE AdventureWorks;
GO
CREATE PROCEDURE checkstate @param varchar(11)
AS
IF (SELECT StateProvince FROM Person.vAdditionalContactInfo WHERE ContactID = @param) = 'WA'
    RETURN 1
ELSE
    RETURN 2;
GO

lazaroms replied on Friday, May 14, 2010

Hi Cris:

Thank you for your response and sorry for my misstype.

What I.m trying to do with that procedure is:

-Create a temporal table.

-Fill the tmp table with a result of a query

-Return the content of the tmp table

-Delete the tmp table

 

ALTER PROCEDURE [dbo].[spPrueba]

AS
 
CREATE TABLE #tmp_ListaPolos(polo_Id UniqueIdentifier, polo_Nomb varchar(50))
 
INSERT INTO #tmp_ListaPolos

SELECT trsx_Polo.polo_Id, trsx_Polo.polo_Nomb FROM trsx_Polo ORDER BY trsx_Polo.polo_Id

SELECT * FROM #tmp_ListaPolos

DROP TABLE #tmp_ListaPolos

 

I have not found a way to make the O/R designer "understand" that the result is rather an objectinfo type tahan an integer type.

lazaroms replied on Wednesday, June 30, 2010

Finally I dit it.

The solution is working on the vb module of the DBML, not in the designer.vb module.

Your need to create a class for your returning type and then create a method for the sotred procedure.

The structure of the SP method is the same that the DBML autogenerates for the other SP.

 

Copyright (c) Marimer LLC