public void SetDataLimit() { #region Set SMO SQL Struct Data Limit _smo_server.SetDefaultInitFields(typeof(Smo.Database), new String[] { "Name", "RecoveryModel", "CompatibilityLevel", "Collation", "Owner", "CreateDate", "ExtendedProperties" }); _smo_server.SetDefaultInitFields(typeof(Smo.Schema), new String[] { "Name", "IsSystemObject", "Owner", "ExtendedProperties" }); _smo_server.SetDefaultInitFields(typeof(Smo.Table), new String[] { "Name", "Schema", "IsSystemObject", "CreateDate", "Owner", "ExtendedProperties" }); _smo_server.SetDefaultInitFields(typeof(Smo.View), new String[] { "Name", "Schema", "IsSystemObject", "CreateDate", "Owner", "ExtendedProperties" }); _smo_server.SetDefaultInitFields(typeof(Smo.StoredProcedure), new String[] { "Name", "Schema", "IsSystemObject", "CreateDate", "Owner", "ExtendedProperties" }); _smo_server.SetDefaultInitFields(typeof(Smo.UserDefinedFunction), new String[] { "Name", "Schema", "FunctionType", "IsSystemObject", "CreateDate", "Owner", "ExtendedProperties" }); if (_smo_server.VersionMajor >= 10) { _smo_server.SetDefaultInitFields(typeof(Smo.UserDefinedTableType), new String[] { "Name", "Schema", "CreateDate", "Owner", "ExtendedProperties" }); } #endregion }
/// <summary> /// Generates the CREATE PROCEDURE statement to select 1 line from the PK /// </summary> /// <param name="t" value="A SMO table"></param> /// <param name="header" value="Your custom header" remarks="Can be empty if you don't need header"></param> /// <returns>Return the DDL script</returns> public static string ScriptProcedureSelect(this smo.Table t, string header = "", bool setNoCount = true) { smo.Server s = t.Parent.Parent; s.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.Column), true); string create = @"CREATE PROCEDURE [{0}].[{1}_Select] {2}AS "; StringBuilder sb = new StringBuilder(); StringBuilder sbParam = new StringBuilder(); StringBuilder sbWhere = new StringBuilder(); bool hasPrimary = false; IEnumerable <string> columns = t.Columns.Cast <smo.Column>() .Select(c => string.Format("[{0}]", c.Name)).AsEnumerable(); string column = string.Join("\r\n\t, ", columns); bool firstPrimary = true; foreach (smo.Column c in t.Columns) { if (c.InPrimaryKey) { hasPrimary = true; if (!firstPrimary) { sbParam.Append(", "); sbWhere.Append("\tAND "); } firstPrimary = false; sbParam.AppendLine(string.Format("\t@{0} {1}", c.Name, c.DataType.ScriptToSql())); sbWhere.AppendLine(string.Format("[{0}] = @{1}", c.Name, c.Name.Replace(" ", "_"))); } } if (hasPrimary) { if (!string.IsNullOrEmpty(header)) { sb.AppendLine(header); } sb.Append(string.Format(create, t.Schema, t.Name, sbParam.ToString())); if (setNoCount) { sb.AppendLine("SET NOCOUNT ON"); } sb.AppendLine(string.Format("SELECT {0}", column.ToString())); sb.AppendLine(string.Format("FROM [{0}].[{1}]", t.Schema, t.Name)); sb.Append(string.Format("WHERE {0}", sbWhere.ToString())); sb.AppendLine("GO"); } else { sb.AppendLine("-- ####### Scripting error #######"); sb.AppendLine(string.Format("-- {0}.{1} has no primary key !", t.Schema, t.Name)); sb.AppendLine("-- ###############################"); } return(sb.ToString()); }
/// <summary> /// Generates the CREATE PROCEDURE statement to insert 1 line /// </summary> /// <param name="t" value="A SMO table"></param> /// <param name="header" value="Your custom header" remarks="Can be empty if you don't need header"></param> /// <returns>Return the DDL script</returns> public static string ScriptProcedureInsert(this smo.Table t, string header = "", bool setNoCount = true) { smo.Server s = t.Parent.Parent; s.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.Column), true); string create = @"CREATE PROCEDURE [{0}].[{1}_Insert] {2}AS "; StringBuilder sb = new StringBuilder(); StringBuilder sbParam = new StringBuilder(); StringBuilder sbColumn = new StringBuilder(); StringBuilder sbValues = new StringBuilder(); bool hasOtherColumn = false; bool first = true; foreach (smo.Column c in t.Columns) { if (!c.Identity && !c.Computed) // We can't update identity and computed { hasOtherColumn = true; if (!first) { sbParam.Append(", "); sbColumn.Append("\t, "); sbValues.Append("\t, "); } first = false; sbParam.AppendLine(string.Format("\t@{0} {1}", c.Name.Replace(" ", "_"), c.DataType.ScriptToSql())); sbColumn.AppendLine(string.Format("[{0}]", c.Name)); sbValues.AppendLine(string.Format("@{0}", c.Name.Replace(" ", "_"))); } } if (hasOtherColumn) { if (!string.IsNullOrEmpty(header)) { sb.AppendLine(header); } sb.AppendLine(string.Format(create, t.Schema, t.Name, sbParam.ToString())); if (setNoCount) { sb.AppendLine("SET NOCOUNT ON"); } sb.AppendLine(string.Format("INSERT INTO [{0}].[{1}]", t.Schema, t.Name)); sb.AppendLine(string.Format("({0})", sbColumn.ToString())); sb.AppendLine(string.Format("values({0})", sbValues.ToString())); sb.AppendLine("GO"); } else { sb.AppendLine("-- ####### Scripting error #######"); sb.AppendLine(string.Format("-- {0}.{1} has no column to insert !", t.Schema, t.Name)); sb.AppendLine("-- ###############################"); } return(sb.ToString()); }
private static void SetDefaultInitFields(smo.Server srv) { var defaultProps = new string[] { "Name", "Owner", "DateLastModified", "CreateDate", "Schema" }; var defaultCheckProps = new string[] { "Name", "DateLastModified", "CreateDate" }; srv.SetDefaultInitFields(typeof(smo.Database), "Name", "Owner", "CreateDate"); srv.SetDefaultInitFields(typeof(smo.Check), defaultCheckProps); srv.SetDefaultInitFields(typeof(smo.UserDefinedFunction), defaultProps); srv.SetDefaultInitFields(typeof(smo.StoredProcedure), defaultProps); srv.SetDefaultInitFields(typeof(smo.Trigger), defaultCheckProps); srv.SetDefaultInitFields(typeof(smo.View), defaultProps); srv.SetDefaultInitFields(typeof(smo.Table), defaultProps); }
/// <summary> /// Generates the CREATE PROCEDURE statement to update 1 line from the PK /// </summary> /// <param name="t" value="A SMO table"></param> /// <param name="header" value="Your custom header" remarks="Can be empty if you don't need header"></param> /// <returns>Return the DDL script</returns> public static string ScriptProcedureUpdate(this smo.Table t, string header = "", bool setNoCount = true) { smo.Server s = t.Parent.Parent; s.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.Column), true); string create = @"CREATE PROCEDURE [{0}].[{1}_Update] {2}AS"; StringBuilder sb = new StringBuilder(); StringBuilder sbParam = new StringBuilder(); StringBuilder sbWhere = new StringBuilder(); StringBuilder sbSet = new StringBuilder(); bool hasPrimary = false; bool hasOtherColumn = false; bool firstPrimary = true; bool firstSet = true; bool firstParam = true; foreach (smo.Column c in t.Columns) { if (c.InPrimaryKey) { hasPrimary = true; if (!firstPrimary) { sbWhere.Append("\tAND "); } firstPrimary = false; sbWhere.AppendLine(string.Format("[{0}] = @{1}", c.Name, c.Name.Replace(" ", "_"))); } else { if (!c.Identity && !c.Computed) // We can't update identity and computed { hasOtherColumn = true; if (!firstSet) { sbSet.Append(", "); } firstSet = false; sbSet.AppendLine(string.Format("\t[{0}] = @{1}", c.Name, c.Name.Replace(" ", "_"))); } } if (!firstParam) { sbParam.Append("\t, "); } firstParam = false; sbParam.AppendLine(string.Format("@{0} {1}", c.Name.Replace(" ", "_"), c.DataType.ScriptToSql())); } if (hasPrimary && hasOtherColumn) { if (!string.IsNullOrEmpty(header)) { sb.AppendLine(header); } sb.AppendLine(string.Format(create, t.Schema, t.Name, sbParam.ToString())); if (setNoCount) { sb.AppendLine("SET NOCOUNT ON"); } sb.Append(string.Format("UPDATE [{0}].[{1}]", t.Schema, t.Name)); sb.Append(string.Format("SET {0}", sbSet.ToString())); sb.Append(string.Format("WHERE {0}", sbWhere.ToString())); sb.AppendLine("GO"); } else { sb.AppendLine("-- ####### Scripting error #######"); sb.AppendLine(string.Format("-- {0}.{1} has no primary key or no column to update !", t.Schema, t.Name)); sb.AppendLine("-- ###############################"); } return(sb.ToString()); }
/// <summary> /// Compare components from two databases and create script for create new oject, /// drop old object and alter changed indexes and columns /// </summary> /// <param name="productionDatabase"></param> /// <param name="sourceDatabase"></param> /// <param name="dropScriptText"></param> /// <param name="createScriptText"></param> /// <param name="alterScriptText"></param> public void GenerateUpgradeScript( string productionDatabase, string sourceDatabase, out string dropScriptText, out string createScriptText, out string alterScriptText) { DateTime startTime = DateTime.Now; WriteTime("Start work for " + productionDatabase + " database."); string sqlServerName = string.IsNullOrEmpty(Configuration.Default.SqlServerName) == false ? Configuration.Default.SqlServerName : Environment.MachineName; m_SQLServer = new Server( new ServerConnection( sqlServerName, Configuration.Default.SqlUserName, Configuration.Default.SqlPassword)); m_SQLServer.SetDefaultInitFields(true); m_ProductionDatabase = m_SQLServer.Databases[productionDatabase]; m_SourceDatabase = m_SQLServer.Databases[sourceDatabase]; /*******************************************************************************/ var items2Drop = new List <IDroppable>(); var items2Create = new List <ICreatable>(); var items2Alter = new List <IAlterable>(); WriteTime("Start collect script items"); // // Get droppable and creatable items info // CollectScriptItems(items2Drop, items2Create, items2Alter); WriteTime("Stop collect script items"); // // Create "drop script" and "create script" // var generatedDropScript = new List <StringCollection>(); var generatedCreateScript = new List <StringCollection>(); var generatedAlterScript = new List <StringCollection>(); WriteTime("Create drop scripts..."); Trace.TraceInformation("Create drop/alter/create scripts...\r\n"); m_SQLServer.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql; m_SQLServer.ConnectionContext.CapturedSql.Clear(); foreach (IDroppable droppableItem in items2Drop) { droppableItem.Drop(); } generatedDropScript.Add(m_SQLServer.ConnectionContext.CapturedSql.Text); WriteTime("Create alter scripts..."); m_SQLServer.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql; m_SQLServer.ConnectionContext.CapturedSql.Clear(); foreach (IAlterable alterableItem in items2Alter) { alterableItem.Alter(); } generatedAlterScript.Add(m_SQLServer.ConnectionContext.CapturedSql.Text); WriteTime("Create create scripts..."); m_SQLServer.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql; m_SQLServer.ConnectionContext.CapturedSql.Clear(); foreach (ICreatable creatableItem in items2Create) { creatableItem.Create(); } var preSql = new StringCollection { string.Format("ALTER DATABASE {0} set TRUSTWORTHY ON\r\n", productionDatabase) }; generatedCreateScript.Add(preSql); generatedCreateScript.Add(m_SQLServer.ConnectionContext.CapturedSql.Text); m_SQLServer.ConnectionContext.CapturedSql.Clear(); // // Generate BvSpDataSync_CreateSurveySBObjects call for all // surveys, except default instance. // var postSql = new StringCollection(); if (!string.IsNullOrEmpty(m_DatabaseEngine.GetInstanceName())) { foreach (int surveyId in m_DatabaseEngine.SurveyIds) { postSql.Add( string.Format( "EXEC BvSpDataSync_CreateSurveySBObjects {0}, '{1}', null", surveyId, m_DatabaseEngine.GetSurveyNameBySurveySid(surveyId))); } } generatedCreateScript.Add(postSql); /*******************************************************************************/ StringBuilder dropScript = PostProcessScriptText( productionDatabase, generatedDropScript); dropScriptText = dropScript.ToString(); StringBuilder createScript = PostProcessScriptText( productionDatabase, generatedCreateScript); createScriptText = createScript.ToString(); StringBuilder alterScript = PostProcessScriptText( productionDatabase, generatedAlterScript); alterScriptText = alterScript.ToString(); DateTime stopTime = DateTime.Now; stopTime = stopTime.AddHours(-startTime.Hour); stopTime = stopTime.AddMinutes(-startTime.Minute); stopTime = stopTime.AddSeconds(-startTime.Second); WriteTime(string.Format("Stop work: {0}\r\n\r\n", stopTime.ToLongTimeString())); }