Execute *.sql with .NET

Do you ever want to execute slq statements at once. I mean SQL Script file.

Normally we are using like following commands to Execute Single SQL statement.

SqlCommand cmd = new SqlCommand(sSQL, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();

but this way you can execute one command at a time. We cannot execute SQL script using above command.

Normal SQL Script Like this.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__titleauth__au_id__0519C6AF]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[titleauthor] DROP CONSTRAINT FK__titleauth__au_id__0519C6AF
GO

CREATE TABLE [dbo].[authors] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contract] [bit] NOT NULL
) ON [PRIMARY]
GO

When executing above script SqlCommand gives an exception because it does not know about "GO" like statements.


But simply we can execute SQL Script by following by removing GO keyword.

Example:

This is not a complete example. But i explain you important lines.
Here i assumed you have taken Whole SQL Script into txtSqlScript.Text

i have created two string arrays. one to hold "GO" keyword and one to hold SQL Statments taken from Split function.

String.Split is a function that split a single string into multiple strings by a seperator.
The seperator is the "GO"

After finished Split process i have executes each statement by cmd.ExecuteNonQuery();


string[] removeWord ={"GO"};
string[] statements;
statements = txtSqlScript.Text.Split(removeWord,StringSplitOptions.RemoveEmptyEntries);

foreach (string singleStatement in statements)
{
MessageBox.Show(singleStatement );
SqlCommand cmd = new SqlCommand(sSQL, conn, t);
Console.WriteLine(singleStatement);
cmd.ExecuteNonQuery();
cmd.Dispose();
}

May be there are More ways. But I did this by my self. Im happy with this.
Leave a Comment