Modified:
branches/5.0/CHANGES
branches/5.0/Driver/Source/CommandBuilder.cs
branches/5.0/Driver/Source/Resources.Designer.cs
branches/5.0/Driver/Source/Resources.resx
branches/5.0/TestSuite/CommandBuilderTests.cs
Log:
Bug #25569 UpdateRowSource.FirstReturnedRecord does not work
Fixed this by exposing a property on the command builder where the user can request that generated keys be returned and then applying a final select later in the process. Thanks go to Lynn Eriksen for some good feedback on this bug and for submitting an idea for how it could be fixed.
Modified: branches/5.0/CHANGES
===================================================================
--- branches/5.0/CHANGES 2007-03-02 19:14:21 UTC (rev 626)
+++ branches/5.0/CHANGES 2007-03-02 22:15:46 UTC (rev 627)
@@ -3,6 +3,7 @@
Bugs fixed
----------
Bug #25605 BINARY and VARBINARY is returned as a string
+ Bug #25569 UpdateRowSource.FirstReturnedRecord does not work
Version 5.0.4 2-28-2007
Modified: branches/5.0/Driver/Source/CommandBuilder.cs
===================================================================
--- branches/5.0/Driver/Source/CommandBuilder.cs 2007-03-02 19:14:21 UTC (rev 626)
+++ branches/5.0/Driver/Source/CommandBuilder.cs 2007-03-02 22:15:46 UTC (rev 627)
@@ -30,32 +30,36 @@
namespace MySql.Data.MySqlClient
{
- /// <include file='docs/MySqlCommandBuilder.xml' path='docs/class/*'/>
+ /// <include file='docs/MySqlCommandBuilder.xml' path='docs/class/*'/>
#if !PocketPC
- [ToolboxItem(false)]
- [System.ComponentModel.DesignerCategory("Code")]
+ [ToolboxItem(false)]
+ [System.ComponentModel.DesignerCategory("Code")]
#endif
- public sealed class MySqlCommandBuilder : DbCommandBuilder
- {
- #region Constructors
+ public sealed class MySqlCommandBuilder : DbCommandBuilder
+ {
+ private string finalSelect;
+ private bool returnGeneratedIds;
- /// <include file='docs/MySqlCommandBuilder.xml' path='docs/Ctor/*'/>
- public MySqlCommandBuilder()
- {
- QuotePrefix = QuoteSuffix = "`";
- }
+ #region Constructors
- /// <include file='docs/MySqlCommandBuilder.xml' path='docs/Ctor2/*'/>
- public MySqlCommandBuilder(MySqlDataAdapter adapter) : this()
- {
- DataAdapter = adapter;
- }
+ /// <include file='docs/MySqlCommandBuilder.xml' path='docs/Ctor/*'/>
+ public MySqlCommandBuilder()
+ {
+ QuotePrefix = QuoteSuffix = "`";
+ }
- #endregion
+ /// <include file='docs/MySqlCommandBuilder.xml' path='docs/Ctor2/*'/>
+ public MySqlCommandBuilder(MySqlDataAdapter adapter)
+ : this()
+ {
+ DataAdapter = adapter;
+ }
- #region Properties
+ #endregion
- /// <include file='docs/mysqlcommandBuilder.xml' path='docs/DataAdapter/*'/>
+ #region Properties
+
+ /// <include file='docs/mysqlcommandBuilder.xml' path='docs/DataAdapter/*'/>
public new MySqlDataAdapter DataAdapter
{
get { return (MySqlDataAdapter)base.DataAdapter; }
@@ -70,29 +74,41 @@
}
}
- #endregion
+ /// <summary>
+ /// Indicates whether the command builder should generate a SELECT statement
+ /// to populate any autogenerated fields. We provide this property rather
+ /// than rely on the MySqlCommand.UpdatedRowSource property since a user should
+ /// still be able to write a custom insert command and not have our work interfere.
+ /// </summary>
+ public bool ReturnGeneratedIdentifiers
+ {
+ get { return returnGeneratedIds; }
+ set { returnGeneratedIds = value; }
+ }
- #region Public Methods
+ #endregion
- /// <summary>
- /// Retrieves parameter information from the stored procedure specified
- /// in the MySqlCommand and populates the Parameters collection of the
- /// specified MySqlCommand object.
- /// This method is not currently supported since stored procedures are
- /// not available in MySql.
- /// </summary>
- /// <param name="command">The MySqlCommand referencing the stored
- /// procedure from which the parameter information is to be derived.
- /// The derived parameters are added to the Parameters collection of the
- /// MySqlCommand.</param>
- /// <exception cref="InvalidOperationException">The command text is not
- /// a valid stored procedure name.</exception>
- public static void DeriveParameters(MySqlCommand command)
- {
- if (!command.Connection.driver.Version.isAtLeast(5,0,0))
- throw new MySqlException("DeriveParameters is not supported on MySQL versions " +
- "prior to 5.0");
+ #region Public Methods
+ /// <summary>
+ /// Retrieves parameter information from the stored procedure specified
+ /// in the MySqlCommand and populates the Parameters collection of the
+ /// specified MySqlCommand object.
+ /// This method is not currently supported since stored procedures are
+ /// not available in MySql.
+ /// </summary>
+ /// <param name="command">The MySqlCommand referencing the stored
+ /// procedure from which the parameter information is to be derived.
+ /// The derived parameters are added to the Parameters collection of the
+ /// MySqlCommand.</param>
+ /// <exception cref="InvalidOperationException">The command text is not
+ /// a valid stored procedure name.</exception>
+ public static void DeriveParameters(MySqlCommand command)
+ {
+ if (!command.Connection.driver.Version.isAtLeast(5, 0, 0))
+ throw new MySqlException("DeriveParameters is not supported on MySQL versions " +
+ "prior to 5.0");
+
// retrieve the proc definitino from the cache.
string spName = command.CommandText;
if (spName.IndexOf(".") == -1)
@@ -145,12 +161,13 @@
return (MySqlCommand)GetInsertCommand(false);
}
- /// <include file='docs/MySqlCommandBuilder.xml' path='docs/RefreshSchema/*'/>
- public override void RefreshSchema()
- {
+ /// <include file='docs/MySqlCommandBuilder.xml' path='docs/RefreshSchema/*'/>
+ public override void RefreshSchema()
+ {
base.RefreshSchema();
- }
- #endregion
+ finalSelect = null;
+ }
+ #endregion
/// <summary>
@@ -158,20 +175,20 @@
/// </summary>
/// <param name="columnName"></param>
/// <returns></returns>
- protected override string GetParameterName(string columnName)
- {
- StringBuilder sb = new StringBuilder(columnName);
- sb.Replace(" ", "");
- sb.Replace("/", "_per_");
- sb.Replace("-", "_");
- sb.Replace(")", "_cb_");
- sb.Replace("(", "_ob_");
- sb.Replace("%", "_pct_");
- sb.Replace("<", "_lt_");
- sb.Replace(">", "_gt_");
- sb.Replace(".", "_pt_");
- return sb.ToString();
- }
+ protected override string GetParameterName(string columnName)
+ {
+ StringBuilder sb = new StringBuilder(columnName);
+ sb.Replace(" ", "");
+ sb.Replace("/", "_per_");
+ sb.Replace("-", "_");
+ sb.Replace(")", "_cb_");
+ sb.Replace("(", "_ob_");
+ sb.Replace("%", "_pct_");
+ sb.Replace("<", "_lt_");
+ sb.Replace(">", "_gt_");
+ sb.Replace(".", "_pt_");
+ return sb.ToString();
+ }
protected override DbCommand InitializeCommand(DbCommand command)
{
@@ -179,7 +196,7 @@
}
- protected override void ApplyParameterInfo(DbParameter parameter, DataRow row,
+ protected override void ApplyParameterInfo(DbParameter parameter, DataRow row,
StatementType statementType, bool whereClause)
{
((MySqlParameter)parameter).MySqlDbType = (MySqlDbType)row["ProviderType"];
@@ -208,7 +225,42 @@
private void RowUpdating(object sender, MySqlRowUpdatingEventArgs args)
{
base.RowUpdatingHandler(args);
+
+ if (args.StatementType != StatementType.Insert) return;
+
+ if (ReturnGeneratedIdentifiers)
+ {
+ if (args.Command.UpdatedRowSource != UpdateRowSource.None)
+ throw new InvalidOperationException(
+ Resources.MixingUpdatedRowSource);
+ args.Command.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
+ if (finalSelect == null)
+ CreateFinalSelect();
+ }
+
+ args.Command.CommandText += finalSelect;
}
+ /// <summary>
+ /// We only need to return the single auto generated column since the base
+ /// ADO.Net classes will take care of mapping it onto the datarow for us.
+ /// </summary>
+ private void CreateFinalSelect()
+ {
+ StringBuilder select = new StringBuilder(";SELECT last_insert_id() AS ");
+
+ DataTable dt = GetSchemaTable(DataAdapter.SelectCommand);
+
+ foreach (DataRow row in dt.Rows)
+ {
+ if (!(bool)row["IsAutoIncrement"])
+ continue;
+
+ select.AppendFormat("`{0}`", row["ColumnName"]);
+ break;
+ }
+
+ finalSelect = select.ToString();
+ }
}
}
Modified: branches/5.0/Driver/Source/Resources.Designer.cs
===================================================================
--- branches/5.0/Driver/Source/Resources.Designer.cs 2007-03-02 19:14:21 UTC (rev 626)
+++ branches/5.0/Driver/Source/Resources.Designer.cs 2007-03-02 22:15:46 UTC (rev 627)
@@ -331,6 +331,15 @@
}
/// <summary>
+ /// Looks up a localized string similar to When MySqlCommandBuilder.ReturnGeneratedIdentifiers is true, MySqlCommand.UpdatedRowSource must be set to None..
+ /// </summary>
+ internal static string MixingUpdatedRowSource {
+ get {
+ return ResourceManager.GetString("MixingUpdatedRowSource", resourceCulture);
+ }
+ }
+
+ /// <summary>
/// Looks up a localized string similar to NamedPipeStream does not support seeking.
/// </summary>
internal static string NamedPipeNoSeek {
Modified: branches/5.0/Driver/Source/Resources.resx
===================================================================
--- branches/5.0/Driver/Source/Resources.resx 2007-03-02 19:14:21 UTC (rev 626)
+++ branches/5.0/Driver/Source/Resources.resx 2007-03-02 22:15:46 UTC (rev 627)
@@ -318,4 +318,7 @@
<data name="ParameterMustBeDefined" xml:space="preserve">
<value>Parameter '{0}' must be defined.</value>
</data>
+ <data name="MixingUpdatedRowSource" xml:space="preserve">
+ <value>When MySqlCommandBuilder.ReturnGeneratedIdentifiers is true, MySqlCommand.UpdatedRowSource must be set to None.</value>
+ </data>
</root>
\ No newline at end of file
Modified: branches/5.0/TestSuite/CommandBuilderTests.cs
===================================================================
--- branches/5.0/TestSuite/CommandBuilderTests.cs 2007-03-02 19:14:21 UTC (rev 626)
+++ branches/5.0/TestSuite/CommandBuilderTests.cs 2007-03-02 22:15:46 UTC (rev 627)
@@ -273,7 +273,7 @@
DataTable dt = new DataTable();
da.Fill(dt);
dt.Columns[0].AutoIncrement = true;
- //Assert.IsTrue(dt.Columns[0].AutoIncrement);
+ Assert.IsTrue(dt.Columns[0].AutoIncrement);
dt.Columns[0].AutoIncrementSeed = -1;
dt.Columns[0].AutoIncrementStep = -1;
DataRow row = dt.NewRow();
@@ -294,5 +294,45 @@
Assert.AreEqual(1, dt.Rows[0]["id"]);
Assert.AreEqual("Test", dt.Rows[0]["name"]);
}
+
+ /// <summary>
+ /// Bug #25569 UpdateRowSource.FirstReturnedRecord does not work
+ /// </summary>
+ [Test]
+ public void AutoIncrementColumnsOnInsert2()
+ {
+ execSQL("DROP TABLE IF EXISTS test");
+ execSQL("CREATE TABLE test (id INT UNSIGNED NOT NULL " +
+ "AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))");
+ MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
+ MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
+ cb.ReturnGeneratedIdentifiers = true;
+
+ DataTable dt = new DataTable();
+ da.Fill(dt);
+ dt.Rows.Clear();
+
+ try
+ {
+ DataRow row = dt.NewRow();
+ row["name"] = "Test";
+ dt.Rows.Add(row);
+ da.Update(dt);
+ Assert.AreEqual(1, dt.Rows[0]["id"]);
+
+ row = dt.NewRow();
+ row["name"] = "Test2";
+ dt.Rows.Add(row);
+ da.Update(dt);
+ Assert.AreEqual(2, dt.Rows[1]["id"]);
+ }
+ catch (Exception ex)
+ {
+ Assert.Fail(ex.Message);
+ }
+
+ Assert.AreEqual(1, dt.Rows[0]["id"]);
+ }
+
}
}
| Thread |
|---|
| • Connector/NET commit: r627 - in branches/5.0: . Driver/Source TestSuite | rburnett | 2 Mar |
