Data Access Layer Advice

[BenSCooper]

Dear All,
Any advice on this issue would be most appreciated as I really don't know what to do for the best.

As part of a wider project I'm writing code\stored procedures to perform the simple task of inserting a row into a SQL table.
I have my SP which accepts a param for each field in the table (approx 100 fields).
I'm looking to design a function which will pass these params to the SP. My question is, how should I best be passing these params from the Business Layer to the Data access function? My thoughts so far:

1) Have the function accept a param for each param that the SP is expecting e.g.
public Function (param1, param2,...param100)
However, it seems unwieldy forcing users to type in 100 params whenever they wish to use this function.

2) Create a class with the parameters defined and pass an instance of the class to the function.
However, I'm concerned that this is just giving me another object to update whenever the database changes, just so I can pass data to the data access function.

3) Run a SELECT query on the table in question to return a blank datatable object. The datatable can then be populated referencing the named columns for clarity and passed to the function.
However, I'm concerned that in my efforts to make the ongoing maintenance easier I'm suggesting a horribly inefficient method here.

To add to the problem I'm currently coding in Access 2003. Phase 1 is moving the tables to SQL 2005. Phase 2 will involve a front end rewrite in .Net. However, I would like to begin moving towards a .Net style of coding for elements I'm rewriting in phase 1 to make my life easier in the coming months.

Any advice on Access or .Net solutions to this query would really help me out as I can't get a clear picture of what the best practice here should be.

Thanks
Ben Cooper

 

[gmmastros]

You're likely to get a variety of responses here.

In my opinion...

Do not do #3. In a high transaction environment, this will kill your performance unnecessarily.

I don't like option #2 because someone could instantiate a class and only partially fill in the data and send to the database. This could lead to "hard to find" bugs.

I prefer option #1 because you are "forcing users to type in 100 params whenever they wish to use this function". I look at it this way.... suppose 2 years from now you add functionality to your app that requires you to add another parameter to this unwieldy function. You would be required to add a parameter to the stored procedure, add a parameter to the DAL function, and then also add a parameter when you call it. If you make all the parameters required, then you will get a syntax error at compile time. This will make it easier to identify places within your code that needs to be modified.

I have been working on the same application for 13 years now. There have been a lot of revisions to this application. When a user asks, "Can we add XYZ functionality?", my first thought is... Yeah, that's not too difficult to add. My next thought is, "where are all the places in code that need to be modified for this functionality?". Since I use option #1, it's relatively easy to find all the places in code.

But, don't take my word for it. I encourage you to wait for others to chime in with their own advice.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

#1 would be ok, but it can get pretty confusing as a developer to pass in 100 parameters to a function.

#2 I like because you can create properties of your object which allows easier coding for your developers. They can use a with clause and list out all of the properties on an individual line for example. However, if you were going to go this route, you might as well also look at just using a Typed Dataset since you will get the benefits of defined columns you would realize with the custom object, as well as data specific elements of the Dataset.

But really, if you are worried about having to make alterations to another object in code, just consider that a schema change to a database is not a minor thing itself, and doing something thoroughly usually pays off.

#3 I don't see this as being inefficient. If you use the .FillSchema function once on application startup or screen start up, it shouldn't impact your server.

If your application's sole purpose is to insert a single row into the database, then I would go with #1.

If the user is to insert or update multiple rows, I would go with the Typed Dataset.

 

[PRPhx]

As far as using method #1, a couple of questions need to be asked.
1)Is data required to be entered for all fields? Can fields be filled with default data if the user skips ovewr it?

2) Can any of the fields be populated from a list box or something similiar? I.E. a states field can be done using a list box. This reduces the amount of typing a user has to do, and it makes for consistant data.

3) And where will you validate each field? When the user leaves the field or just before the data is sent to the database? Doing the validation in either place has it's advantages/disadvantages.

As far as moving from Access to SQL Server, I would think about doing it sooner than later, before you have to deal with lotsof data.