A value copy, not reference, of SqlParameter?

  • Thread starter Thread starter Tim Zych
  • Start date Start date
T

Tim Zych

' Declare a new parameter object
Dim param() As SqlParameter = New SqlParameter(0) {}
' Set this to null and make it an InputOutput parameter
param(0) = New SqlParameter("@Something, DBNull.Value) ' Can also be
non-null, but sometimes is null
param(0).Direction = ParameterDirection.InputOutput
' But, before we begin, store a copy of the existing parameters into another
variable
Dim param2() As SqlParameter = New SqlParameter(0) {}
param2 = param
' Run the stored proc. The stored proc has an OUTPUT parameter which will
return a value to the caller
SqlHelper.ExecuteNonQuery("ConnStr", CommandType.StoredProcedure,
"dbo.MyStoredProc, param)

WHERE
MyStoredProc's @Something parameter is an OUTPUT parameter, and after the
stored proc is finished running, BOTH SqlParameters have the same value.

It looks like "param2 = param" is a reference copy. How can I copy by value
only so that, when the Output argument returns a value to the first
parameter, param2 does NOT have the same value. I want a value copy, not a
reference copy. How do I do that?
 
Tim,
It looks like "param2 = param" is a reference copy. How can I copy by value
only so that, when the Output argument returns a value to the first
parameter, param2 does NOT have the same value. I want a value copy, not a
reference copy. How do I do that?
Click to expand...

SqlParameter implements ICloneable, so try this

param2(0) = CType(CType(param(0), ICloneable).Clone(), SqlParameter)


Mattias
 
Thanks...that seems to work fine for a single parameter.
Is there a way to modify it so that I can copy the entire parameter list
over?

Here's what I'm testing with.

This works for the Output parameter.

ALTER PROCEDURE dbo.zzzTest
(
@Arg INT OUTPUT
)
AS
SET @Arg = 999
RETURN

' This works for a single parameter
Dim arParms() As SqlParameter = New SqlParameter(0) {}
arParms(0) = New SqlParameter("@Arg", DBNull.Value)
arParms(0).Direction = ParameterDirection.InputOutput
arParms(0).DbType = DbType.Int32

Dim arParms2() As SqlParameter = New SqlParameter(0) {}

' Your implementation:
arParms2(0) = CType(CType(arParms(0), ICloneable).Clone,
SqlParameter)

Dim ConnStr As String
ConnStr = "<MyConnectionString>"

Call SqlHelper.ExecuteNonQuery(ConnStr ,
CommandType.StoredProcedure, _
"dbo.zzzTest", arParms)

Debug.Write(arParms(0).Value) ' Returns 999
Debug.Write(arParms2(0).Value) ' Returns NULL (desired result)


How do you modify this so that it works with multiple parameters. Basically
I'd like to extend the code to do this. This is test code...I hope nobody
asks me why I have such a stupid example...it's a test.

ALTER PROCEDURE dbo.zzzTest
(
@Arg INT OUTPUT,
@Arg2 INT
)
AS
SET @Arg = 999
RETURN

Dim arParms() As SqlParameter = New SqlParameter(1) {}
arParms(0) = New SqlParameter("@Arg", DBNull.Value)
arParms(0).Direction = ParameterDirection.InputOutput
arParms(0).DbType = DbType.Int32
arParms(1) = New SqlParameter("@Arg2", DBNull.Value)

Dim arParms2() As SqlParameter = New SqlParameter(1) {}

How do I pass all parameters (1...N) from arParms to arParms2, value-only,
not reference? Or am I missing something?

Thanks.
 
Thanks...that seems to work fine for a single parameter.
Is there a way to modify it so that I can copy the entire parameter list
over?

Here's what I'm testing with.

This works for the Output parameter.

ALTER PROCEDURE dbo.zzzTest
(
@Arg INT OUTPUT
)
AS
SET @Arg = 999
RETURN

' This works for a single parameter
Dim arParms() As SqlParameter = New SqlParameter(0) {}
arParms(0) = New SqlParameter("@Arg", DBNull.Value)
arParms(0).Direction = ParameterDirection.InputOutput
arParms(0).DbType = DbType.Int32

Dim arParms2() As SqlParameter = New SqlParameter(0) {}

' Your implementation:
arParms2(0) = CType(CType(arParms(0), ICloneable).Clone,
SqlParameter)

Dim ConnStr As String
ConnStr = "<MyConnectionString>"

Call SqlHelper.ExecuteNonQuery(ConnStr ,
CommandType.StoredProcedure, _
"dbo.zzzTest", arParms)

Debug.Write(arParms(0).Value) ' Returns 999
Debug.Write(arParms2(0).Value) ' Returns NULL (desired result)

How do you modify this so that it works with multiple parameters. Basically
I'd like to extend the code to do this. This is test code...I hope nobody
asks me why I have such a stupid example...it's a test.

ALTER PROCEDURE dbo.zzzTest
(
@Arg INT OUTPUT,
@Arg2 INT
)
AS
SET @Arg = 999
RETURN

Dim arParms() As SqlParameter = New SqlParameter(1) {}
arParms(0) = New SqlParameter("@Arg", DBNull.Value)
arParms(0).Direction = ParameterDirection.InputOutput
arParms(0).DbType = DbType.Int32
arParms(1) = New SqlParameter("@Arg2", DBNull.Value)

Dim arParms2() As SqlParameter = New SqlParameter(1) {}

How do I pass all parameters (1...N) from arParms to arParms2, value-only,
not reference? Or am I missing something?

Thanks.
Click to expand...


VB.Net has a nifty statement called For Next which should help you :

Dim arParams2(arParms.Length-1) As SqlParameter
For x As Integer = 0 To arParms.Length - 1
arParms2(x) = CType(CType(param(x), ICloneable).Clone(),
SqlParameter)
Next
 
Funny you should post that...that was exactly how I decided to "get around"
the one fell swoop issue.

Thanks.


news:[email protected]...
Click to expand...


VB.Net has a nifty statement called For Next which should help you :

Dim arParams2(arParms.Length-1) As SqlParameter
For x As Integer = 0 To arParms.Length - 1
arParms2(x) = CType(CType(param(x), ICloneable).Clone(),
SqlParameter)
Next
 
Funny you should post that...that was exactly how I decided to "get around"
the one fell swoop issue.

Thanks.





VB.Net has a nifty statement called For Next which should help you :

Dim arParams2(arParms.Length-1) As SqlParameter
For x As Integer = 0 To arParms.Length - 1
arParms2(x) = CType(CType(param(x), ICloneable).Clone(),
SqlParameter)
Next
Click to expand...


Another option that is available is after you add the parameters to
the SqlCommand object, you can use the command object's CopyTo method
to copy them to an array.

Chris
 
Chris said:
Another option that is available is after you add the parameters to
the SqlCommand object, you can use the command object's CopyTo method
to copy them to an array.

Chris
Click to expand...

The CopyTo method will only copy the references to the parameters, not
create clones of the parameters.
 
Back
Top