Writing a generic DAL

[Greg]

I have done this with some success. Updates work generically, deletes work
generically, all using DataAdapter and passing DataSet Changes to one
method.

My problem is getting Inserts to work generically. The issue here in Output
parameters. How do I flag a datatable or something else coming in with the
DataSet that when the Datatable contains an Added Row and its looping
through the insert code, building input parameters it also needs to build an
output param and here are all the values needed for the new param
constructor?

Currently, I build all the Insert\Update command parameters in the
RowUpdating event like this:

Dim col As DataColumn
Dim drw As DataRow = e.Row
Dim myParam As SqlParameter

Select Case e.StatementType
Case StatementType.Update
For Each col In drw.Table.Columns
Try
' check the original row against the current row
to see if they are different
If Not (drw(col,
DataRowVersion.Current).Equals(drw(col, DataRowVersion.Original))) Then
' build a parameter for the changed column
myParam = New SqlParameter("@" &
col.ColumnName, drw.Item(col).ToString)
e.Command.Parameters.Add(myParam)
End If
Catch ex As Exception
Throw New Exception("Exception occurred in " &
className & ".OnRowUpdating Event. Column: " & col.ColumnName & ". " _
& ex.message)
End Try
Next

Case StatementType.Delete
For Each colName As String In PrimaryKeys
myParam = New SqlParameter("@" & colName, drw.Item(col,
DataRowVersion.Original).ToString)
e.Command.Parameters.Add(myParam)
Next

Case StatementType.Insert
For Each col In drw.Table.Columns
Try
' build output params
If col.ColumnName = "names_id" Then
myParam = New SqlParameter("@newid",
SqlDbType.VarChar, _
6,
ParameterDirection.Output, _
False, CType(0,
Byte), CType(0, Byte), _
"names_id",
DataRowVersion.Current, _
Nothing)
e.Command.Parameters.Add(myParam)
ElseIf col.ColumnName = "control" Then
myParam = New SqlParameter("@ncontrol",
SqlDbType.VarChar, _
6,
ParameterDirection.Output, _
False, CType(0,
Byte), CType(0, Byte), _
"control",
DataRowVersion.Current, _
Nothing)
e.Command.Parameters.Add(myParam)
Else
myParam = New SqlParameter("@" &
col.ColumnName.Remove(0, col.ColumnName.IndexOf("_") + 1),
drw.Item(col).ToString)
e.Command.Parameters.Add(myParam)
End If

Catch ex As Exception
End Try
Next
End Select

So, as you can see, the Insert has hard code for the output params. I want
to get rid of this so I can send any DataTable insert through this
eventhandler. Currently, it only supports 2 of my Datatables, one with
'control' as the PK and one with 'names_id' as the PK.

 

[Michael Lang]

I have done this with some success. Updates work generically, deletes
work generically, all using DataAdapter and passing DataSet Changes to
one method.

My problem is getting Inserts to work generically. The issue here in
Output parameters. How do I flag a datatable or something else coming
in with the DataSet that when the Datatable contains an Added Row and
its looping through the insert code, building input parameters it also
needs to build an output param and here are all the values needed for
the new param constructor?

Currently, I build all the Insert\Update command parameters in the
RowUpdating event like this:

You may find some help here:

http://sourceforge.net/projects/genadonet

Michael Lang, MCSD

 

[Frans Bouma]

I have done this with some success. Updates work generically, deletes
work generically, all using DataAdapter and passing DataSet Changes to
one method.

You can't write a generic dal. Period. Reason? Ever tried to pull out
rows with user defined types from an Oracle database using .NET? Tried to
handle REF CURSOR resultsets (more than 1) in the same way as you will in
SqlServer?

Simple C R U D procedures is not the problem, a 'generic' dal falls
flat on its face when it comes to details. That's not the fault of the
'generic' dal, it's the fault of the differences in databases that can't be
abstracted away in a generic dal, due to limits in .NET (user defined type
support due to limitations in dataset/datatable) and the flaws in the design
of the database exception tree in .NET which is awful.

FB

 

[Steve S]

SO...... I should save my efforts... and throw away my code that works
across three different databases.... because your having problems with
user defined datatypes in Oracle ? THANKS.... I shall quote this in the
staff meeting this week.

For my two cents.... if you design an application around the need to have
special user defined datafield types thta can't be stored by several
different vendor products.... maybe you should look at designing the
system a little differently.... as you are locking yourself into one
solution......

Not a good choice in today's world..... and maybe more importantly.....
how long will it be before someone up in management decides...... "We need
to project this out to our customers"... ?

If your application really does require such special requirements.....
then why would one even think that something as pase.... as a GENERIC DAL
would be good enough for such an application ?

