Nested CLR Stored Procedures

[Guest]

I don't know id this is ADO but the SQLServer.clr newgroup is not in the
managed groups list so sorry if I have misposted

I have an one clr stored procedure that would like to call from another
another.


public static void ParentSproc(SqlInt64 id, SqlString userName, out
SqlInt32 recordsAffected)
{
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = @"DELETE FROM Blah WHERE id= @id";

SqlParameter p_id = new SqlParameter("@id", SqlDbType.BigInt);
p_id.Value = id;
command.Parameters.Add(p_id);

connection.Open();
recordsAffected = command.ExecuteNonQuery();
connection.Close();

if (recordsAffected > 0)
{
Audit(id, "Blah ", "DELETE", userName);
}
}
}

This works well except I would like to pass the command or the connection
through so it can be reused. and not have to be closed and reopened.

But when I deploy to SQL Server it complains that the Command object is not
a SqlParameter type, (which, in fact, it's not). So is there a way to wrap
numerous sprocs in one connection?

Thanks

Robert Zurer

 

[Pablo Castro [MS]]

Hi,

You can't pass the connection through a SqlParameter object, the SqlClient
provider won't know how to marshal it through the SQL call interface.

However, not that when you're using the context connection, establishing the
connection ("Open") is a very fast operation, as it's simply hooking up with
the environment, no connection is actually being opened (if a proc is
running is because somebody executed it through a connection, and "open" on
the context connection actually means "pick up that connection"). So just
open/close the connections in each nested proc, you won't see a perf hit
because of that.

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

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

 

[Guest]

Thanks so much for your reply.

Performance was one concern, but my main reason for asking was that I
wanted to execute multiple procedures within one transaction.
Traditionally I would open a transaction execute my commands, commit and
close the connection.

I'm not sure if this is supported in CLR procedures.

 

[Sahil Malik [MVP C#]]

Robert,

The stored procedures written in CLR or otherwise, will automatically enlist
in the current running transaction.
I would simply use Context Connections in your CLR procs, and wrap your CLR
stored proc calls in an external SqlTransaction - that should do the trick.
And if from within the SQLCLR stored procedure you wanted to rollback the
transaction, you could simply call
System.Transactions.Transaction.Current.Rollback

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________