I can't change the schema of this database. It's an ERP database with its own triggers and its version updates would squash mine. Since Notification Services is going away or gone, is there any other way to do this? Perhaps some low level COM API or something in the .NET SQL Server Management APi?
Since you can't add triggers I think your only option is timed polling. When you use polling you're not actually receiving updates but querying on a semi-frequent cycle for changes to data which basically ends up working like a notification.
This is not trivial to implement but also not that difficult. The complexity comes from the number and structure of tables that need to be polled. The best way it works is to query on a DateTime column in each table (if one is available) and comparing the last max time against the max time in the current query. If the two are different then a change occurred and you fire off whatever biz logic you need to.
If there is no usable DateTime column you could also test for primary key changes (compare the max of an autoincerementing field.) For inserts the DateTime and int PKey comparisons will both work. For updates only the DateTime comparison will work (no pkey increment on update) but for detecting deletes it gets much more interesting if you actually remove a row from a db since there is no DateTime change and no pkey increment since the data that you key on went away. (Counting a table's rows will break logic if the same number of items get inserted and removed between polling queries.)
The System.Data.SqlClient namespace has some classes that might help such as SqlDependency. Beware though:
SqlDependency was designed to be used in ASP.NET or middle-tier services where there is a relatively small number of servers having dependencies active against the database. It was not designed for use in client applications, where hundreds or thousands of client computers would have SqlDependency objects set up for a single database server.
This means that if you use SqlDependency you should make sure that it is a server component that uses some mechanism to notify clients of changes (pushing), or publishing easily-queryable data for clients to "pull" on.