ASP.NET, SQL Server, WPF, C#, VB.NET

"Soren S. Jorgensen" <nospam@nodomain.com> wrote in message  
  
news:%23sL5njKGGHA.2320@TK2MSFTNGP11.phx.gbl...  
  
Really?  Not if I run the following procedure:  
  
////////////////////////////////////////  
  
public partial class StoredProcedures  
  
{  
  
[Microsoft.SqlServer.Server.SqlProcedure]  
  
public static void SomeProc()  
  
{  
  
SqlConnection conn = new SqlConnection("context connection=true");  
  
conn.Open();  
  
SqlContext.Pipe.Send(conn.State.ToString());  
  
conn.Close();  
  
return;  
  
}  
  
};  
  
////////////////////////////////////////  
  
--  
  
Adam Machanic  
  
Pro SQL Server 2005, available now  
  
http://www.apress.com/book/bookDisplay.html?bID=457  
  
--

How are you able to check if a context connection already has been opened ??  
  
ConnectionState on the connection will always return "Closed"  
  
Say you got a function in the CLR that you want to call from both inside and  
  
outside (through TSQL wrappers) the CLR. When calling the function from  
  
T-SQL the connection should be opened! On the other hand, if you already got  
  
a connection open inside CLR and then just call the static function (method)  
  
from whitin your code, the connection should not be opened.  
  
Seems silly that you have to create a new command and execute that, to "run"  
  
your function inside CLR  
  
Soren

"Soren S. Jorgensen" <nospam@nodomain.com> wrote in message  
  
news:%23tYTXYPGGHA.3700@TK2MSFTNGP15.phx.gbl...  
  
What exception are you seeing?  I believe that this should only throw an  
  
exception if your function is not marked for data access (set the DataAccess  
  
parameter of the SqlFunctionAttribute to DataAccessKind.Read).  
  
That's correct -- in early versions the SqlContext object had a  
  
Connection object that was already open.  That all changed with the April  
  
CTP.  The idea, as far as I'm aware, is to make code as portable across  
  
tiers as possible.  The same code can be used on the data tier or in the  
  
database, in many cases, simply by changing the connection string to use the  
  
context connection or not.  
  
--  
  
Adam Machanic  
  
Pro SQL Server 2005, available now  
  
http://www.apress.com/book/bookDisplay.html?bID=457  
  
--

"Soren S. Jorgensen" <nospam@nodomain.com> wrote in message  
  
news:%23b4$T$RGGHA.1124@TK2MSFTNGP10.phx.gbl...  
  
Can you post code to duplicate that exception?  I tried the function you  
  
posted.  As-is, I received an exception telling me that data access is not  
  
allowed (because you didn't have DataAccess=DataAccessKind.Read set in your  
  
SqlFunctionAttribute).  Once I fixed that problem, I noticed you were using  
  
the SqlPipe, which is not available from within user-defined functions.  
  
Upon removing that, the function runs fine on my end, opening the connection  
  
and closing it again without exception.  
  
--  
  
Adam Machanic  
  
Pro SQL Server 2005, available now  
  
http://www.apress.com/book/bookDisplay.html?bID=457  
  
--

By the way, here is the working version of the function:  
  
[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read)]  
  
public static int SomeFunc()  
  
