C# execute batch SQL command, catch exception, then continue with rest of batch

Question

I’m executing batch SQL commands in C# using SQLConnection and command. I need to be able to know which statement fails, and I can’t do these one at a time because of performance issues. Is there any way in C# that I can execute a batch SQL statement, and in the case of failure, tell me what statement fails (the index, id, or anything so I can know which one) and THEN continue with the rest of the statements.

Thanks

csm8118 · Accepted Answer · 2014-04-08T16:01:03.493

1

You didn't mention what database you're using, but if you're using SQL Server 2005 or greater, you can use try/catch for this. Here's an example.

BEGIN TRY
    select 1/0
END TRY
BEGIN CATCH
    SELECT 'statement 1 failed' AS Statement,ERROR_MESSAGE() as ErrorMessage,ERROR_SEVERITY() AS Severity;
END CATCH

BEGIN TRY
    select 1.0/2
END TRY
BEGIN CATCH
    SELECT 'statement 2 failed' AS Statement,ERROR_MESSAGE() as ErrorMessage,ERROR_SEVERITY() AS Severity;
END CATCH

In this case I'm catching the errors and just returning them as a result set, but you could create a temp table/variable at the beginning, insert into that when an error happens, and then select all rows from that table at the end.

EDIT: Here's an example that will throw an error in a trigger:

create table csm (id int)
go
create trigger tr_i_csm on csm for insert as
declare @d int
select @d=sum(id) from inserted
if (@d>=10)
begin
 raiserror('error',@d,0)
end
go

BEGIN TRY
    BEGIN TRAN
    insert into csm values (5)
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK
    SELECT 'statement 1 failed' AS Statement,ERROR_MESSAGE() as ErrorMessage,ERROR_SEVERITY() AS Severity;
END CATCH

BEGIN TRY
    BEGIN TRAN
    insert into csm values(16)
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK
    SELECT 'statement 2 failed' AS Statement,ERROR_MESSAGE() as ErrorMessage,ERROR_SEVERITY() AS Severity;
END CATCH

BEGIN TRY
    BEGIN TRAN
    insert into csm values(2)
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK
    SELECT 'statement 3 failed' AS Statement,ERROR_MESSAGE() as ErrorMessage,ERROR_SEVERITY() AS Severity;
END CATCH

selecT * from csm

edited Apr 08 '14 at 16:01

answered Apr 01 '14 at 18:52

csm8118

1,213
9
11

