Skip to main content

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

Comments

  1. Your query might have the characters 'GO' in it. For example, a table could be named GOLF_SCORES.

    This would be an improvement:

    string[] removeWord ={"\r\nGO\r\n"};

    ReplyDelete

Post a Comment

Popular posts from this blog

Application Configuration FIle Visual Studio 2005

Through the application configaration file You can easily save values to later usage. you can create a application configuration file from project > Add New Item (Ctrl + Shift + A) example You want to add get string from config file Create a Config file using above step. after that insert following code to the config file. its a XML file. appsettings add key="DatabasePath" value="c:\\projects\data\spider.mdb" add key="SupportEmail" value="webmaster-1@dotnetspider.com" appsettings configuration in the coding add following Dim app As New System.Configuration.AppSettingsReader Dim s As String s = app.GetValue("DatabasePath", Type.GetType("System.String")) MessageBox.Show(s) its very easy when you are handling databases. you can store database connection string in that config for later reusabilty. Please post your comments

Expire Session When the Browser Close [Codeigniter]

I wanted user to log again to the site whenever they close the browser. i searched everywhere and i found the easiest way. application/config/config.php sess_expire_on_close = TRUE Easy Session Preferences You'll find the following Session related preferences in your file: Preference Default Options Description sess_cookie_name ci_session None The name you want the session cookie saved as. sess_expiration 7200 None The number of seconds you would like the session to last. The default value is 2 hours (7200 seconds). If you would like a non-expiring session set the value to zero: 0 sess_expire_on_close FALSE TRUE/FALSE (boolean) Whether to cause the session to expire automatically when the browser window is closed. sess_encrypt_cookie FALSE TRUE/FALSE (boolean) Whether to encrypt the session data. sess_use_database FALSE TRUE/FALSE (boolean) Whether to save the session data to a database. You must...