Dapper. Execute Query with GOs

Question

I am trying to use Dapper to run a SQL Query:

use master
go

if exists (select name from sys.databases where name = N'TestDB')
drop database [TestDB]
go

create database [TestDB] on primary ( 
  name = 'TestDB_Data',
  filename = '$Path\TestDB_Data.mdf',
  size = 40MB,
  maxsize = 2GB,
  filegrowth = 20MB 
)

use [TestDB]
go

create table dbo.Posts
(
  Id int identity not null,
  Body nvarchar (max) null
);

I am using Dapper as follows:

using (SqlConnection connection = new SqlConnection(connectionString)) {
  connection.Open();
  connection.Execute(sqlQuery);
}

However, I get an error when using GO.

But if I remove the GO statements I get an error when creating Posts because the table TestDB wasn't created.

Is there a way to use Dapper to solve this?

I was able to do this only using SQL Server SDK.

Answer 1

GO is not a SQL statement it is merely a batch separator, in fact you could rename it to whatever you want.

The way Management Studio figures this out is by parsing the SQL. It is easy to write a trivial parser that can chunk the statements up. Just split the string on "GO" and send each statement to Dapper.

However, to be 100% correct you need a sophisticated parser.

select '
GO ' GO from [GO]