0

I am using npgsql with a postgres DB, writing a DB updater that will need to execute a series of (usually) DDL statements like "ALTER TABLE ADD COLUMN blah..." and the like. The simple way to do this is with Command.ExecuteNonQuery, however I need to know if each statement succeeded or not. ExecuteNonQuery returns "the number of rows effected" but it appears that this is always 0 for DDL regardless of success or failure, since the content of the table is not altered, only the structure.

What is the simplest way of determining the success or failure of arbitrary DDL (and update) commands read in from a text file? Can/Should/Must I wrap each statement in a procedure with an exception handler? Is there a simpler, more straightforward way?

One issue I see with wrapping in a procedure is that the procedure itself must then be part of the DB, and the update process is no longer as independent. I would like to avoid this. Of course, my updater could create the procedure if needed, and remove it after, but this seems cumbersome. Am I missing something obvious?

________Edit for clarification:

@Laurenz Albe is of course completely correct, if there is a syntax error, key duplication, missing column referenced, etc., an exception will be thrown. This probably covers nearly all cases. However, I have encountered issues where the SQL is valid but may or may not actually do anything useful - for example, if there is a WHERE clause, I may be expecting some (particular) number of 'rows effected'. The absence of an exception merely tells me that the statement was valid. When can or cannot I rely on the return value of ExecuteNonQuery, or is there a simple alternative?

1

If a statement fails, you'll get an exception.

There is no way to miss that unless you catch and ignore exceptions, which is never ok.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.