/// <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; }