So my take on this is...... YES TOTO... THERE IS A KANSAS..... and for
those of us that have thought they found it..... (or are content to think
we have found it)..... it cost us alot of TEQUILA to find it.....

 

[Frans Bouma]

SO...... I should save my efforts... and throw away my code that
works across three different databases.... because your having
problems with user defined datatypes in Oracle ? THANKS.... I shall
quote this in the staff meeting this week.

What's your problem? simple ANSI SQL queries will work on a variety
of databases, but like I said, it's not that, it's the details that count.
When you're talking about 'generic' it should be generic ALWAYS, otherwise
it is only generic in some areas.

For my two cents.... if you design an application around the need to
have special user defined datafield types thta can't be stored by
several different vendor products.... maybe you should look at
designing the system a little differently.... as you are locking
yourself into one solution......

Erm, a 'generic' solution can handle all kinds of databases, right?
Or is 'generic' the name of the biggest common subset of functionality
supported by all the databases targeted by the generic solution? I don't
call that 'generic', sorry

FB

 

[Steve S]

What's your problem? simple ANSI SQL queries will work on a variety
of databases, but like I said, it's not that, it's the details that count.
When you're talking about 'generic' it should be generic ALWAYS, otherwise
it is only generic in some areas.

AH... a 100% purest.... if it doesn't handle 100% of the requirements...
than it should not be represented as GENERIC... maybe that is a fair
statement..... YOU WIN!!!!

Then so is.....for every rule... I can find at least two exceptions.... that
will occur at some point in time..... but that should not stop one from
implementing a solution to handle the other 95% of the events.... it just
means that you should know how to identify and handle your exceptions....
the other 5%

The practical aspects of building ONE item to handle every possible aspect
of a DALs task would keep someone employed for many years..... building
something has NO practical implementation or carry so much extra baggage..
it becomes useless or impractical to implement due to hardware requirements.
And the ROI on such a project?

We haven't even begun to hit on the topic of SPs vs dynamic SQL.... and the
ROI of each (which should also include the TCO). I'm sure there are some
very pointed and well defended positions on both sides of the topic....

My point is.... a system can be built to handle the vast majority of all DAL
requirements. Since I can't use the term Generic... this Universal Data
Adapter (UDA) could handle 95% of all enterprise data access
requirements...(ours does)..... has a very good ROI as it handles the vast
majority of all the update, insert and delete commands from ever being
written.... Therefore, I can focus my junior developers with building
business objects, accessing databases and I get to review a minimal number
of SQL statements for efficiency...

But then again... we use industry standard field definitions..... and some
of them include LOB and VARCHARs..... guess I haven't pushed the systems to
the limits of where I would need something more....

Since you asked........ As for my problem ? Should I make an appointment to
discuss this ? And will I be charged by the hour ? If so... hopefully it
won't be much... as I have already spent my two cents (see below)

CHILL.... and take a look at what I'm saying..... and realize that there
might well be a place for such a component..... from the other discussions
previous posted in the group..... it seems as if I'm not the only one that
has had the same thoughts..... about building a UDA......

OH WELL.. so the UDA might need an adapter.... in certain cases..... so does
the one from IBM

PS--- have no fear... I haven't quit the day job to take up comedy.....

 

[Greg Robinson]

Frans, I should have stated up front that this will always be for SQL
Server.

 

[Frans Bouma]

Frans, I should have stated up front that this will always be for SQL
Server.

Ah, so 'generic' is not about 'support whatever database you like',
but a dal you can use in more than 1 project targeting whatever catalog in
SqlServer.

Tough call... 'generic DAL' in the sense of 're-use it over and over
again' is also very hard to do, it still requires a lot of customization
before you can re-use a library that implements DAL functionality.

FB

 

[Patrice Scribe]

I'm not sure to understand. Do you want an output param for each PK column
in the table ? In this case why don't you find out the PK as you do in your
delete type statement ?

You could also deriveparameters (and cache this) or even if SQL Server just
query for the SP parameters at startup...

Patrice

--

 

[Greg]

Correct, and I thought about the PK or cache approach. In fact, I am
already using the cache approach in other part of our DAL. However, I would
like to avoid the round trips to SQL Server. Especially since we will be
using the DAL remotely.

I basically want an output param built in the DAL for each sproc that
returns an output param, and, I want the DAL to be smart enough to know it
needs to build an output param. So, IMHO, something about the
DataSet\DataTable passed into the DAL,or some other param to the DAL
(Updates only) needs to describe this output param. What I am messing with
know is a custom structure that stores all of the param info, then pass this
with the Datatable in the ExtendedProperties.

 

[Patrice Scribe]

Do you really need a custom structure ? You could just use
Information_Schema.Parameters. It's probably not different than pulling this
out from a custom structure (that you'll have to maintain separately).

Patrice

--