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

2634
3
06-27-2016 06:00 AM
ChipHankley1
New Contributor III

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.

Tags (1)
3 Replies
RobertScheitlin__GISP
MVP Esteemed Contributor

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.

ChipHankley1
New Contributor III

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!

0 Kudos
RobertScheitlin__GISP
MVP Esteemed Contributor

Chip,

   Here are a couple of url I would recommend:

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

VB-Tips - Tips