using CLR Stored Procedure

[lausivcid]

Hi,

Is it possible to read the SqlContext.Pipe.Send? string output of a
SQL Server 2005 CLR Stored Procedure in an external WinForms C#
project?

Thanks,
Lausivcid

 

[Alberto Poblacion]

in message

Is it possible to read the SqlContext.Pipe.Send? string output of a
SQL Server 2005 CLR Stored Procedure in an external WinForms C#
project?

Yes, from the client side the Pipe.Send behaves like a PRINT statement in
T-SQL, so you can receive its output in the same way, by means of the
InfoMessage event of your SqlConnection object.

 

[lausivcid]

in message


Yes, from the client side the Pipe.Send behaves like a PRINT statement in
T-SQL, so you can receive its output in the same way, by means of the
InfoMessage event of your SqlConnection object.

It was late, please let me rephrase the question: how can I call
and return a string from the below SQL Server 2005 hosted CLR SPRIC
from an external WinForms C# project?


public partial class FirstCLRRoutines // from Wrox Professional SQL
Server 2005 CLR Programming
// http://www.wrox.com/WileyCDA/WroxTitle/productCd-0470054034.html
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_SqlTypesNull(SqlInt32 oInt)
{
if (!oInt.IsNull)
SqlContext.Pipe.Send(Convert.ToString(oInt.Value));
else
SqlContext.Pipe.Send("Input was NULL");
}
};

Thanks,
lausivcid

 

[Alberto Poblacion]

in message

in message


Yes, from the client side the Pipe.Send behaves like a PRINT statement
in
T-SQL, so you can receive its output in the same way, by means of the
InfoMessage event of your SqlConnection object.

It was late, please let me rephrase the question: how can I call
and return a string from the below SQL Server 2005 hosted CLR SPRIC
from an external WinForms C# project?


public partial class FirstCLRRoutines // from Wrox Professional SQL
Server 2005 CLR Programming
// http://www.wrox.com/WileyCDA/WroxTitle/productCd-0470054034.html
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_SqlTypesNull(SqlInt32 oInt)
{
if (!oInt.IsNull)
SqlContext.Pipe.Send(Convert.ToString(oInt.Value));
else
SqlContext.Pipe.Send("Input was NULL");
}
};

This is the code to call the procedure from any project (WinForms or
other) that can access the Sql Server:

SqlConnection cn = new SqlConnection(connectionstring);
cn.InfoMessage += new SqlInfoMessageEventHandler(myRoutine);
SqlCommand cmd = new SqlCommand("usp_SqlTypesNull", cn);
cmd.Parameters,AddWithValue("@oInt", valueToSend);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuerty();
...

void myRoutine(object sender, SqlInfoMessageEventArgs e)
{
// e.Message contains the string from your proc.
}

 

[lausivcid]

using the SqlInfoMessageEventHandler works! Thanks very much. I
still wanted to see if I could get parameterized results.

changing the CLR SPROC to:

public partial class FirstCLRRoutines
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static SqlInt32 usp_SqlTypesNull(SqlInt32 oInt, out
SqlString rv )
{
if (!oInt.IsNull)
rv=Convert.ToString(oInt.Value);
else
rv="Input was NULL";
return 0; // Error Code
}
};

made this work:

class SampleDB : DataContext
{
[Function(Name = "dbo.usp_SqlTypesNull")]
[return: Parameter(DbType = "Int")]
public int usp_SqlTypesNull(
[Parameter(Name = "oInt", DbType = "Int")] int
oInt,[Parameter(Name = "rv", DbType = "NChar(50)")] ref string rv)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())),
oInt,rv);
rv = (string)(result.GetParameterValue(1));
int errorCode = ((int)(result.ReturnValue));
return errorCode;
}
public SampleDB(IDbConnection connection) : base(connection) { }

}

SampleDB sdb = new SampleDB(connection);
string rv = null;
sdb.usp_SqlTypesNull(12, ref rv);
// rv == "12 "
sdb.usp_SqlTypesNull(null, ref rv);
// rv == "Input was NULL "


Thanks again,
Lausivcid

in message

in message
Is it possible to read the SqlContext.Pipe.Send? string output of a
SQL Server 2005 CLR Stored Procedure in an external WinForms C#
project?

Yes, from the client side the Pipe.Send behaves like a PRINT statement
in
T-SQL, so you can receive its output in the same way, by means of the
InfoMessage event of your SqlConnection object.

It was late, please let me rephrase the question: how can I call
and return a string from the below SQL Server 2005 hosted CLR SPRIC
from an external WinForms C# project?


public partial class FirstCLRRoutines // from Wrox Professional SQL
Server 2005 CLR Programming
// http://www.wrox.com/WileyCDA/WroxTitle/productCd-0470054034.html
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_SqlTypesNull(SqlInt32 oInt)
{
if (!oInt.IsNull)
SqlContext.Pipe.Send(Convert.ToString(oInt.Value));
else
SqlContext.Pipe.Send("Input was NULL");
}
};

This is the code to call the procedure from any project (WinForms or
other) that can access the Sql Server:

SqlConnection cn = new SqlConnection(connectionstring);
cn.InfoMessage += new SqlInfoMessageEventHandler(myRoutine);
SqlCommand cmd = new SqlCommand("usp_SqlTypesNull", cn);
cmd.Parameters,AddWithValue("@oInt", valueToSend);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuerty();
...

void myRoutine(object sender, SqlInfoMessageEventArgs e)
{
// e.Message contains the string from your proc.
}