John said:
Hi
Every time I change the structure of a table, I need to adjust the command
texts for select, insert, update & delete commands. Is there a way that an
app, on load time, can automatically take a table name and create the
necessary datatable, dataadapter and more importantly the corresponding
commandtexts from it?
Yes. This is what the CommandBuilder objects do. For instance:
Dim cmd as new SqlCommand("select * from MyTable",conn)
dim da as new SqlDataAdapter(cmd)
dim cb as new SqlCommandBuilder(da)
dim cmdInsert as SqlCommand = cb.GetInsertCommand()
dim cmdUpdate as SqlCommand = cb.GetUpdateCommand()
dim cmdDelete as SqlCommand = cb.GetDeleteCommand()
The CommandBuilder will query the database for the structure of MyTable and
generate appropriate insert, update and delete commands.
But you really might not want to do this at run time. You need to bind your
programs to the structure of your database, declaring the appropriate
variables, applying the appropriate UI edits etc. So a change in your
schema will often require changes in your application code.
I think what you want is an easy way to regenerate your commands after a
schema change. This is where stongly-typed datasets come in. Re-create
your stongly-typed datasets after your schema change, and then the compiler
will tell you what source code was affected by the schema change. For
instance if you rename a column from "ACCT_CODE" to "ACCOUNT_CODE", after
regenerating the datasets any code referenced the old name won't compile,
and will show up in your task list.
dim ds as new MyTypedDataset()
....
for each dr as MyTypedDataSet.MyTableRow in ds.MyTableRows
if dr.ACCT_CODE = "CRX" then
. . .
end if
next
David
