Autocreate Data Access Layer

[Rbrt]

I have a class with properties that correspond to the columns in a database
table. So for example the column BookTitle in table Books will have a
corresponding property BookTitle in the class Books.

I am writing a data access layer that creates a list of Book objects and so
I have to read the data from the table, and then loop through all the records
and set the properties of each instance of the Book class by getting the
corresponding value from the datatable, e.g....

If not dtBooks.Rows(intIdx)("BookTitle").Equals(DBNull.Value) then
BookInstance.BookTitle = datatableBooks.Rows(RowIndex)("BookTitle").value
end if

There are a lot of columns in the table and properties in the class and each
time I add a new column and property, I have to add new code to the data
access layer.

Is there any way to simplify the data access layer so that it can
automatically loop through the records and set the appropriate properties of
the class object just by looking at column names?

I've been trying to figure out how to do this using reflection, but it's
tough slogging.

Thanks!

 

[sloan]

You'd have to resort to reflection, which will significantly reduce
performance.

1. Code them by hand.
2. Use a Code Generator, to do the work that you would normally do by hand.
But this gives you static code. Its a good "start up" code..that you tweak
over time.
3. Use reflection, (which you're proposing).

I would NEVER suggest #3. Its too slow, and a bad architecture decision
from the get-go, IMHO.


If youre set on doing it...then here:
http://www.codeproject.com/useritem...22471&exp=0&select=1954453&df=100#xx1954453xx

That is my follow up post to the guy's article. Scroll to the top to see
his article.

...

 

[Rbrt]

I was wondering about performance. I guess I'll stick to code by hand.

Thanks for your response.

 

[randy.buchholz]

I use PowerDesigner (Option 2) and it's OR Mapping capabilities to generate
the DAL. You can easily create custom generation templates and get pretty
specific. When I need to add new columns to the tables of object I just add
them to my model, compare my new model to the old and the system will
generate new and modified code for the changes.

 

[Robbe Morris - [MVP] C#]

http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp

I've been using this for quite some time and it works wonderfully.
The key to its being able to perform well is that it keeps a static
copy of the reflection results the first time a method is executed.


--
Robbe Morris [Microsoft MVP - Visual C#]
AdvancedXL Server, Designer, and Data Analyzer
Convert cell ranges in Excel to rule driven web surveys
Free download: http://www.equalssolved.com/default.aspx