{  
  
try  
  
{  
  
SqlConnection conn = new SqlConnection("context  
  
connection=true");  
  
conn.Open();  
  
// Do stuff...  
  
conn.Close();  
  
}  
  
catch(Exception e)  
  
{  
  
return -1;  
  
}  
  
return 0;  
  
}  
  
--  
  
Adam Machanic  
  
Pro SQL Server 2005, available now  
  
http://www.apress.com/book/bookDisplay.html?bID=457  
  
--  
  
"Soren S. Jorgensen" <nospam@nodomain.com> wrote in message  
  
news:%23b4$T$RGGHA.1124@TK2MSFTNGP10.phx.gbl...

Yes - but when doing this will throw a Exception in the function  
  
I was just assuming that a context connection always was available in the  
  
context of the current batch (kinda like a singleton), and a call to  
  
SqlConnection.Open() just was something implemented to make server code look  
  
more like client code, guess not!  
  
I seem to remember being told that in early releases of SQL2005 a call to  
  
SqlConnection.Open() was not nessecary in any case.  
  
So the point is; to be able to use SomeFunc()  from both T-SQL code and CLR  
  
code, you either use a SqlCommand to execute the function from inside the  
  
proc (I guess) or wrap conn.Open() in a try/catch clause in the function.  
  
public partial class StoredProcedures  
  
{  
  
[Microsoft.SqlServer.Server.SqlProcedure]  
  
public static void SomeProc()  
  
{  
  
SqlConnection conn = new SqlConnection("context connection=true");  
  
conn.Open();  
  
SqlContext.Pipe.Send(("Procedure state: " + conn.State.ToString());  
  
int res = StoredProcedures.SomeFunc();  
  
conn.Close();  
  
return;  
  
}  
  
[Microsoft.SqlServer.Server.SqlFunction]  
  
public static int SomeFunc()  
  
{  
  
try  
  
{  
  
SqlConnection conn = new SqlConnection("context  
  
connection=true");  
  
SqlContext.Pipe.Send("Function state: " +  
  
conn.State.ToString());  
  
conn.Open();  
  
// Do stuff...  
  
conn.Close();  
  
}  
  
catch(Exception e)  
  
{  
  
SqlContext.Pipe.Send(e.ToString());  
  
return -1  
  
}  
  
return 0;  
  
}  
  
}  
  
Soren  
  
"Adam Machanic" <amachanic@hotmail._removetoemail_.com> skrev i en  
  
meddelelse news:u0sXvqLGGHA.552@TK2MSFTNGP10.phx.gbl...

"Soren S. Jorgensen" <nospam@nodomain.com> wrote in message  
  
news:ec61PsSGGHA.608@TK2MSFTNGP14.phx.gbl...  
  
Okay, now I see what you're trying to do... But the question is, why?  
  
Can you explain your requirement for an open connection in both the stored  
  
procedure and the function simultaneously?  
  
--  
  
Adam Machanic  
  
Pro SQL Server 2005, available now  
  
http://www.apress.com/book/bookDisplay.html?bID=457  
  
--

You may be able to a Singleton pattern with the connection object.  Expose a  
  
Connection static property on some UDT that either returns the current  
  
connection or creates a new one.  Not sure of all the issues that may or may  
  
not have.  
  
--  
  
William Stacey [MVP]  
  
"Soren S. Jorgensen" <nospam@nodomain.com> wrote in message  
  
news:upWSWTTGGHA.3624@TK2MSFTNGP09.phx.gbl...  
  
and  
  
is'nt  
  
without  
  
seeing  
  
called  
  
stored

Not following.  An exception is thrown even if you don't have a current conn  
  
open?  
  
Also, are you saying the conn object will attach to an existing open  
  
connection?  
  
--  
  
William Stacey [MVP]  
  
"Soren S. Jorgensen" <nospam@nodomain.com> wrote in message  
  
news:u28nSiTGGHA.1424@TK2MSFTNGP12.phx.gbl...  
  
Expose  
  
from  
  
proc  
  
CLR

"Soren S. Jorgensen" <nospam@nodomain.com> wrote in message  
  
news:upWSWTTGGHA.3624@TK2MSFTNGP09.phx.gbl...  
  
So what?  
  
Why are you opening the connection before calling the function in your  
  
CLR proc?  
  
--  
  
Adam Machanic  
  
Pro SQL Server 2005, available now  
  
http://www.apress.com/book/bookDisplay.html?bID=457  
  
--

System.InvalidOperationException: The context connection is already in use.  
  
Well - in theory, this sounds like a good idea - but in practice ??  
  
Soren

There is nothing wrong with the function it self, but the way it's called in  
  
SomeProc will produce that exception  
  
First deploy the code below, then run this script:  
  
declare @result nvarchar(max)  
  
exec dbo.SomeProc @result = @result output  
  
select @result as SomeProcResult  
  
exec dbo.OtherProc @result = @result output  
  
select @result as OtherProcResult  
  
select dbo.SomeFunc() as SomeFuncResult  
  
Here's the (entire) code:  
  
using System;  
  
using System.Data;  
  
using System.Data.SqlClient;  
  
using System.Data.SqlTypes;  
  
using Microsoft.SqlServer.Server;  
  
public partial class StoredProcedures  
  
{  
  
[SqlProcedure]  
  
public static void SomeProc([SqlFacet(MaxSize = -1)] out string result)  
  
{  
  
result = "N/A";  
  
using(SqlConnection conn = new SqlConnection("context connection=true"))  
  
{  
  
conn.Open();  
  
result = StoredProcedures.SomeFunc();  
  
}  
  
}  
  
[SqlProcedure]  
  
public static void OtherProc([SqlFacet(MaxSize = -1)] out string result)  
  
{  
  
result = "N/A";  
  
using(SqlConnection conn = new SqlConnection("context connection=true"))  
  
{  
  
conn.Open();  
  
SqlCommand command = conn.CreateCommand();  
  
command.CommandText = "select dbo.SomeFunc()";  
  
SqlDataReader reader = command.ExecuteReader();  
  
while(reader.Read())  
  
{  
  
result = reader.GetString(0);  
  
}  
  
reader.Close();  
  
}  
  
}  
  
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]  
  
public static string SomeFunc()  
  
{  
  
string result = "N/A";  
  
try  
  
{  
  
using(SqlConnection conn = new SqlConnection("context connection=true"))  
  
{  
  
conn.Open();  
  
result = "No exception";  
  
}  
  
}  
  
catch(Exception e)  
  
{  
  
result = e.ToString();  
  
}  
  
return result;  
  
}  
  
}  
  
Running the script will return:  
  
SomeProcResult = System.InvalidOperationException: The context connection is  
  
already in use...  
  
OtherProcResult = No exception  
  
SomeFuncResult = No exception  
  
Soren

I want to be able to use the function from a regular T-SQL proc, and from a  
  
CLR proc as well !!  
  
My point was that if I want to use the function from inside the CLR proc I  
  
have to "wrap" it into a SqlCommand (instead of calling it directly)  
  
This gives me a overhead of code, and (I think) a roundtrip outside CLR and  
  
back again!  
  
So, if there can be only one context connection (wich makes sence) why is'nt  
  
a second call to SqlConnection.Open() simply ignored. The connection  
  
(returned from a second new SqlConnection(...)) is perfectly useable without  
  
calling Open()!!  
  
But, when the function is called from inside the CLR I have no way of seeing  
  
whether this connection is already open. When called from outside CLR, of  
  
course, it needs to be opened.  
  
Soren  
  
"Adam Machanic" <amachanic@hotmail._removetoemail_.com> skrev i en  
  
meddelelse news:%23sNQSySGGHA.3864@tk2msftngp13.phx.gbl...

Thanx, but this will also do the trick (not so pretty, though)  
  
using(SqlConnection conn = new SqlConnection("context connection=true"))  
  
{  
  
try  
  
{  
  
conn.Open();  
  
}  
  
catch  
  
{  
  
}  
  
// Do stuff  
  
}  
  
Million dollar question:  
  
Why are an exeption thrown if the connection is valid ??  
  
Soren  
  
"William Stacey [MVP]" <william.stacey@gmail.com> skrev i en meddelelse  
  
news:eyg5RZTGGHA.2912@tk2msftngp13.phx.gbl...

"Soren S. Jorgensen" <nospam@nodomain.com> wrote in  
  
news:upWSWTTGGHA.3624@TK2MSFTNGP09.phx.gbl:  
  
Yes you are right, that iut will roundtrip from CLR to SQL and back.  
  
However this is all in-proc, so it's not that bad - but I agree with  
  
your sentiment.  
  
You must see something different from what I am seeing. In the  
  
proc/function which is called from the first proc, if I create a new  
  
SqlConnection and do not open it - I'll get an exception if I, for  
  
example, try to execute a command which returns data.  
  
Niels  
  
--  
  
**************************************************  
  
* Niels Berglund  
  
* http://staff.develop.com/nielsb  
  
* nielsb@no-spam.develop.com  
  
* "A First Look at SQL Server 2005 for Developers"  
  
* http://www.awprofessional.com/title/0321180593  
  
**************************************************

"Soren S. Jorgensen" <nospam@nodomain.com> wrote in message  
  
news:%23xoz3aeGGHA.1332@TK2MSFTNGP10.phx.gbl...  
  
Good! :)  
  
The more I thought about this the more it didn't make sense.  The  
  
context connection is a singleton resource, and just like any other  
  
singleton you shouldn't be able to have two of them at once, whether or not  
  
you explicitly attach to it (i.e., open the connection.)  
  
--  
  
Adam Machanic  
  
Pro SQL Server 2005, available now  
  
http://www.apress.com/book/bookDisplay.html?bID=457  
  
--

No, sorry. I'm off track here. When I'm actually executing the command, I  
  
get an exception saying that the connection needs to be opened (as it's  
  
surpose to do) :-)  
  
