Strategies for the Always Connected Windows Client Data Architecture

Question

Let me first say: this is my first post here, it is a little longer, and I have not done Windows Forms development for many years ... with this in mind, please excuse me if this is not a direct programming question and please refrain from me because I really need help!

I was asked to develop a Windows Forms application for our company, which is negotiating with a central (local area network) Linux Server, which hosts the PostgreSQL database. The application should allow users to authenticate with the system and then conduct normal transactions with the PG database. I usually suggest writing a web form application for Mono, but clients should use local resources such as USB peripherals, so that’s out of the question. Although this may seem unclear, my questions are listed below:

Dilemma # 1:

The application must always be connected. How should I structure my DAL / BLL - should it be on the server or with the client?

Dilemma # 2:

I read Client Application Services (CAS) and it seems like it is great for authentication since everything is through a URI. I know a .NET data provider exists for PostgreSQL, but not too sure if CAS will work on a Linux (Debian) server? Believe me, I would take my hands dirty and try myself, but I need to come up with a logical design before the resources are allocated to me for "trial purposes"!

Dilemma No. 3:

If the DAL / BLL should be located on the server, is it possible to somehow create data services and expose only these services for authenticated clients. There is a requirement (security) in which the connection string with the username and password in the database cannot be present on any client machines ... even if the security on the database side is pretty tough. I assume that the only way to do this would be to create various CRUD data service methods that appear in the ASP.NET application, and WindowsForms query the data or save the data in the ASP.NET application (via the URI) and they return the result or value . Will I take it right? Should I look for WCF data services? and will WCF work with a database other than SQL Server?

Thank you for taking the time to read this, but know that I am desperately looking for any advice on this! THANKS MILLION !!!!

EDIT:

I am also considering using NHibernate as my ORM

Answer 1

Some parts of your questions are complex and beyond my experience. However, in general, you can do almost everything that you have invested in the CAP theorem and the like.

DAL / BLL can generally be in any of the levels. I have put a lot of things in my database, and some at the middle level, but this should allow reuse in different environments that may or may not be your goal. The fact is that I carefully thought out the separation of problems that arise here, and which centralization of logic you want to place. The farther back, the more it becomes available, but this is not always a free compromise.

I am not completely familiar with CAS, but it looked like AJAX from what I saw on the MSDN website. This may be wrong, but if it is correct, then you have a problem in that such requests may be inactive, and this may be a problem if you need a permanent connection.

In general, depending on what you are saying, this sounds purely to make a two-tier rather than three-tier application, and have a DAL / BLL on the client, possibly supported by stored procedures on the server. You can then install PostgreSQL to authenticate against what you use on your network (KRB5 if AD is what I would recommend). This simplifies data access and allows you to manage permissions based on database authentication. Since you can authenticate users based on AD, you can set permissions accordingly.

One important consideration will be the number of connections. PostgreSQL has some places where every current connection needs to be checked and retried, and in some cases there may be problems with starting and breaking the connection. Therefore, pooling is one important decision. Regardless of whether you use pooling to improve performance, it depends on what you are doing, but I have seen cases where PostgreSQL handled 600 connections without serious problems.