| Bug #25569 | UpdateRowSource.FirstReturnedRecord does not work | ||
|---|---|---|---|
| Submitted: | 12 Jan 2007 3:01 | Modified: | 9 Mar 2007 14:37 |
| Reporter: | Lynn Eriksen | ||
| Status: | Closed | ||
| Category: | Connector/Net | Severity: | S2 (Serious) |
| Version: | 5.03 | OS: | .net framework 2.0 |
| Assigned to: | Target Version: | ||
[12 Jan 2007 3:04]
Lynn Eriksen
I have added a test asp.net web site.
[12 Jan 2007 3:05]
Lynn Eriksen
Shows the problen wih the UpdateRowSource.FirstReturnRecord issue
Attachment: Test ASPNet WebSite.zip (application/x-zip-compressed, text), 104.03 KiB.
[2 Mar 2007 0:24]
Lynn Eriksen
I believe I have a fix for this.
The problem is that the Ado.net 2.0 native CommandBuilder does not create a select
command that returns the values based on the last_insert_id funcation. However, it will
process it if a select command is appended to the insert command and UpdateRowSource is
set to 'FirstReturnedRecord'
Here is the fix and it is applied to the end command builder class:
Old Code
________________________________________________
private void RowUpdating(object sender, MySqlRowUpdatingEventArgs args)
{
base.RowUpdatingHandler(args);
}
New Code
________________________________________________
private void RowUpdating(object sender, MySqlRowUpdatingEventArgs args)
{
base.RowUpdatingHandler(args);
if (args.StatementType == StatementType.Insert)
{
args.Command.CommandText += "; " + CreateFinalSelect(true);
args.Command.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
}
}
private string CreateFinalSelect(bool forinsert)
{
StringBuilder sel = new StringBuilder();
StringBuilder where = new StringBuilder();
String TableName = null;
foreach (DataRow row in
this.GetSchemaTable(this.DataAdapter.SelectCommand).Rows)
{
// don't include functions in where clause
string baseTableName = (string)row["BaseTableName"];
if (baseTableName == null || baseTableName.Length == 0)
continue;
if (TableName == null)
{
TableName = baseTableName;
}
string colname = Quote(row["ColumnName"].ToString());
string parmName = GetParameterName(row["ColumnName"].ToString());
if (sel.Length > 0)
sel.Append(", ");
sel.Append(colname);
if ((bool)row["IsKey"] == false) continue;
if (where.Length > 0)
where.Append(" AND ");
where.Append("(" + colname + "=");
if (forinsert)
{
if ((bool)row["IsAutoIncrement"])
where.Append("last_insert_id()");
else if ((bool)row["IsKey"])
where.Append(this.ParameterMarker + parmName);
}
else
{
where.Append(this.ParameterMarker + "Original_" + parmName);
}
where.Append(")");
}
return "SELECT " + sel.ToString() + " FROM " + TableName +
" WHERE " + where.ToString();
}
private string Quote(string table_or_column)
{
return this.QuotePrefix + table_or_column + this.QuoteSuffix;
}
[2 Mar 2007 0:40]
Lynn Eriksen
I believe I have a fix for this.
The problem is that the Ado.net 2.0 native CommandBuilder does not create a select
command that returns the values based on the last_insert_id funcation. However, it will
process it if a select command is appended to the insert command and UpdateRowSource is
set to 'FirstReturnedRecord'
Here is the fix and it is applied to the end command builder class:
Old Code
________________________________________________
private void RowUpdating(object sender, MySqlRowUpdatingEventArgs args)
{
base.RowUpdatingHandler(args);
}
New Code
________________________________________________
private void RowUpdating(object sender, MySqlRowUpdatingEventArgs args)
{
//must be called first
base.RowUpdatingHandler(args);
//adds FirstReturnedRecord support
if (args.StatementType == StatementType.Insert)
{
args.Command.CommandText += "; " + CreateFinalSelect(true);
args.Command.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
}
}
private string CreateFinalSelect(bool forinsert)
{
StringBuilder sel = new StringBuilder();
StringBuilder where = new StringBuilder();
String TableName = null;
foreach (DataRow row in this.GetSchemaTable(this.DataAdapter.SelectCommand).Rows)
{
// don't include functions in where clause
string baseTableName = (string)row["BaseTableName"];
if (baseTableName == null || baseTableName.Length == 0)
continue;
if (TableName == null)
{
TableName = baseTableName;
}
string colname = Quote(row["ColumnName"].ToString());
string parmName = GetParameterName(row["ColumnName"].ToString());
if (sel.Length > 0)
sel.Append(", ");
sel.Append(colname);
if ((bool)row["IsKey"] == false) continue;
if (where.Length > 0)
where.Append(" AND ");
where.Append("(" + colname + "=");
if (forinsert)
{
if ((bool)row["IsAutoIncrement"])
where.Append("last_insert_id()");
else if ((bool)row["IsKey"])
where.Append(this.ParameterMarker + parmName);
}
else
{
where.Append(this.ParameterMarker + "Original_" + parmName);
}
where.Append(")");
}
return "SELECT " + sel.ToString() + " FROM " + TableName +
" WHERE " + where.ToString();
}
private string Quote(string table_or_column)
{
return this.QuotePrefix + table_or_column + this.QuoteSuffix;
}
The new code must be applied at this point and not sooner because:
1) the 'base.RowUpdatingHandler(args)' method call resets the command object used to
update the row and does not call the GetInsertCommand method
2) instead of calling 'GetInsertCommand' the command regenerated by a call to a private
method 'BuildInsertCommand'.
The methods for building the select query were imported from the 1.09 branch and seem to
work properly thus far, but this will probably require some cleanup.
Note: The hard part was determining where to add the call to the 'CreateFinalSelect'
method. I first tried to override the GetInsertCommand but it did not work. I eventally
determined that a call was being made to 'CommandText' and the sql was different then I
had specified. I was able to use the Reflector 'Analyze' funtionality to find all calls
to the 'Set' method and then deduct where the command was being reset. I think that
Microsoft's documentation falls way short here, becasue the call to
'base.RowUpdatingHandler' does ALOT more than advertised.
[3 Mar 2007 0:03]
Reggie Burnett
Fixed in 5.0.5. The previously submitted patch was not appropriate for a few reasons but it did provide some insight into how to fix it. Thanks Lynn!
[9 Mar 2007 14:37]
MC Brown
A note has been added to the 5.0.5 changelog.
Description: When performing and update of a datatable using the adpater/command builder como does not return the first record for updating the datarows. This means the the last_insert_id() value for autonumber columns is not returned successfully to the row. This is a regression as the feature exists pre 1.08. When you run the demo I have suggested below you'll see that the last_insert_id() is not being updated, whcih means that the last inserted row values are not being returned from the database on the command builder insert command execution. How to repeat: Here is the code for an asp.net page to repeat... Default.aspx.cs: using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using MySql.Data.MySqlClient; public partial class _Default : System.Web.UI.Page { //tables schema for this test //------------------------------------------------ //DROP TABLE IF EXISTS `test`.`update_test`; //CREATE TABLE `test`.`update_test` ( // `TestID` int(10) unsigned NOT NULL AUTO_INCREMENT, // `TestValue` varchar(45) NOT NULL, // PRIMARY KEY (`TestID`) //) ENGINE=InnoDB DEFAULT CHARSET=latin1; protected void Page_Load(object sender, EventArgs e) { //create testset TestDataSet TestSet = new TestDataSet(); for (int TryCount = 0; TryCount < 3; TryCount++) { TestDataSet.TestTableRow TestRow = TestSet.TestTable.NewTestTableRow(); TestRow.TestValue = Guid.NewGuid().ToString(); TestSet.TestTable.AddTestTableRow(TestRow); } //create connection MySqlConnection Connection = new MySqlConnection("Database=test;server=localhost;User Id=root;Password=stdlae13;Pooling=false;"); //create adapter MySqlDataAdapter Adapter = new MySqlDataAdapter("Select TestID,TestValue From update_test", Connection); //create commandbuilder MySqlCommandBuilder Builder = new MySqlCommandBuilder(Adapter); Adapter.InsertCommand = Builder.GetInsertCommand(); Adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord; //update Connection.Open(); Adapter.Update(TestSet.TestTable); Connection.Close(); //display UpdatedRowSource this.GridView1.DataSource = TestSet.TestTable; this.GridView1.DataBind(); //notice the the TestID is not reurn } } Default.aspx: <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> Notice the the TestID does not have a postitve value so the<br /> UpdateRowSource.FirstReturnedRecord is never being applied.<br /> <br /> <asp:GridView ID="GridView1" runat="server"> </asp:GridView> </div> </form> </body> </html> And a DataSet for app_code: <?xml version="1.0" encoding="utf-8"?> <xs:schema id="TestDataSet" targetNamespace="http://tempuri.org/TestDataSet.xsd" xmlns:mstns="http://tempuri.org/TestDataSet.xsd" xmlns="http://tempuri.org/TestDataSet.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop" attributeFormDefault="qualified" elementFormDefault="qualified"> <xs:annotation> <xs:appinfo source="urn:schemas-microsoft-com:xml-msdatasource"> <DataSource DefaultConnectionIndex="0" FunctionsComponentName="QueriesTableAdapter" Modifier="AutoLayout, AnsiClass, Class, Public" SchemaSerializationMode="IncludeSchema" xmlns="urn:schemas-microsoft-com:xml-msdatasource"> <Connections> </Connections> <Tables> </Tables> <Sources> </Sources> </DataSource> </xs:appinfo> </xs:annotation> <xs:element name="TestDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true" msprop:Generator_UserDSName="TestDataSet" msprop:Generator_DataSetName="TestDataSet"> <xs:complexType> <xs:choice minOccurs="0" maxOccurs="unbounded"> <xs:element name="TestTable" msprop:Generator_UserTableName="TestTable" msprop:Generator_RowDeletedName="TestTableRowDeleted" msprop:Generator_RowChangedName="TestTableRowChanged" msprop:Generator_RowClassName="TestTableRow" msprop:Generator_RowChangingName="TestTableRowChanging" msprop:Generator_RowEvArgName="TestTableRowChangeEvent" msprop:Generator_RowEvHandlerName="TestTableRowChangeEventHandler" msprop:Generator_TableClassName="TestTableDataTable" msprop:Generator_TableVarName="tableTestTable" msprop:Generator_RowDeletingName="TestTableRowDeleting" msprop:Generator_TablePropName="TestTable"> <xs:complexType> <xs:sequence> <xs:element name="TestID" msdata:AutoIncrement="true" msdata:AutoIncrementSeed="-1" msdata:AutoIncrementStep="-1" msprop:Generator_UserColumnName="TestID" msprop:Generator_ColumnVarNameInTable="columnTestID" msprop:Generator_ColumnPropNameInRow="TestID" msprop:Generator_ColumnPropNameInTable="TestIDColumn" type="xs:int" /> <xs:element name="TestValue" msprop:Generator_UserColumnName="TestValue" msprop:nullValue="_empty" msprop:Generator_ColumnVarNameInTable="columnTestValue" msprop:Generator_ColumnPropNameInTable="TestValueColumn" msprop:Generator_ColumnPropNameInRow="TestValue" type="xs:string" default="" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> <xs:unique name="TestTableKey1" msdata:PrimaryKey="true"> <xs:selector xpath=".//mstns:TestTable" /> <xs:field xpath="mstns:TestID" /> </xs:unique> </xs:element> </xs:schema>