INSERT from DataSet with generated PKey

  • Thread starter Thread starter Ken Allen
  • Start date Start date
K

Ken Allen

I want to be abel to permit the user to be able to view (via a DataGrid
right now), delete, edit, and add entries to a table in a database. I have
figured out nost of the issues, except...

My table has a primary key that is a sequential identifier that I do not
wish to expose to the end user at all. I retrieve the entire record from the
database table into a dataset and associate that with the DataGrid, but I
set the width of the primary key column to 0 to hide it. Cool. For delete
and edit operations I am fine. For insert operations, I want to be able to
dynamically assign the value for the primary key dynamically; the user
should not and cannot enter it. An added wrinkle is that several users may
attempt at add records to the table concurrently.

If I were doing this with direct SQL, say in a stored procedure, I would
code the statement like:

BEGIN TRANSACTION
SELECT @ID = 1 + MAX(PKey) FROM MyTable

INSERT INTO MyTable(PKey, UserValue1, UserValue2, UserValue3)
VALUES(@ID, @uVal1, !uVal2, @uVal3)
COMMIT

I would like to use the SqlDataAdapter.Update() call for a batch process,
but I cannot figure out how to craft the INSERT statement to (1) permit me
to dynamically assign the primary key field (which cannot be NULL!) or (2)
indicate in my SqlCommand that the values are to come from the dataset
itself.

Any references or answers?

-Ken
 
I have been doing more research, and I am still not clear on how to achieve
what I want. I undestood the basics of the insert/update/delete methods on
the SqlDataAdapter class instances, but some of the mechanisms that are
mentioned are unclear.

For example, in the discussion of the InsertCommand the following text
appears:
Note If execution of this command returns rows, these rows may be added
to the DataSet depending on how you set the UpdatedRowSource property of the
SqlCommand object.
What is not clear is how an INSERT, UPDATE or DELETE statement in SQL can
possibly return any rows at all! The only way that I can see for this to be
effective would be to develop a stored procedure and invoke that -- what a
pain! I have worked with databases for a long time, and I am familiar with
the use and benefits of stored procedures, but experience has shown that it
is desirable to keep as much of the related code together as possible, and
splitting the update logic into two sections, requiring a database upgrade
on top of replacing the code, makes maintenance more difficult. I would
prefer to be able to code all of the actions in one place.

I have read about the OnRowUpdating and OnRowUpdated event handlers, and I
may be able to use this, but none of the examples illustrate effective use
of these events. I did manage to figure out how to place code in the
OnRowUpdating event handler to update the parameter value before each row is
inserted into the database, and this seems to work.

-ken
 
Ken,
For SQL server your INSERT/DELETE/... commands can be batch statements.
For Example
With MyTable having MyKey Int, Identity and MyField VARCHAR(??)
"INSERT INTO MyTable (MyField) VALUES (@param); SELECT MyKey, MyField FROM
MyTable WHERE MyKey = SCOPE_IDENTITY();"
This would return a row with the current key and field values that can be
merged back into the row sent for update. If you use a stored procedure
just return SCOPE_IDENTITY() and define a parameter on the associated
command with a Direction of ReturnValue. @@IDENTITY won't work correctly as
there can be other statements fired by a trigger associated with a table.
I'd suggest a copy of 'ADO.NET Core Reference' by David Sceppa (MS
Press) as it covers these kinds of things in detail and has lots of examples
to look at.

Ron Allen
 
Back
Top