Can I use T-SQL try-catch in ADO.NET

Question

In ADO.NET we have methods for starting a transaction, reading the query results, etc. My question is, will such T-sql statements as BEGIN TRANSACTION and particularly TRY-CATCH work if I just include them to command-text and then ExecuteNonQuery?

mySqlCommand.CommandText = 
@"BEGIN TRY
    SELECT TOP 1 [id] FROM myTable;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;"

var result = mySqlCommand.ExecuteScalar(); // or just  mySqlCommand.ExecuteNonQuery()

PS: I know, I can just test this myself. But I am asking this as SO. So that afterwards someone like me will be able to just google that. So, obviously, I don't expect answers like 'why not just try'. Thank you.

Actually, you _should_ try it and post the results as an answer. That's why the `Answer your own question` checkbox on the ask a question page exists. — Oded, May 20 '13 at 13:03
I will, if nobody does. Although, it is more up to me what I should and what I shoudn't — Artur Udod, May 20 '13 at 13:04
Sure, but don't be surprised if this attitude generates bad responses ;) — Oded, May 20 '13 at 13:09
Sure, I am not surprised. I expected this, as I said. Altough I believe this question may be useful for someone. — Artur Udod, May 20 '13 at 13:23

score 2 · Accepted Answer · answered May 20 '13 at 13:03

2

Any valid T-SQL or a stored procedure are valid for SqlCommand.CommandText. So in short, yes you can if you are using SQL Server 2005 or greater. However it would be best to use this in a stored procedure.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtext.aspx

answered May 20 '13 at 13:03

Darren

68,902
24
138
144

1

Provided that the version of SQL Server being used supports TRY/CATCH. – Oded May 20 '13 at 13:04

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

2

You can do this (with ExecuteScalar)

But it adds no value because you should have .net error handling anyway. Let's say you have a varchar from the SELECT but int from the CATCH block too: you now have 2 different datatypes to consume.

TRY/CATCH is more intended for stored procedures and to handle transaction, as per Nested stored procedures containing TRY CATCH ROLLBACK pattern?

edited May 23 '17 at 12:18

Community

1
1

answered May 20 '13 at 13:05

gbn

422,506
82
585
676

Can I use T-SQL try-catch in ADO.NET

2 Answers2