My mistake - but never the less, point is still: why don't they just give me  
  
the one and only context connection (of the batch) when creating a second  
  
connection in the function ?  
  
Soren

You're right, I had a default value in the original function preventing the  
  
SqlCommand to actually being executed (damn...) !!  
  
I get an exception as well now :-/  
  
Soren

The original code is of course a bit more complex than the simplistic  
  
samples from previous posts - but it can fixed with a little redesign, or I  
  
simply wrap the function call into a SqlCommand!  
  
Soren

"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in  
  
news:eoD7QChGGHA.1312@TK2MSFTNGP09.phx.gbl:  
  
No, but what he is saying is that the context connection should flow  
  
from proc1 to the function. Somehow we should be able to indicate that  
  
this is not a new context connection, but re-use of the initial.  
  
Niels  
  
--  
  
**************************************************  
  
* Niels Berglund  
  
* http://staff.develop.com/nielsb  
  
* nielsb@no-spam.develop.com  
  
* "A First Look at SQL Server 2005 for Developers"  
  
* http://www.awprofessional.com/title/0321180593  
  
**************************************************

"Soren S. Jorgensen" <nospam@nodomain.com> wrote in  
  
news:ehhOOKpGGHA.1396@TK2MSFTNGP11.phx.gbl:  
  
