Running SQL Scripts with a .NET Application

I was recently asked to show how to run a SQL Script on a SQL Server but being controlled by a .NET application. The other guy had been trying to use isql for the task but somehow wasn’t getting it to work. I commented that I’d needed something similar for three different projects recently, but I’d taken a completely different approach. At the time I rushed an explanation of how I achieved the same result. So here is a less rushed explanation of how to run a SQL Script on a SQL Server. I would also imagine this would work for other database systems as well with some slight modifications.

At its simplest you can throw just about any SQL statements you like through the SqlCommand object in .NET and have .NET execute them.

SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(script,connection);

That’s it. You can just call command.ExecuteNonQuery(); if you don’t expect, or are not interested in, any values that might come back. You could have a SqlAdapter.Fill a DataSet, or a get a DataReader.

For a demonstration I have put a simple application together that reads in a script from a file, or even from embedded resource and runs the script it finds. If the script returns any data a DataSet is populated and the tables are then output to the console.

The demo application takes two command line arguments. The first is either a -r (for embedded resource) or -f (for a file). The second argument is either the name of the resource, or the name of the file. For example:

RunSQL -r GroupedTurnover

runs the embedded script called RunSQL.GroupedTurnover.sql. For convenience the application will add the prefix and the suffix.

The file can contain GO delimiters so that you can have the application process the script in batches rather than need one script per batch.

Downloads:

• The Source Code (9kb zipped)
• The Demo Application (5kb zipped)

NOTE: The demo application needs a default instance of SQL Server 2000 or MSDE running on the local machine which contains the Northwind database. If you download the source code you can, of course, change these settings to suite your need.

NOTE: This was rescued from the Wayback Machine. The original was dated Friday, 15th October, 2005. The downloads currenly point to the Wayback Machine version. These will be updated shortly.