How to run a sql script using C#

Question

I have a sql script to create a new database which i need to create when our product is installed. For this i need to fire the script using c#. DB is sql-server 2005 express. Plz help....

The sql script is as follows:

USE [master]
GO
/****** Object:  Database [Jai]    Script Date: 02/12/2010 11:01:25 ******/
CREATE DATABASE [Jai] ON  PRIMARY 
( NAME = N'Jai', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Jai.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Jai_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Jai_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'Jai', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Jai].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [Jai] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [Jai] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [Jai] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [Jai] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [Jai] SET ARITHABORT OFF 
GO
ALTER DATABASE [Jai] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [Jai] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [Jai] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [Jai] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [Jai] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [Jai] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [Jai] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [Jai] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [Jai] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [Jai] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [Jai] SET  ENABLE_BROKER 
GO
ALTER DATABASE [Jai] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [Jai] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [Jai] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [Jai] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [Jai] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [Jai] SET  READ_WRITE 
GO
ALTER DATABASE [Jai] SET RECOVERY FULL 
GO
ALTER DATABASE [Jai] SET  MULTI_USER 
GO
ALTER DATABASE [Jai] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [Jai] SET DB_CHAINING OFF

dude, use SMO as don an astander mentioned and be done with it. — Sky Sanders, Feb 12 '10 at 09:01

score 9 · Answer 1 · answered Feb 12 '10 at 07:58

9

Here is a post from MSDN explaining how to do it using SMO:

using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";
            FileInfo file = new FileInfo("C:\\myscript.sql");
            string script = file.OpenText().ReadToEnd();
            SqlConnection conn = new SqlConnection(sqlConnectionString);
            Server server = new Server(new ServerConnection(conn));
            server.ConnectionContext.ExecuteNonQuery(script);
        }
    }
}

answered Feb 12 '10 at 07:58

Don

9,511
4
26
25

2

I think this will choke on the GO statements – Rune Grimstad Feb 12 '10 at 11:11
Should point out that the FileInfo.OpenText method returns a StreamReader. You'd want to make sure you're disposing that. – rossisdead May 04 '11 at 01:10
@RuneGrimstad It won't choke on GO. This is using the SQL Server Management assemblies instead of the standard SqlCommand. – Juha Palomäki Oct 12 '13 at 13:07
This requires referencing DLLs which can be found from SQL Server folder, C:\Program Files\Microsoft SQL Server\XXXX\SDK\Assemblies for 64-bit version. For more info see: http://stackoverflow.com/questions/3879987/i-cant-add-microsoft-sqlserver-management-common-to-my-asp-net-mvc-application – Juha Palomäki Oct 12 '13 at 13:08

score 7 · Answer 2 · answered Feb 12 '10 at 08:09

7

When I need to run sql scripts containing GO statements I usually read the entire file into a string and split it into a string array using GO as the delimiter.

I then connect to the database and run each statement in order.

It's quite easy and works well. Just make sure to keep your database connection open while running all statements. Also you may consider running them all in a transaction.

answered Feb 12 '10 at 08:09

Rune Grimstad

35,612
10
61
76

is this whole process time taking and slow ? – HotTester Feb 12 '10 at 08:13
1

Is MSSQL transactional "enough" to rollback `alter table` and the like? – Jørn Schou-Rode Feb 12 '10 at 08:45
@HotTester: I've run some large scripts this way and they will run more or less just as fast as if you run them from the SQL Management Console. – Rune Grimstad Feb 12 '10 at 11:12
1

@Jørn: No, I don't think you can alter a table inside a transaction. – Rune Grimstad Feb 12 '10 at 11:17
9

Just pray you don't have any string data that contains the word "go" in it! – roufamatic Aug 08 '11 at 23:33
You could split on ^GO\n or a more correct Regex. – ProfK Apr 25 '13 at 23:38

score 6 · Accepted Answer · edited May 23 '17 at 11:48

6

Have a look at

edited May 23 '17 at 11:48

Community

1
1

answered Feb 12 '10 at 07:49

Adriaan Stander

162,879
31
289
284

1

but in the link given by you it clearly says that it will only work if the script does not have GO statement...... my script is for creation of a new database... it contains multiple GO.. – HotTester Feb 12 '10 at 07:51
Also have a look at the second link, which was asked here on SO, and the 3rd link also mentions workarounds for this. – Adriaan Stander Feb 12 '10 at 07:53
As far as I know (I'm not 100% sure, but pretty sure), there is no such thing as a "GO statement" in T-Sql. It is a feature supported by the management studio, not the SQL language itself. The tools split up the script into multiple scripts, and let the SQL server execute each script in turn. Therefore it will never be possible to have SQL server execute a script containing GO statements. You will have to split it up into multiple scripts yourself. – Pete Feb 12 '10 at 07:57
the third link is actually based on the answer which is in first link! I have already tried these links.... and as they failed i came to SO... nevertheless thanks for help... – HotTester Feb 12 '10 at 07:59
3

If "GO" is an issue, you could parse the .SQL file and run each section through ExecuteNonQuery. – Steven Sudit Feb 12 '10 at 08:02
But wouldn't doing this be a performance issue ? I am currently executing script only for creation of database (only of 50 odd lines). Further i would require to create script for creation of 374 tables, 1210 sp, 65 views and 88 functions ! In this case it would become cucombersome ! – HotTester Feb 12 '10 at 08:06
1

In that case the extra time needed to split the scripts on "GO" and run individual queries would be so little compared to the time needed to effectively create all those objects... – Sorin Comanescu Feb 12 '10 at 08:18
1

@hottester: you misunderstood what you read (or didn't) apparently. SMO will execute your DDL scripts. – Sky Sanders Feb 12 '10 at 09:00
@astander Hi, I want to do the same which is asked in this question..I have referred the link http://stackoverflow.com/questions/650098/how-to-execute-an-sql-script-file-using-c-sharp.. But how can I execute this class after or during installation.. – mahesh Aug 28 '12 at 06:43

How to run a sql script using C#

3 Answers3

Linked