Amen brother!! It just to be like this before they merged the server and  
  
client side provider's. I so wish they'd go back to that; i.e. the  
  
connection as a property of the SqlContext.  
  
Niels  
  
--  
  
**************************************************  
  
* Niels Berglund  
  
* http://staff.develop.com/nielsb  
  
* nielsb@no-spam.develop.com  
  
* "A First Look at SQL Server 2005 for Developers"  
  
* http://www.awprofessional.com/title/0321180593  
  
**************************************************

"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in  
  
news:u1c9XKrGGHA.1452@TK2MSFTNGP11.phx.gbl:  
  
Well, first of all - it did work before; before they merged the server  
  
and client provider, secondly it works if you call it over SqlCommand,  
  
i.e:  
  
void Proc1() {  
  
SqlConnection conn = new SqlConnection("Context Connection=true");  
  
conn.Open();  
  
SqlCommand cmd = conn.CreateCommand();  
  
cmd.CommandText = "Exec Proc2";  
  
}  
  
void Proc2() {  
  
SqlConnection conn = new SqlConnection("Context Connection=true");  
  
conn.Open();  
  
SqlCommand cmd = conn.CreateCommand();  
  
cmd.CommandText = "do_something_else";  
  
}  
  
But sure, I can see that it is a singleton and therefore it shouldn't  
  
