object relational mapping

[Guest]

Anyone got any ideas/sample code on how to construct classes which map to
database tables, in particular the CRUD type code. What I had in mind so far
was to have a constructor in the classes which takes the primary key of the
database table as a parameter and calls a stored procedure which will run a
select to retrive the relevant row of data. These database values will then
be set to the property values in the class (assuming one property per column
in the database table).

There will also be a persist method which would access the values of all the
properties and then call a stored procedure which runs an update statement.

Am I on the right lines?

PS I know there are object-relational mapping type products but for various
reasons I have decided not to use one.

Thanks

 

[Guest]

You are on the right track. The question comes down to how you encapsulate.
It is easiest, if possible, to have a DataSet underneath the hood, as it
gives you the ability to treat it as an object (expose the individual
properties) while still being able to take advantage of ADO.NET. This is not
always possible, but look at most ObjectDataSource samples for .NET 2.0 and
you will see that most use DataSets underneath the hood.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Guest]

Scott,

A couple of things to keep in mind:

1. You will need a way to handle concurrency issues.
2. You will need a way to retrieve database-generated values (autonumber,
identity, timestamp, etc) for new rows.

Kerry Moorman

 

[W.G. Ryan eMVP]

Kathleen Dollard has a book on Code Generation in .NET
http://www.gendotnet.com/ (here's her web site). She did one of the best
jobs discussing OR/M that I've seen any author do in any book - it's
phenomenal - honestly. There's 1,000 reasons to pick up that book but the
OR/M Chapter alone justifies the price.

THere are some open source OR/M Tools that you could dissect for ideas.
However if you're seriously going to use OR/M, I wouldn't write off the
commercial ones, you will probably spend as much money even getting started
in ORM as you will by buying either of the best, Deklarit or LLBLGenPro - if
nothing else I'd recommdn downloading them and seeing for yourself hwo
productive you'll be immediately.

 

