/// <summary>
/// Gets the required version.
/// </summary>
/// <param name="connectionStringModel">The connection string model.</param>
/// <returns></returns>
internal static string GetRequiredVersion(ConnectionStringModel connectionStringModel)
{
string result = string.Empty;
if (connectionStringModel != null)
{
string useStatement = string.IsNullOrWhiteSpace(connectionStringModel.Database) ? string.Empty : "USE " + connectionStringModel.Database + @";
";
string sql = @"
DECLARE @VersionResult AS [nvarchar](50);
SET @VersionResult = NULL;
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SQLDeployToolLog]') AND type in (N'U'))
SELECT TOP 1 @VersionResult = [TargetVersion] from dbo.[SQLDeployToolLog] ORDER BY [ExecutedTime] DESC;
ELSE
BEGIN
CREATE TABLE [dbo].[SQLDeployToolLog](" +
"[Key] [uniqueidentifier] NULL," +
"[ExecutedBy] [nvarchar](50) NULL," +
"[BaseVersion] [nvarchar](50) NULL," +
"[TargetVersion] [nvarchar](50) NULL," +
"[IsFull] [bit] NULL," +
"[ExecutedTime] [datetime] NOT NULL default(getutcdate())" +
@") ON [PRIMARY];
END
IF @VersionResult IS NULL
SET @VersionResult = ' ';
SELECT @VersionResult;
";
result = ExecuteSqlScalar(connectionStringModel.ToConnectionString(), useStatement + sql) as string;
}
return(result);
}
/// <summary>
/// Runs the SQL script.
/// </summary>
/// <param name="connectionStringModel">The connection string model.</param>
/// <param name="scriptFullPath">The script full path.</param>
public static void RunSqlScript(ConnectionStringModel connectionStringModel, List <string> scriptFullPath)
{
string connectionString = connectionStringModel.ToConnectionString();
try
{
WriteOutputLine("Ready to execute SQL scripts...");
foreach (var path in scriptFullPath)
{
FileInfo fileSource = new FileInfo(path);
string script = fileSource.OpenText().ReadToEnd();
WriteOutput("Executing SQL script for: " + path + " ...");
ExecuteSql(connectionString, script);
WriteOutput("Done." + Environment.NewLine);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Executes the specified solution name.
/// </summary>
/// <param name="solutionName">Name of the solution.</param>
public void Execute(string solutionName)
{
string fullOrIncremental = string.Empty;
if (runningXml != null && connectionModel != null)
{
fullOrIncremental = (isFull ? Constants.Full : Constants.Incremental);
/// Read configuration values for later use.
#region Read version
try
{
WriteOutputLine("Checking settings & xml contents...");
baseVersion = isFull ? null : new Version(runningXml.Attribute(Constants.BaseVersion).Value);
targetVersion = new Version(runningXml.Attribute(Constants.TargetVersion).Value);
WriteOutputLine("Running version selected: " + fullOrIncremental);
WriteOutputLine("Base version: " + (isFull ? "*" : baseVersion.ToString()));
WriteOutputLine("Target version: " + targetVersion.ToString());
}
catch (Exception ex)
{
WriteOutputLine("Failed to load version info caused by: " + ex.Message);
return;
}
#endregion
}
#region Try to initialize SQL connection instance
SqlConnection sqlConnection = null;
try
{
sqlConnection = new SqlConnection(connectionModel.ToConnectionString());
}
catch (Exception ex)
{
WriteOutputLine("Failed to initialize SQL connection instance caused by: " + ex.Message);
return;
}
#endregion
#region Try to match version for deployment
try
{
WriteOutput("Trying to check database version ... ");
string versionString = ScriptHelper.GetRequiredVersion(connectionModel);
Version currentVersion = !string.IsNullOrWhiteSpace(versionString) ? new Version(versionString) : null;
if (!isFull && currentVersion != null && currentVersion.CompareTo(baseVersion) != 0)
{
WriteOutputLine("Unmatched version to deploy. Current:" + currentVersion.ToString() + ", Required:" + baseVersion.ToString());
return;
}
else
{
WriteOutputLine("matched.");
}
}
catch (Exception ex)
{
WriteOutputLine("Failed to match version caused by: " + ex.Message);
return;
}
#endregion
#region Prepare deployment scripts
string baseContainer = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, fullOrIncremental);
WriteOutputLine("Original script container: " + GetFileUrl(baseContainer));
string targetContainer = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, solutionName.ToShortId() + "_" + fullOrIncremental + "_" + (baseVersion == null ? string.Empty : baseVersion.ToString()) + "_" + targetVersion.ToString());
WriteOutputLine("Output script container: " + GetFileUrl(targetContainer));
if (!Directory.Exists(targetContainer))
{
Directory.CreateDirectory(targetContainer);
WriteOutputLine("Directory created for output script container: " + GetFileUrl(targetContainer));
}
List <string> scriptNames = new List <string>();
GenerateScripts(scriptNames, baseContainer, targetContainer, runningXml, Constants.Table, connectionModel.Database);
GenerateScripts(scriptNames, baseContainer, targetContainer, runningXml, Constants.Function, connectionModel.Database);
GenerateScripts(scriptNames, baseContainer, targetContainer, runningXml, Constants.View, connectionModel.Database);
GenerateScripts(scriptNames, baseContainer, targetContainer, runningXml, Constants.ViewBaseFunction, connectionModel.Database);
GenerateScripts(scriptNames, baseContainer, targetContainer, runningXml, Constants.StoredProcedure, connectionModel.Database);
GenerateScripts(scriptNames, baseContainer, targetContainer, runningXml, Constants.Data, connectionModel.Database);
var bcpFilePath = GenerateBCPCommand(connectionModel, baseContainer, targetContainer, runningXml, Constants.BCP, connectionModel.Database);
#endregion
WriteOutputLine();
bool isSuccess = false;
try
{
ScriptHelper.RunSqlScript(connectionModel, scriptNames);
if (!string.IsNullOrWhiteSpace(bcpFilePath))
{
ScriptHelper.RunBatCommand(bcpFilePath, writeOutputDelegate);
}
WriteOutputLine("----------------------------------------------");
WriteOutputLine("Completed!");
WriteOutputLine();
isSuccess = true;
}
catch (Exception ex)
{
WriteOutputLine("Error occurred!");
WriteOutputLine("----------------------------------------------");
WriteOutputLine(ex.Message);
WriteOutputLine();
}
if (isSuccess)
{
string operatorInfo = connectionModel.IsWindowsAuthentication ? System.Security.Principal.WindowsIdentity.GetCurrent().Name : connectionModel.UserName;
ScriptHelper.WriteExecutionScriptLog(operatorInfo, connectionModel.ToConnectionString(), baseVersion, targetVersion, isFull);
WriteOutputLine("SQL log has updated to:" + targetVersion.ToString());
}
}
/// <summary>
/// Gets the required version.
/// </summary>
/// <param name="connectionStringModel">The connection string model.</param>
/// <returns></returns>
internal static string GetRequiredVersion(ConnectionStringModel connectionStringModel)
{
string result = string.Empty;
if (connectionStringModel != null)
{
string useStatement = string.IsNullOrWhiteSpace(connectionStringModel.Database) ? string.Empty : "USE " + connectionStringModel.Database + @";
";
string sql = @"
DECLARE @VersionResult AS [nvarchar](50);
SET @VersionResult = NULL;
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SQLDeployToolLog]') AND type in (N'U'))
SELECT TOP 1 @VersionResult = [TargetVersion] from dbo.[SQLDeployToolLog] ORDER BY [ExecutedTime] DESC;
ELSE
BEGIN
CREATE TABLE [dbo].[SQLDeployToolLog](" +
"[Key] [uniqueidentifier] NULL," +
"[ExecutedBy] [nvarchar](50) NULL," +
"[BaseVersion] [nvarchar](50) NULL," +
"[TargetVersion] [nvarchar](50) NULL," +
"[IsFull] [bit] NULL," +
"[ExecutedTime] [datetime] NOT NULL default(getutcdate())" +
@") ON [PRIMARY];
END
IF @VersionResult IS NULL
SET @VersionResult = ' ';
SELECT @VersionResult;
";
result = ExecuteSqlScalar(connectionStringModel.ToConnectionString(), useStatement + sql) as string;
}
return result;
}
/// <summary>
/// Runs the SQL script.
/// </summary>
/// <param name="connectionStringModel">The connection string model.</param>
/// <param name="scriptFullPath">The script full path.</param>
public static void RunSqlScript(ConnectionStringModel connectionStringModel, List<string> scriptFullPath)
{
string connectionString = connectionStringModel.ToConnectionString();
try
{
WriteOutputLine("Ready to execute SQL scripts...");
foreach (var path in scriptFullPath)
{
FileInfo fileSource = new FileInfo(path);
string script = fileSource.OpenText().ReadToEnd();
WriteOutput("Executing SQL script for: " + path + " ...");
ExecuteSql(connectionString, script);
WriteOutput("Done." + Environment.NewLine);
}
}
catch (Exception ex)
{
throw ex;
}
}