Seems like you are in afterburner, crossing a lot of territory in one post
....

1. You can write a "smart client" app in .Net. Get the SDK from Microsoft,
it's free. You will be writing to the Compact Framework instead of the full
framework.
2. You can xcopy the app you create to the client (PocketPC, Smartphone,
etc.). Contingent of course upon the correct framework being installed, as
well as any prerequisites such as SQLCE, which leads to ....
3. You can "communicate" from the on-board database back to the server in a
multitude of ways. You can use SQLCE or SQL Mobile and create a replication
app (which does indeed have pretty good security), or you could write an app
that uses webservice calls with SSL.
4. All of what you mention is do-able, but isn't easy as it seems -- of
course nothing on the Microsoft platform is. Most everything to do with
smart device development is a "gumption trap", burning off untold hours in
order to resolve ridiculously trivial issues generated by wacko sidebars
like that psycho ActiveSync, the paint-drying emulators, the threading
requirements of the 2.0 framework, the decrepit sluggishness of VS2005, and
the fact that CF is a subset of the full framework, which inevitably seems
to have left out precisely the class, function, or propery that you so
desperately need at some crucial point ...

I don't write ASP apps, so I'm not sure I interpret the cause and effect as
you do. While there are some developers creating "smart device" apps, it
seems to me that there are TONS more developers creating ASP.Net apps. In
fact, I get a Monster update every day and almost to the ad, employers are
looking for people developing for ASP.Net. Also, the beauty of using an ASP
is that you can control the data and the app, so it would seem to me that
everything is still headed toward ASP apps. The smart client apps will
ultimately be simple consumers of the webservices.