SQL Server 2005 CLR Stored procedure - Select and then update.

I am trying to write a stored procedure that selects from one table, reads through that table record by record, and uses info from each record to create an update statement which i then run.

My problem is that SQL server is not letting me open more than one SqlConnection at a time.

Here is the code i have:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
    public static void Concat()
        SqlConnection conn = new SqlConnection("context connection=true");        
        SqlCommand cmd = new SqlCommand();
        SqlCommand cmd2 = new SqlCommand();
        SqlDataReader dataReader;
        SqlString strPm = "";

        cmd.Connection = conn;
        cmd2.Connection = conn;
        cmd.CommandText = "SELECT BLAH FROM DB.MyTable WHERE Blah2 != 1";
        dataReader = cmd.ExecuteReader();

        while (dataReader.Read())
            if (!dataReader.IsDBNull(1))
                strPm = Convert.ToString(dataReader["BLAH"]);
            cmd2.CommandText = "update MyTable2 set BLAH = '" + strPm + "'";

            cmd2.ExecuteNonQuery();  // <-- Fails here



when i run this procedure in SQL Server Management Studio it says:

Msg 6549, Level 16, State 1, Procedure Concat, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'Concat':
System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.
   at System.Data.SqlClient.SqlInternalConnectionSmi.ValidateConnectionForExecute(SqlCommand command)
   at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
   at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at StoredProcedures.Concat()
. User transaction, if any, will be rolled back.

Does anyone know how to do this?

LVL 15
Who is Participating?
NightmanConnect With a Mentor CTOCommented:
Ahh - missed that - context connection.

You can only have one open context connection at a time within a clr procedure ("context connection=true").  

have a look at http://codebetter.com/blogs/sahil.malik/archive/2005/07/26/129824.aspx for a good explanation (and see the comments from Niels Berglund at the bottom)
Quite simply you cannot have more than one active datareader on a single connection. Rather create a new connection object (you aren't - you are setting cmd2.Connection to the same connection object) and use that for the update.
dave4dlAuthor Commented:
Thanks for the post Nightman,

I gave that a shot when this problem cropped up and i got the error message:

Msg 6522, Level 16, State 1, Procedure Concat, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'Concat':
System.InvalidOperationException: The context connection is already in use.
   at System.Data.SqlClient.SqlInternalConnectionSmi.Activate()
   at System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnectionString options, Object providerInfo, DbConnection owningConnection)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at StoredProcedures.Concat()
Does the same not happen for you?
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

dave4dlAuthor Commented:
I guess i should say i added

SqlConnection conn2 = new SqlConnection("context connection=true");

and later opened that connection
dave4dlAuthor Commented:
I tried putting all my update statements into an array so i could execute them later but the stored proc failed saying it ran out of memory (there are over a million rows in this table).

I could save them to a file and execute that file later but i really dont want to have my stored procs storing stuff temporarily in holding files (because it feels like the wrong thing to do and because i forsee a lot of problems like concurrent runs, folder existence/permissions, disk space, etc).  This seems like it should be such a simple thing to do.
dave4dlAuthor Commented:
The codebetter link seems to just say that what i am trying to do (an update that runs for each record within a datareader "Read" loop) is not possible.
You may be able to do this directly (and far more efficiently) using T-SQL.

The general syntax for updating one table from another table is:

SET Col1 = TableB.Col2
WHERE TableA.Col6 = TableB.Col7

It may be that you are doing something more complicated than that, or maybe you want to learn CLR programmign in SQL. Not sure. If this looks like an option for you let me know and I can expand on it.
dave4dlAuthor Commented:
thanks for the post nmcdermaid.

Your right that i should just use sql if what i did in my example code for the post was all i needed to do.
Unfortunately i do need to use a clr stored proc because i am actually doing more than i posted.  In order to simplify my post i took out the more complex logic i was using that combined values from multiple rows.  
nmcdermaidConnect With a Mentor Commented:
You can probably still do it.... depends what you're doing. I can expand more if you give me an idea of your business rules.

If you are concatenating strings from multiple rows into one row it may eb a little trickier but you can use a T-SQL cursor.

It will still be quicker and you won't run into out of memory issues.

I don't mind helping if you want to expand on the logic. In fact I would be very interested to see if there is a T-SQL solution to your issue.

I agree with nmcdermaid - looking at the logic there is probably a set-based T-SQL method to do this (might not be, but even a T-SQL cursor would be more efficient). Post some of the logic here and I'm sure that we can help.

dave4dlAuthor Commented:
Thanks for your posts guys.

Actually i already had a solution that uses sql and cursors but it is really slow.  I needed to combining parts of varchars from multiple rows and the performance was really slow.  I have noticed that sql server string functions are less than speedy so i thought i could get a performance gain by coding it in a clr stored procedure.  

What i ended up doing was breaking the tasks into smaller chunks and using a clr function with a clr aggregate to get what i was looking for (and the performance gain was there but not very significant).

Thanks again for your help!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.