odd SqlInfoMessageEventHandler problem (source code provided)

[codefragment@googlemail.com]

Hi
I'm trying to get the InfoMessage code below working. What I'm
finding is that the print statements have to be before the select
statement, which kind of defies the point in my scenario.
e.g. here the event will fire for 'hi mum' but not for 'hi again'
What am I doing wrong here?

thanks


<add name="Northwind "
connectionStrin g="Integrated
Security=SSPI;d atabase=Northwi nd;server=local host;Connect Timeout=30;
Persist Security Info=False"
/>



private void ShowPrintStatem ents()
{
DbProviderFacto ry factory = SqlClientFactor y.Instance;

ConnectionStrin gSettings settings =
ConfigurationMa nager.Connectio nStrings["Northwind"];
string theConnectionSt ring = settings.Connec tionString;

DbConnection connection = factory.CreateC onnection();
if (connection is SqlConnection)
{
((SqlConnection )connection).In foMessage += new
SqlInfoMessageE ventHandler(Cha pter4_InfoMessa ge);
}

connection.Conn ectionString = theConnectionSt ring;
connection.Open ();

DbCommand command = factory.CreateC ommand();
command.Connect ion = connection; command.Command Type =
CommandType.Tex t;
command.Command Text = "print 'hi mum';select 1 as somecolumn;prin t
'hi again'";
DbDataReader reader = command.Execute Reader();

reader.Close();
}

void Chapter4_InfoMe ssage(object sender, SqlInfoMessageE ventArgs e)
{
string message = e.Message;
}

[Erland Sommarskog]

Re: odd SqlInfoMessageE ventHandler problem (source code provided)

codefragment@go oglemail.com (codefragment@g ooglemail.com) writes:

I'm trying to get the InfoMessage code below working. What I'm
finding is that the print statements have to be before the select
statement, which kind of defies the point in my scenario.
e.g. here the event will fire for 'hi mum' but not for 'hi again'
What am I doing wrong here?

You need to call .NextResult to get the second message. To do thing
properly, you should always iterate over .NextResult until returns NULL,
to be sure that you get all results and messages.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[codefragment@googlemail.com]

Re: odd SqlInfoMessageE ventHandler problem (source code provided)

You need to call .NextResult to get the second message. To do thing

properly, you should always iterate over .NextResult until returns NULL,
to be sure that you get all results and messages.

Yep, that did it, thanks :-)