C# program to run sql scripts

Calling command line programs is certainly a useful feature of Visual Studio but I probably wouldn't implement that here. Dan Graveen. I created a few vb. Net apps to backup and restore databases, but I used SMO as well.

This way it's also easy to add a progress bar and thread it so your user doesn't think your program stopped responding. I also like the additional security of not relying on opening sql scripts that could be easily modified.

Nice demonstration though for executing processes programmatically. Anye Mercy. I wouldn't use this for a backup specifically however it will be very useful in updating the hundreds to thousands of databases in our multitenant environment with the latest dacpac file from the new VS Data Tier Application project by iterating through a list of databases and generating the sqlcmd needed to run the dacpac dynamically for each one.

I do not think it means what you think it means. You must be logged in to reply to this topic. Login to reply.

April 26, at pm April 27, at am Nice code but how can I check if everything goes correcty? Is there any error code returned? Please Sign up or sign in to vote. See more: C. Hello, I want to execute sql script in sql server through C. Posted 7-Jan pm K N R. Add a Solution. Sergey Alexandrovich Kryukov 8-Jan am. K N R 8-Jan am. Top Rated Most Recent. Accept Solution Reject Solution. Following first link will provide you the solution to get server and database information from web.

How to retrieve the databaseName and the serverName from the connectionstring in the webconfig? And following links will provide you a solution to run a. Posted 7-Jan pm Monjurul Habib.

Yep, Sqlcmd will do. Monjurul Habib 8-Jan am. Uday P. Singh 8-Jan am. ProEnggSoft Mar am. Monjurul Habib 1-Apr pm. Load the script from the file into a string, and set it as the cmdText to an SqlCommand object. Smo; using Microsoft. Common; using System. IO; using System. SqlClient; public partial class ExcuteScript : System. This solution worked for me for being able to drop and recreate a database, and add tables via the referenced SQL script file.

This method doesn't allow using the "GO" command in your script which is allowed when you run a script from SQL Management Studio or the osql command. ExecuteNonQuery definitely does I'm using it right now.

Note that you need to add references to the project, to Microsoft. ConnectionInfo, Microsoft. Sdk and Microsoft. Smo for this answer to work. To me it didn't work when using. Show 10 more comments.

Multiline RegexOptions. IgnoreCase ; Connection. Close ;. Gilberto Andrade 3 4 4 bronze badges. Hacko Hacko 1, 1 1 gold badge 15 15 silver badges 10 10 bronze badges. This solution won't even close the file after it's done using it.

That could be critical. Use "RegexOptions. IgnoreCase" to match "Go" or "go" cases too. I think the RegexOptions. CultureInvariant flag should be used as well. If there's a comment after GO, it will attempt to execute it as a command.

Show 1 more comment. Show string. Format "Please check the SqlServer script. LineNumber, ex. OK, MessageBoxIcon. Show ex. Message, "Warning", MessageBoxButtons. Xtian11 Xtian11 1, 1 1 gold badge 18 18 silver badges 12 12 bronze badges.

Nice code, one very minor thing is that doesn't need connection. Close the connection will be closed by the using you've wrapped it in.

Hidden; proc. GetDirectoryName batchFileName ; proc. Start ; proc. WaitForExit ; if proc. Binoj Antony Binoj Antony ReadAllText Server. Message; lblmsg. Ariel Magbanua 2, 6 6 gold badges 33 33 silver badges 45 45 bronze badges.

Neelam saini Neelam saini 71 1 1 silver badge 1 1 bronze badge. Added additional improvements to surajits answer: using System; using Microsoft. This worked for me in. Net 4.