In a former life, I had a ArcGIS Server web app (WebADF) that needed to display updated line data from a non-versioned featureclass in SDE every 15 minutes. The source data was in a non-spatial SQL Server 2008 table in another database owned by another department. Their application pulled data down from a web service every 15 minutes. I needed to push that data, which is basically a ton X/Y point data that represented line features, from their table up to my line featureclass.
I ended up building a .NET standalone executable that called a pretty involved stored procedure which queried their database for new data and then, utilizing SQL Spatial methods, pushed that point data (by building lines from the points) into my non-versioned SDE featureclass which used the SQL Spatial geometry datatype to store the spatial data. We had a job scheduler that would kick off the .NET app every 15 minutes. If data was there to pick up in the remote database, it got processed; if no data was there, it just exited.
Worked great and was super fast. This allowed us to update the data behind the web app without having to stop/start/bounce the map service. Since we were bypassing the SDE stack, it didn't matter if the featureclass was locked up by the service or if someone had it opened in ArcMap - when the panned/zoomed/refreshed in any application, they got the fresh data as it came in.
Some of the caveats to look out for:
- Make sure your data is OGC-valid (
STIsValid() method), or you can crap out your entire featureclass. Never found a elegant way to handle this because we didn't want to alter the data, so we just logged the error and looked into them on a case by case basis, which there weren't many
- Inserting and updating are two different calls, so you have to keep track of some sort of record ID - if it's already there then you update, if not you insert - all do-able in the stored procedure
Beginning Spatial with SQL Server 2008 is a good book to start out with as well.