Click here to Skip to main content
15,886,519 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am using the SchemaComparison object to compare a database schema with a dacpac file, once the comparison is completed I generate a script with the changes, now I would like to know how can I execute the script to update a database:
C#
var comparison = new SchemaComparison(sourceDacpac, targetDatabase);
SchemaComparisonResult comparisonResult = comparison.Compare();
var script = comparisonResult.GenerateScript(databaseName);
if(script.Success)
{ // Execute script }


What I have tried:

I tried this code, but I am getting an error SqlException: Incorrect syntax near ':', I think this is coming from the first lines of the script

GO
:setvar DatabaseName "NAME"

C#
using(Microsoft.Data.SqlClient.SqlConnection connection = new Microsoft.Data.SqlClient.SqlConnection(myConnection))
{
Server server = new Server(new ServerConnection(connection));
server.ConnectionContext.StatementTimeout = 0;
server.ConnectionContext.ExecuteNonQuery(script);
}
Posted
Updated 11-Nov-22 22:21pm

1 solution

The GO statements are for SQL Server, if you want to run the script from C# you will have to remove these statements and probably split the SQL commands in chunks that can be executed.

A method that is often suggested is using SMO:
execute-a-large-sql-script-with-go-commands[^]
But personally I'm not a big fan of SMO (and SQL Server btw) as SMO tends to change between releases, so I can not recommend this.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900