Bug in SqlCommand?

[phil]

I am experiencing a problem with SqlCommand and
SqlParameters. I create a seperate command based on a
single connect/transaction and then add sqlparameters to
each before I prepare the command.

The insert works fine, the update command comes back
with: : The SqlParameter with ParameterName '@P2' is
already contained by another SqlParameterCollection.

The code runs against a table foo (identity int one, int
two, int three). Here's the code:

using System;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
protected const string sqlInsert = "INSERT INTO foo
(two, three)" +
" Values(@P2,@P3);" +
" SELECT SCOPE_IDENTITY() FROM foo;";

protected const string sqlUpdate =
"UPDATE foo SET two=@P2,three=@P3 WHERE one=@P1";

protected static SqlParameter[] asParms =
{
new SqlParameter("@P1",
SqlDbType.Int), //one
new SqlParameter("@P2",
SqlDbType.Int), //two
new SqlParameter("@P3",
SqlDbType.Int) //three
};


private int Insert(ref SqlTransaction trans)
{
SqlDataReader reader = null;
try
{
if (insertCommand != null)
{
insertCommand.Connection =
trans.Connection;
insertCommand.Transaction = trans;
}
else
{
insertCommand = new SqlCommand(null,
trans.Connection);
insertCommand.Transaction = trans;
insertCommand.CommandText = sqlInsert;

foreach (SqlParameter p in asParms)
{
insertCommand.Parameters.Add(p);
}
insertCommand.Parameters.RemoveAt("@P1");
insertCommand.Prepare();
}

//command.Parameters["@P0"].Value =
or.OutageId;
this.SetCommonParamValues(-1, ref
insertCommand);

reader = insertCommand.ExecuteReader();

int results = 0;
if (reader.Read())
{
decimal dec = reader.GetDecimal(0);
results = Convert.ToInt32(dec);
}
else
{
Console.Write("bad");
}
reader.Close();
reader = null;
return results;
}
catch (Exception e)
{
Console.Write("boy");
return -1;
}
finally
{
if (reader != null) reader.Close();
}
}


protected static SqlCommand insertCommand = null;


// dumb proc to add some values
protected static int t = 0;
private void SetCommonParamValues(int key, ref
SqlCommand command)
{
if (key != -1)
command.Parameters["@P1"].Value =
key;
command.Parameters["@P2"].Value = t;
command.Parameters["@P3"].Value = ++t;

}


private bool Update(int key, ref SqlTransaction
trans)
{
try
{
if (updateCommand != null)
{
updateCommand.Connection =
trans.Connection;
updateCommand.Transaction = trans;
}
else
{
updateCommand = new SqlCommand(null,
trans.Connection);
updateCommand.Transaction = trans;
updateCommand.CommandText = sqlUpdate;

foreach (SqlParameter p in asParms)
{
updateCommand.Parameters.Add(p);
}

updateCommand.Prepare();

}
this.SetCommonParamValues(key, ref
updateCommand);

int rows = updateCommand.ExecuteNonQuery
();
return (rows == 1);
}
finally
{
}
}

protected static SqlCommand updateCommand = null;

[STAThread]
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection
("server=localhost;database=MyDatabase;uid=sa;pwd=Buggy");
conn.Open();
SqlTransaction t = conn.BeginTransaction();
Class1 c = new Class1();
int i = c.Insert(ref t);
c.Update(i, ref t);
t.Commit();
i = c.Insert(ref t);
c.Update(i, ref t);
t.Commit();
t.Dispose();
conn.Close();
}
}
}

Much thanks for any help!
Phil

 

[Pablo Castro [MS]]

This is by design. Parameters can be bound to only one parameter collection
at a time. This means that if you want to re-use the parameters in your
asParms array in a second SqlCommand instance, you'll have to remove them
from the first command's parameter collection (i.e. calling Clear() in the
"Parameters" property of the command).

Also note that if you actually do that (instead of keeping two copies of
parameters) then calling prepare is pointless, because if you change the
number/type of parameters the command is automatically un-prepared. Whether
you choose to skip the prepare or to duplicate the parameters depends on
your actual scenario.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

