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()));
}
