Geek Answers Handbook

Run SQL script from C # and log errors

I am trying to execute a script file (.sql) in a database from a C # Windows application. The SQL file contains "GO" statements; this means that I am using an SMO object.

I am trying to continue with an error and also write down any error that may occur during the execution of the script in the database. Is there any way to do this?

This is the code I'm using:

using (SqlConnection sqlConnection = new SqlConnection(connectionString)) { ServerConnection svrConnection = new ServerConnection(sqlConnection); Server server = new Server(svrConnection); string script = File.ReadAllText("upgradeDatabase.sql"); try { server.ConnectionContext.ExecuteNonQuery(script, ExecutionTypes.ContinueOnError); } catch (Exception ex) { //handling and logging for the errors are done here } }

Any help is appreciated!

+4
source share
4 answers

I think you have two problems:

First, you call the ExecuteNonQuery method, which takes a string, not a StringCollection. I believe that this method does not recognize GO used to separate batch statements. Instead, the documentation of the ExecuteNonQuery method that accepts a StringCollection indicates that GOs are recognized

The second problem is the passed ExecutionTypes.ContinueOnError . In this case, the same documentation states that you cannot receive exceptions if something fails.

I think the best approach should be to split the input text by GO and then build a StringCollection to go to the ExecuteNonQuery method and check the returned array of the affected strings. Something like this (should be checked)

 using (SqlConnection sqlConnection = new SqlConnection(connectionString)) { ServerConnection svrConnection = new ServerConnection(sqlConnection); Server server = new Server(svrConnection); string script = File.ReadAllText("upgradeDatabase.sql"); string[] singleCommand = Regex.Split(script, "^GO", RegexOptions.Multiline); StringCollection scl = new StringCollection(); foreach(string t in singleCommand) { if(t.Trim().Length > 0) scl.Add(t.Trim()); } try { int[] result = server.ConnectionContext.ExecuteNonQuery(scl, ExecutionTypes.ContinueOnError); // Now check the result array to find any possible errors?? } catch (Exception ex) { //handling and logging for the errors are done here } }

Of course, the alternative executes each individual statement and removes the ContinueOnError flag. Then write down and write down the possible exceptions. But it will, of course, be slower.

+4
source

you can use smo library where : add to project

 FileInfo file = new FileInfo("upgradeDatabase.sql"); string script = file.OpenText().ReadToEnd(); SqlConnection conn = new SqlConnection(sqlConnectionString); Server server = new Server(new ServerConnection(conn)); server.ConnectionContext.ExecuteNonQuery(script);
0
source

I really like this option, which uses the InfoMessage event and handler:

  using(SqlConnection sc = new SqlConnection(connStr)) { sc.InfoMessage += new SqlInfoMessageEventHandler(sc_InfoMessage); Server db = new Server(new ServerConnection(sc)); db.ConnectionContext.ExecuteNonQuery(commandFileText, ExecutionTypes.ContinueOnError); }

You will need to add a link to the following:

  • Microsoft.SqlServer.ConnectionInfo.dll
  • Microsoft.SqlServer.Management.Sdk.Sfc.dll
  • Microsoft.SqlServer.Smo.dll
  • Microsoft.SqlServer.SqlEnum.dll
0
source

I am not a programmer (DBA), so I'm not sure. I suggest the following may be helpful

svrConnection.FireInfoMessageEventOnUserErrors = true;

0
source

More articles:


All Articles