Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL2000 SQL-server-2005 C# , +
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 Smile | :)
 
<edit>
With scripts I mean scripts that create a database, update a database(add or alter tables or columns) and backup the database.
</edit>
Posted 4-Dec-09 22:23pm
Edited 5-Dec-09 0:39am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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");
conn.Open();
SqlCommand cmd = new SqlCommand("CREATE PROCEDURE SelectThree AS BEGIN SELECT 3 END", conn);
cmd.ExecuteNonQuery();
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?
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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[^]
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 381
1 Sergey Alexandrovich Kryukov 245
2 Marcin Kozub 225
3 Praneet Nadkar 217
4 /\jmot 189
0 OriginalGriff 8,284
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,614
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 13 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100