Custom stored procedure that calls a view

Hi There

I am having trouble with the netiers component of codesmith.
My main problem is that I have a view and would like to search on that view using a like % clause.
I cant see any built in methods that allows you do do this.

I wrote a custom stored procedure that calls that view and filters on string using like%

The main table is called medic

The View is Called medicdetails.
The procedure is called MedicDetail_SearchCriteria

I need it to return a Vlist when i genrate the code it it adds it to the Medic service which returns a Tlist    Medic.Detail_SearchCriteria

If i change the custom stored procedure starts with property not to include the TableName it does not create the service or add the method.

Is there a way to add it to the medicdetail service vlist

There is an inbuilt way to do this so you do not need a custom stored procedure...

e.g. My view is called AllNames 

AllNamesParameterBuilder p = new AllNamesParameterBuilder();
p.AppendLike(AllNamesColumn.Name, "ri%");
VList<AllNames> names = DataRepository.AllNamesProvider.Find(p);

This basically will find all names that start with ri%

You will also need to reference using <namespace>.Data.Bases;

HTH Rippo 

Make sure you select the View in the Nettiers options.  Once you select it, it generated almost the exact same methods as a table does.

Also, in addition to Find, you can use Get and GetPaged for a view to do the same thing.  If you look at the find stored procedure, it does a Where on every column.  If you pass in your own where, it only does the Where on your 1 column.  In Sql Server, this should give much better performance.  I am not a huge Find fan in Nettiers as far as the proc goes.  Maybe I will add this to my performance tool and perf test these 2 methods.  My money would be on the Find being alot slower.  I have been wrong before though

 Any takers??  Anyone want to bet on Find?

MedicDetailsFilters lookupFilter = new MedicDetailsFilters();
lookupFilter.AppendLike(MedicDetailsColumns.SomeColumnName, "a%");
DataRepository.MedicDetailsProvider.Get(lookupFilter.ToString(), "");

jeff

Geoff, where did you get the conclusion of every column being searched upon using the find? From my code above the following paramertized query is created (SQL 2005)

exec sp_executesql N'
                
                BEGIN
                
                WITH PageIndex AS (
                    SELECT TOP 2147483647 row_number() OVER (ORDER BY [Name]) AS RowIndex
                    , [NameId]
                    , [Name]
                    , [OriginID]
                    , [Origin]
                    , [Sex]
                    , [Description]
                    , [SoundEx]
                    FROM [dbo].[AllNames] where  (Name LIKE @Param0)
                )
                SELECT
                       [NameId],
                       [Name],
                       [OriginID],
                       [Origin],
                       [Sex],
                       [Description],
                       [SoundEx]
                  FROM PageIndex
                 WHERE RowIndex > 0
                   AND RowIndex <= 2147483647
                ORDER BY [Name];
                
                -- get total count
                SELECT COUNT(*) AS TotalRowCount FROM [dbo].[AllNames] where  (Name LIKE @Param0);
                
                END
            ',N'@Param0 nvarchar(4)',@Param0=N'ric%'

What might slow it down is the paging as technically the user has not specified whether this is necessasry,  I have not tested this against a custom stored procedre but I bet the results are not to far apart. 

Yea, you are right, in some cases find uses Dynamic sql, in other cases it uses the Find stored procedure.  I don't think in all cases though it is using Dynamic Sql.  I could be wrong.

In the case of Dynamic Sql, it could actually be a little faster.  I will find out.  I will perf test them.

jeff

Where does the SP _<ENTITY>_Find get used? I agree that that SP is pretty ugly, I suppose the paramertized queries now superseed this.

I Have seem to got it working.I am using the append method but I do not see an appendlike method

query.Append(MedicDetailColumn.Shortsurname, surname.Replace(" ", "") + '%');

return m_medicDetailService.Find(query.GetParameters());

I also find that when you use the Append method it doesnt find names with two words in eg Jon Peirre ect
if you use the appendequals it doesnt allow you to put + '%' in .
I just cxreated another column in the view that removes the spaces and search on that.

Thanks for your help guys.

What version of NetTiers are you using?

According to lthe logs AppendLike was added to NetTiers on 26th Sept 2007. 

You may need to download the latest nightly build from http://nettiers.com/builds/

 HTH Rippo

I am using 4.1.3

If I do an update will it affect anything i have done up until now.Is it fully backward compatible.

thanks Stanton

I think that the verison you quoted is your codesmith verison, this should not matter. What we are actually after is the version of the Nettiers templates you are using.

To get the nettiers version you are using go to windows explorer and navigate to the netTiers folder. There should be a changes.log file. Open this to get the version of the nettiers templates you are using.

 e.g. .netTiers v2.2.0.725 

Templates are pretty much backwards compatible, however I would back up your old netTiers templates before you download and use the new templates as there were a few breaking changes around october last year. Also as netTiers grows then new properties are added to the tmeplates that might meanyour old cst files migt not open (this basically means you need to reset the properties)

Personally I try and keep my templates up to date to minimise the risk of breaking changes, but then risk the problem of new bugs appearing.