Click here to Skip to main content
14,695,387 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hey all

I need to execute SQL scripts from a C# application, the trick is that if possible I need to be able to do it with SQL Server 2000, 2005 & 2008.

Googling around I've seen that it can be done using the SQL SMO objects, in fact I've used SQL SMO before, but it was only introduced in SQL 2005 and up as far as I can tell.

So my question is are there any other ways to execute SQL scripts from C# code that will support SQL 2000 and up incl express editions.

Any suggestions or links appreciated :)

With scripts I mean scripts that create a database, update a database(add or alter tables or columns) and backup the database.
Updated 5-Dec-09 0:39am

Depends on what you mean by "scripts". Do you mean arbitrary SQL script files? In that case, I'm not sure. But if you mean individual statements, such as to create a stored procedure, then the below technique seems to work fine for me.
SqlConnection conn = new SqlConnection("MyConnectionString");
SqlCommand cmd = new SqlCommand("CREATE PROCEDURE SelectThree AS BEGIN SELECT 3 END", conn);

If you have a bunch of such commands in a text file, usually GO must be placed between the commands. In that case, you could just parse the file by splitting wherever you see a newline, followed by a "GO", followed by a newline. Then you could use the above technique to run each command. That would work in most cases.

If that doesn't work for you, I'd use SQL Profiler to track all interaction with a SQL Server 2005 database and then use SMO to run a script against that database. That should tell you at least what SQL is run so that SMO can do its job. You might then be able to use those same techniques with all versions of SQL Server (2000, 2005, 2008). The tricky part would be if SMO does any complex parsing before it sends queries SQL Server. But, hey, cross that bridge when you get to it.

P.S. "SMO - Manage your SQL Server!" seems to indicate that you can use SMO with SQL Server 2000. Maybe you just can't use 2005 features with a 2000 database?
We've been using sqlcmd.exe[^] to run scripts and it's been solid with SQL Server 2000 and 2005, regular and express flavors. The docs say that it should perform similarly with SQL 2008.

This tool is installed with 2005 and 2008 but for 2000 you'll need to download it from here MSDN[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900