I am experiencing a problem with SqlCommand and
SqlParameters. I create a seperate command based on a
single connect/transaction and then add sqlparameters to
each before I prepare the command.

The insert works fine, the update command comes back
with: : The SqlParameter with ParameterName '@P2' is
already contained by another SqlParameterCollection.

The code runs against a table foo (identity int one, int
two, int three). Here's the code:

using System;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
protected const string sqlInsert = "INSERT INTO foo
(two, three)" +
" Values(@P2,@P3);" +
" SELECT SCOPE_IDENTITY() FROM foo;";

protected const string sqlUpdate =
"UPDATE foo SET two=@P2,three=@P3 WHERE one=@P1";

protected static SqlParameter[] asParms =
{
new SqlParameter("@P1",
SqlDbType.Int), //one
new SqlParameter("@P2",
SqlDbType.Int), //two
new SqlParameter("@P3",
SqlDbType.Int) //three
};


private int Insert(ref SqlTransaction trans)
{
SqlDataReader reader = null;
try
{
if (insertCommand != null)
{
insertCommand.Connection =
trans.Connection;
insertCommand.Transaction = trans;
}
else
{
insertCommand = new SqlCommand(null,
trans.Connection);
insertCommand.Transaction = trans;
insertCommand.CommandText = sqlInsert;

foreach (SqlParameter p in asParms)
{
insertCommand.Parameters.Add(p);
}
insertCommand.Parameters.RemoveAt("@P1");
insertCommand.Prepare();
}

//command.Parameters["@P0"].Value =
or.OutageId;
this.SetCommonParamValues(-1, ref
insertCommand);

reader = insertCommand.ExecuteReader();

int results = 0;
if (reader.Read())
{
decimal dec = reader.GetDecimal(0);
results = Convert.ToInt32(dec);
}
else
{
Console.Write("bad");
}
reader.Close();
reader = null;
return results;
}
catch (Exception e)
{
Console.Write("boy");
return -1;
}
finally
{
if (reader != null) reader.Close();
}
}


protected static SqlCommand insertCommand = null;


// dumb proc to add some values
protected static int t = 0;
private void SetCommonParamValues(int key, ref
SqlCommand command)
{
if (key != -1)
command.Parameters["@P1"].Value =
key;
command.Parameters["@P2"].Value = t;
command.Parameters["@P3"].Value = ++t;

}


private bool Update(int key, ref SqlTransaction
trans)
{
try
{
if (updateCommand != null)
{
updateCommand.Connection =
trans.Connection;
updateCommand.Transaction = trans;
}
else
{
updateCommand = new SqlCommand(null,
trans.Connection);
updateCommand.Transaction = trans;
updateCommand.CommandText = sqlUpdate;

foreach (SqlParameter p in asParms)
{
updateCommand.Parameters.Add(p);
}

updateCommand.Prepare();

}
this.SetCommonParamValues(key, ref
updateCommand);

int rows = updateCommand.ExecuteNonQuery
();
return (rows == 1);
}
finally
{
}
}

protected static SqlCommand updateCommand = null;

[STAThread]
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection
("server=localhost;database=MyDatabase;uid=sa;pwd=Buggy");
conn.Open();
SqlTransaction t = conn.BeginTransaction();
Class1 c = new Class1();
int i = c.Insert(ref t);
c.Update(i, ref t);
t.Commit();
i = c.Insert(ref t);
c.Update(i, ref t);
t.Commit();
t.Dispose();
conn.Close();
}
}
}

Much thanks for any help!
Phil

 

[Pablo Castro [MS]]

May be it's not quite obvious, but it's noted in the Add method of
SqlParametersCollection:

From MSDN:
ArgumentException
The SqlParameter specified in the value parameter is already added to this
or another SqlParameterCollection.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

Thanks Pablo, actually found that out last night about
11PM So it is my bug... Now my code does just what
you suggest, creates two copies of the same parameters
for both commands.

Did I miss the fact in the documentation that this was
designed this way? Perhaps I am more unique than I'd
like to admit, but I doubt it -- I am sure others have
followed down this path. I read the documentation but
nothing struck me as this behavior being so...

