Monday, September 17, 2007

How to Execute a SQL Script from .NET Code?

The old days of splitting scripts by the GO statement or using osql are over! SMO is here!

It happens from time to time that you need to execute a T-SQL script - e.g. you are writing a database update manager or whatever.

And it's always the old problem of the 'GO' separator, isn't it? ADO.NET just won't swallow it!

The solution was always to either use some external tool (like osql or sqlcmd) to execute the script or to parse the script for all the 'GO'-es. But both those methods were kind of... not perfect.

The good news is - you don't have to worry about it anymore!

If you have a closer look at SQL Server Management Objects (SMO), you will find the following method:
which can actually do all the job for you!

If course it is only available in SQL Server 2005. But isn't that what progress is all about - new things have more features than old ones? ;)

You can find the SMO assemblies here:
C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
if you have SQL Server 2005 installed on your machine.

No comments: