how to Connecting Android Flutter app & asp.net site with sql server database

there are several ways to do this. In one case I used the jtds drivers and direct connected to sql server. No web server, no in-between system at all. This could work if you are to be on the same network as the SQL server. It can be quick and dirty - and no web system is required. However, this is not really the recommended approach. But I did write a sqlite->sql server sync routine based on this idea. So when you get back to work location, you on the same network (wifi) and thus the android could DIRECT connect to sql server. As noted, this is of limited use, and certainly not appropriate for a "real time" connection from the phone to some database say while traveling on the road.

But this type of direct connection is handy, and does not require any web site. But it DOES require a direct connection to the sql server. So off site, then a VPN is required for this setup.

This so called jdbc "direct" connection is VERY much like say using ODBC. (jdbc direct type 4 connection). I used this one:

http://jtds.sourceforge.net/

however, the preferred approach - due to "many" reasons (one being security), then the standard suggestion here is to:

Have the phone do "web service" calls to the web site, and the web site then hits the database for you.

So, if you scope out on paper, you might need say:

Get Active pick ups (say this is some kind of pick up system to pick up say bottles)

So the phone could have a local database for most operations. But on the web site, you would (could) build a web service call. Say "GetPickUps". That web service call would then pull from the database the active pending pick-ups and return the data to the phone (probably in xml, or better yet these days JSON format).

So, say when a pick is done? Then you might on the phone while displaying the pick up, yo have a button "done". When you hit done, you need to update the status of the pickup

So, this is kind of like building store procedures in SQL server. You build a few "web methods". So we could say build a web method called

PickUpDone    pickupID

So you call that web service called PickUpDone (and pass it the pickID), and that routien on the web site then would update the status of the given pickup.

What kind of web service call? Well, you can use SOAP + xml, but these days most use REST calls. That is just fancy term for a web site url. So, our pickup done REST call might look like this:

www.mycoolsite.com/WebServices/PickUpDone&?pickupID=12345

Now you did tag this as asp.net.

So, if in web side code you write and expose a standard C# (or vb.net) sub say like this:

<WebMethod()>
Public Sub PickUpDone(pickupID as integer)
   ' code here to update data base
End sub

At this point the above is ALL you need to do. in asp.net the above routine when marked + tagged as "web method" now out of the box supports soap, REST and will even return json or xml (without ANY changes to the above!!!).

Now in some cases? Well, you really don't know ahead of time what "set" of functions and web service calls you need. So over time some enterprising people decided this:

Why not create a web service call that takes any SQL update statement and run it! So there are a number of "popular" bridges or systems that you install on the web server, and then a client side that conforms to sending sql statements to that one web service. As a result, you can create new sql updates etc., and NOT have to write a new web service. (these are jdbc type 2 and 3 systems).

Most of these systems are written in PHP - I don't know if a jdbc driver and setup has been written for .net (and asp.net sites). So in place of custom web service calls that do a "specific" task, you can adopt a jdbc driver that simple passes and pulls sql statements from the phone to and from the web site "service" that in turn hits the database. In effect, the web service becomes a "sort of" ODBC type driver in which the code and logic to do the database updates occur from code on the phone. I think in most cases, your better off to break this out into a set of "tasks" and custom web service calls.

As noted, web service calls out of the box without ANY extra effort supports SOAP, post-backs with headers and REST calls - and does so really without extra efforts on your parts.

So at the end of the day? You can direct connect to the database in some cases - no web server in-between. But this has limited connection options (you can't be on the road and let everyone on the internet direct hit and talk to the database - so you go through a web site).

So you can go with a "general" approach. You install some code and support for a jdbc driver on the web site - and then all code and updates can be done from the phone client side.

But, just like it tends to be better to cook up and build a set of store procedures when using SQL server, the same applies here, but NOW talking about a set of web services you create on the web site which then in turn will be called by the phone. You better off to create these web services server side, since then the on-line web site can also consume and use that code you write. So both your web site and phones are thus using the same code base for a lot of operations.

In summary:

you can direct connect to sql server - no web services at all

you can write web service web calls

you can adopt a "general" web service that accepts sql commands 
from client side and passes that to sql server. So this is a "one time"
setup of one web service on the web site, and then it facilitates all calls from
the client side - in this case a phone. However, it tends to be far easier
to write server side code with the server side tools, and thus in most cases
writing custom web services calls is the preferred approach. (since you writing
server side code - and that can be used by other non Android phones or even
the web site code (JavaScript and ajax calls).