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
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.
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
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.
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
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.
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