Thanks again,
Phil

-----Original Message-----
This is by design. Parameters can be bound to only one parameter collection
at a time. This means that if you want to re-use the parameters in your
asParms array in a second SqlCommand instance, you'll have to remove them
from the first command's parameter collection (i.e. calling Clear() in the
"Parameters" property of the command).

Also note that if you actually do that (instead of keeping two copies of
parameters) then calling prepare is pointless, because if you change the
number/type of parameters the command is automatically un-prepared. Whether
you choose to skip the prepare or to duplicate the parameters depends on
your actual scenario.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

I am experiencing a problem with SqlCommand and
SqlParameters. I create a seperate command based on a
single connect/transaction and then add sqlparameters to
each before I prepare the command.

The insert works fine, the update command comes back
with: : The SqlParameter with ParameterName '@P2' is
already contained by another SqlParameterCollection.

The code runs against a table foo (identity int one, int
two, int three). Here's the code:

using System;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
protected const string sqlInsert = "INSERT INTO foo
(two, three)" +
" Values(@P2,@P3);" +
" SELECT SCOPE_IDENTITY() FROM foo;";

protected const string sqlUpdate =
"UPDATE foo SET two=@P2,three=@P3 WHERE one=@P1";

protected static SqlParameter[] asParms =
{
new SqlParameter("@P1",
SqlDbType.Int), //one
new SqlParameter("@P2",
SqlDbType.Int), //two
new SqlParameter("@P3",
SqlDbType.Int) //three
};


private int Insert(ref SqlTransaction trans)
{
SqlDataReader reader = null;
try
{
if (insertCommand != null)
{
insertCommand.Connection =
trans.Connection;
insertCommand.Transaction = trans;
}
else
{
insertCommand = new SqlCommand(null,
trans.Connection);
insertCommand.Transaction = trans;
insertCommand.CommandText = sqlInsert;

foreach (SqlParameter p in asParms)
{
insertCommand.Parameters.Add(p);
}
insertCommand.Parameters.RemoveAt ("@P1");
insertCommand.Prepare();
}

//command.Parameters["@P0"].Value =
or.OutageId;
this.SetCommonParamValues(-1, ref
insertCommand);

reader = insertCommand.ExecuteReader();

int results = 0;
if (reader.Read())
{
decimal dec = reader.GetDecimal(0);
results = Convert.ToInt32(dec);
}
else
{
Console.Write("bad");
}
reader.Close();
reader = null;
return results;
}
catch (Exception e)
{
Console.Write("boy");
return -1;
}
finally
{
if (reader != null) reader.Close();
}
}


protected static SqlCommand insertCommand = null;


// dumb proc to add some values
protected static int t = 0;
private void SetCommonParamValues(int key, ref
SqlCommand command)
{
if (key != -1)
command.Parameters["@P1"].Value =
key;
command.Parameters["@P2"].Value = t;
command.Parameters["@P3"].Value = ++t;

}


private bool Update(int key, ref SqlTransaction
trans)
{
try
{
if (updateCommand != null)
{
updateCommand.Connection =
trans.Connection;
updateCommand.Transaction = trans;
}
else
{
updateCommand = new SqlCommand(null,
trans.Connection);
updateCommand.Transaction = trans;
updateCommand.CommandText = sqlUpdate;

foreach (SqlParameter p in asParms)
{
updateCommand.Parameters.Add(p);
}

updateCommand.Prepare();

}
this.SetCommonParamValues(key, ref
updateCommand);

int rows = updateCommand.ExecuteNonQuery
();
return (rows == 1);
}
finally
{
}
}

protected static SqlCommand updateCommand = null;

[STAThread]
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection
("server=localhost;database=MyDatabase;uid=sa;pwd=Buggy");
conn.Open();
SqlTransaction t = conn.BeginTransaction();
Class1 c = new Class1();
int i = c.Insert(ref t);
c.Update(i, ref t);
t.Commit();
i = c.Insert(ref t);
c.Update(i, ref t);
t.Commit();
t.Dispose();
conn.Close();
}
}
}

Much thanks for any help!
Phil

.