ORMLite in a normal Plugin

[SBarnes]

Hi Mike,

Given the new Web API is up and running would it now be possible to use ORMLite within a normal plugin to query the database, which might make for less code to produce a list of entities, and if this is possible could you please provide a rough example?

[Mike.Sheen]

If you add references to ServiceStack.Common and ServiceStack.OrmLite.SqlServer, then the following works inside any plugin:

Code: Select all

var dbFactory = new OrmLiteConnectionFactory(Plugin.Manager.Database.ConnectionString, ServiceStack.OrmLite.SqlServer2012Dialect.Provider);
using (var db = dbFactory.Open())
{
   var query = db.From<CN_Contact>()
            .Join<CN_Contact, CN_Main>((contact, prospect) => prospect.ProspectID == contact.ProspectID)
            .Join<CN_Main, DB_Main>((prospect, debtor) => prospect.DebtorID == debtor.DebtorID && debtor.AccountNo == "1001");
                     
}


[SBarnes]

Hi Mike,

Shouldn't the code be this?

It is what I had to change it to to get it to compile under 7.181?

Code: Select all

      var dbFactory = new OrmLiteConnectionFactory(Plugin.Manager.Database.ConnectionString, ServiceStack.OrmLite.SqlServer2012Dialect.Provider);
      using (var db = dbFactory.Open())
      {
         var query = db.From<CN_Contact>()
                  .Join<CN_Contact, CN_Main>((contact, prospect) => prospect.Id == contact.ProspectID)
                  .Join<CN_Main, DB_Main>((prospect, debtor) => prospect.DebtorID == debtor.Id && debtor.AccountNo == "1001");
                           
      }

[Mike.Sheen]

Hi Mike,

Shouldn't the code be this?

It is what I had to change it to to get it to compile under 7.181?

Code: Select all

      var dbFactory = new OrmLiteConnectionFactory(Plugin.Manager.Database.ConnectionString, ServiceStack.OrmLite.SqlServer2012Dialect.Provider);
      using (var db = dbFactory.Open())
      {
         var query = db.From<CN_Contact>()
                  .Join<CN_Contact, CN_Main>((contact, prospect) => prospect.Id == contact.ProspectID)
                  .Join<CN_Main, DB_Main>((prospect, debtor) => prospect.DebtorID == debtor.Id && debtor.AccountNo == "1001");
                           
      }

Yes - in 7.00.181.00 that code is correct - I rather thoughtlessly posted code for 7.00.182.00 - which has abandoned the convention of renaming primary key fields to "Id" and instead uses the actual column name.
We did this to make things a bit clearer - and while we understand such changes might make people nervous about developing against the API - we promise to be really conscious of such changes once we release a public, stable version.

in 7.00.182.00 we also generate the AutoQuery requests for each table and put those in JiwaServiceModel.dll under the namespace JiwaFinancials.Jiwa.JiwaServiceModel.Tables - so for each table there is a class for querying the corresponding table, complete with the fields necessary for strong typed querying. That will be more obvious when we release 7.00.182.00 tomorrow (hopefully).

Mike

[SBarnes]

Hi Mike,

OK understood that will actually be fantastic, it should allow access to pretty much any table without the need "cookie cutter" SQL reader type code, which was the main reason for my original question and will really open up things with Linq etc.

I also noticed that the Stored Procedures and Views made the cut as well into JiwaServiceModel, which means pretty all reporting data functionality is available is there any plans to include the database functions as well being generated by the templates?

[Mike.Sheen]

I also noticed that the Stored Procedures and Views made the cut as well into JiwaServiceModel, which means pretty all reporting data functionality is available is there any plans to include the database functions as well being generated by the templates?

SQL functions are not supported for strong typing, so no - nothing regarding SQL functions will be included. That's doesn't stop you from using them in ad-hoc queries, you just don't get the pleasantries like you do with stored procedures or tables.

I made an article on how to call a stored procedure within the REST API, but it would apply to use inside plugins also - I haven't spent much time exploring the optimal way to get results from a stored procedure, so if my article is lacking in any way let me know and we can get that updated.

[neil.interactit]

Hi Mike,

In a similar fashion to the above thread, I thought it might possible to use ORMLite within your Sample Plugin - Editable List Maintenance Form, significantly reducing boilerplate code.

I am attempting:

Code: Select all

    protected override void iSave()
    {
        var dbFactory = new OrmLiteConnectionFactory(Manager.Database.ConnectionString, SqlServer2012Dialect.Provider);
        using (var db = dbFactory.Open())
        {
            if (DeleteFlag) db.Delete(this);
            else if (InsertFlag) db.Insert(this);
            else db.Update(this);
        }
    }


but this gives:

isave.png (7.89 KiB) Viewed 2843 times

Is there a simple fix for this, or am I barking up the wrong tree?

Chhers,
Neil

[Mike.Sheen]

Hi Mike,

In a similar fashion to the above thread, I thought it might possible to use ORMLite within your Sample Plugin - Editable List Maintenance Form, significantly reducing boilerplate code.

I am attempting:

Code: Select all

    protected override void iSave()
    {
        var dbFactory = new OrmLiteConnectionFactory(Manager.Database.ConnectionString, SqlServer2012Dialect.Provider);
        using (var db = dbFactory.Open())
        {
            if (DeleteFlag) db.Delete(this);
            else if (InsertFlag) db.Insert(this);
            else db.Update(this);
        }
    }


but this gives:

isave.png

Is there a simple fix for this, or am I barking up the wrong tree?

Chhers,
Neil

I don't know what the cause of your error is - what do I need to do to get the same error? Make it easy for me to see the problem and the solution gets to you much quicker!

Also, just note that if you're going to use OrmLite for your database updates, you potentially won't be operating on the same SQL transaction as Jiwa if your save operation was invoked after we've already started one. In most cases that won't be an issue - as I doubt you'll come across a scenario where someone (perhaps a plugin) has created your business logic object and wants to wrap it's save operation inside an existing transaction.

For that reason we haven't used OrmLite for our own business logic objects (yet) - we'll use OrmLite for database updates only if they are isolated and not going to potentially causes inconsistency (such as logging or updating webhooks tables).

[neil.interactit]

Ah, ok, thanks Mike.

I hadn't realised that transaction thread issue. I might leave it for now.

Cheers,
Neil