SQL hard coding instead of using object

[JuiceyBrucey]

Hi, I am very experienced with SQL but not experienced in using it the way is it is used in the JIWA plug ins. I am used to hard coding the SQL rather than using objects.
I would like to be able to write the SQL in the plug in service as plain text like this:
SELECT * FROM
DB_Main
WHERE
DebtorID='a3bbee6ec5f343038940'
ORDER BY DebtorID ASC;
For example....

rather than using this method:

Db.From<CN_Contact>()
.Join<CN_Contact, CN_Main>((contact, prospect) => prospect.DebtorID == contact.ProspectID)
.Join<CN_Main, DB_Main>((prospect, debtor) => prospect.DebtorID == debtor.DebtorID && debtor.DebtorID == request.DebtorID;

Is there a tutorial that shows me how to add my own SQL?
Cheers

[Mike.Sheen]

I am used to hard coding the SQL rather than using objects.
I would like to be able to write the SQL in the plug in service as plain text like this:
SELECT * FROM
DB_Main
WHERE
DebtorID='a3bbee6ec5f343038940'
ORDER BY DebtorID ASC;

There are various plugins we ship with which show how to do plain SQL queries - it's just using the standard .NET SqlClient - a quick example of an ad-hoc query:

Code: Select all

System.Data.SqlClient.SqlDataReader SQLReader = null;
System.Data.SqlClient.SqlParameter SQLParam = null;

try
{
   string SQL = @"SELECT * FROM DB_Main WHERE DebtorID = @DebtorID";   

   using (System.Data.SqlClient.SqlCommand SQLCmd = new System.Data.SqlClient.SqlCommand(SQL, Manager.Database.SQLConnection, Manager.Database.SQLTransaction))
   {         
      SQLParam = new System.Data.SqlClient.SqlParameter("@DebtorID", System.Data.SqlDbType.Char);
      SQLParam.Value = "a3bbee6ec5f343038940";
      SQLCmd.Parameters.Add(SQLParam);         
      
      SQLReader = Manager.Database.ExecuteReader(SQLCmd);

      if (SQLReader.Read() == true)
      {
         string accountNo = (string)Manager.Database.Sanitise(SQLReader, "AccountNo");
         string name = (string)Manager.Database.Sanitise(SQLReader, "Name");
      }
   }
}
finally
{
   if (SQLReader != null)
      SQLReader.Close();         
}         

[JuiceyBrucey]

Thank you very much for your reply.
This is all a crash course in c# methods to me as I am used to PHP, MySQL. But I really like this language. It is very interesting.
Here is my highly plagiarised code that I am struggling with:

using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using JiwaFinancials.Jiwa;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Drawing;
using ServiceStack;
using ServiceStack.DataAnnotations;
using ServiceStack.Model;
using JiwaFinancials.Jiwa.JiwaServiceModel.Tables;
using ServiceStack.Auth;
using System.Linq;
using ServiceStack.OrmLite;

namespace JiwaFinancials.Jiwa.JiwaServiceModel
{
public class RESTAPIPlugin : System.MarshalByRefObject, JiwaFinancials.Jiwa.JiwaApplication.IJiwaRESTAPIPlugin
{
public void Configure(JiwaFinancials.Jiwa.JiwaApplication.Plugin.Plugin Plugin, ServiceStack.ServiceStackHost AppHost, Funq.Container Container, JiwaApplication.Manager JiwaApplicationManager)
{
AppHost.RegisterService<CustomServices>();
AppHost.Routes.Add(typeof(DebtorGetAllSpecificPricesRequest), "/Custom/{DebtorID}/SpecificPrices", "GET", "Retrieves all debtor specific prices for a debtor.", "");
}
}

#region "Requests"
[Serializable()]
[ApiResponse(200, "Transactions read OK")]
[ApiResponse(401, "Not authenticated")]
[ApiResponse(404, "No debtor with the Account No. provided was found")]
public class DebtorGetAllSpecificPricesRequest : IReturn<DebtorGetAllSpecificPricesResponse>
{
public string DebtorID { get; set; }
}
#endregion

#region "Responses"
[Serializable()]
public class DebtorGetAllSpecificPricesResponse
{
public List<IN_DebtorSpecificPrice> DebtorPriceList {get; set;}
}
#endregion

#region "Services"
public class CustomServices : Service
{
[Authenticate]
public DebtorGetAllSpecificPricesResponse Get(DebtorGetAllSpecificPricesRequest request)
{
var query = Db.From<IN_DebtorSpecificPrice>().Where<IN_DebtorSpecificPrice>((Prices)=>Prices.DebtorID == request.DebtorID);
/*"DB_Trans.DebtorID == request.DebtorID;"*/
return new DebtorGetAllSpecificPricesResponse() { DebtorPriceList = Db.Select(query)} ;
}
}
#endregion
}

Where the SQL query is created, I need to learn how to write my own SQL, and execute it. I am getting parts here and there, but not actually understanding it completely.
I need to make this:
var query = Db.From<IN_DebtorSpecificPrice>().Where<IN_DebtorSpecificPrice>((Prices)=>Prices.DebtorID == request.DebtorID);

into something like this:
SqlCommand command = new SqlCommand("SELECT * FROM IN_DebtorSpecificPrice WHERE DebtorID=[{request.DebtorID} ORDER BY QuantityBreak ASC", connection);
command.ExecuteNonQuery();

But I am uncertain about what I use as the connection argument for SqlCommand(), and how to add variables from the API request into the SQL string.
Any advice would be greatly appreciated.
Cheers

[JuiceyBrucey]

Ok, got this far:
using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using JiwaFinancials.Jiwa;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Drawing;
using ServiceStack;
using ServiceStack.DataAnnotations;
using ServiceStack.Model;
using JiwaFinancials.Jiwa.JiwaServiceModel.Tables;
using ServiceStack.Auth;
using System.Linq;
using ServiceStack.OrmLite;


namespace JiwaFinancials.Jiwa.JiwaServiceModel
{
public class RESTAPIPlugin : System.MarshalByRefObject, JiwaFinancials.Jiwa.JiwaApplication.IJiwaRESTAPIPlugin
{
public void Configure(JiwaFinancials.Jiwa.JiwaApplication.Plugin.Plugin Plugin, ServiceStack.ServiceStackHost AppHost, Funq.Container Container, JiwaApplication.Manager JiwaApplicationManager)
{
AppHost.RegisterService<CustomServices>();
AppHost.Routes.Add(typeof(DebtorGetAllSpecificPricesRequest), "/AAA_testing/{DebtorID}/SpecificPrices", "GET", "Retrieves all debtor specific prices for a debtor.", "");
}
}

#region "Requests"
[Serializable()]
[ApiResponse(200, "Transactions read OK")]
[ApiResponse(401, "Not authenticated")]
[ApiResponse(404, "No debtor with the Account No. provided was found")]
public class DebtorGetAllSpecificPricesRequest : IReturn<DebtorGetAllSpecificPricesResponse>
{
public string DebtorID { get; set; }
}
#endregion

#region "Responses"
[Serializable()]
public class DebtorGetAllSpecificPricesResponse
{
public List<IN_DebtorSpecificPrice> DebtorPriceList {get; set;}
}
#endregion

#region "Services"
public class CustomServices : Service
{
[Authenticate]
public DebtorGetAllSpecificPricesResponse Get(DebtorGetAllSpecificPricesRequest request)
{
System.Data.SqlClient.SqlDataReader SQLReader = null;
System.Data.SqlClient.SqlParameter SQLParam = null;
//var query = Db.From<IN_DebtorSpecificPrice>().Where<IN_DebtorSpecificPrice>((Prices)=>Prices.DebtorID == request.DebtorID);
/*"DB_Trans.DebtorID == request.DebtorID;"*/

try
{
string SQL = @"SELECT * FROM IN_DebtorSpecificPrice WHERE DebtorID=@DebtorID ORDER BY QuantityBreak ASC;";

using (System.Data.SqlClient.SqlCommand SQLCmd = new System.Data.SqlClient.SqlCommand(SQL, Manager.Database.SQLConnection))
{
SQLParam = new System.Data.SqlClient.SqlParameter("@DebtorID", System.Data.SqlDbType.Char);
SQLParam.Value = request.DebtorID;
SQLCmd.Parameters.Add(SQLParam);

SQLReader = Manager.Database.ExecuteReader(SQLCmd);

if (SQLReader.Read() == true)
{
//string accountNo = (string)Manager.Database.Sanitise(SQLReader, "AccountNo");
//string name = (string)Manager.Database.Sanitise(SQLReader, "Name");
return new DebtorGetAllSpecificPricesResponse()
{
DebtorPriceList = Db.Select(query)
};
}
}
}
finally
{
if (SQLReader != null)
SQLReader.Close();
}
}
}
#endregion
}

getting error about this:
Manager.Database
Saying it does not exist.

and I am not sure how to return the list that I am seeking. I realise this is incorrect, but not sure what should be there:
return new DebtorGetAllSpecificPricesResponse()
{
DebtorPriceList = Db.Select(query)
};

Thank you very much.
Cheers

[SBarnes]

The db you are trying to use on the Service class is an ORM Lite database connection not the Manager.Database that Mike referred you to and the two are not interchangeable.


To use ORM Lite with an adhoc query it looks something like this and uses an anonymous object to pass in the parameter. To use IN_DebtorSpecificPrice a class for this will already exist in Jiwa's JiwaFinancials.Jiwa.JiwaServiceModel namespace, , in other words the class is JiwaFinancials.Jiwa.JiwaServiceModel.IN_DebtorSpecificPrice, ORM Lite uses reflection to match fields from the SQL to class properties.

Code: Select all

List<Person> results = db.SqlList<Person>("SELECT * FROM Person WHERE Age < @age", new { age=50});


and therefore the code you are trying to create would look like the following for ORM Lite

Code: Select all

List<JiwaFinancials.Jiwa.JiwaServiceModel.IN_DebtorSpecificPrice> results = db.SqlList<Person>("SELECT * FROM IN_DebtorSpecificPrice WHERE DebtorID= @DebtorID ORDER BY QuantityBreak ASC", new { DebtorID="DEBTOR ID GOES HERE"});


Personally I find the brevity of the ORM Lite code preferable as the other way shown below involves a fair bit more cookie cutter code just to fill an object.


If you are using Jiwa's database on the manager to fill a list it goes something like the below, bear in mind that code below is from inside a class that is a Jiwa collection so Add(img); would where this wasn't the case it becomes result.Add(img); where result might be List<INExtraImage> declared and initialised in the function and then returned instead of the function being void and you won't have some the event handling code like OnRead or properties like Reading.

Code: Select all

      public void ReadRecords(String InvID)
      {

         string Sql = null;
         SqlDataReader SQLReader = null;
         bool oldReading = Reading;

         try
         {
            Reading = true;
            Clear();

            //var db = JiwaFinancials.Jiwa.JiwaApplication.Manager.Instance.Database;
            var db = this.Manager.Database;
            //next line launches the debugger
            //  //System.Diagnostics.Debugger.Break();

            SqlParameter parameter = new SqlParameter("@InventoryID", SqlDbType.Char)
            {
               Value = InvID
            };


            Sql =
               @"select RecID, InventoryID, Description, Image, LastUpdated, LastUploaded, Deleted, WooCommID from IN_ExtraImages where InventoryID = @InventoryID and deleted = 0 order by Description";

            this._inventoryID = InvID;

            using (SqlCommand SQLCmd = new SqlCommand(Sql, db.SQLConnection, db.SQLTransaction))
            {
               SQLCmd.CommandTimeout = this.Manager.Database.DefaultCommandTimeout;
               SQLCmd.Parameters.Add(parameter);

               SQLReader = SQLCmd.ExecuteReader();

               while (SQLReader.Read() == true)
               {


                        INExtraImage img = this.Manager.CollectionItemFactory.CreateCollectionItem<INExtraImage>();
                        img.RecID = db.Sanitise(SQLReader, "RecID").ToString();
                        img.InventoryID = db.Sanitise(SQLReader, "InventoryID").ToString();
                        img.Description = db.Sanitise(SQLReader, "Description").ToString();
                        img.Image = (byte[])db.Sanitise(SQLReader, "Image");
                        img.LastUpdated = DateTime.Parse(db.Sanitise(SQLReader, "LastUpdated").ToString());
                        img.LastUploaded = DateTime.Parse(db.Sanitise(SQLReader, "LastUploaded").ToString());
                        img.Deleted = bool.Parse(db.Sanitise(SQLReader, "Deleted").ToString());
                        img.WooCommID = int.Parse(db.Sanitise(SQLReader, "WooCommID").ToString());



         Add(img);

               }

               SQLReader.Close();
            }

            base.OnRead();

         }
         finally
         {
            Reading = oldReading;

            if ((SQLReader != null))
            {
               SQLReader.Close();
            }

         }
      }


By the way if you want Jiwa manager inside a service class you do this but to make it work you need to add the Jiwa Rest Api Plugin as a reference under plugin references if it is not already there.

Code: Select all

JiwaFinancials.Jiwa.JiwaApplication.Manager manager = this.SessionAs<JiwaAuthUserSession>().Manager;


If you want more help with the ORM Lite way see https://github.com/ServiceStack/ServiceStack.OrmLite, there are also some T4 templates that will help generate code for views, tables and stored procedures.

And one last trick if the name of your plugin would come before that of Jiwa's Rest API plugin, then change the execution order of your plugin to 1 so that Jiwa's Rest API plugin loads first as thing load in order of execution order and then name otherwise you can get some issues.

[JuiceyBrucey]

Thank you very much for your reply and the time you put into it. It is much appreciated.
I am really struggling with this. I keep running into error after error no matter which way I do things.
I will keep going at it and let you know how I went.
Cheers

[Mike.Sheen]

Thank you very much for your reply and the time you put into it. It is much appreciated.
I am really struggling with this. I keep running into error after error no matter which way I do things.
I will keep going at it and let you know how I went.
Cheers

In your last post, you mentioned currently had two issues - firstly:

getting error about this:
Manager.Database
Saying it does not exist.

Stuart gave you a line of code you can use in your service method to get a Manager:

Code: Select all

JiwaFinancials.Jiwa.JiwaApplication.Manager manager = this.SessionAs<JiwaAuthUserSession>().Manager;

So then in your method all references to Manager you can change to manager and that should have solved that issue.

I would have given you the same, but your original question wasn't framed around the REST API, so I dumbly responded with the mindset of this being within a standard plugin, not in the context of a REST API plugin.

Your second issue you mentioned was:

how to return the list that I am seeking

It looks like you've done the right thing to me - you defined your own response class, DebtorGetAllSpecificPricesResponse, and made your request DebtorGetAllSpecificPricesRequest implement IReturn<DebtorGetAllSpecificPricesResponse> and your adding of the route associated the request DebtorGetAllSpecificPricesRequest with a GET on "/Custom/{DebtorID}/SpecificPrices", and finally you return the correct type from your service method Get in the CustomServices class.

That looks all correct to me - what issue are you having now? Perhaps posting a simple plugin demonstrating the issue would assist us helping.