Am I missing something?

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

I have a vb.net database with access backend. Every time I add a new field
in the db, I open a separate project, run the data adapter wizard on the
table, copy the code for all four commands (select, delete, insert, update)
and paste into the data layer class for the relevant table in the main
project. In the main project I then change the dbdate types to date types as
former give error. I then delete the table from dataset and manually drag it
again from the server explorer, so it gets the new field. I then set
defaults for all Boolean fields to false in the table in the dataset,
otherwise it gives error.

Am I doing something wrong or is the above the only way to do this?

Thanks

Regards
 
John,
Am I doing something wrong or is the above the only way to do this?
Click to expand...
I don't think you are doing anything wrong per se, however it does seem you
are taking the long winded approach... Of course the "long winded" approach
will ensure every thing is set correctly...

I would simply modify the existing code rather then recreating every thing
every time.

I would open the DataSet in the DataSet designer and simply add the new
field to the existing table.

I would either open the DAL in the Component designer or code view and add
the new field to the respective commands.

In case you did not realize it, you can make your DAL Component based, by
using Project - Add Component to create a new "DAL", then drag & drop
Connections, Adapters, Commands from the Data tab of the Toolbox. You can
set properties of these data components in the properties window. Later you
can open the DAL Component in the Component designer and select the various
data components & using the properties window, use the Query designer to
modify the SQL statements.

Of course if you are using straight code, I would simply modify the
"straight code".

Hope this helps
Jay
 
Hi

Component based DAL sounds interesting. However, can I do the following with
component based DAL's;

1. Share a common dataconnection between datadapters in different component
DAL's? I have separate DAL's for suppliers & clients but I want to share the
same db connection? Or, should I make a single DAL for both clients &
suppliers?

2. Filter a dataadpter result on any given value? For example can I filter
client's by current or prospects? Users would like to switch between the two
from time to time.

3. Sort a dataadapter result in any given order say by company id or company
name?

Thanks

Regards
 
John,
1. Share a common dataconnection between datadapters in different component
DAL's? I have separate DAL's for suppliers & clients but I want to share the
same db connection? Or, should I make a single DAL for both clients &
suppliers?
Click to expand...
I would make the Connection itself a property of the DAL, in the Connection
Property Set procedure, update each of the Connection properties of any
contained commands. I would consider making these DALs owned by a different
object that owned the connection, thus when this top level object changed
its Connection it would update each of the child DAL's Connection.
2. Filter a dataadpter result on any given value? For example can I filter
client's by current or prospects? Users would like to switch between the two
from time to time.
Click to expand...
I would consider: making two adapters (current adapter & prospect adapter)
or a parameter to a stored procedure that makes the decision on the server.
Of course if current or prospects is a simply comparison on a single field I
would make a parameterized query.
3. Sort a dataadapter result in any given order say by company id or company
name?
Click to expand...
Again: I would consider: making two adapters (Company Id & company ) or a
parameter to a stored procedure that makes the decision on the server.

Of course if you can have "current by id", "prospect by id", "current by
name", "prospect by name", I would look even more at a smarter single stored
procedure that could change its criteria & sort order "easily"...
Unfortunately Access currently does not support stored procedures.

Note although I may have 4 adapters, I would consider having a single method
that is called, that based on parameters passed decided which of the four
adapters were used. Alternatively you can have multiple methods using a
single adapter, simply changing the parameters used...

Hope this helps
Jay
 
Back
Top