WinForm fat client: Architecture decisions: Did I make the wrong ones?

Question

I have a .NET Winform Click-Once-deployed fat client that talks to three databases, 2 SQL Server databases and a Oracle one.

The first SQL Server database, I will call the Master. I wanted add more tables and columns to the Master database but I was told I would not be given rights to do so, that it was non negotiable. So, I decided to add a new SQL Server database, which I will call the Extensions database to store additional information that I would like to place in the Master.

Since I did not have rights to place the Extension database on the same server as the Master, I created the Extension db on another server and created a db link from the Extension database server to the Master's database server. This allowed me to perform queries that spanned the two databases. I also had a third database, an Oracle one, that I would occasionally connect to.

Because I thought that connecting to the Oracle database requires installing Oracle drivers not likely to be on the end user's PC, I decided to have the WinForm client call a web service and the web service would return the results. For consistency sake and because my gut told me that the database access operations should be performed from one place, I took the same approach when the fat client needed to talk to the SQL Server databases, namely, they went through a web service.

Now I am questioning whether I am taking too big of a performance hit by first having to call the web server and then have the web server call the database instead of opening up a connection on the user's PC in the fat client and connecting directly to the SQL Server databases. Note that the app which I am referring to is an unofficial utility that needs access to these three production databases but is not permitted to actually run in production, so I suspect that the normal high-speed pipes between my development web server and the production database do not apply here.

For best performances, should my Winform app be establishing a connection directly to the SQL Server databases? What are the trade-offs?

Is it still true today that additional drivers are need to connect to an Oracle database or is all of that in the .NET framework now and I could connect directly to even the Oracle database, too, if i wanted?

Also, I found that using the link server is slower than creating two separate db connections and pooling the data together. My best results came when I initiated two a-sync queries and then combined the data together as if it was returned by one query using a db link. However, this leads to maintenance issues and led me to regret this approach when I needed to modify the SQL do joins across databases.

Answer 1

I think if you have configured the Oracle server as a linked server in your SQL server you don't need separate Oracle drivers. You can just use your SQL server as the data source.

Try to create stored procedures to get faster result. If you use SQL management studio you can check the execution plan to optimize your stored procedures (configure indices).

I don't think Webservices are needed in this scenario.

Answer 2

I don't know your performance requirements but a wcf web service is fast. I see nothing wrong in having a middle tier (your web service) for database access. It is also very secure.

Answer 3

I have been into developing enterprise applications for couple of years and that has made me think that middle tier(web services in your case) always makes sense and connecting to DB from a UI directly is always a NO for me. I don't think you have made a wrong decision. In the long run having a middle tier does make a lot of sense. Also note that, the web-service you have built can be used by different applications.