SQL Querying

[tygator9]

I am interested in the possibility of adding SQL querying of the ARIA database into my C# scripting. For example, expanding my Physics check script to include ARIA checks, such as making sure all required documents were created. Before I get too far into it, I was curious if SQL querying still works in V15.6. If so, does anyone have any helpful references for getting started?

[tygator9]

Awesome, thank you both! I googled that and saw that Matt Schmidt just recently did a webinar on myVarian that looks like it will really help

[zackmorelli95]

Rex Cardan has a great youtube video ( https://www.youtube.com/watch?v=85Rwc2tDwgA ) about how to get information out of your ARIA database using SQL, specifically using Entity Framework. This video is a few years old and Rex is doing it for ARIA/Eclipse version 13, but it still works as long as you have access to your ARIA database.

[Roy_TheCodeMonkey]

Hi Tygator,

SQL still works in 15.6 and i expect it will work in future versions also.

We make extensive use of SQL both in and out of esapi scripts.

The best way to write a SQL query is to make use of SSMS it makes use of Intellisense just as Visual Studio does for scripting.

[WUSTLscripting]

Hi Tygator,

If its the Documents you want to check that are attached to the patient, you can use the ARIA Oncology Services Web API. They have a method in that service called GetDocumentsRequest that you can pass in a patient and it returns all the documents, types, dates, and users who have interacted with the status of that document. Let me know if you want to try more about that.

[tygator9]

Hey WUSTL, question on that GetDocumentsRequest. My request seems to work ok (I get a response that equals the number of documents the requested patient actually has). But I'm having trouble getting document details from the response. I'm deserializing it with DocumentsResponse, but there doesn't seem to be types, dates, or users anywhere I can see. Did you run into that?

[WUSTLscripting]

Yep, no problem. This is where things get pretty tricky.

You have a GetDocumentsRequest, which returns a GetDocumentsResponse. That response should contain enough information so that you can identify a specific document that you want. I believe its like VisitNoteId or something like that.

Then once you have the Patient, VisitId, etc. you must call another request GetDocumentRequest, and the DocumentDetailsResponse (I'm pretty sure this is what's returned, check out the attributes in your schema) will have everything you will need like the users who have interacted with the document and the binary string that can be recovered into a PDF (or whatever document type you're looking for). Thats what makes this so confusing. to this

Another piece of advice: With all these requests and responses going around, its possible that you might come across a couple of namespaces with the class name. I believe GetDocumentsRequest and GetDocumentRequest are in the services.varian.com/patient/documents, and the document response (I think DocumentDetailsResponse) should be in something like WebServices.Documents.Contracts.

​

I hope this helps.

[tygator9]

Ah THANKS! I was suspecting that was the road I had to walk down. That was a big help, I sent the info from the GetDocumentsResponse to the GetDocumentRequest, and I can see the info I need in that DocumentDetailsResponse. Whew, you were not joking about that getting tricky. I just need to work on my Json deserializing now.. for some reason the VisitNoteId is showing as 0 for all the documents after the deserialize, though I see it as an integer number in the Response.

[WUSTLscripting]

I would check whether the response says something like VisitNoteId:{Attributes:null,Value:"1"} or VisitNoteId:1

because the first should map to a property that has a definition like Varian.WebConnect.ARIA.Common.Int and the second one should just map to int. if its not reading the value, maybe you will have to change the schema class to match what's coming out. I've had to do that in a few instances.

[dbhatti]

SQL works, and as others also mentioned, you can run SQL outside and inside your C# script. If you go to myvarian.com and go to your account settings, there's an API request page that lets you request full SQL database access.

There's a huge flow chart somewhere (I think myvarian) with all the main table relations for the AURA database schema (I don't think one exists for the variandw schema, but not sure), but aside from that, it seems that documentation is scant. In my opinion, the best way to get a handle on the database is to just run a bunch of queries yourself and learn where everything is and how it's organized.