Newbie: MS Access frontend to ASP.NET conversion 2

[pharcyder]

Hello!
I am trying to "convert" a bigger MS Access application which currently uses MS SQL Server 2000 as the database.
The main reasons for the change are so I don't need to distribute the Access frontend to each of the 30 users (whenever I make a small change in a report or added a new field) and the relatively bad usability of the MS Access reporting services, as currently only 2 users are capable of creating own views (or even reports) for reporting, when they need perhaps 2 or 3 new queries in a week (which are also littering the database as there is no way to store the views in another place via the MS Access frontend as in the database).

Okay, now I know how to use HTML, I know CSS and I know how to program, besides using the crappy VB for apps in Access and writing stored procedures and views for the current application.
I know and used the old ASP and some PHP before .NET but of course you can't compare them anymore.

As I am used designing forms in Access I just started to drop FormViews in an empty ASP.NET page and directly connected them to the SQL database via a SqlDataSource, then arranged the labels nicely and added a dropdown box for navigation and voilá: It nearly looks and behaves like the Access frontend, and if I nicely format the EditItemTemplate to look the same as the ItemTemplate this thing would be basically usable.
That is what I wanted, but I soon felt I am missing something, as it is really annoying to add a new field to the "designed" templates if I forgot one (the wizard naturally wants to delete the formatting and so I have to add them manually).

Is there another way doing this or is this the common route?
Do I need to add a second tier, as, is it advisable to directly connect to the database and use the SqlDataSource connected to the FormViews?
Or should I use views or stored procedures to do this?

And secondly: Is it the common route designing and formatting the templates by hand or are there other methods, like storing the design data in a separate database or table and then creating it dynamically?
(I am currently using a stylesheet and trying to avoid using the automatic formatting)

So, could you please advise me if I should continue like this or if you see any problems with this or if there are other methods which would help me later if I used them from the start.

Sorry for the long read, but I felt to better add a little more information...

Thanks in advance!

 

[jmeckley]

Do I need to add a second tier, as, is it advisable to directly connect to the database and use the SqlDataSource connected to the FormViews?

What you currently have was designed using the RAD (Rapid Application Development) methology. This is what VS promotes with all the "neat" controls. It's a way to quickly develop small apps with the least amount of coding.

There is nothing wrong with this method, however in larger projects this can become a maintance nightmare as you have duplicated code through out the project.

You could seperate your code into Data Access, Business Logic and Presentation layers.
Your DAL could be typed datasets.
Your BLL would create instances of the DAL objects required and preform custom logic against the returned data.
Your PAL would call the BLL methods to get the data. You could use the ObjectDataSource as your binder between the BLL and the Grids/Views.

I believe this is the MS way.

Or should I use views or stored procedures to do this?

This is an age old debate. ultimately it comes down to this. security and preventing sql injection attacks. Whether you choose dynamical sql or stored proc remember to use command parameter objects to prevent sql injection attacks.

Is it the common route designing and formatting the templates by hand or are there other methods, like storing the design data in a separate database or table and then creating it dynamically?

this can be done either way: design time (declarative syntax) or runtime (dynamically add controls). How this is done is totally up to you. just remember to add your controls in the OnInit event (don't check for postback) then bind data, set visiblity... all that stuff in the OnLoad event.


Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[AlexCuse]

Jason -

This was an excellent post. Very well thought out, and I am sure worthy of some special recognition

Alex

Ignorance of certain subjects is a great part of wisdom

 

[pharcyder]

Jason, many thanks for your elaborate answern, this helped me a lot!