Checking FK constraints in advance from the client?

[Matt C.]

I have a file-parse/insert-into-db app I'm writing. Currently I'm using
foreign key constraints with lookup tables to ensure all inserted code
values are on the permitted list. If an invalid code is sent, I catch the
SqlException and log the error to a different table.

There are some weaknesses with this approach, and I'd like to detect the FK
violations on the client prior to the insert. I figure this means drawing
down all the lookup tables and running a client-side validation on each one
(blah). What I'm not sure about is the best way to managing this lookup
data in ADO.NET. I'm considering two general approaches:

1) Use stored procedures to pull down each lookup table as a DataTable.
Define a PK on each DataTable (blah). Use Rows.Find to check each value as
part of the client-side validation. (Could also use DataView, too, I guess,
and skip the PK def?)

2) Pull down lookup data as above, but load the relevant codes from each
lookup table into a Hashtable, with each code as a key. Then use
Hashtable.HasKey in the client side validation.

Presumably 2) would run faster at validate time, and it skips the annoying
PK definition process. I'm leaning that way, but I've not used the
Hashtable much, there might be issues I'm not aware of.

Anyway, I'd like to solicit other's opinions, including suggestions on
completely different approaches. I can't be the first person who's tried to
implement something like this; if there's a best practice I'm happy to
follow it.

Matt

 

[Lukas Alber]

Matt
I also think 2) would be faster. However, if you know the database schema at
design time, I'd do something similar to 1): Create a typed dataset with the
table you want to insert and the lookup tables (the key columns only). By
dragging tables from the Server Explorer onto the design surface of the
dataset, this can be done rather quickly. Add the relations to the dataset
definition (again, visual studio will allow you to do this without writing
actual code). You then won't even be able to add a row to the datatable if
it contains invalid foreign keys. The advantage of this approach in my
opinion is that your application just needs a copy of the database schema in
the form of the dataset, you do not need to explicitly write procedural code
that does exactly what SQLServer can do too. It also may be helpful if ever
you have to adapt your app to changes in the database schema
Lukas

 

[Matt C.]

Matt
I also think 2) would be faster. However, if you know the database
schema at design time, I'd do something similar to 1): Create a typed
dataset with the table you want to insert and the lookup tables (the
key columns only). By dragging tables from the Server Explorer onto the
design surface of the dataset,

Say what?

I don't use the GUI tools for data management, so the above doesn't make
much sense to me.

However, I'm not above giving them a try. I use VS.NET 2003; do you have
any references to a tutorial, etc for the kind of stuff you're talking
about?

this can be done rather quickly. Add
the relations to the dataset definition (again, visual studio will
allow you to do this without writing actual code). You then won't even
be able to add a row to the datatable if it contains invalid foreign
keys. The advantage of this approach in my opinion is that your
application just needs a copy of the database schema in the form of the
dataset, you do not need to explicitly write procedural code that does
exactly what SQLServer can do too. It also may be helpful if ever you
have to adapt your app to changes in the database schema Lukas

Hmmm. I'm not sure this-all will really be compatible with what I've
already worked out. (e.g., I don't add anything to datatables, inserts are
done via parameterized stored procedures.) But I'll at least look into it
some more.

Thanks for the response.

Matt

 

[Lukas Alber]

Say what?

I don't use the GUI tools for data management, so the above doesn't make
much sense to me.

However, I'm not above giving them a try. I use VS.NET 2003; do you have
any references to a tutorial, etc for the kind of stuff you're talking
about?

Most of the stuff I picked up from the walkthroughs in VS.NET (you find a
nice selection by searching VS-Help for walkthrough "typed dataset"). The
rest I discovered by dragging everything I could find from a Data Connection
in the Server Explorer Window onto the design surface of a dataset or
component.

Hmmm. I'm not sure this-all will really be compatible with what I've
already worked out. (e.g., I don't add anything to datatables, inserts are
done via parameterized stored procedures.) But I'll at least look into it
some more.

Sorry, my wording was ambigous. I meant inserts in a System.Data.Datatable,
i.e. in memory. To actually write the data to the SQLServer I'd use
SQLDataAdapter.Update(), with the insert command of the data adapter using
the stored procedure.

 

[Matt C.]

Most of the stuff I picked up from the walkthroughs in VS.NET (you find
a nice selection by searching VS-Help for walkthrough "typed dataset").
The rest I discovered by dragging everything I could find from a Data
Connection in the Server Explorer Window onto the design surface of a
dataset or component.

Heh. I can do that.

Sorry, my wording was ambigous. I meant inserts in a
System.Data.Datatable, i.e. in memory. To actually write the data to
the SQLServer I'd use SQLDataAdapter.Update(), with the insert command
of the data adapter using the stored procedure.

Could conceivably work. Probably should take a look at it, just to see how
it works if nothing else.

Thanks for the suggestions, again.

Matt