topic Re: Custom Widget to Retrieve Data from SQL Server - Where to Start? in ArcGIS Web AppBuilder Questions

Custom Widget to Retrieve Data from SQL Server - Where to Start?

ChipHankley1 — Mon, 27 Jun 2016 13:00:16 GMT

We host a SQL Server database that stores the detailed results of sample data. The database schema is moderately complex.

I would like to develop a widget that would allow a user to "query" the database. I envision the widget giving the user the option to select several options - like date range, chemical name, sample matrix, etc, then having the widget return a "layer" showing the location of the results.

I'd like this to be a completely ArcGIS Online solution (i.e. no need to host my own instance of ArcGIS Server).

Where would I start developing something like this? I can think of numerous pathways to get to this endpoint. The two (very rough) ideas that I have come up with so far:

Create a stored procedure on the SQL server side that returns query results as JSON.
Build a Geoprocessing script that
- passes the inputs to SQL Server
- builds a query table
- builds an event theme
- converts event theme to a temporary feature class

The second option seems easy to build, but not very practical, and I'm not sure if there's any way to leverage the concept of temporary or "in memory" feature classes on ArcGIS Online.

I feel like a solution to this must already exist in some form, and I'm not really wed to any particular approach... I'm just trying to develop the correct questions or "Google Syntax" so that I can hone in on work that others have already done.

Thanks for the help.

Re: Custom Widget to Retrieve Data from SQL Server - Where to Start?

RobertScheitlin__GISP — Mon, 11 Jul 2016 15:51:16 GMT

Chip,

What I do on my end is just create a RESTful web service in Visual Studio VB.net and then consume this from a custom widgets code using esriRequest.

Re: Custom Widget to Retrieve Data from SQL Server - Where to Start?

ChipHankley1 — Sun, 25 Dec 2016 01:54:59 GMT

Hey Robert... thanks and sorry it's taken me so long to acknowledge this. I've been contemplating this all fall. I have a couple of use cases now for building a REST interface for our database.

There is so much information out there about REST... Can you recommend a good starting point? I don't know if I need to build my own API or if there is something that I can just drop on top of my database.

Thanks!

Re: Custom Widget to Retrieve Data from SQL Server - Where to Start?

RobertScheitlin__GISP — Sun, 25 Dec 2016 18:13:55 GMT

Chip,

Here are a couple of url I would recommend:

Create and Consume RESTFul Service in .NET Framework 4.0 - CodeProject

VB-Tips - Tips