ADO.Net / Multiple SQL Statements in One Command

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Craig - 04 Mar 2008 20:56 GMT

I'm writing an application to assist in the deployment of some complex
database schema out of a working DDL script that has already been generated.

We're targeting SQL 2000 and 2005, and the script runs fine now inside Query
Analyzer / Management Studio. It's *long* though, and creates thousands of
objects (stored procs, tables, views) and hundreds of rows of pre-populated
data (just lots of inserts after the tables are created).

Is there a way I can run this giant script using System.Data.SqlClient? I
attempted a simple example and SqlConnection and SqlCommand choke on anything
with "GO" in it since it's not actually T-SQL.

Am I really going to have to try to intelligently divide this file up by
"GO" statements into individual SQL commands and run them individually or is
there some other way to execute batch statements?

Reply to this Message

Todd Beaulieu - 04 Mar 2008 22:55 GMT

As you've discovered, GO is not valid in ado.net. so, you'll either have to
break it up or run it through osql, or whatever it's called these days. Write
it to a file and shell out to the tool. It seems cheesy, but it's actually a
decent approach.

> I'm writing an application to assist in the deployment of some complex
> database schema out of a working DDL script that has already been generated.
[quoted text clipped - 11 lines]
> "GO" statements into individual SQL commands and run them individually or is
> there some other way to execute batch statements?

Reply to this Message

ADO.Net / Multiple SQL Statements in One Command

Free Magazines