I have been looking into the using SMO into trying to create, alter and drop tables/views/stored procedures using similar structure to the code below:
if (taskType == TaskType.Alter.ToString())
{
if (item.SourceResults != null && item.SourceResults != "" && item.SelectionCheckboxProperty == true && item.DatabasePropertyType == DatabasePropertyType.Table)
{
sourceConnectionstring = $"server={sourceservername};Database={sourcedatabase};User Id={sourceusername};Password={sourcepassword};Encrypt=False;Persist Security Info=True;Integrated Security=False;";
using (SqlConnection sourceConnection = new SqlConnection(sourceConnectionstring))
{
await sourceConnection.OpenAsync();
Server sourceserver = new Server(sourceservername);
Database sourceDataBase = sourceserver.Databases[sourcedatabase];
Table sourceTable = sourceDatabase.Tables[item.SourceResults];
Scripter scripter = new Scripter(sourceserver);
scripter.Options.ScriptDrops = true;
scripter.Options.ScriptForCreateOrAlter = true;
scripter.Options.ScriptForAlter = false;
scripter.Options.ScriptSchema = true;
scripter.Options.ScriptData = false;
Urn tableUrn = sourceTable.Urn;
try
{
ScriptDatalabel.Visibility = Visibility.Visible;
StringCollection createScript = scripter.Script(new Urn[] { tableUrn });
StringBuilder sb = new StringBuilder();
However, I have been struggling to get 'ALTER' to any table/stored procedure/view as I keep on getting 'CREATE' all the time when I used debug in visual in createScript.
I have created code above and results were always create instead of alter when the source and target were identical. (e.g. say if table were identical but target is missing like one or more columns, it will always do create instead of alter).
Edit1:
I have tried changing the options into
scripter.Options.ScriptDrops = false;
//scripter.Options.ScriptForCreateOrAlter = true;
scripter.Options.ScriptForAlter = true;
scripter.Options.ScriptSchema = true;
scripter.Options.ScriptData = false;
However, my createScript is always showing the following:
that is the only item in the array list. For altering, what would be the best approach for getting alter script? What options should I be using?
Edit2:
I have a few extra questions:
- Does the SMO package have this alter feature? If it does, then is there a limitation to it?
- Is there a reason why I would be getting "CREATE" instead "ALTER"?
- Would it be best for me to look into altering Tables/Views/Stored Procedures manually? Like creating If statements and creating Strings based on whether there is a difference (like missing column/s; dropping and/or add/remove foreign keys; etc.).
- Is there a different package that does the same thing as SMO?
- Is there any tutorial/blog/website that would explain how to use the SMO package in C#?
- Is it best to create like WPF application that implements a similar database comparison like in RedGate, etc.. Or Use Visual Studio SQL database project for Schema comparison?
Edit 3:
I have tried to add the option EnforceScriptingOptions but it didn't work. Is there another approach to resolve this issue?
