private void WriteDeployFile(string projectFile, string procName, string procFile, string outputDir)
{
var script = GetFileContents(procFile);
if (string.IsNullOrEmpty(script))
{
OutputWindowMessage.WriteMessage("Could not read script file: " + procFile);
return;
}
var variables = new SsdtVariableProvider().GetVariables(projectFile);
foreach (SqlCmdVariable v in variables)
{
script = script.Replace(v.Name, v.Value);
}
var outputScript = new StringBuilder();
outputScript.AppendFormat(DeploymentScriptGenerator.BuildDeploy(script));
string outputScriptPath = Path.Combine(outputDir,
string.Format("{0}.sql", DeploymentScriptGenerator.GetLastPartOfName(procName)));
try
{
WriteFileContents(outputScriptPath, outputScript.ToString());
OutputWindowMessage.WriteMessage("Written file: " + outputScriptPath);
}
catch (Exception e)
{
OutputWindowMessage.WriteMessage("Could not write file: " + outputScriptPath + " - error: " + e.Message);
}
}
public void Generate_create_from_create_schema()
{
var result =
DeploymentScriptGenerator.BuildDeploy(
"create schema [bloo blah blum]");
Assert.AreEqual("if not exists (select * from sys.schemas where name = 'bloo blah blum')\r\nbegin\r\n\texec sp_executesql N'create schema [bloo blah blum]'\r\nend\r\n", result);
}
public void escape_identifier_escapes_name_and_schema_and_db()
{
const string identifier = "database name.schema.table name";
const string expected = "[database name].[schema].[table name]";
var actual = DeploymentScriptGenerator.EscapeIdentifier(identifier);
Assert.AreEqual(expected, actual);
}
public void escape_identifier_escapes_name()
{
const string identifier = "table name";
const string expected = "[table name]";
var actual = DeploymentScriptGenerator.EscapeIdentifier(identifier);
Assert.AreEqual(expected, actual);
}
public void Generate_drop_from_create_proc()
{
var result =
DeploymentScriptGenerator.BuildDeploy(
"create procedure test as select 1 from test");
Assert.AreEqual(@"if exists (select * from sys.procedures where object_id = object_id('test'))
drop procedure [test];
GO
create procedure test as select 1 from test", result);
}
private void DeploySingleFile(object state)
{
try
{
var project = state as Project;
var variables = new SsdtVariableProvider().GetVariables(project.FullName);
var settings = Config.Configuration.GetSettings(project);
var filename = GetSelectedSolutionExplorerFileName();
if (String.IsNullOrEmpty(filename))
{
OutputWindowMessage.WriteMessage("Couldn't GetConfig filename");
return;
}
if (!filename.EndsWith(".sql"))
{
OutputWindowMessage.WriteMessage("Single file deploy only works with .sql files - boo hoo hoo");
return;
}
var procname = ScriptProperties.GetScriptDetail(File.ReadAllText(filename)).Name;
if (string.IsNullOrEmpty(procname))
{
OutputWindowMessage.WriteMessage("Couldn't GetConfig proc name - boo hoo hoo");
return;
}
var fileContents = GetFileContents(filename);
foreach (SqlCmdVariable v in variables)
{
fileContents = fileContents.Replace(v.Name, v.Value);
}
if (!DtcAvailable())
{
OutputWindowMessage.WriteMessage("Unable to deploy file, deploy uses msdtc to protect changes. Please ensure the service is enabled and running");
return;
}
using (var scope = new TransactionScope())
{
try
{
var script = DeploymentScriptGenerator.BuildDeploy(fileContents);
var batches = script.Split(new string[] { "\r\nGO\r\n" }, StringSplitOptions.RemoveEmptyEntries);
foreach (var batch in batches)
{
new SqlGateway(settings.ConnectionString).Execute(batch);
}
scope.Complete();
OutputWindowMessage.WriteMessage(string.Format("Deployed File: {0}\r\n", filename));
}
catch (NullReferenceException)
{
OutputWindowMessage.WriteMessage(string.Format("Unable to deploy file {0}\r\n", filename));
}
catch (Exception ex)
{
OutputWindowMessage.WriteMessage(string.Format("Unable to deploy file {0} error : {1}\r\n",
filename, ex.Message));
}
}
}
catch (Exception e)
{
OutputWindowMessage.WriteMessage("Deploying file Failed: " + e.Message);
}
}
public void throws_exception_on_non_create_proc_scripts()
{
Assert.Throws <TSqlDeploymentException>(() => DeploymentScriptGenerator.BuildDeploy("create table test (a int, b int)"));
}