Have soln but need understanding (return IDENTITY issue).

  • Thread starter Thread starter Girish
  • Start date Start date
G

Girish

I was struggling all day with autonumbers not being returned by my
sqldataadapter when I did an insert. I have found the solution (from the
newsgroups). Im just confused a little since I tried two ways to do this and
one didnt work. I just wanted to know why.

Heres the code that didnt work:
---------------------------------
//_dataCommand is an instance of SqlDataAdapter
//_dataTable is an instance of DataTable

SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
_dataCommand.InsertCommand = bldr.GetInsertCommand();
_dataCommand.InsertCommand.CommandText += ";Select SCOPE_IDENTITY() as
id";
_dataCommand.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
//_dataCommand.Update(_dataTable.Select("", "", DataViewRowState.Added));
_dataCommand.Update(_dataTable);
_dataTable.AcceptChanges();

Heres the code that DID work:
-------------------------------
//_dataCommand is an instance of SqlDataAdapter
//connection is an instance of ConnectionProvider which has a property
called DBConnection of type SqlConnection
//_dataTable is an instance of DataTable

SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
SqlCommand cmdInsert = new
SqlCommand(bldr.GetInsertCommand().CommandText, connection.DBConnection);
cmdInsert.CommandText += ";Select SCOPE_IDENTITY() as id";

SqlParameter[] aParams = new
SqlParameter[bldr.GetInsertCommand().Parameters.Count];
bldr.GetInsertCommand().Parameters.CopyTo(aParams, 0);
bldr.GetInsertCommand().Parameters.Clear();

for(int i=0 ; i < aParams.Length; i++)
{
cmdInsert.Parameters.Add(aParams);
}

_dataCommand.InsertCommand = cmdInsert;
_dataCommand.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
_dataCommand.Update(_dataTable);
_dataTable.AcceptChanges();


---------------------------------------------

1) Can someone explain why my first code didnt work? When I looked at traces
in sql profiler, the "SELECT SCOPE_IDENTITY()" wasnt being executed at all.

2) I have some other issues. Ive read and read and read but cant seem to
figure out what AcceptChanges() is meant for and how
dataCommand.Update(_dataTable.Select("", "", DataViewRowState.Added)) is
different from dataCommand.Update(_dataTable);

Appreciate any help on this.

Thanks,
Girish
 
Hi Girish,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you have 3 questions. The first one is
"SELECT SCOPE_IDENTITY()" wasn't executed even if you added it manually in
to the command text. The second one is you need to know the difference
between updating the whole table and updating selected rows. You also need
to know the function of AcceptChanges method. If there is any
misunderstanding, please feel free to let me know.

1) The SqlCommandBuilder registers itself as a listener for RowUpdating
events that are generated by the SqlDataAdapter specified in this property.
According to the document, the Transact-SQL statements for the insert
command are first generated either when the application calls Update or
GetInsertCommand. In your first code which did not work,
_dataCommand.InsertCommand and CommandBuilder.GetInsertCommand's return
value reference to the same object. When you call _dataCommand.Update
method, the T-SQL statement was regenerated. So your modification to it get
lost. This is the behavior of SqlCommandBuilder object by design.

Here is a link for your reference:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatasqlclientsqlcommandbuilderclassgetinsertcommandtopic.asp

2) The difference between dataCommand.Update(_dataTable.Select("", "",
DataViewRowState.Added)) and dataCommand.Update(_dataTable) is that, the
first one only update the new inserted rows while the latter one updates
all the inserted, modified and deleted rows to the source database.

3) AcceptChanges commits all the changes made to this table since the last
time AcceptChanges was called. It will set the RowState property of all the
rows in the DataTable to Unchanged so that they won't be updated again next
time you call DataAdapter.Update. Because the DataAdapter.Update method
figures out which row to update by the RowState property. You needn't call
DataTable.AcceptChanges explicitly because DataAdapter.Update method will
call it for you after it has updated the source database.

For more information, please check the following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatatableclassacceptchangestopic.asp

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Thanks for your answer Kevin.

"AcceptChanges commits all the changes made to this table since the last
time AcceptChanges was called."

I think this statement is very misleading. The acceptChanges does not do the
commit. It just changes the rowstate. The Update method actually does the
commit to the database if I understood what you meant. Im going to try and
understand your answer to my first q about scope_identity. I need sometime
to absorb it.

Thanks for your answers again!
Girish
 
2) I have some other issues. Ive read and read and read but cant seem t
figure out what AcceptChanges() is meant for and ho
dataCommand.Update(_dataTable.Select("", "", DataViewRowState.Added)) i
different from dataCommand.Update(_dataTable)
Click to expand...

AcceptChanges is simply that - it tells the dataset to accept any changes that have been made to it ( effectively setting the rowstate of each row within the dataset to 'Unchanged'

The first command
dataCommand.Update(_dataTable.Select("", "", DataViewRowState.Added)
Click to expand...
will only perform updates on rows that have been added to the datatable since 'AcceptChanges' was called - effectively only propagating inserts to the database

The second
dataCommand.Update(_dataTable)
Click to expand...
will perform updates on all changes to the datatable regardless of rowstate ie. Updates and deletes as well as inserts

Finally, you should be aware that the DataAdapter.Update method causes AcceptChanges to be run by default - to avoid this you must set up an event handler for the DataAdapter.RowUpdated event and tell it not to eg.

AddHandler da.RowUpdated, AddressOf OnRowUpdate

Private Shared Sub OnRowUpdated(ByVal sender As Object, ByVal e As SqlRowUpdatedEventArgs

' See if the change is an inser
If e.StatementType = StatementType.Insert The

' If it is an insert, don't perform AcceptChanges on the ro
e.Status = UpdateStatus.SkipCurrentRo

End I

End Su
 
Hi Girish,

Sorry for making things misleading. What you said is exactly what I meant
in my last post.

If anything is unclear, please feel free to reply to the post. I'm standing
by to be of assistance.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top