I have written a query which is a composite query like one update query and one insert query is there. I am using ExecuteScalar. Is there a possibility that one query will fail and other will be succeeded. It will be a whole failure or partial failure, if one of the queries is wrong.
can someone please help :)
No matter how you invoke it; for a composite operation it is entirely possible that the first operation will succeed and the second operation will fail (for example, a unique key violation or a foreign key violation). If you need to ensure all-or-nothing, then surround the call to Execute* with a transaction, and only commit when you know it all worked.
You can also create transactions at the SQL level, but the tools at your disposal are easier to get wrong if you do it in TSQL. It does, to give due credit, avoid a few round-trips - but usually not so much that it is worth fighting the language to do it. Ultimately SQL is designed for good set-based DML operations - it isn't quite as good at more procedural code like execution flow management.
Yes chances are there that your one query fails and other execute. and why not to use ExecuteNonQuery instead of ExecuteScalar. Its better you use "transactions" for these type of scenarios
