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) using MySql5.
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 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
