public void GenerateCreateScript_ShouldNotModifyDeployOptions()
{
// Arrange
var packageName = "MyPackage";
var firstReference = new TestModelBuilder()
.AddTable("MyFirstTable", ("Column1", "nvarchar(100)"))
.SaveAsPackage();
var secondReference = new TestModelBuilder()
.AddTable("MySecondTable", ("Column1", "nvarchar(100)"))
.AddReference(firstReference)
.SaveAsPackage();
var tempFile = new FileInfo(Path.GetTempFileName());
var packageBuilder = new PackageBuilder();
packageBuilder.UsingVersion(SqlServerVersion.Sql150);
packageBuilder.SetMetadata(packageName, "1.0.0.0");
packageBuilder.AddReference(firstReference);
packageBuilder.AddReference(secondReference);
packageBuilder.ValidateModel();
var deployOptions = new DacDeployOptions();
deployOptions.IncludeCompositeObjects = true;
deployOptions.CreateNewDatabase = true;
var expectedCreateScriptFileName = $"{packageName}_Expected_Create.sql";
// Act - Generate expected script
packageBuilder.SaveToDisk(tempFile);
using var package = DacPackage.Load(tempFile.FullName);
using var expectedCreateScriptFile = File.Create(Path.Combine(tempFile.DirectoryName, expectedCreateScriptFileName));
DacServices.GenerateCreateScript(expectedCreateScriptFile, package, packageName, deployOptions);
expectedCreateScriptFile.Close();
// Act - Generate script
packageBuilder.GenerateCreateScript(tempFile, packageName, deployOptions);
// Assert
var expectedScriptContent = File.ReadAllText(Path.Combine(tempFile.DirectoryName, expectedCreateScriptFileName));
expectedScriptContent.ShouldNotBeNullOrEmpty();
var scriptContent = File.ReadAllText(Path.Combine(tempFile.DirectoryName, $"{packageName}_Create.sql"));
scriptContent.ShouldNotBeNullOrEmpty();
expectedScriptContent.ShouldBe(scriptContent);
// Cleanup
tempFile.Delete();
}
public void CanGetReferences()
{
// Arrange
var referencePackage = new TestModelBuilder()
.AddTable("MyTable", ("Column1", "nvarchar(100)"))
.SaveAsPackage();
var model = new TestModelBuilder()
.AddReference(referencePackage)
.Build();
// Act
var references = model.GetReferencedDacPackages();
// Assert
references.Any().ShouldBeTrue();
references.First().ShouldBe(referencePackage);
}
public void AddReference_FileIsNotDacpac()
{
// Arrange
string reference = new TestModelBuilder()
.AddStoredProcedure("MyStoredProcedure", "SELECT 1;")
.SaveAsPackage(".dll");
var packageBuilder = new PackageBuilder();
packageBuilder.UsingVersion(SqlServerVersion.Sql150);
// Act & Assert
Should.Throw <ArgumentException>(() => packageBuilder.AddReference(reference))
.Message.ShouldStartWith("Invalid filetype .dll");
// Cleanup
File.Delete(reference);
}
public void CanAddReference()
{
// Arrange
var referencePackage = new TestModelBuilder()
.AddTable("MyTable", ("Column1", "nvarchar(100)"))
.SaveAsPackage();
// Act
var model = new TestModelBuilder()
.AddReference(referencePackage)
.AddStoredProcedure("MyProc", "SELECT * FROM MyTable;")
.Build();
// Assert
var validationErrors = model.Validate();
validationErrors.Any().ShouldBeFalse();
}
public void CanGetModelValidationErrors()
{
// Arrange
var model = new TestModelBuilder()
.AddStoredProcedureFromFile("../../../../TestProjectWithErrors/Procedures/csp_Test.sql")
.Build();
// Act
var modelValidationErrors = model.GetModelValidationErrors(Enumerable.Empty <string>());
// Assert
modelValidationErrors.ShouldNotBeEmpty();
var error = modelValidationErrors.First();
error.Severity.ShouldBe(Microsoft.SqlServer.Dac.Model.ModelErrorSeverity.Error);
error.ToString().ShouldBe("../../../../TestProjectWithErrors/Procedures/csp_Test.sql(2,18):ModelValidationError Error SQL71501: SqlSubroutineParameter: [dbo].[csp_Test].[@p_Parameter] has an unresolved reference to SqlBuiltInType [dbo].[MyCustomType].");
}
public void AddReferenceDatabaseLiteralNewFormat()
{
// Arrange
var referencePackage = new TestModelBuilder()
.AddTable("MyTable", ("Column1", "nvarchar(100)"))
.SaveAsPackage();
// Act
var model = new TestModelBuilder()
.AddReference(referencePackage, "dbl=SomeDatabase")
.AddStoredProcedure("MyProc", "SELECT * FROM SomeDatabase.dbo.MyTable;")
.Build();
// Assert
var validationErrors = model.Validate();
validationErrors.Any().ShouldBeFalse();
}
public void AddReference_FileExists()
{
// Arrange
var reference = new TestModelBuilder()
.AddStoredProcedure("MyStoredProcedure", "SELECT 1;")
.SaveAsPackage();
var packageBuilder = new PackageBuilder();
packageBuilder.UsingVersion(SqlServerVersion.Sql150);
// Act
packageBuilder.AddReference(reference);
// Assert
packageBuilder.Model.GetObject(Procedure.TypeClass, new ObjectIdentifier("dbo", "MyStoredProcedure"), DacQueryScopes.All).ShouldNotBeNull();
// Cleanup
File.Delete(reference);
}
public void CanGetModelValidationErrors()
{
// Arrange
var model = new TestModelBuilder()
.AddStoredProcedureFromFile("../../../../TestProjectWithErrors/Procedures/csp_Test.sql")
.Build();
// Act
var modelValidationErrors = model.GetModelValidationErrors(Enumerable.Empty <string>());
// Assert
modelValidationErrors.ShouldNotBeEmpty();
var error = modelValidationErrors.First();
error.Severity.ShouldBe(Microsoft.SqlServer.Dac.Model.ModelErrorSeverity.Error);
error.ErrorCode.ShouldBe(71501);
error.SourceName.ShouldBe("../../../../TestProjectWithErrors/Procedures/csp_Test.sql", StringCompareShould.IgnoreCase);
}
public void AddReferenceServerVariableAndDatabaseLiteral()
{
// Arrange
var referencePackage = new TestModelBuilder()
.AddTable("MyTable", ("Column1", "nvarchar(100)"))
.SaveAsPackage();
// Act
var model = new TestModelBuilder()
.AddReference(referencePackage, "dbl=SomeDatabase|srv=SomeServer")
.AddSqlCmdVariables(new string[] { "SomeServer" })
.AddStoredProcedure("MyProc", "SELECT * FROM [$(SomeServer)].[SomeDatabase].dbo.MyTable;")
.Build();
// Assert
var validationErrors = model.Validate();
validationErrors.Any().ShouldBeFalse();
}
public void AddReference_DifferentDatabase()
{
// Arrange
var reference = new TestModelBuilder()
.AddStoredProcedure("MyStoredProcedure", "SELECT 1;")
.SaveAsPackage();
var packageBuilder = new PackageBuilder();
packageBuilder.UsingVersion(SqlServerVersion.Sql150);
// Act
packageBuilder.AddExternalReference(reference, "SomeOtherDatabase");
// Assert
var referencingStoredProcedure = "CREATE PROCEDURE [MyOtherStoredProcedure] AS BEGIN EXEC [SomeOtherDatabase].[dbo].[MyStoredProcedure] END";
packageBuilder.Model.AddObjects(referencingStoredProcedure);
packageBuilder.Model.Validate().Any().ShouldBeFalse();
// Cleanup
File.Delete(reference);
}