Returning pk value to DataTable
Posted by: maart6
Date: November 13, 2006 09:56AM

Hi all,

I've got a question about returning the value of a pk-column to a DataTable
after inserting a row (via a data-adapter).

Here is the SQL and code concerned:

//===================================================================
// The table

CREATE TABLE `client` (
`id_client` int(10) unsigned NOT NULL auto_increment,
`name` varchar(40) NOT NULL default '',
PRIMARY KEY (`id_client`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


//===================================================================
// Stored procedure

CREATE PROCEDURE pInsertClient (OUT id_client INT, IN name VARCHAR(40))
BEGIN

INSERT INTO client(name) VALUES (name);
SET id_client = LAST_INSERT_ID();

END$$

The code:


//===================================================================
// Creating the data-adapter
...
try
{
// QueryHandler::getConnection() returns a static connection
dataAdapter = gcnew MySqlDataAdapter("SELECT * FROM client", QueryHandler::getConnection());

// Insert
MySqlCommand^ insertCommand = gcnew MySqlCommand("pInsertClient");
insertCommand->Connection = QueryHandler::getConnection();

insertCommand->CommandType = CommandType::StoredProcedure;

MySqlParameter^ parameter1 = insertCommand->Parameters->Add("?name", MySqlDbType::VarChar, 40, "name");
parameter1->Direction = ParameterDirection::Input;
MySqlParameter^ parameter2 = insertCommand->Parameters->Add("?id_client", MySqlDbType::Int32, 32, "id_client");
parameter2->Direction = ParameterDirection::Output;

dataAdapter->InsertCommand = insertCommand;

dataSet = gcnew DataSet();
dataTable = gcnew DataTable("client");
// dataTable declared elsewhere
dataSet->Tables->Add(dataTable);
dataAdapter->MissingSchemaAction = MissingSchemaAction::AddWithKey;
dataAdapter->Fill(dataTable);

DataColumn^ pkCol = dataTable->Columns["id_client"];
array<DataColumn^>^ columnArray = { pkCol };
dataTable->PrimaryKey = columnArray;


catch (MySqlException^ mE)
{
MessageBox::Show("An exception occured: \n" + mE->Message);
}
...

//===================================================================

// Performing an insert to the client table

...
DataRow^ newRow = dataTable->NewRow();

newRow["id_client"] = static_cast<int^>(0); // dummy value
newRow["name"] = this->textBox->Text;


dataTable->Rows->Add(newRow);


dataAdapter->Update(dataTable);
...
//===================================================================

This all works fine. But when I try to optimize the updating of the DataTable
by changing the last line of code:

dataAdapter->Update(dataTable);

to:

DataSet^ changes = dataSet->GetChanges(DataRowState::Added);
dataAdapter->Update(changes->Tables["client"]);


The primairy key value that is generated in the source database will not be
returned to the pk-column in the DataTable. I have seen multiple examples that
use the latter structure to prefend

I wonder if it's really necessary to limit the update statement to the
isolated changed rows by calling GetChanges, because when it's applied
to the entire DataTable these rows also have to be determined.

So, can anybody help me out with the following questions:

1. Is it usefull to apply the Update-statement to a narrowed down selection
of changed rows from the DataTable?

2. If so, how to accomplish this without having to use the RowUpdated event?


Thanks a lot in advance!

Maart

Options: ReplyQuote


Subject
Written By
Posted
Returning pk value to DataTable
November 13, 2006 09:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.