Problems with setting up an n-tier architecture

[Scott]

I thought I would stop hard-coding all my db commands and came up with (what
I thought is) an n-tier architecture to deal with data access. I have set
up a simple scenario to test:

I have an object named "SQLConnect.vb" which is supposed to be the generic
data layer and has a function in it called "RunSQLWithDataSet" and takes a
SQL string and a connection string. The idea is that a business object can
call it with the 2 pieces of information and return a dataset. This works
fine.

Taking it to the next level, I wanted to make a "RunStoredProc" Function to
call stored procedures. My thinking is that I would pass a 2D array to pass
the datatypes and values which would be passed to the stored procedure in
the function. In short: this is not working. Is there an example or
template for this sort of thing available? I can't believe I am the only
one to try this. Is there a better way? Is there ideas out there already
on how to architect this?

Scott

 

[M K]

Hmm... I don't know what code you are using, but off the top of my head,
your method would look something like this:
Public Sub RunStoredProc (inputCmdString, inputParamArray)
Dim SQLCmd As New SQLClient.Command(inputCmdString)
SQLCmd.CommandType = StoredProcedure
For Each _item In inputParamArray
SQLCmd.Parameters.Add(New
SQLClient.Parameter(_item("dataType"),_item("value")))
Next
End Sub

Something like that. What code are you actually using?

 

[Colin Young]

You are still hard-coding your DB commands using this sort of scheme. All
you are doing is duplicating the functionality of ADO.Net, and you aren't
even gaining independance from the underlying database because you still
have all the SQL commands in your business objects.

Check out www.llblgen.com for an example of how to write a good layer to
isolate your business objects from the DB implementation.

Colin

 

[Jason]

Scott,

Take a look at this link:
http://www.c-sharpcorner.com/Code/2002/July/GenericDataAccessCompActivator.asp

Also look at Microsoft's Data Access Application Block for .Net:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp

Good Luck