Using Custom Stored Procedure with Joins in Website through Data Layer?

I wrote a custom stored procedure that uses inner joins to join 2 tables and return a dataset. Nettiers recognizes that it is a custom stored procedure in my database but I have no way of accessing it through the datasource as a SelectMethod. My code for the SP is below.

My goal is to use this as my select Method and to use the default Insert/Delete/Update methods for all the other actions. The reason for this is that I have a linking table and want to be able to display a human-readable version of the link between regions and zipcodes while in the backend, simply storing their ID's into the link table.

What is the best way to accomplish this? I suppose I could also write custom Insert/Update/Delete procs if needed, but I would like for them to be available through a datasource, like all the other standard methods. 

All help would be appreciated, as the deadline for my project is fast approaching and this is a big piece of my dataaccss.

Thanks 

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo._RegionZipcode_GetJoinedList') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[_RegionZipcode_GetJoinedList]
GO


CREATE PROCEDURE [dbo].[_RegionZipcode_GetJoinedList]

AS
                                SELECT
                                        [RegionZipcode].[regionzipcodeid],
                                        [RegionZipcode].[regionid],
                                        [RegionZipcode].[zipcodeid],
                                        [Region].[regionname],
                                        [Zipcode].[zip],
                                        [Zipcode].[zipext],
                                        [RegionZipcode].[whencreated],
                                        [RegionZipcode].[lastupdated]
                                FROM
                                        [dbo].[RegionZipcode]
                                INNER JOIN [dbo].[Region] ON [Region].[regionid]=[RegionZipcode].[regionid]
                                INNER JOIN [dbo].[Zipcode] ON [Zipcode].[zipcodeid]=[RegionZipcode].[zipcodeid]

                                Select @@ROWCOUNT

 

Can you not create a VIEW from the three tables and access the human readable data from a VLIST ratrher that a dataset, this way you will have strongly typed data rather than a plain old dataset!

Then in code you can do something like

 VList<AllRegions> allRegions = DataRepository.AllRegions.XXXX();

or am I missing what you are asking...... 

That may be possible in this instance since I have no parameters. Can I access the view through a datasource on my aspx page?

 In the case where I pass in parameters to the select proc for a search aspx page, I can't use a view because there are dynamic components to it and a view is static.

 Any thoughts? 

aabragan:

That may be possible in this instance since I have no parameters. Can I access the view through a datasource on my aspx page?

I dont believe you can do this using a standard  .NET native datasource. I think you need to relook at how NetTiers will work in this instance for you.

When you get some spare time I suggest you generate the  "Web Library" in "05 WebLibrary Advanced" and generate the "Website Admin" in "06 Website advanced" and have a look at some of the ASPX pages in the XXX.website\admin folder. This hopefully get you to where you want to go.

HTH Rippo 

I ended up creating a class for my table based on its providerbase abstract class and implemented that class' methods in the AppCode directory of the website. This included an implementation for my custom stored proc. Basically I had to write my own handler, transactions and all, to do selects, inserts, updates and deletes. I am however hooking into the default insert, update and delete stored procs generated by nettiers to manipulate records, rather than rewrite everything. I am then instantiating an object of that type and hooking my grids and controls to those methods. I don't really see any other easy way to address the custom stuff, given what nettiers, does or does not generate.

Once I get everything working smoothly I will post my class so that the next person doesn't have to go digging too much.

Thanks for all your help Rippo. 

Rippo or anyone have any other thoughts on a concrete implementation of something like this to access data in a custom fashion? 

 Did you look at the website admin that can be generated from NetTiers? This might give you something that you want.

 I already generated the web admin components and the website and the service layer and looked through the code they use. It only gave me the basic entity by entity data access functionality and not access to complex selects. My quandary here is that I have a joined/complex sql statement that I am using for my select to get data, but standard insert/update/delete statements, generated by nettiers.