sql server - Returning results and messages from a SQL Stored procedure in a C# winform -

sql server - Returning results and messages from a SQL Stored procedure in a C# winform -

i have been supplied stored procedure needs run in ssms 'results text' selected results , messages displayed in right order.

the reason stored proc used build script transferring client setup 1 database another, print statements used wrap informational messages , error handling around insert statements...

print 'inserting values [aip_m_site_labels]'

insert [aip_m_dev_site_labels] values(2,90014,'stage indicators - total stages of',0,'of',0,0)

insert [aip_m_dev_site_labels] values(2,90025,'ledger _ ref',0,'n/a',0,0)

if @@error <> 0

begin

print('error inserting [aip_m_site_labels]')

goto executionfail

end

is there way execute stored procedure in c# winform allow me save output? i've far managed result set(s) , messages separately not yet 'merged' , in right order.

you need subscribe infomessage event of sqlconnection object. should work:

define @ class level event handler can access it:

stringbuilder sb = new stringbuilder("");

for wherever sql stuff is:

var conn = new sqlconnection("your connection string"); conn.open(); conn.infomessage += conn_infomessage; var commandtext = "your sp name"; var command = new sqlcommand(commandtext, conn) {commandtype = commandtype.storedprocedure}; command.executenonquery();

the event handler grab info (doesn't need static, mine is):

static void conn_infomessage(object sender, sqlinfomessageeventargs e) { sb.appendline(e.message); }

edit saw note getting print , job results in 1 shot. instead of using .executenonquery(), utilize .executereader() , utilize sqldatareader normal. infomessage event should still fire, , you'll need manually merge results of both sqldatareader , infomessage event somewhere in method. careful appending class level stringbuilder , doing stuff in method causing event fire.

c# sql-server winforms