How to run a sql script using C# [duplicate]

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
– Sky Sanders, Commented Feb 12, 2010 at 9:01

Don · Accepted Answer · 2010-02-12 07:58:45Z

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, 2010 at 7:58

Don

9,6814 gold badges28 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rune Grimstad Over a year ago

I think this will choke on the GO statements

rossisdead Over a year ago

Should point out that the FileInfo.OpenText method returns a StreamReader. You'd want to make sure you're disposing that.

Juha Palomäki Over a year ago

@RuneGrimstad It won't choke on GO. This is using the SQL Server Management assemblies instead of the standard SqlCommand.

Juha Palomäki Over a year ago

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: stackoverflow.com/questions/3879987/…

Rune Grimstad · Accepted Answer · 2010-02-12 08:09:53Z

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, 2010 at 8:09

Rune Grimstad

36.5k10 gold badges66 silver badges77 bronze badges

6 Comments

HotTester Over a year ago

is this whole process time taking and slow ?

Jørn Schou-Rode Over a year ago

Is MSSQL transactional "enough" to rollback alter table and the like?

Rune Grimstad Over a year ago

@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 Over a year ago

@Jørn: No, I don't think you can alter a table inside a transaction.

roufamatic Over a year ago

Just pray you don't have any string data that contains the word "go" in it!

|

Community · Accepted Answer · 2017-05-23 11:48:42Z

6

Have a look at

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Feb 12, 2010 at 7:49

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

9 Comments

HotTester Over a year ago

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..

Adriaan Stander Over a year ago

Also have a look at the second link, which was asked here on SO, and the 3rd link also mentions workarounds for this.

Steven Sudit Over a year ago

If "GO" is an issue, you could parse the .SQL file and run each section through ExecuteNonQuery.

Sorin Comanescu Over a year ago

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...

Sky Sanders Over a year ago

@hottester: you misunderstood what you read (or didn't) apparently. SMO will execute your DDL scripts.

|

Collectives™ on Stack Overflow

How to run a sql script using C# [duplicate]

3 Answers 3

4 Comments

6 Comments

9 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

6 Comments

9 Comments

Linked

Related