Geek Answers Handbook

Best practices for running SQL Server scripts from a .NET application?

What is the recommended method to run SQL Server script from a .NET application and why?

I am creating an application that we can run from our installer to handle updating our database when installing a previous version of our application. The database has a version table in which I can programmatically access and determine which upgrade scripts are run to update the database. It makes sense for me to paste this into an application that our installer can simply call to update.

I am looking for the best way to execute the relevant upgrade scripts. Searching the Internet I saw recommendations for breaking script statements into "GO" and using SqlCommand.ExecuteNonQuery () . I also saw recommendations for using SQL Server Management Objects (SMOs) .

I am looking for advice on the pros and cons of various ways to execute these scripts from a .NET application.

+4
source share
3 answers

The best method is any method that meets all functional and non-functional limitations.

What speed do you need, are there any problems with the current situation What is your backup / crash strategy

I would probably use SqlCommand.ExecuteNonQuery () because it is part of the .net runtime, which does not require additional installation of applications that the user does not need.

From your question, I assume that there is an existing application with a lot of inline query string. I would try to get into some kind of repository that automatically puts them on the server (as a stored procedure) and inside your code.

Do not be afraid of plain text files or other configuration files with a request.

+1
source

Although this may not suit your needs, which must be done from a .NET application, sqlcmd is well-suited (and designed) for this process. By implementing feature analysis and running SQL Script, you duplicate the functionality of the tool. sqlcmd is available in a standalone installer that may be associated with your application.

If you decide to use the SqlCommand.ExecuteNonQuery () related solution, for larger scripts there will be more memory to read in the script in a smaller buffer that looks for the "GO" statement, instead of all -once ".

+2
source

I had consistent results using System.Diagnostics.Process and a call in OSQL.exe. its made for sql file ....

I created a temporary file in which I wrote my embedded sql file if OSQL executed it and then cleaned it.

process.StartInfo = new System.Diagnostics.ProcessStartInfo(); process.StartInfo.FileName = OSQLpath; process.StartInfo.UseShellExecute = false; ... if (!System.IO.Directory.Exists(workingDirectory)) System.IO.Directory.CreateDirectory(workingDirectory); ... StringBuilder sbArgs = new StringBuilder(); sbArgs.Append("-S ").Append(_serverName); sbArgs.Append(" -d ").Append(_databaseName); sbArgs.Append(" -E"); sbArgs.Append(" -i ").Append("\"").Append(inputSQLFilePath).Append("\""); // input file sbArgs.Append(" -o ").Append("\"").Append(System.IO.Path.Combine(workingDirectory, String.Format("osqloutput_{1}_{0}.txt", fileUnique, fileName))).Append("\""); // output file process.StartInfo.RedirectStandardOutput = true; process.StartInfo.RedirectStandardError = true; process.StartInfo.Arguments = sbArgs.ToString(); process.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden; process.StartInfo.CreateNoWindow = true; ... System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; process.Start(); process.WaitForExit();
+1
source

More articles:


All Articles