Roll Your Own AutoQuery

[SBarnes]

Hi Mike,

Due to a customer who has their own tables in a Jiwa database I have created the necessary classes to support my own Auto Query with the help of the T4 templates in visual studio, and by following what the API plugin does I have been able to get it working below is the plugin class (I have left out the service, request query and response classes for brevity) and my question is simply whilst I realise the route must be added and the service must be registered i.e. the last two lines are needed, are the first three lines necessary given that the Web API plugin is already doing this or are they needed because there is no guarantee which plugin Jiwa will load first?

Code: Select all

   public class RESTAPIPlugin : System.MarshalByRefObject, JiwaFinancials.Jiwa.JiwaApplication.IJiwaRESTAPIPlugin
   {
      public ServiceStack.AutoQueryFeature AutoQueryFeature;
      
      public void Configure(JiwaFinancials.Jiwa.JiwaApplication.Plugin.Plugin Plugin, ServiceStack.ServiceStackHost  AppHost, Funq.Container Container, JiwaApplication.Manager JiwaApplicationManager)
       {
           AppHost.Plugins.Add(new AutoQueryFeature() { EnableAutoQueryViewer = true, MaxLimit = 100 });
      
         AppHost.Plugins.Add(new AutoQueryDataFeature { MaxLimit = 100 });

         
         Container.Register<ServiceStack.Data.IDbConnectionFactory>(new OrmLiteConnectionFactory(JiwaApplicationManager.Database.ConnectionString, ServiceStack.OrmLite.SqlServer2012Dialect.Provider));            
         
         AppHost.RegisterService<CustomTermServices>();
         
         
         AppHost.Routes.Add(typeof(HOS_CustomTermQuery), "/Queries/HOS_CustomTerm", "GET", "Retrieves Hos Custom Terms.", "");
         
         
       }
        
 
   }

[Mike.Sheen]

Hi Stuart,

are the first three lines necessary given that the Web API plugin is already doing this or are they needed because there is no guarantee which plugin Jiwa will load first?

They're not needed - you can control the order the plugins are loaded with the Execution Order property of the plugin - just make your Execution Order greater than the REST API Execution Order.

Mike

[SBarnes]

Thanks Mike,

I thought of the execution order after I made the post but thanks for confirming it.

I'll be sorting out stored procedures next so the article you posted will no doubt be of great use.

Thanks again.

[Mike.Sheen]

I'll be sorting out stored procedures next so the article you posted will no doubt be of great use.

On that - whilst the article I posted is the recommended method for invocation of stored procedures, there is a another way which is demonstrated in the DebtorBackorderServices class of our REST API. It uses a helper function defined in the REST API which, if you add a plugin reference within your own plugin to the REST API plugin you can access that (incidentally, adding a plugin reference to another plugin will automatically set the Execution Order appropriately).

We did that because we wanted to use the existing Jiwa stored procedure usp_Jiwa_Debtor_BackOrderFulfillments which is a stored procedure which returns two result sets - the first being the Jiwa grid column schema, the second the data rows - and we didn't find a way of ORMLite handling that and as fans of the DRY principle we didn't want to replicate the logic in that stored procedure by creating another which only returned one result set to satisfy ORMLite.

The helper function, SqlDataReaderToList, simply does what ORMLite does behind the scenes - you pass in a SQLDataReader and a generic type and it returns a List of that generic type, which each item in the list having its properties set by a mapping convention of class property matching the column name of the SQLDataReader columns.

It's probably of no interest to you at this moment, but it might be worth taking a mental note of in case you find yourself in the same situation as we did with multiple result sets.

Mike

[SBarnes]

Thanks Mike,

I'll have a look at it, given the system that I am having to update, as you aware was customised around 15 years ago, and there are a lot of extra tables and stored procedures that the current web site implements that the Web Api, ServiceStack and ORM Lite with T4 are going to get used to do a lot of the "heavy lifting" for and then using ASP.Net MVC to be the front end.

In addition to this I have to replace a system that receives orders from a POS system in six remote locations for which I don't have the source code, fortunately thanks to the Web API again it'll be a windows based application that I've already gotten going that will also call the Web Api after it reads the file from the POS system and insert the orders.

I know I've said it before but thanks again for developing the Web Api and the ability to add to it and customise it as otherwise I don't want to even consider the amount of work that would have been involved in doing it all from scratch.

[Mike.Sheen]

I know I've said it before but thanks again for developing the Web Api and the ability to add to it and customise it as otherwise I don't want to even consider the amount of work that would have been involved in doing it all from scratch.

You're welcome - and we don't mind at all that you repeat gratitude or praise - as developers the only feedback we normally get is negative - which is human nature, I guess - I don't compliment service providers when their service works as expected, but like anyone else I'm quite happy to complain when it doesn't.