How do I create a generic overall .NET programming structure for my company?

Question

I'm in a company that writes their own business data applications when there's no good off the shelf alternative. Most often it's - a login screen - some screens that are mostly visualizations of SQL Server tables - some reports

In the past they've used MS Access. These are not exactly the most elite coders but they mostly get how to structure a database and what they want for a UI.

We'd rather have a generic login screen, menu screen, etc. that we can plunk into an app and get started. This should help with dev speed and maintainability.

Of course there's more than one way to do this and at the core of our problems seems to be data access. Options seem to include:

1) GUI datasets/table adapters like the videos on ASP.NET show you how to do. Some of us have been doing a lot of this. Good for dev speed. Sometimes the data connections change without you noticing. The huge method signatures (one parameter per field on an insert or update) is error prone. Sometimes the parameters don't detect properly. Dynamic parameters (e.g. report with optional filters) is very difficult.

2) Extremely structured n-tier style. The grid's datasource is a collection of objects for example. The presentation layer calls a static method in the object A which calls a data access layer method which instantiates A for each row returned from the database. A needs to have get/set methods and internal variables and constructor variables for every field in the table. Each field gets typed in so many times. Nothing is bound to anything ever. Likely overkill for apps this simple.

3) Generic tables drive everything with generic functions that loop through the list of fields to dynamically create UPDATE and INSERT statements. Selects are mostly SELECT * type of things. Rapid development but the lack of strong typing scares me. Seems like SQL Injection landmines are everywhere but I'm assured that doubling up the quotes is all that's needed to avoid that.

There must be more options out there but I don't know what they are. Do you have any suggestions for me/us?

Answer 1

Table Adapters are ancient. Not to say that they're not still useful, but I wouldn't personally use one without a good reason to. Linq to SQL was made precisely for this kind of rapid development. Way quicker to get set up than datasets, and also much easier to query and update.

There's also Entity Framework and NHibernate, but those are more targeted at larger domain-centric applications, not quick-and-dirty data access/updates.

Answer 2

In this case, it seems your issues resolve completely around data access, and you are mentioning quite antiquated methods in the .NET space.

Have you taken a look at LINQ-to-SQL or LINQ-to-Entities? Both of those technologies address most if not all of your issues, with the latter being the only solution of the two if you are not using a SQL-Server based back end.

Answer 3

1 & 2) Look into ASP.NET MVC

3) Look into LINQ to SQL

Answer 4

Sounds to me like you are looking for some sort of scaffolding tools. There are plenty of them out there. SubSonic has one for sure.

Here is a link from MSDN

Answer 5

Have you looked into Dynamic Data? It's awesome for a nice, clean DB CRUD solution with almost no time investment (if the DB is structured properly).

You cas add a login screen or just use Windows Integrated authentication for consistency.

Also, the learning curve is almost nothing. I did my first site in the 20 minutes it took to watch the intro video, and it's so easy even the most basic beginner-level programmer can do it.

Answer 6

Have you seen ASP.NET Dynamic Data?