/// <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>
/// Generates the BCP command.
/// </summary>
/// <param name="connectionStringModel">The connection string model.</param>
/// <param name="baseFolder">The base folder.</param>
/// <param name="resultContainer">The result container.</param>
/// <param name="sectionXml">The section XML.</param>
/// <param name="databaseName">Name of the database.</param>
/// <returns>System.String.</returns>
internal static string GenerateBCPCommand(ConnectionStringModel connectionStringModel, string baseFolder, string resultContainer, XElement sectionXml, string databaseName)
{
string fullPath = Path.Combine(resultContainer, sectionXml.Name.LocalName + "_All.bat");
try
{
if (File.Exists(fullPath))
{
File.Delete(fullPath);
}
StringBuilder stringBuilder = new StringBuilder();
if (!string.IsNullOrWhiteSpace(databaseName) && sectionXml.IsEnabled())
{
stringBuilder.Append(@"@echo off
");
string lineFormat = @"@echo ---------------------------------------------
@echo Importing data for {1} ...
@bcp [{0}].[dbo].[{1}] in ""{2}"" -S {4} -w {3}
";
foreach (XElement node in sectionXml.Elements())
{
string targetTable = node.GetAttributeValue("TargetTable");
if (!string.IsNullOrWhiteSpace(targetTable))
{
string fileNamePath = Path.Combine(baseFolder, sectionXml.Name.LocalName + @"\" + node.Value);
if (File.Exists(fileNamePath))
{
stringBuilder.AppendFormat(lineFormat,
// Database Name
databaseName,
// Table Name
targetTable,
// BCP file name
fileNamePath,
// Login info: -T or
connectionStringModel.IsWindowsAuthentication ? "-T" : string.Format("-U {0} -P{1}", connectionStringModel.UserName.SafeToString(), connectionStringModel.Password.SafeToString()),
// server name
connectionStringModel.Server
);
}
}
}
}
// Save to file
File.WriteAllText(fullPath, stringBuilder.ToString(), Encoding.ASCII);
}
catch (Exception ex)
{
WriteOutputLine("Failed to generate BCP command file caused by:" + ex.Message);
}
return(fullPath);
}
/// <summary>
/// Initializes a new instance of the <see cref="SqlExecutor" /> class.
/// </summary>
/// <param name="writeOutputDelegate">The write output delegate.</param>
/// <param name="connectionSetting">The connection setting.</param>
/// <param name="runningXml">The running XML.</param>
public SqlExecutor(WriteOutputDelegate writeOutputDelegate, ConnectionStringModel connectionSetting, XElement runningXml)
{
this.writeOutputDelegate = writeOutputDelegate;
connectionModel = connectionSetting;
if (runningXml != null)
{
this.runningXml = runningXml;
isFull = runningXml.Name.LocalName.Equals("full", StringComparison.InvariantCultureIgnoreCase);
}
ScriptHelper.WriteOutputDelegate = writeOutputDelegate;
}
/// <summary>
/// Initializes a new instance of the <see cref="SqlExecutor" /> class.
/// </summary>
/// <param name="writeOutputDelegate">The write output delegate.</param>
/// <param name="connectionSetting">The connection setting.</param>
/// <param name="runningXml">The running XML.</param>
public SqlExecutor(WriteOutputDelegate writeOutputDelegate, ConnectionStringModel connectionSetting, XElement runningXml)
{
this.writeOutputDelegate = writeOutputDelegate;
connectionModel = connectionSetting;
if (runningXml != null)
{
this.runningXml = runningXml;
isFull = runningXml.Name.LocalName.Equals("full", StringComparison.InvariantCultureIgnoreCase);
}
ScriptHelper.WriteOutputDelegate = writeOutputDelegate;
}
/// <summary>
/// Gets the connection string model by control.
/// </summary>
/// <param name="result">if set to <c>true</c> [result].</param>
/// <returns></returns>
public ConnectionStringModel GetConnectionStringModelByControl()
{
ConnectionStringModel model = new ConnectionStringModel();
model.Server = this.txtServerIP.Text;
model.Database = this.txtDatabase.Text;
model.UserName = this.txtUID.Text;
model.Password = this.txtPwd.Text;
if (rdBtnWindow.Checked)
{
model.UserName = model.Password = string.Empty;
}
return model;
}
/// <summary>
/// Loads the load connection string from base64.
/// </summary>
/// <param name="connectionString">The connection string.</param>
/// <returns></returns>
public static ConnectionStringModel LoadConnectionStringFromBase64(string connectionString)
{
ConnectionStringModel model = null;
try
{
byte[] bytes = Convert.FromBase64String(connectionString);
model = ConnectionStringModel.LoadConnectionString(System.Text.Encoding.UTF8.GetString(bytes));
}
catch
{
}
return(model);
}
/// <summary>
/// Loads the connection string.
/// </summary>
/// <param name="node">The node.</param>
/// <returns></returns>
public static ConnectionStringModel LoadConnectionString(XElement node)
{
ConnectionStringModel result = null;
if (node != null && node.Name.LocalName == "ConnectionString")
{
result = new ConnectionStringModel();
result.UserName = GetAttributeValue(node, "UserName");
result.Password = GetAttributeValue(node, "Password");
result.Server = GetAttributeValue(node, "Server");
//result.Port = ConvertStringToPort(GetAttributeValue(node, "Port"));
result.Database = GetAttributeValue(node, "Database");
}
return(result);
}
/// <summary>
/// Generates the BCP command.
/// </summary>
/// <param name="connectionModel">The connection model.</param>
/// <param name="baseContainer">The base container.</param>
/// <param name="targetContainer">The target container.</param>
/// <param name="rootNode">The root node.</param>
/// <param name="sectionName">Name of the section.</param>
/// <param name="databaseName">Name of the database.</param>
/// <returns>System.String.</returns>
private string GenerateBCPCommand(ConnectionStringModel connectionModel, string baseContainer, string targetContainer, XElement rootNode, string sectionName, string databaseName)
{
if (sectionName == Constants.BCP)
{
var node = rootNode.Element(sectionName);
if (node == null)
{
WriteOutputLine("Skip to generate scripts for " + sectionName + " caused by no section node found.");
}
else
{
var path = ScriptHelper.GenerateBCPCommand(connectionModel, baseContainer, targetContainer, node, databaseName);
WriteOutputLine(sectionName + " path: " + (string.IsNullOrWhiteSpace(path) ? Constants.NA : ("file://" + path.Replace('\\', '/'))));
return(path);
}
}
return(null);
}
/// <summary>
/// Loads the connection by string.
/// </summary>
/// <param name="connectionString">The connection string.</param>
/// <returns></returns>
public static ConnectionStringModel LoadConnectionString(string connectionString)
{
ConnectionStringModel result = new ConnectionStringModel();
if (!string.IsNullOrWhiteSpace(connectionString))
{
try
{
SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder(connectionString);
result.Server = connectionBuilder.DataSource;
result.UserName = connectionBuilder.UserID;
result.Password = connectionBuilder.Password;
result.Database = connectionBuilder.InitialCatalog;
}
catch (Exception ex) { }
}
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>
/// Loads the connection by string.
/// </summary>
/// <param name="connectionString">The connection string.</param>
/// <returns></returns>
public static ConnectionStringModel LoadConnectionString(string connectionString)
{
ConnectionStringModel result = new ConnectionStringModel();
if (!string.IsNullOrWhiteSpace(connectionString))
{
try
{
SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder(connectionString);
result.Server = connectionBuilder.DataSource;
result.UserName = connectionBuilder.UserID;
result.Password = connectionBuilder.Password;
result.Database = connectionBuilder.InitialCatalog;
}
catch (Exception ex) { }
}
return result;
}
/// <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>
/// Generates the BCP command.
/// </summary>
/// <param name="connectionStringModel">The connection string model.</param>
/// <param name="baseFolder">The base folder.</param>
/// <param name="resultContainer">The result container.</param>
/// <param name="sectionXml">The section XML.</param>
/// <param name="databaseName">Name of the database.</param>
/// <returns>System.String.</returns>
internal static string GenerateBCPCommand(ConnectionStringModel connectionStringModel, string baseFolder, string resultContainer, XElement sectionXml, string databaseName)
{
string fullPath = Path.Combine(resultContainer, sectionXml.Name.LocalName + "_All.bat");
try
{
if (File.Exists(fullPath))
{
File.Delete(fullPath);
}
StringBuilder stringBuilder = new StringBuilder();
if (!string.IsNullOrWhiteSpace(databaseName) && sectionXml.IsEnabled())
{
stringBuilder.Append(@"@echo off
");
string lineFormat = @"@echo ---------------------------------------------
@echo Importing data for {1} ...
@bcp [{0}].[dbo].[{1}] in ""{2}"" -S {4} -w {3}
";
foreach (XElement node in sectionXml.Elements())
{
string targetTable = node.GetAttributeValue("TargetTable");
if (!string.IsNullOrWhiteSpace(targetTable))
{
string fileNamePath = Path.Combine(baseFolder, sectionXml.Name.LocalName + @"\" + node.Value);
if (File.Exists(fileNamePath))
{
stringBuilder.AppendFormat(lineFormat,
// Database Name
databaseName,
// Table Name
targetTable,
// BCP file name
fileNamePath,
// Login info: -T or
connectionStringModel.IsWindowsAuthentication ? "-T" : string.Format("-U {0} -P{1}", connectionStringModel.UserName.SafeToString(), connectionStringModel.Password.SafeToString()),
// server name
connectionStringModel.Server
);
}
}
}
}
// Save to file
File.WriteAllText(fullPath, stringBuilder.ToString(), Encoding.ASCII);
}
catch (Exception ex)
{
WriteOutputLine("Failed to generate BCP command file caused by:" + ex.Message);
}
return fullPath;
}
/// <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>
/// Generates the BCP command.
/// </summary>
/// <param name="connectionModel">The connection model.</param>
/// <param name="baseContainer">The base container.</param>
/// <param name="targetContainer">The target container.</param>
/// <param name="rootNode">The root node.</param>
/// <param name="sectionName">Name of the section.</param>
/// <param name="databaseName">Name of the database.</param>
/// <returns>System.String.</returns>
private string GenerateBCPCommand(ConnectionStringModel connectionModel, string baseContainer, string targetContainer, XElement rootNode, string sectionName, string databaseName)
{
if (sectionName == Constants.BCP)
{
var node = rootNode.Element(sectionName);
if (node == null)
{
WriteOutputLine("Skip to generate scripts for " + sectionName + " caused by no section node found.");
}
else
{
var path = ScriptHelper.GenerateBCPCommand(connectionModel, baseContainer, targetContainer, node, databaseName);
WriteOutputLine(sectionName + " path: " + (string.IsNullOrWhiteSpace(path) ? Constants.NA : ("file://" + path.Replace('\\', '/'))));
return path;
}
}
return null;
}
/// <summary>
/// Sets the control value by connection string model.
/// </summary>
/// <param name="connectionStringModel">The connection string model.</param>
private void SetControlValueByConnectionStringModel(ConnectionStringModel connectionStringModel)
{
this.txtServerIP.Text = connectionStringModel.Server;
this.txtDatabase.Text = connectionStringModel.Database;
this.txtUID.Text = connectionStringModel.UserName;
this.txtPwd.Text = connectionStringModel.Password;
this.rdBtnWindow.Checked = connectionStringModel.IsWindowsAuthentication;
}
/// <summary>
/// Loads the connection string.
/// </summary>
/// <param name="node">The node.</param>
/// <returns></returns>
public static ConnectionStringModel LoadConnectionString(XElement node)
{
ConnectionStringModel result = null;
if (node != null && node.Name.LocalName == "ConnectionString")
{
result = new ConnectionStringModel();
result.UserName = GetAttributeValue(node, "UserName");
result.Password = GetAttributeValue(node, "Password");
result.Server = GetAttributeValue(node, "Server");
//result.Port = ConvertStringToPort(GetAttributeValue(node, "Port"));
result.Database = GetAttributeValue(node, "Database");
}
return result;
}