Saturday, March 3, 2012

great usage for anonymous delegates or Lambda's- catching error messages

lets say I had a procedure that ran sql thus
create procedure CheckMessages
as
begin
RAISERROR('starting',10,3) WITH NOWAIT
select 1
RAISERROR('cont',10,5) WITH NOWAIT
select 2
RAISERROR('cont',10,7) WITH NOWAIT
select 3
RAISERROR('ending',10,9) WITH NOWAIT
end


I could capture the state in c# thus with a lambda:
            using (SqlConnection con = new SqlConnection(sqlConn))
                    {
                                         
                        con.InfoMessage += (sender2,e2) =>  
                                {
                                    if (e2.Errors.Count > 0)
                                    {
                                       Console.WriteLine(e2.Message);
                                         
                                    }






alternatively I could use an anonymous delegate


 con.InfoMessage += delegate(object sender2, SqlInfoMessageEventArgs e2)


This will not work with ExecuteNonQuery() which will wait until the end of the process to send messages.
likewise c# will only process messages with c.ExecuteScalar() that procede the first sql statement.
with raiseerror u need WITH NOWAIT
Alternatively -  use Print 

No comments:

Post a Comment