WCF Ria Services using Stored Procedure

I'm trying to develop a SL4 business app using ria services to run a stored procedure and return the result set for use in a chart.

The problem is, that when I add my SP to the LINQtoSQL Class, or the ADO Entity, the return type can not be found by the designer and it tries to use int as the return type. I tried to create a manual dataclass in my LINQtoSQL class, and manually add the column names that the SP returns, then used the code below.

The <DailyData> in the IQueryable is the dataclass that I manually created, and added the column names that I know the SP returns. I also set a primary key on the dataclass.

When I try to run the app, I get an error that the dbo.GetCarbonData cannot be found.

I'm new to this and sure I've done somethning wrong in the LINQtoSQL creation.

Can anyone help here.

Thanks


[EnableClientAccess()]
    public class LINQtoSQLDomainService : LinqToSqlDomainService<LINQtoSQLClassDataContext>
    {
        
        
        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        public IQueryable<DailyData> GetCarbon(string sDate,string Month,string MeterInstance)
        {
            return this.DataContext.GetCarbonData(0,0,0,Convert.ToDateTime("8:00"),Convert.ToDateTime("21:00"),Convert.ToDateTime("2010/1/1"),42,0,0,0,0,0,0);
        }

Open in new window

LVL 1
wint100Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AshokCommented:
It tries to use int as the return type.

This is your problem.

Stored procedure must be fixed so that it does not create this situation.
I mean return type cannot be int, it must be resultset.

    * Create a dummy stored procedure with same signature(input parameters & output columns) by returning dummy values
    * Generate the dbml file using the dummy stored procedure
    * Drop the dummy stored procedure and create the actual stored procedure on the database

See this @
http://www.techdreams.org/microsoft/fixing-linq-to-sql-issue-stored-procedure-definition-returns-int-instead-of-resultset-2/2752-20090614

HTH
Ashok
0
AshokCommented:
I think you should add "Web Services and WCF" ZONE to this question.

Ashok
0
AshokCommented:
I just realized you cannot have more than 3 ZONES.

But, I think "Microsoft Development" should be replaced by "Web Services and WCF" ZONE.

Ashok
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

wint100Author Commented:
OK, I'll try and move it.

My problem is that the LINQ designer says my SP doens't return any columns, even though it does.

I tried to create a class in the DBML with the columns I know are returned, but it says there's an error and the dbo.SPName is invalid!?

I'll try your example this afternoon.
0
wint100Author Commented:
Can you give example of  * Drop the dummy stored procedure and create the actual stored procedure on the database?

Where is this done exactly?
0
AshokCommented:
Dummy Stored Procedure Example.....

CREATE PROCEDURE pGetProfileDetails
(
 @P_IdentifyingUID BIGINT,
 @P_IdentifyingType VARCHAR(50)  

)
AS
BEGIN
      SELECT      'test' AS Name,
            'test' AS Industry,
            'test' AS FunctionName,
            'test' as JobRoles,
            'test' as Skills,
            'test' as Specializations
END

Change SQL SELECT Statement accordingly.

1) Remove your SP from DBML and Add above SP (or REPLACE with Above SP)
2) Make Sure you not get int
3) Now you are SET on VS side
4) On Database side, re-create your original SP
5) Test it.

That's it.

HTH
Ashok
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AshokCommented:
Dummy Stored Procedure is created on Database side.
Then it is used in VS side.

Ashok
0
wint100Author Commented:
Ah, so once VS has seen the dummy SP and created the relevant DBML, I can manually remove the dummy SP. and re-use my existing code?
0
AshokCommented:
Yes

Once DBML is created, and after changing SP, if you do not try to update new SP in VS

it will work.

Ashok
0
wint100Author Commented:
Now I'm getting error:

Error      3      The entity 'GetCarbonData2Result' in DomainService 'LINQtoSQLDomainService' does not have a key defined. Entities exposed by DomainService operations must have at least one public property marked with the KeyAttribute.      EnergySuiteV2
0
wint100Author Commented:
Below is my dummy SP that is requiring a key.
USE [EnergySuiteDB]
GO
/****** Object:  StoredProcedure [dbo].[GetCarbonData2]    Script Date: 04/18/2010 14:39:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetCarbonData2] @CostValueWeekday MONEY,@CostValueWeeknight MONEY,@CostValueWeekend MONEY,@sTime DateTime,@eTime DateTime,@sDate DATETIME,@Meter int,@ClChRate money,@ChCap int,@CapRate money,@GasRate int,@CO2 int,@Carbon int
AS
BEGIN

SELECT 
    'test' AS Date,
    'test' AS CO2_data,
    'test 'as Carbon_data,
    'test' as kWh_data
    

 
END

Open in new window

0
AshokCommented:
Change SQL Statement to return zero record from actual database table.

Something like

SELECT
    Date,CO2_data,Carbon_data,kWh_data
from myTable
where 1=2

This way all fields will get actual data type and automatically assign Primary Key, etc.
In your last post, all fields may be considered as TEXT fields.

HTH
Ashok
0
AshokCommented:
Forgot one more thing.  Any reserved field must be using square brackets (I think).

Change SQL Statement to return zero record from actual database table.

Something like

SELECT
    [Date],CO2_data,Carbon_data,kWh_data
from myTable
where 1=2

This way all fields will get actual data type and automatically assign Primary Key, etc.
In your last post, all fields may be considered as TEXT fields.

HTH
Ashok
0
wint100Author Commented:
Now it's gone back to not being able to detect a return type!?
0
wint100Author Commented:
OK, i was trying to use a #temptable. I linked to a real table and it went back to complaining about no key defined, even though I set a primary key in the Table I created.
0
wint100Author Commented:
I've now switched to ADO.NET Entity Data model, added the SP and created a function import, and it once again complains about the key.

My new SP is below:
USE [EnergySuiteDB]
GO
/****** Object:  StoredProcedure [dbo].[GetCarbonData2]    Script Date: 04/18/2010 17:19:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetCarbonData2] @CostValueWeekday MONEY,@CostValueWeeknight MONEY,@CostValueWeekend MONEY,@sTime DateTime,@eTime DateTime,@sDate DATETIME,@Meter int,@ClChRate money,@ChCap int,@CapRate money,@GasRate int,@CO2 int,@Carbon int
AS
BEGIN
CREATE UNIQUE CLUSTERED INDEX IX_TempReportDataTimestampRecordNumber_ReportData ON myTable (Date)
 

SELECT 
    
    [Date],
    CO2_data,
    Carbon_data,
    kWh_data
    
    from dbo.myTable
    

    

 
END

Open in new window

0
AshokCommented:
Do you have primary key on any of your four fields?

Ashok
0
wint100Author Commented:
Yes, I created a Table called myTable and set the Date field as the primary key. I went though and recreated my DBML and Domainclass just to make sure, but I still get this error
0
wint100Author Commented:
I've opened another question on this issue, as I think you answered my original question so it's only fair to add more points to my problem:

http://www.experts-exchange.com/Programming/Languages/.NET/Web_Services/Q_25943408.html

Thanks

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.