Nested CLR Stored Procedures

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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.
 
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
__________________________________________________________
 
Back
Top