I have been tasked with building a new android app. I need to persist app data to an on-premise SQL Server. No data needs to be transferred from SQL Server to the android app, that is, I only need the C in CRUD. I'm not sure how I should set this up whatsoever. Can anybody help with the architecture? Will I need to persist the data to a local SqlLite DB first then run something to transfer to the on-premise SQL Server DB?
I've never used Azure or built a restful service in .NET. We have an old .NET web service (.asmx) that I would prefer to use for this. Is that possible?
So there are a few elements to this question. I'll answer in three parts.
1. Web (REST)
You will need (like you mentioned) a RESTful service to connect outside requests to the on-premise SQL server. I believe you can use .NET (.asmx) to create such an API (there are many guides you can find online). You could also use something like ASP .NET Core or more modern technologies such as Node.js, Ruby on Rails, etc.
You are going to need one endpoint for the "C" of CRUD. Whether this will be a PUT or POST endpoint will depend on the specific details of your implementation and use case. See this SO answer: PUT vs. POST in REST
You will also likely need some kind of authentication system to ensure only authorized individuals are accessing your database. For this you could try something like Microsoft's Identity Framework, some more custom authentication scheme (see this), or other libraries.
2. Android
On the android side, you are going to need to access the API through the internet. The easiest way to do this would be utilizing some kind of networking library (I would recommend OkHttp). Since network requests (hitting an API) can take time, it is also highly advised to do this off of the main/UI thread. Doing this in Android is made much easier through the use of a threading library such as RxJava. There are many examples out there on how to use OkHttp with RxJava (see this answer for example).
3. Caching
This is sort of an extra credit part. If you would like to cache create operations on the Android device (for example if you didn't have network), you would then need to use on-device persistent storage. If this isn't a requirement, you can skip this. For on-device persistent storage, I would recommend looking into the Android Room persistence library (which also has great integration with RxJava).
Just to be clear, you will only need to persist the data to a local SqLite database in the instance where you want to cache create operations.
Good luck!