Autocreate Data Access Layer

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!

Re: Autocreate Data Access Layer

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/useritems/BusinessObjectHelper.as p?forumid=322471&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" wrote in message
news:0A7799D8-1A76-4A8C-8D93-C82EBED1BA75@microsoft.com...
>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!

Re: Autocreate Data Access Layer

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

Thanks for your response.

"sloan" wrote:

>
> 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/useritems/BusinessObjectHelper.as p?forumid=322471&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" wrote in message
> news:0A7799D8-1A76-4A8C-8D93-C82EBED1BA75@microsoft.com...
> >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!
>
>
>

Re: Autocreate Data Access Layer

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.

"sloan" wrote in message
news:uFK%23My5UIHA.4684@TK2MSFTNGP06.phx.gbl...
>
> 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/useritems/BusinessObjectHelper.as p?forumid=322471&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" wrote in message
> news:0A7799D8-1A76-4A8C-8D93-C82EBED1BA75@microsoft.com...
>>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!
>
>

Re: Autocreate Data Access Layer

http://www.eggheadcafe.com/articles/adonet_source_code_gener ator.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




"Rbrt" wrote in message
news:3D26D602-F1DB-4C5F-9CD4-D82E267D8BE9@microsoft.com...
>I was wondering about performance. I guess I'll stick to code by hand.
>
> Thanks for your response.
>
> "sloan" wrote:
>
>>
>> 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/useritems/BusinessObjectHelper.as p?forumid=322471&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" wrote in message
>> news:0A7799D8-1A76-4A8C-8D93-C82EBED1BA75@microsoft.com...
>> >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!
>>
>>
>>