C# SqlConnection means Sql Server. Anything else would have a different connection object: OleDbConnection, OdbcConnection, MySqlConnection, etc. – Joel Coehoorn Apr 01 '14 at 20:07
so basically, you're putting a try catch around every statement? Also, as I mentioned, if there's ANY way do to this in C#, tell me. I doubt it'd be difficult at all to change implementation – Teddy Black Apr 01 '14 at 21:20
@TeddyBlack yes, pretty much. Then you can control what you do in case of error. – csm8118 Apr 01 '14 at 21:24
@TeddyBlack I don't think there is anything native to C# that supports your scenario. The way you phrased your question makes me think you're already creating the SQL to execute manually before providing it to SqlCommand, so adding try/catch to the SQL might be your best bet. – csm8118 Apr 01 '14 at 21:28
@csm8118 Sounds like a pretty good idea. Is there some way to create the SQL statement AFTER adding it to SQLCommand? I'm not sure what you meant by that comment. – Teddy Black Apr 03 '14 at 13:02
@csm8118 Also, do you think adding a try catch around everything will slow down performance at all in the case where everything succeeds anyway? I'm not super cultured on SQL, so I thought I'd ask. – Teddy Black Apr 03 '14 at 13:06
@TeddyBlack I think there might be a very small overhead with using try/catch, but based on what I've seen it shouldn't be anything significant. Regarding my previous comment, you can change the `CommandText` property of SQLCommand after assigning it but before running the command, but what I was getting at was to add the try/catch logic to the string that you are building up before you pass that sql string to the SQLCommand. – csm8118 Apr 03 '14 at 15:58
@csm8118 Ah. Thanks. It'd still be significantly faster then if I did them 1 by 1 though right? – Teddy Black Apr 03 '14 at 17:03
@TeddyBlack Yes I would think so. – csm8118 Apr 03 '14 at 17:36
@csm8118 I'm getting an error when trying to do this. I set it up just like you have it expect in my catch, I need to add the key of the failure into some other table. The error is : "the current transaction cannot be committed and cannot support operations that write to the log file. This transaction needs to be rolled back." – Teddy Black Apr 03 '14 at 20:14
@TeddyBlack Interesting. Could you provide your full code and SQL statement as you have it with these changes? You could either update your question, or email me directly if you don't want to post it. My email is on my profile if you want to do that. – csm8118 Apr 04 '14 at 15:00
@csm8118 I can't give you the code because I'd probably get fired :). But it's nothing fancy. All that it's doing is dynamically making the sql statement with parameters and running it. I figured out why I'm getting this error though. So, the main reason why the batch will fail is because of a trigger failure. So when I insert into the table, if a trigger activates, this is happening outside of my control (hence outside of the try catch in the original sql batch statemnent). So if that trigger fails, SQL can't catch that exception, and instead the C# application catches it. – Teddy Black Apr 07 '14 at 00:02
At which point, the whole batch fails and can't continue as its now lost. Do you have any idea of how to handle this? – Teddy Black Apr 07 '14 at 00:02
@TeddyBlack I did a bit of testing and it seems like it will handle errors that happen in triggers (most of the times at least. see http://technet.microsoft.com/en-us/library/ms175976%28v=sql.90%29.aspx, "Errors unaffected by a try...catch"). Since you can't share your code, can you try running the code I posted and see if it works for you? – csm8118 Apr 07 '14 at 14:55
@csm8118 yeah, it was me. Everything works fine now. Thanks a lot. – Teddy Black Apr 07 '14 at 16:40
@csm8118 actually I found the problem. If I trigger fails in the middle of a transaction, it apparently becomes doomed and uncommittable. Here's an example of what I'm doing BEGIN TRAN T2 BEGIN TRY insert stuff END TRY BEGIN CATCH END CATCH commit t2; If the trigger in the table fails, sql "dooms" the entire transaction. What can I do about this. – Teddy Black Apr 07 '14 at 19:42
@TeddyBlack You could try creating a separate transaction for each block. BEGIN TRY BEGIN TRAN insert... COMMIT END TRY BEGIN CATCH ROLLBACK handle error END CATCH – csm8118 Apr 07 '14 at 21:01
@csm8118 I don't want to rollback the whole thing, I just want to stop, commit what worked and have it return the amount of rows affected, which it can't do since it failed. There's an exception in Java called BatchUpDateException, which seems to be pretty much what I need as it gives me the failures. Is there some equivalent to this in C#? – Teddy Black Apr 08 '14 at 13:48
@TeddyBlack I edited my example to show how I was suggesting you use transactions. When a statement fails, you don't rollback the whole thing, just the statement that failed. – csm8118 Apr 08 '14 at 16:01
Thanks again. I guess I was just kind of hoping there was a way to do this without something so haxy looking. Since if I'm going to just surround each statement with a transaction, why even bother using them. – Teddy Black Apr 08 '14 at 16:48

score 0 · Answer 2 · edited May 23 '17 at 12:11

One option is to include print statements in your batches following each query. You can then look at the output to find failures. (See here for information on how to read this).

In a prior job, we had a number of nightly stored procedures that ran via Sql Agent, and some other non-database jobs written in C# that ran as Windows Scheduled Tasks. We eventually wrote a c# program to call the stored procedures, instead of Sql Agent, so that we could have all of our scheduling (and logging!) in one place (scheduled tasks). We also had support for executing an Sql file via the program. Receiving Print message output was how we handled logging.

Of course, this implies the ability to modify your batch scripts. It also means writing the sql such that a failed statement won't terminate the whole job.

interesting. Yeah, I need do need it to keep track of which ones fail, but I also don't want it to terminate if one fails. — Teddy Black, Apr 01 '14 at 21:21

C# execute batch SQL command, catch exception, then continue with rest of batch

2 Answers2