W
Wed Oct 26 11:48:24 CDT 2005
Comments inline:
"Siegfried Heintze" <siegfried@heintze.com> wrote in message
news:u2KOT%23j2FHA.1268@TK2MSFTNGP10.phx.gbl...
> Whenever I create a new database query, I have to answer a couple of
> questions that are even harder to ignore if you are using visual studio:
>
> (1) Should I create a new table in an existing dataset or a new dataset?
> Is
> there peril in having one table per dataset and having lots of data sets?
--It depends. If you use Typed DataSets exclusively, then you'll often have
multiple datasets instead of just one. While you could use just one, in
many cases this isn't practical. have multiple datasets like this allow you
to use Strong typing which is a peformance booster, provides intellisense
support and will catch column and table name changes at compile time. If
you are using Typed datasets, then you need to know what they'll look like
in advance, so you pretty much need to go the 'one dataset for each type"
route. There are many times that features like WriteXML are only available
to datasets too, so you may need to stick a table in a dataset just to get
the funcionality. It's probably a bit of an oversmiplification to say
this, but the more that you are using your datasets as business objects, the
more you'll probably want to lean toward having one dataset for each entity.
> (2) Should I use a generic late binding dataset or have visual studio
> create
> a custom early binding dataset class for me?
--If you are in a scenario where you don't know what the data is goign to
look like then you have to use untyped datasets. Otherwise you should
probably opt for typed datasets. Typed ones perform better, have
intellisense support and are much easier to work with (for isntance, you
don't have to worry about remembering column names or table names). But,
there may be times when you aren't sure what a query will look like. In
those cases, you need to use untyped ones. Also, if you go the route of
Typed datasets, you probably want to make sure you innclude TableMappings
and ColumnMappings (one nice way to augment the data access application
block is to add overloads for this) so that if your field names in the db
change (or their aliases), you can just change the column mappings and be
done with it. Either way though, if you're using typed datasets and the
column or table names change, you're going to have to do some modification.
So you gain a lot by using typed datasets but you lose flexibility. In my
personal experience, we've done some pretty large state projects using only
typed datasets b/c we knew what our data would look like. We've also done
two where we used mainly untyped ones b/c the user create the query on the
fly. Using typed datasets, this would have been impossible. On the other
hand, if we didn't use typed datasets in the first case, there would have
no doubt been a lot more bugs involved and we wouldn't have caught breaking
changes as quickly. Given the choice, I have a STRONG bias toward typed
datasets unless I absolutely can't use them.
> (3) Should I implement my one to many relationship in the SQL with an
> INNER
> JOIN clause or should I create two datatables and join them in the dataset
> using the visual studio dataset GUI designer using drag and drop?
--Those aren't the only two choices per se, you can add a DataRelation
programatically ;-) (I know, it's a distinction without a difference).
However for performance reasons, update ease and data integrity, use
multiple tables attaching them with DataRelations where applicable. If you
do a join on a three colum table with 1000 rows to a 1 column table with say
2 rows, you'll be pulling over 4 columns worth of data, 1000 times. If you
use a datarelation, you'll pull over 3 columns 1000 times and 1 column 2
times. This is going to greatly reduce network conjestion and use a lot
less resources on the client machine (the extent of this depends on the
query but as you can see by this example, it doesn't take much before things
are dramatically different)
> (4) If I have a many to many relationship, is it possible to implement
> this
> in the dataset GUI designer instead of in the SQL with INNER JOIN
> statements?
--Yes (Sahil Malik's new Professional ADO.NET 2.0 has a great example of
doing many-to-many relationships
http://www.amazon.com/gp/product/1590595122/104-4588449-3161543?v=glance&n=283155&n=507846&s=books&v=glance)
What would be the advantage?
-The same as above, just to an even greater extent.
> (5) Am I going to supply the dataset with add, delete and update objects?
> What is the advantage of assigning these Command objects to the Dataset
> instead of just calling them directly as needed if they are needed?
--You assing these to the DataAdapter. You can delete, add, update from a
dataset, but the commands that fire against the db are done via a
DataAdapter. The benefit is calling
DataAdapter.Update(myDataSet.MYdataTable);
vs looping through each one, figuring out what needs done, building the
command each time and firing it. It's a lot cleaner and easier to
maintain - and a lot less error prone to just configure your adapter and
call Update.
> (5a) When you are configuring a new data adapter, you get the oppertunity
> to
> let visual studio automatically generate the SQL statements to INSERT,
> DELETE and UPDATE and assign their respective command objects to the
> dataset. I've done it and it even worked, but I did not feel like I
> understood it. How does visual studio know how to automatically generate
> the
> SQL update, add and delete SQL statements when you have a complex join
> statement anyway?
--If can't generate anything but Select commands on a Joined scenario.
Otherwise for single tables, it just looks to the primary key to build the
statements (you need a PK on each table in order for the CommandBuilder or
the Designer to work).
> (5b) When is visual studio not able to automatically generate the UPDATE,
> DELETE, and INSERT SQL statements and their respective command objects? I
> received a message from Visual Studio recently complaining that I was not
> using enough PK to automatically generate the UPDATE, DELETE and INSERT
> SQL
> statements. This was OK since I could not imagine how Visual Studio was
> going to know how to implement these statements anyway. Was it because I
> was
> joining some fields that were secondary indices?
--You must have PK for this to work and it only works on single table
queries.
>
> I've successfully used the dataset GUI designer to connect the foreign key
> field to the PK of another table. (I'm not talking about the query
> designer
> here -- this is connecting fields in the dataset after they have been read
> out of the database tables). It worked. I was impressed but I was not
> (still
> not) clear on the advantage. When I tried to do the same thing with a many
> to many I could not figure out how to do it.
>
> So I'd love some help on the perils and merits to each approach!
> Thanks,
> Siegfried
HTH,
Bill
>
>