work; but is is not like we are opening a new connection per se, I am in  
  
a context and I want to use the connection that is opened in that  
  
context.  
  
Niels  
  
--  
  
**************************************************  
  
* Niels Berglund  
  
* http://staff.develop.com/nielsb  
  
* nielsb@no-spam.develop.com  
  
* "A First Look at SQL Server 2005 for Developers"  
  
* http://www.awprofessional.com/title/0321180593  
  
**************************************************

"Niels Berglund" <nielsb@develop.com> wrote in message  
  
news:Xns974D541765585nielsbdevelopcom@207.46.248.16...  
  
Can you do that with any other type of singleton resource?  Think about  
  
files, GDI resources, etc.  You can't do that with any of them.  Why should  
  
this be any different?  
  
--  
  
Adam Machanic  
  
Pro SQL Server 2005, available now  
  
http://www.apress.com/book/bookDisplay.html?bID=457  
  
--

Excactly, thanks Niels :)  
  
Or even better, just reference the (singleton) context connection in every  
  
new construct of SqlConnection and then stop throwing an exception in  
  
SqlConnecttion.Open() if the connection already is open (under the  
  
assumption that SqlConnection class is "just" a wrapper class around the  
  
actual connection, this should be do-able).  
  
In T-SQL we don't care about the connection (as long as we got it, it's  
  
always there), we don't open a "new" connection for each function call !!  
  
If MS has decided to implement the context connection like a SqlConnection,  
  
so peace be with that (in my humble opinion a reference to the connection  
  
could be obtained smarter by putting it on SqlContext, like "public static  
  
SqlConnection SqlContext.Connection { get; }"), but still; the context  
  
connection is the context connection, no matter how many times I try to  
  
reference it!!  
  
Soren

"Niels Berglund" <nielsb@develop.com> wrote in message  
  
news:Xns974DAFE00C4C4nielsbdevelopcom@207.46.248.16...  
  
No problem...  
  
private static int GetMeSomeData(SqlConnection conn)  
  
{  
  
//...  
  
}  
  
--  
  
Adam Machanic  
  
Pro SQL Server 2005, available now  
  
http://www.apress.com/book/bookDisplay.html?bID=457  
  
--

"Soren S. Jorgensen" <nospam@nodomain.com> wrote in message  
  
news:uk55BnsGGHA.1332@TK2MSFTNGP10.phx.gbl...  
  
I'm not sure that's true.  My understanding is that one of the design  
  
goals behind the unified provider is that code can be migrated between tiers  
  
very easily -- in many cases, all that will be required is changing the  
  
connection string.  
  
--  
  
Adam Machanic  
  
Pro SQL Server 2005, available now  
  
http://www.apress.com/book/bookDisplay.html?bID=457  
  
--

You are inside the CLR, in a hosted environment, of the MS SQL Server 2005.  
  
This makes all the difference! There is no environment like this anywhere  
  
else, futhermore there's a lot of other things you may or may not do in this  
  
environment that you cannot do anywhere else. Say, creating an executable  
  
context connection, triggers, UDF's ect. This will always be server-side,  
  
not client-side, code!  
  
Soren

"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in  
  
news:u$lcotsGGHA.3120@TK2MSFTNGP10.phx.gbl:  
  
.... and that was one of the most stupid decisions they've made!!  
  
(and if you ask me nicely. I'll tell you what I really think <g>)  
  
Niels  
  
--  
  
**************************************************  
  
* Niels Berglund  
  
* http://staff.develop.com/nielsb  
  
* nielsb@no-spam.develop.com  
  
* "A First Look at SQL Server 2005 for Developers"  
  
* http://www.awprofessional.com/title/0321180593  
  
**************************************************