[Frans Bouma [C# MVP]]

Anyone got any ideas/sample code on how to construct classes which
map to database tables, in particular the CRUD type code. What I had
in mind so far was to have a constructor in the classes which takes
the primary key of the database table as a parameter and calls a
stored procedure which will run a select to retrive the relevant row
of data. These database values will then be set to the property
values in the class (assuming one property per column in the database
table).

that's very basic indeed, but single row fetches work more or less
that way. I'd drop the procedures though, it requires you to keep a lot
in sync with one another.

The real problems begin when you want to filter on objects (Get me all
customers with orders in June!) or you want to fetch a customer, its
orders and the order's orderlines, for orders in June. Or you want to
save a new customer, its new orders and orderlines, in a transaction,
validated and identity values read back and synced automatically, and
everything is saved in the right order.

There will also be a persist method which would access the values of
all the properties and then call a stored procedure which runs an
update statement.

Am I on the right lines?

yes, but that's not bringing you very far. As soon as demands arise to
get more functionality, you'll find yourself writing a lot of code. I
know, I've spend the last 3 years writing an O/R mapper. Full time,
that is.

PS I know there are object-relational mapping type products but for
various reasons I have decided not to use one.

Well, it's your time of course. I really don't see why you would roll
your own, as it's a waste of time, because you'll make the same
mistakes all those O/R mapper programmers already made in the past and
you'll end up AT BEST with an O/R mapper layer which is as good as the
very first beta version of an established O/R mapper, solely because
it's not your main work. O/R mappers are hard to write as you need a
lot of code to get the abstraction which makes them so easy to use.

I'd be very interested to hear what your reasons are to spend at least
6 months full time on a persistence layer instead of getting one from
the shelve. Hint: ask yourself: do you write your own grid controls as
well?

Frans

--

 

[Guest]

Thanks for the reply

Reasons that put me off OR mappers were as below, if you can give some
rebuttal I would be interested. I have not written them off entirely just was
a bit hesitant at this stage.

1. Performance - would the dynamic sql generated by an OR mapper be as high
performing as hand crafted stored procedures

2. Security - would the OR mappers protect against sql injection the same
way parameterised stored procedures would

3. Vendor stability - if the vendor goes out of business am I stuck with
something that would not be any use for eg. future versions of visual studio

4. Flexibility - would the OR mapper give me all the flexibility I need

 

[Frans Bouma [C# MVP]]

Thanks for the reply

Reasons that put me off OR mappers were as below, if you can give
some rebuttal I would be interested. I have not written them off
entirely just was a bit hesitant at this stage.

1. Performance - would the dynamic sql generated by an OR mapper be
as high performing as hand crafted stored procedures

Yes, and often faster. Two examples:
- update queries contain only the fields actually updated. This means
that you don't have to write stored procedures with nullable parameters
or update procedures which update all fields.
- prefetch paths. Say you fetch 10 customers, per customer 10 orders
and per order 10 orderlines. With procedures, how many queries will you
actually execute? With prefetch paths in most O/R mappers, you need 3
queries, one for each type. For procs, you normally execute 1(for
customers) + 10 (each customer's orders) + 100 (each order's
orderlines).

You could opt for 3 procs as well, but you then would be in for a lot
of fun as you then need to write the merging code as well

2. Security - would the OR mappers protect against sql injection the
same way parameterised stored procedures would

Almost all O/R mappers use parameterized queries, and use parameters
for every value passed into the query. This thus means that no value
can ever become part of the actual statement, which thus means that
there is no way you can alter the SQL statement through the value.

Furthermore, using O/R mappers you don't have to fall back to string
concatenation yourself when you want to write a search routine. I've
often seen stored procedures which performed a generic search and under
the hood performed string concatenation, but _without_ parameters!

3. Vendor stability - if the vendor goes out of business am I stuck
with something that would not be any use for eg. future versions of
visual studio

That's always a thing to consider but it's with _EVERYTHING_ you use.
Ask the VB6 programmers how happy they are about MS killing off their
language . A big vendor is no guarantee something will last.

Most O/R mapper vendors have sourcecode available for their customers,
we do that too.

4. Flexibility - would the OR mapper give me all the flexibility I
need

An O/R mapper is about providing convenience for data-access. This can
give you a lot of flexibility in one area and limitations in another
area, depending on the architecture of the O/R mapper. I can assure
you, the more flexibility build in, the more time it takes to write
that code. So if you want to write the most flexible O/R mapper there
is yourself, it will take a lot of time and work. And even then, with
procedures and datasets, there are also limitations and inflexibilities
to work with.

I'd say: download a few demos/trials/free ones and check them out and
invest a day or 2 with them, and who knows you'll find one who actually
matches what you're looking for and you save yourself a lot of work and
time!

FB

--

 

[W.G. Ryan eMVP]

You weren't this nice to me when I brought up the issue ;-)

 

[Frans Bouma [C# MVP]]

You weren't this nice to me when I brought up the issue ;-)

I weren't nice to you? Hmm, must have had a really bad day then . I
wholehearthy appologize, my friend

FB

--

 

[Cor Ligthert]

Frans & Bill,

What is "relational mapping", a kind of love letters?

))

Cor

 

[Frans Bouma [C# MVP]]

Frans & Bill,

What is "relational mapping", a kind of love letters?

hehehe

Though, Cor, why would a NERD consider 'relational' being anything
ELSE than something about databases?

FB

--

 

[Guest]

hmm....There are lot of ORM tools available. I have used nhibernate which you
can download for free from sourceforge.net. The tool is really cool feature
and I have successfully implemented it in production. Not even a single
problem with performance yet.

You can download it here:

http://sourceforge.net/projects/nhibernate/

Tutorials at:
http://nhibernate.sourceforge.net/

http://www.theserverside.net/articles/showarticle.tss?id=NHibernate

http://www.theserverside.net/articles/showarticle.tss?id=NHibernateP2



To compare different tools in the market go to:

http://c2.com/cgi/wiki?ObjectRelationalToolComparisonDotNet


HTH
Rajesh Meenrajan
MCSD.NET
http://meenrajan.blogspot.com

 

[Frans Bouma [C# MVP]]

To compare different tools in the market go to:

http://c2.com/cgi/wiki?ObjectRelationalToolComparisonDotNet

What's the use of comparing tools if there are just OSS tools listed??

FB

--