Mutating OracleParameter reference
Posted: Fri 20 Feb 2015 10:20
Hi,
When a parameter is added to a command and the parameter contains a trailing "$" sign, it happens that the returned instance is removed from the command ParameterCollection once the command is executed. This makes it impossible to create a command and loop over results updating the command parameters.
This happens with Devart 8.4.225 and can be reproduced with the following code:
However - if the "$" is removed from the parameter names everything works as expected:
BR
When a parameter is added to a command and the parameter contains a trailing "$" sign, it happens that the returned instance is removed from the command ParameterCollection once the command is executed. This makes it impossible to create a command and loop over results updating the command parameters.
This happens with Devart 8.4.225 and can be reproduced with the following code:
Code: Select all
#region usings
using System.Data;
using Devart.Data.Oracle;
using Microsoft.VisualStudio.TestTools.UnitTesting;
#endregion
namespace MyCompany.DataProvider.Test.ProviderEvaluation
{
[TestClass]
public class DevartParameterReferenceTest
{
#region Constants and Fields
private const string ConnectionString = "Data Source=YPURSERVER;User ID=YOURUSER;Password=YOURPASSWORD;";
private const string TestProcedure = @"CREATE OR REPLACE PROCEDURE SAMPLE_DEVART_REF (P_FROMSERVICE$ VARCHAR2, P_METHOD$ VARCHAR2, P_DATA$ VARCHAR2)
IS
BEGIN
null;
END;";
#endregion
[TestInitialize]
public void Initialize()
{
using (OracleConnection connection = new OracleConnection(ConnectionString))
{
connection.Open();
using (OracleCommand createSpCommand = connection.CreateCommand())
{
createSpCommand.CommandText = TestProcedure;
createSpCommand.ExecuteNonQuery();
}
}
}
[TestMethod]
public void ParamReferencesStayValid()
{
using (OracleConnection connection = new OracleConnection(ConnectionString))
{
connection.Open();
using (OracleCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "SAMPLE_DEVART_REF";
command.Parameters.Add("P_FROMSERVICE$", OracleDbType.NVarChar, "...some value...", ParameterDirection.Input);
OracleParameter paramMethod = command.Parameters.Add("P_METHOD$", OracleDbType.NVarChar);
OracleParameter paramData = command.Parameters.Add("P_DATA$", OracleDbType.NVarChar);
for (int i = 0; i < 5; i++)
{
paramMethod.Value = "Method" + i;
paramData.Value = "Data" + i;
Assert.AreSame(paramMethod, command.Parameters["P_METHOD$"], "P_METHOD$ ref equal before {0}", i);
Assert.AreSame(paramData, command.Parameters["P_DATA$"], "P_DATA$ ref equal before {0}", i);
command.ExecuteNonQuery();
Assert.AreSame(paramMethod, command.Parameters["P_METHOD$"], "P_METHOD$ ref equal after {0}", i);
//unreachable - previous assertion fails...
//without assertions the same value would be updated each time.
Assert.AreSame(paramData, command.Parameters["P_DATA$"], "P_DATA$ ref equal after {0}", i);
}
}
}
}
}
}
Code: Select all
#region usings
using System.Data;
using Devart.Data.Oracle;
using Microsoft.VisualStudio.TestTools.UnitTesting;
#endregion
namespace MyCompany.DataProvider.Test.ProviderEvaluation
{
[TestClass]
public class DevartParameterReferenceTest
{
#region Constants and Fields
private const string ConnectionString = "Data Source=YPURSERVER;User ID=YOURUSER;Password=YOURPASSWORD;";
private const string TestProcedure = @"CREATE OR REPLACE PROCEDURE SAMPLE_DEVART_REF (P_FROMSERVICE VARCHAR2, P_METHOD VARCHAR2, P_DATA VARCHAR2)
IS
BEGIN
null;
END;";
#endregion
[TestInitialize]
public void Initialize()
{
using (OracleConnection connection = new OracleConnection(ConnectionString))
{
connection.Open();
using (OracleCommand createSpCommand = connection.CreateCommand())
{
createSpCommand.CommandText = TestProcedure;
createSpCommand.ExecuteNonQuery();
}
}
}
[TestMethod]
public void ParamReferencesStayValid()
{
using (OracleConnection connection = new OracleConnection(ConnectionString))
{
connection.Open();
using (OracleCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "SAMPLE_DEVART_REF";
command.Parameters.Add("P_FROMSERVICE", OracleDbType.NVarChar, "...some value...", ParameterDirection.Input);
OracleParameter paramMethod = command.Parameters.Add("P_METHOD", OracleDbType.NVarChar);
OracleParameter paramData = command.Parameters.Add("P_DATA", OracleDbType.NVarChar);
for (int i = 0; i < 5; i++)
{
paramMethod.Value = "Method" + i;
paramData.Value = "Data" + i;
Assert.AreSame(paramMethod, command.Parameters["P_METHOD"], "P_METHOD$ ref equal before {0}", i);
Assert.AreSame(paramData, command.Parameters["P_DATA"], "P_DATA$ ref equal before {0}", i);
command.ExecuteNonQuery();
Assert.AreSame(paramMethod, command.Parameters["P_METHOD"], "P_METHOD$ ref equal after {0}", i);
Assert.AreSame(paramData, command.Parameters["P_DATA"], "P_DATA$ ref equal after {0}", i);
//everything works great now - each loops write new values...
}
}
}
}
}
}