[ACCEPTED]-Why should a developer use web services instead of direct connections to a db?-web-services

1. Security. You're not granting DB access 52 to anyone but web server/app user.

   This is 51 extra important when you have tons of users. You 50 avoid the pain of user/role maintenance 49 on DB side.

2. DB load reduction. Web service 48 can cache the data it retrieved from DB.

3. Database 47 connection pooling (hat/tip @Dogs).

   A web 46 service can use a small pool of permanently 45 opened DB connections. The helps in a variety 44 of ways:

   • DB connection pool is limited on 43 database server side.

   • opening a new DB connection 42 is VERY costly (especially to database server).

4. Ability 41 for fault tolerance - the service can switch 40 between primary/DR data sources without 39 having details of fail-over be implemented 38 by service consumers.

5. Scalability - the service 37 can spread requests between several parallel 36 data sources without having details of the 35 resource picking be implemented by service 34 consumers.

6. Encapsulation. You can change 33 underlying DB implementation without impacting 32 service users.

7. Data enrichment (this includes 31 anything from client customization to localization 30 to internalization). Basically any of these 29 might be useful but any of them is a major 28 load on database and often very hard to 27 implement inside a DB.

8. May or may not apply 26 to you - certain architecture decisions 25 are not DB acces friendly. E.g. Java Servers 24 running on Unix have an easy access to a 23 database, whereas a java client running 22 on a Windows PC is not database aware nor 21 do you possibly want it to be.

9. Portability. Your 20 clients may not all be on the same platform/architecture/language. Re-creating 19 a good data access layer in each one of 18 those is harder (since it must take into 17 account such issues as above-mentioned failovers/etc...) than 16 building a consumer layer for a web service.

10. Performance 15 tuning. Assuming the alternative is clients 14 running their own queries (and not pre-canned 13 stored procedures), you can be 100% sure 12 that they will start using less than optimal 11 queries. Also, if the web service bounds 10 the set of allowable queries, it can help 9 with your database tuning significantly. I 8 must add that this logic is equally applicable 7 to stored procedures, not unique to web 6 services.

A good list can also be found on this page: 'Encapsulating Database Access: An Agile "Best" Practice'

Just 5 to be crystal clear - some of these issues 4 may not be applicable to ALL situations. Some 3 people don't care about portability. Some 2 people don't need to worry about DB security. Some 1 people don't need to worry about DB scalability.

In my opinion, you should not automatically 4 be exposing your database as a web service. If 3 it turns out you need a service to expose 2 your data, then write one, but not all database 1 access should be done through web services.

1. There is no reason why a database connection should not be secure
2. You can encapsulate the database in a data access layer (possibly Entity Framework)
3. Web services are no easier to consume than a well-written data access layer.

• Web Services form an API, defining the allowed interactions between external systems and the application's data.
• Web Services decouple the database from external interactions and enabling the data layer to be managed independently of those outside influences.
• Allowing access only by Web Services ensures that application logic gets the chance to execute, protecting data integrity.
• Web Services allow the most appropriate authentication/authorization measures to be taken, as opposed to a database requiring a username and password/table level privileges.
• Web Services provide an opportunity for automatic service discovery and configuration to be used.
• Web Services traffic can be encrypted for transit over unsecured networks. Don't know of any direct DB connection solutions that enable that...?

Most of these points go for any formal API, not 1 specifically Web Services.

Writing a web service that simply wraps 19 calls to stored procedures seems to be a 18 misguided approach to designing a good DAL. More 17 than likely, your stored procedures are 16 legacy code left over from a older client-server 15 systems i.e. business rules are buried in 14 the SPs. If that's the case, you're really 13 attempting is creating a silk purse from 12 a sow's ear.

Moreover, your adding a SOAP 11 message protocol layer that adds a performance 10 hit to web apps that have been 'coerced' to 9 dating this 'pig'. I'm working a project 8 right now where our new MVC-4 app has been 7 instructed to use such a DAL. We have the 6 burden of having to change both the WebMethod 5 signature and the SP signature whenever 4 a new user story comes along necessitating 3 such changes; which for us, is every single 2 sprint. Inherent in such a passthru approach 1 is two tightly coupled tiers.

1)Headache of maintaining database is reduced 11 from developers side so that they can focus 10 only on developement.

2)Web service supports 9 communication of different platforms (operating 8 systems like windows,ios,android etc) in 7 a very easy and effective method. for example 6 consider a situation when android application 5 & ios application wants to communicate 4 to a website which is java based so for 3 communication of all the three things webservice 2 is the best solution instead of maintaing 1 three different databases.

In general

1. Web Service level promotes reuse of common data requests for multiple applications
2. Web Service can be set up with version management which deflects many issues arising from application level development. For example if I am new to a project which existing application do I use as a good model for configuring my application to use existing database sources.
3. Web Service has evolved to allow flexible options for sending requests and getting response results back in a common format such as JSON by using a simple URI which means that client applications can be developed using a more common standard that encourages dependable uniform interfaces.

I am just getting stared with 43 ASP.NET Web Api and plan on making data 42 services first.

I have recently been focusing 41 on .NET MVC web applications with the use 40 of the entity framework.

1. If you already use MVC the Web Api also uses MVC with the Api controller so the learning curve to build the services are fairly painless.

I recently found 39 myself in a frustrating predicament with 38 an MVC web app that I was building originally 37 based on Oracle stored procedures. The original 36 version as Oracle 9 or even earlier which 35 presented another problem with Visual Studio 34 2012 pushing a more modern connection factory 33 approach with load time assemblies finding 32 the right dll files to use based on web 31 config connections and TNS names.

Attempts 30 to connect to the database failed with 'no 29 longer supported' error messages. Out of 28 curiosity I downloaded Oracle 12c and made 27 some application level connections that 26 worked nicely with my TNS names and the 25 load assembly dll and I was able to work 24 with Oracle with no problem.

There were some 23 web services built that were working with 22 connections to the older Oracle version. They 21 were built with methods that were specifically 20 mapped to selected tables however to my 19 disappointment. I would have to write my 18 own.

I was told that the group that was responsible 17 for maintaining the Oracle databases that 16 they would be writing new stored procedures 15 to replace the older ones that I was using 14 to abstract the client interface and business 13 logic layers.

So my first thoughts were 12 that all of the common data requests such 11 as filling up drop down list or auto completes 10 with enterprise wide data be done through 9 data services that would call the Oracle 8 stored procedures. Why repeat that process 7 over each application and have each developer 6 struggle with configuration and version/load 5 assembly, TNS issues?

so....

1. For multiple database server issues such as using Oracle stored procedures in a .NET MVC application that might usually be using EF for the SQL Server data usage why not push those headaches up to Web Api service methods where those configuration issues can be isolated.
2. Again the client interfacing can be done using JavaScript, JQuery and JSON which you are already using if you are doing this using Web Api to make SQL Server data requests.

I am an Application 4 Developer/Analyst and not a DBA so my perspective 3 is one from experience with the never ending 2 frustration of having to constantly modify 1 applications when database tools evolve.