public void CreateOrAlterView(string name, string body, bool isSchemaBound = true)
{
DropTable(name);
var view = SmoDatabase.Views[name];
var exists = view != null;
if (!exists)
{
view = new View(SmoDatabase, name);
}
var createOrAlter = exists
? "ALTER"
: "CREATE";
view.TextHeader = string.Format("{0} VIEW {1} AS", createOrAlter, name);
view.TextBody = body;
if (!exists)
{
view.Create();
}
else
{
view.Alter();
}
}
private Model.View GetNewView(Microsoft.SqlServer.Management.Smo.View smoView)
{
Model.View view = new Model.View(smoView.Name, Script.GetSingluar(smoView.Name), false);
// Columns
int ordinalPosition = 0;
List <Microsoft.SqlServer.Management.Smo.Column> smoColumns = new List <Microsoft.SqlServer.Management.Smo.Column>();
foreach (Microsoft.SqlServer.Management.Smo.Column smoColumn in smoView.Columns)
{
smoColumns.Add(smoColumn);
}
smoColumns.Sort(new SortComparer <Microsoft.SqlServer.Management.Smo.Column>("ID", System.ComponentModel.ListSortDirection.Ascending));
foreach (Microsoft.SqlServer.Management.Smo.Column smoColumn in smoColumns)
{
if (UnsupportedDataTypes.ToLower().IndexOf("'" + smoColumn.DataType.Name.ToLower() + "'") >= 0)
{
continue;
}
Model.Column column = new Model.Column(smoColumn.Name, Script.GetSingluar(smoColumn.Name), false, smoColumn.Name, view, ordinalPosition, smoColumn.Nullable, smoColumn.DataType.Name, smoColumn.DataType.MaximumLength,
false, smoColumn.Identity, smoColumn.Default, true);
view.AddColumn(column);
ordinalPosition++;
}
return(view);
}
public static void UpdateDataSet(this MSMO.View view, Data.ApplicationDataSet.DBViewsRow dataRow)
{
try
{
dataRow.CreateDate = view.CreateDate;
try
{
dataRow.DateLastModified = view.DateLastModified;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 10);
#endif
}
dataRow.Owner = view.Owner;
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 11);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
}
}
/// <summary>
/// Initializes a new instance of the View class.
/// </summary>
public View(SMO.View view)
{
long startTime = Common.WriteToDebugWindow(string.Format("Enter SMOH.{0}({1})", "View", view));
// Save the real view in case we need to get something from it.
_view = view;
CreateDate = view.CreateDate.ToString("yyyy-MM-dd hh:mm:ss");
try
{
DateLastModified = view.DateLastModified.ToString("yyyy-MM-dd hh:mm:ss");
}
catch (Exception)
{
DateLastModified = "Not Available";
}
ID = view.ID.ToString();
IsSystemObject = view.IsSystemObject;
Name = view.Name;
Owner = view.Owner;
try
{
ExtendedProperties = view.ExtendedProperties;
}
catch (Exception)
{
}
Common.WriteToDebugWindow(string.Format(" Exit SMOH.{0}({1}) Exit", "View", view), startTime);
}
///--------------------------------------------------------------------------------
/// <summary>This loads information from a SQL view.</summary>
///
/// <param name="sqlView">The input sql view.</param>
///--------------------------------------------------------------------------------
public void LoadView(Microsoft.SqlServer.Management.Smo.View sqlView)
{
try
{
// load the basic view information
SqlViewName = sqlView.Name;
CreateDate = sqlView.CreateDate;
DateLastModified = sqlView.DateLastModified;
Urn = sqlView.Urn;
State = sqlView.State.ToString();
// load information for each property
foreach (Column loopColumn in sqlView.Columns)
{
if (DebugHelper.DebugAction == DebugAction.Stop)
{
return;
}
SqlViewProperty property = new SqlViewProperty();
property.SqlViewPropertyID = Guid.NewGuid();
property.SqlView = this;
property.LoadViewProperty(loopColumn, sqlView.TextBody);
SqlViewPropertyList.Add(property);
}
}
catch (ApplicationAbortException)
{
throw;
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Metodo que permite cargar las tablas en el arbol.
/// </summary>
public void Populate(CnnString pCnn)
{
//if (_Tables == null)
LoadTables(pCnn);
if (_Views == null)
{
this.tvwChilds.Nodes.Clear();
return;
}
if (_Views.Count == 0)
{
this.tvwChilds.Nodes.Clear();
return;
}
TreeViewHelper.LoadTreeView(this.tvwChilds, _Views);
_SelectedView = _Views[0];
lblTreeViewSelected.Text = String.Empty;
}
public static void UpdateDataSet(this MSMO.View view, Data.ApplicationDataSet.DBViewsRow viewRow)
{
try
{
viewRow.CreateDate = view.CreateDate;
//viewRow.DataSpaceUsed = view.DataSpaceUsed;
try
{
viewRow.DateLastModified = view.DateLastModified;
}
catch (Exception ex)
{
PLLog.Warning(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1);
}
viewRow.Owner = view.Owner;
}
catch (Exception ex)
{
// TODO(crhodes): Need to wrap anything above that throws an exception
// that we want to ignore, e.g. property not available because of
// SQL Edition.
PLLog.Error(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
throw ex;
}
}
private string getViewAlterSql(SMODBView view)
{
string sql = "";
sql += string.Format("--script for view {0}\r\n", view.Name);
sql += "ALTER VIEW [dbo].[" + view.Name + "] \r\nAS\r\n" + view.TextBody + "\r\n" + "--\r\n";
return(sql.ToString());
}
private void btnView_Click(object sender, EventArgs e)
{
string spFilePath = ConfigurationManager.AppSettings["spFile"];
string spNameFilePath = ConfigurationManager.AppSettings["spNameFile"];
string filePath = @spNameFilePath;
List <string> spName = File.ReadAllLines(filePath).ToList();
Server server = new Server(serverKurulum());
string dbName = ConfigurationManager.AppSettings["db"];
Database db = server.Databases[dbName];
List <SqlSmoObject> list = new List <SqlSmoObject>();
DataTable dataTable = db.EnumObjects(DatabaseObjectTypes.View);
foreach (DataRow row in dataTable.Rows)
{
int count = 0;
string sSchema = (string)row["Schema"];
if (sSchema == "sys" || sSchema == "INFORMATION_SCHEMA")
{
continue;
}
View sp = (View)server.GetSmoObject(
new Urn((string)row["Urn"]));
if (!sp.IsSystemObject)
{
string name = sp.Schema + '.' + sp.Name;
foreach (var item in spName)
{
if (item.Equals(name))
{
list.Add(sp);
count++;
}
}
}
for (int i = 0; i < count; i++)
{
try
{
TextWriter tw = new StreamWriter(spFilePath + sp.Schema + '.' + sp.Name + ".txt", true);
tw.Write(sp.TextHeader);
tw.Write("\n");
tw.Write(sp.TextBody);
tw.Close();
}
catch (Exception ex)
{
MessageBox.Show("hata var: " + ex);
}
}
}
MessageBox.Show("spler kayıt edildi.", "Bilgilendirme Penceresi");
}
/// <summary>
/// 是否相同视图
/// </summary>
/// <param name="sv"></param>
/// <param name="tv"></param>
/// <returns></returns>
private bool IsEqualTowView(SMODBView sv, SMODBView tv)
{
bool isEqual = false;
if (sv.TextHeader == tv.TextHeader && sv.TextBody == tv.TextBody)
{
isEqual = true;
}
return(isEqual);
}
public static void LoadFromSMO(MSMO.View view, Guid viewID)
{
#if TRACE
long startTicks = VNC.AppLog.Trace4("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 0);
#endif
foreach (MSMO.Column column in view.Columns)
{
GetInfoFromSMO(column, viewID);
}
#if TRACE
VNC.AppLog.Trace4("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1, startTicks);
#endif
}
private void tvwChilds_BeforeCheck(object sender, TreeViewCancelEventArgs e)
{
//Nivel de tablas
if (e.Node.Level == 1)
{
if (e.Node.Parent.Text != _SelectedView.Name)
{
_SelectedView = (Microsoft.SqlServer.Management.Smo.View)e.Node.Parent.Tag;
lblTreeViewSelected.Text = _SelectedView.Name;
OnSelectObjectEvent();
}
}
}
private static void Update(MSMO.View view, SQLInformation.Data.ApplicationDataSet.DBViewsRow dataRow)
{
try
{
view.UpdateDataSet(dataRow);
UpdateDatabaseWithSnapShot(dataRow, "");
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 9);
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
}
public MySmo.View GetView(Smo.View smo_v, MySmo.Database parent = null)
{
#region implement
SetDataLimit();
var mysmo_v = new MySmo.View();
mysmo_v.ParentDatabase = parent;
mysmo_v.Name = smo_v.Name;
mysmo_v.Schema = smo_v.Schema;
mysmo_v.CreateTime = smo_v.CreateDate;
mysmo_v.Owner = smo_v.Owner;
mysmo_v.TriggersCount = smo_v.Triggers.Count;
mysmo_v.ExtendedProperties = GetExtendProperties(mysmo_v, smo_v.ExtendedProperties);
var s = "";
if (mysmo_v.ExtendedProperties.TryGetValue(K_MS_Description, out s))
{
mysmo_v.Description = s;
mysmo_v.ExtendedProperties.Remove(K_MS_Description);
}
mysmo_v.Columns = new List <MySmo.Column>();
foreach (Smo.Column smo_c in smo_v.Columns)
{
var mysmo_c = new MySmo.Column
{
ParentDatabase = parent,
ParentTableBase = mysmo_v,
Name = smo_c.Name,
DataType = GetDataType(smo_c),
Computed = smo_c.Computed,
ComputedText = smo_c.ComputedText,
Default = smo_c.Default,
Identity = smo_c.Identity,
IdentityIncrement = smo_c.IdentityIncrement,
IdentitySeed = smo_c.IdentitySeed,
InPrimaryKey = smo_c.InPrimaryKey,
IsForeignKey = smo_c.IsForeignKey,
Nullable = smo_c.Nullable,
RowGuidCol = smo_c.RowGuidCol
};
mysmo_v.Columns.Add(mysmo_c);
}
FormatExtendProperties(mysmo_v);
return(mysmo_v);
#endregion
}
internal static string Generate(Microsoft.SqlServer.Management.Smo.View pView, string project)
{
StringBuilder wClassContainer = new StringBuilder();
string wPrivateMembers_BODY = String.Empty;
string wPublicProperty_BODY = String.Empty;
wClassContainer.Append(FwkGeneratorHelper._Entity_Envelope_tt);
wClassContainer.Replace(CodeGeneratorCommon.CommonConstants.CONST_ENTITY_NAME, pView.Name);
GenerateDataBaseProperttyBody(pView, out wPublicProperty_BODY, out wPrivateMembers_BODY);
//Inserta miembros privados
//wClassContainer.Replace(CodeGeneratorCommon.CommonConstants.CONST_ENTITY_PRIVATE_MEMBERS_BODY, wPrivateMembers_BODY);
//Inserta los atributos publicos
wClassContainer.Replace(CodeGeneratorCommon.CommonConstants.CONST_ENTITY_PUBLIC_PROPERTY_BODY, wPublicProperty_BODY);
wClassContainer.Replace(CodeGeneratorCommon.CommonConstants.CONST_FwkProject_NAME, project);
return(wClassContainer.ToString());
}
public static void LoadFromSMO(MSMO.View view, Guid viewID)
{
#if TRACE
long startTicks = VNC.AppLog.Trace4("Start", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 0);
#endif
foreach (MSMO.Trigger trigger in view.Triggers)
{
if (trigger.IsSystemObject)
{
continue; // Skip System Triggers
}
GetInfoFromSMO(trigger, viewID);
}
#if TRACE
VNC.AppLog.Trace4("End", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1, startTicks);
#endif
}
/// <summary>
/// Compare 2 ddl Views
/// </summary>
/// <param name="sp1">your first smo view</param>
/// <param name="sp2">your second smo view</param>
/// <param name="checkComments">True if you want to compare comments</param>
/// <param name="checkBrackets">True if you want to compare scripts with brackets</param>
/// <param name="ignoreCaseSensitive">True if you want to ignore Case Sensitive. False if Case sensitive</param>
/// <returns></returns>
public static KMOCompareInfo CompareSchema(this smo.View v1, smo.View v2, bool checkComments = false, bool checkBrackets = false, bool ignoreCaseSensitive = true)
{
smo.ScriptingOptions so = new smo.ScriptingOptions();
so.DriAll = true;
string s1 = String.Join(Environment.NewLine, v1.Script(so).Cast <String>().Select(s => s.ToString()).AsEnumerable());
string s2 = String.Join(Environment.NewLine, v2.Script(so).Cast <String>().Select(s => s.ToString()).AsEnumerable());
string message = string.Empty;
if (KMOCompareHelper.CompareScript(s1, s2, ignoreCaseSensitive, checkComments, checkBrackets))
{
return(new KMOCompareInfo {
IsIdentical = true, Message = string.Empty, Script1 = s1, Script2 = s2
});
}
else
{
return(new KMOCompareInfo {
IsIdentical = false, Message = "Script difference", Script1 = s1, Script2 = s2
});
}
}
private void Script_UserDefView(TreeNode ViewNode)
{
System.Collections.Specialized.StringCollection script = new StringCollection();
string Database = WalkBack(ViewNode, 2);
string dbTable = ViewNode.Parent.Text;
Microsoft.SqlServer.Management.Smo.View _dbView = new Microsoft.SqlServer.Management.Smo.View();
if (string.IsNullOrWhiteSpace(Database) || string.IsNullOrWhiteSpace(dbTable))
{
return;
}
tbScripts.Text = string.Empty;
ScriptingOptions oScriptingOptions = new ScriptingOptions();
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.IncludeIfNotExists = false;
_dbView = srvr.Databases[Database].Views[ViewNode.Text];
//foreach (Microsoft.SqlServer.Management.Smo.View View in srvr.Databases[Database].Views)
//{
script = _dbView.Script(oScriptingOptions);
foreach (string str in script)
{
if (str.Contains("USE ["))
{
tbScripts.Text += Environment.NewLine + str + Environment.NewLine;
}
else
{
tbScripts.Text += str + Environment.NewLine;
}
}
FormatSQL(tbScripts.Text);
//}
}
/// <summary>
/// Retorna codigo de miembros privados + los atributos publicos.-
/// </summary>
/// <param name="pView"> Microsoft.SqlServer.Management.Smo.View</param>
/// <param name="pPublicProperty_Body">atributos publicos</param>
/// <param name="pPrivateMembers_Body">miembros privados</param>
static void GenerateDataBaseProperttyBody(Microsoft.SqlServer.Management.Smo.View pView, out String pPublicProperty_Body, out String pPrivateMembers_Body)
{
StringBuilder wPrivateMembers_BODY = new StringBuilder();
StringBuilder wPublicProperty_BODY = new StringBuilder();
foreach (Column wColumn in pView.Columns)
{
//Si es una columna no permitida
if (!FwkGeneratorHelper.NotSupportTypes_ToIncludeInBackEnd.Contains(wColumn.DataType.Name.ToLower()))
{
//if (wColumn.Selected)
//{
//Privados
wPrivateMembers_BODY.Append(FwkGeneratorHelper.GetCsharpMember(wColumn));
//Publicos (con Get y Set)
wPublicProperty_BODY.Append(FwkGeneratorHelper.GetCsharpProperty(wColumn));
//}
}
}
pPrivateMembers_Body = wPrivateMembers_BODY.ToString();
pPublicProperty_Body = wPublicProperty_BODY.ToString();
}
private void tvwChilds_AfterCheck(object sender, TreeViewEventArgs e)
{
if (e.Node == null)
{
return;
}
if (e.Node.Level == 0)
{
Microsoft.SqlServer.Management.Smo.View wTable = (Microsoft.SqlServer.Management.Smo.View)e.Node.Tag;
///TODO: Ver Checked
//wTable.Checked = e.Node.Checked;
//if (wTable.IsColumnsLoaded == false)
if (e.Node.Nodes.Count == 0)
{
TreeViewHelper.LoadColumnsNodes(e.Node, wTable);
OnSelectObjectEvent();
}
this.tvwChilds.AfterCheck -= new System.Windows.Forms.TreeViewEventHandler(this.tvwChilds_AfterCheck);
this.tvwChilds.BeforeCheck -= new TreeViewCancelEventHandler(this.tvwChilds_BeforeCheck);
TreeViewHelper.UncheckChildNodes(e.Node);
this.tvwChilds.AfterCheck += new System.Windows.Forms.TreeViewEventHandler(this.tvwChilds_AfterCheck);
this.tvwChilds.BeforeCheck += new TreeViewCancelEventHandler(this.tvwChilds_BeforeCheck);
OnAfterCheckHandler();
}
if (e.Node.Level == 1)
{
Column wColumn = (Column)e.Node.Tag;
//wColumn.Selected = e.Node.Checked;
if (!TreeViewHelper.HasSelectedNodes(e.Node.Parent))
{
e.Node.Parent.Checked = false;
}
}
}
private void tvwChilds_AfterSelect(object sender, TreeViewEventArgs e)
{
if (this.tvwChilds.SelectedNode == null)
{
return;
}
if (this.tvwChilds.SelectedNode.Level == 0)
{
_SelectedView = (Microsoft.SqlServer.Management.Smo.View) this.tvwChilds.SelectedNode.Tag;
lblTreeViewSelected.Text = _SelectedView.Name;
if (e.Node.Nodes.Count == 0)
{
TreeViewHelper.LoadColumnsNodes(this.tvwChilds.SelectedNode, _SelectedView);
OnSelectObjectEvent();
}
}
if (this.tvwChilds.SelectedNode.Level == 1)
{
lblTreeViewSelected.Text = _SelectedView.Name; //+ "." + this.tvwChilds.SelectedNode.Text.Substring(0, this.tvwChilds.SelectedNode.Text.IndexOf(" "));
}
}
private void _TreeView_AfterSelect(object sender, TreeViewEventArgs e)
{
Cursor csr = null;
try
{
csr = this.Cursor;
this.Cursor = Cursors.WaitCursor;
// 清空右边的 detail 类控件显示
_SplitContainer.Panel2.Controls.Clear();
TreeNode node = e.Node;
object tag = node.Tag;
Type tagType = tag.GetType();
if (tag == null)
{
node.Tag = node.Text; // 保持 tag 不空，方便判断
}
if (typeof(Database) == tagType && node.Nodes.Count == 0)
{
#region Make Empty Sub Tree Nodes
TreeNode tn;
tn = node.Nodes.Add("Tables");
tn.SelectedImageKey = tn.ImageKey = "SQL_Folder.png";
tn.Tag = "Tables";
tn = node.Nodes.Add("Views");
tn.SelectedImageKey = tn.ImageKey = "SQL_Folder.png";
tn.Tag = "Views";
tn = node.Nodes.Add("Stored Procedures");
tn.SelectedImageKey = tn.ImageKey = "SQL_Folder.png";
tn.Tag = "Stored Procedures";
tn = node.Nodes.Add("Functions");
tn.SelectedImageKey = tn.ImageKey = "SQL_Folder.png";
tn.Tag = "Functions";
tn = node.Nodes.Add("UserDefinedDataTypes");
tn.SelectedImageKey = tn.ImageKey = "SQL_Folder.png";
tn.Tag = "UserDefinedDataTypes";
Database db = (Database)tag; // 根据 SQL 的库的版本来决定是否生成某些节点
if (db.CompatibilityLevel >= CompatibilityLevel.Version100)
{
tn = node.Nodes.Add("UserDefinedTableTypes");
tn.SelectedImageKey = tn.ImageKey = "SQL_Folder.png";
tn.Tag = "UserDefinedTableTypes";
}
if (db.CompatibilityLevel >= CompatibilityLevel.Version90)
{
tn = node.Nodes.Add("User Schema");
tn.SelectedImageKey = tn.ImageKey = "SQL_Folder.png";
tn.Tag = "User Schema";
}
tn = node.Nodes.Add("Extended Properties");
tn.SelectedImageKey = tn.ImageKey = "SQL_Folder.png";
tn.Tag = "Extended Properties";
#endregion
}
else if (typeof(Database) == tagType && node.Nodes.Count > 0)
{
Database db = (Database)tag; // 不再重复的生成子节点
}
else if (typeof(Table) == tagType)
{
Table t = (Table)tag;
CTable ct = new CTable(t);
ct.Dock = DockStyle.Fill;
_SplitContainer.Panel2.Controls.Add(ct);
}
else if (typeof(UserDefinedTableType) == tagType)
{
UserDefinedTableType t = (UserDefinedTableType)tag;
CUserTableType ct = new CUserTableType(t);
ct.Dock = DockStyle.Fill;
_SplitContainer.Panel2.Controls.Add(ct);
// FMain_UI_Binder.MakeByTable(_ToolStrip, t);
}
else if (typeof(Microsoft.SqlServer.Management.Smo.View) == tagType)
{
Microsoft.SqlServer.Management.Smo.View v = (Microsoft.SqlServer.Management.Smo.View)tag;
CView cv = new CView(v);
cv.Dock = DockStyle.Fill;
_SplitContainer.Panel2.Controls.Add(cv);
}
else if (typeof(Column) == tagType)
{
// todo
}
else if (typeof(StoredProcedure) == tagType)
{
StoredProcedure sp = (StoredProcedure)tag;
CStoredProcedure csp = new CStoredProcedure(sp);
csp.Dock = DockStyle.Fill;
_SplitContainer.Panel2.Controls.Add(csp);
}
else if (typeof(UserDefinedFunction) == tagType)
{
UserDefinedFunction fun = (UserDefinedFunction)tag;
CFunction cfun = new CFunction(fun);
cfun.Dock = DockStyle.Fill;
_SplitContainer.Panel2.Controls.Add(cfun);
}
else if (typeof(ExtendedProperty) == tagType)
{
ExtendedProperty ep = (ExtendedProperty)tag;
CExtendedProperty cep = new CExtendedProperty(ep);
cep.Dock = DockStyle.Fill;
_SplitContainer.Panel2.Controls.Add(cep);
}
else if (typeof(string) == tagType)
{
string name = (string)tag;
if (name == "Tables")
{
Database db = (Database)node.Parent.Tag;
if (node.Nodes.Count == 0)
{
List <Table> uts = Utils.GetUserTables(db);
foreach (Table tbl in uts)
{
TreeNode tn = new TreeNode(tbl.Schema + "." + tbl.Name);
tn.SelectedImageKey = tn.ImageKey = "SQL_Table.png";
tn.Tag = tbl;
node.Nodes.Add(tn);
foreach (Column c in tbl.Columns)
{
TreeNode n = new TreeNode(c.Name);
n.SelectedImageKey = n.ImageKey = "SQL_Column.png";
n.Tag = c;
tn.Nodes.Add(n);
}
foreach (ExtendedProperty ep in tbl.ExtendedProperties)
{
TreeNode n = new TreeNode(ep.Name);
n.SelectedImageKey = n.ImageKey = "sql_constrain.png";
n.Tag = ep;
tn.Nodes.Add(n);
}
}
}
// ...
}
else if (name == "Views")
{
Database db = (Database)node.Parent.Tag;
if (node.Nodes.Count == 0)
{
List <Microsoft.SqlServer.Management.Smo.View> uvs = Utils.GetUserViews(db);
foreach (Microsoft.SqlServer.Management.Smo.View v in uvs)
{
TreeNode tn = new TreeNode(v.Schema + "." + v.Name);
tn.SelectedImageKey = tn.ImageKey = "SQL_Table.png";
tn.Tag = v;
node.Nodes.Add(tn);
foreach (Column c in v.Columns)
{
TreeNode n = new TreeNode(c.Name);
n.SelectedImageKey = n.ImageKey = "SQL_Column.png";
n.Tag = c;
tn.Nodes.Add(n);
}
foreach (ExtendedProperty ep in v.ExtendedProperties)
{
TreeNode n = new TreeNode(ep.Name);
n.SelectedImageKey = n.ImageKey = "sql_constrain.png";
n.Tag = ep;
tn.Nodes.Add(n);
}
}
}
}
else if (name == "Stored Procedures")
{
Database db = (Database)node.Parent.Tag;
if (node.Nodes.Count == 0)
{
List <StoredProcedure> sps = Utils.GetUserStoredProcedures(db);
foreach (StoredProcedure sp in sps)
{
TreeNode tn = new TreeNode(sp.Schema + "." + sp.Name);
tn.SelectedImageKey = tn.ImageKey = "SQL_StoredProcedure.png";
tn.Tag = sp;
node.Nodes.Add(tn);
foreach (ExtendedProperty ep in sp.ExtendedProperties)
{
TreeNode n = new TreeNode(ep.Name);
n.SelectedImageKey = n.ImageKey = "sql_constrain.png";
n.Tag = ep;
tn.Nodes.Add(n);
}
}
}
}
else if (name == "Functions")
{
Database db = (Database)node.Parent.Tag;
if (node.Nodes.Count == 0)
{
List <UserDefinedFunction> ufs = Utils.GetUserFunctions(db);
foreach (UserDefinedFunction fun in ufs)
{
TreeNode tn = new TreeNode(fun.Schema + "." + fun.Name);
if (fun.FunctionType == UserDefinedFunctionType.Table || fun.FunctionType == UserDefinedFunctionType.Inline)
{
tn.SelectedImageKey = tn.ImageKey = "SQL_Function_Table.png";
}
else
{
tn.SelectedImageKey = tn.ImageKey = "SQL_Function_Scale.png";
}
tn.Tag = fun;
node.Nodes.Add(tn);
foreach (ExtendedProperty ep in fun.ExtendedProperties)
{
TreeNode n = new TreeNode(ep.Name);
n.SelectedImageKey = n.ImageKey = "sql_constrain.png";
n.Tag = ep;
tn.Nodes.Add(n);
}
}
}
}
else if (name == "UserDefinedDataTypes")
{
Database db = (Database)node.Parent.Tag;
if (node.Nodes.Count == 0)
{
List <UserDefinedDataType> uddts = Utils.GetUserDefinedDataTypes(db);
foreach (UserDefinedDataType o in uddts)
{
TreeNode tn = new TreeNode(o.Schema + "." + o.Name + "(" + o.SystemType + " " + o.MaxLength.ToString() + ")");
tn.SelectedImageKey = tn.ImageKey = "SQL_Function_Scale.png";
tn.Tag = o;
node.Nodes.Add(tn);
}
}
}
else if (name == "UserDefinedTableTypes")
{
Database db = (Database)node.Parent.Tag;
if (node.Nodes.Count == 0)
{
List <UserDefinedTableType> utts = Utils.GetUserDefinedTableTypes(db);
foreach (UserDefinedTableType tbl in utts)
{
TreeNode tn = new TreeNode(tbl.Schema + "." + tbl.Name);
tn.SelectedImageKey = tn.ImageKey = "SQL_Table.png";
tn.Tag = tbl;
node.Nodes.Add(tn);
foreach (Column c in tbl.Columns)
{
TreeNode n = new TreeNode(c.Name);
n.SelectedImageKey = n.ImageKey = "SQL_Column.png";
n.Tag = c;
tn.Nodes.Add(n);
}
foreach (ExtendedProperty ep in tbl.ExtendedProperties)
{
TreeNode n = new TreeNode(ep.Name);
n.SelectedImageKey = n.ImageKey = "sql_constrain.png";
n.Tag = ep;
tn.Nodes.Add(n);
}
}
}
}
else if (name == "User Schema")
{
Database db = (Database)node.Parent.Tag;
if (node.Nodes.Count == 0)
{
List <Schema> uss = Utils.GetUserSchemas(db);
foreach (Schema a in uss)
{
TreeNode tn = new TreeNode(a.Name);
tn.SelectedImageKey = tn.ImageKey = "SQL_Schema.png";
tn.Tag = a;
node.Nodes.Add(tn);
}
}
}
else if (name == "Extended Properties")
{
Database db = (Database)node.Parent.Tag;
if (node.Nodes.Count == 0)
{
List <ExtendedProperty> eps = Utils.GetExtendedProperties(db);
foreach (ExtendedProperty a in eps)
{
TreeNode tn = new TreeNode(a.Name);
tn.SelectedImageKey = tn.ImageKey = "SQL_Schema.png";
tn.Tag = a;
node.Nodes.Add(tn);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
this.Cursor = csr; // Restore the original cursor
}
}
/// <summary>
/// Metodo que permite cargar las tablas en el arbol.
/// </summary>
public void Populate(CnnString pCnn)
{
//if (_Tables == null)
LoadTables(pCnn);
if (_Views == null)
{
this.tvwChilds.Nodes.Clear();
return;
}
if (_Views.Count == 0)
{
this.tvwChilds.Nodes.Clear();
return;
}
TreeViewHelper.LoadTreeView(this.tvwChilds, _Views);
_SelectedView = _Views[0];
lblTreeViewSelected.Text = String.Empty;
}
private void tvwChilds_AfterSelect(object sender, TreeViewEventArgs e)
{
if (this.tvwChilds.SelectedNode == null) return;
if (this.tvwChilds.SelectedNode.Level == 0)
{
_SelectedView = (Microsoft.SqlServer.Management.Smo.View )this.tvwChilds.SelectedNode.Tag;
lblTreeViewSelected.Text = _SelectedView.Name;
if (e.Node.Nodes.Count == 0)
{
TreeViewHelper.LoadColumnsNodes(this.tvwChilds.SelectedNode, _SelectedView);
OnSelectObjectEvent();
}
}
if (this.tvwChilds.SelectedNode.Level == 1)
{
lblTreeViewSelected.Text = _SelectedView.Name; //+ "." + this.tvwChilds.SelectedNode.Text.Substring(0, this.tvwChilds.SelectedNode.Text.IndexOf(" "));
}
}
private void tvwChilds_BeforeCheck(object sender, TreeViewCancelEventArgs e)
{
//Nivel de tablas
if (e.Node.Level == 1)
{
if (e.Node.Parent.Text != _SelectedView.Name)
{
_SelectedView = (Microsoft.SqlServer.Management.Smo.View)e.Node.Parent.Tag;
lblTreeViewSelected.Text = _SelectedView.Name;
OnSelectObjectEvent();
}
}
}
public CView(Microsoft.SqlServer.Management.Smo.View v)
{
InitializeComponent();
_v = v;
}
public void Script(string srvname, string dbName, string destination)
{
tbxOutput.Text = "Scripting the " + dbName + " database." + "\r\n";
if (destination == "")
{
destination = Environment.CurrentDirectory + "\\";
}
else
{
if (destination[destination.Length - 1] != Convert.ToChar("\\"))
{
destination += "\\";
}
}
tbxOutput.Text += "Output directory set to " + destination + "\r\n";
/* *************************** */
/* CHECK FOR VALID DESTINATION */
/* *************************** */
tbxOutput.Text += "Checking for valid destination directory...\r\n";
if (!Directory.Exists(destination))
{
throw new DirectoryNotFoundException("The specified destination directory does not exist.");
}
else
{
tbxOutput.Text += "Destination directory is valid.\r\n";
/* *********************** */
/* CREATE FOLDER STRUCTURE */
/* *********************** */
tbxOutput.Text += "Establishing folder structure...\r\n";
newFolders.Clear();
try
{
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd")))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"));
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"));
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + @"\Programmability\Functions\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\" + "\r\n";
}
}
catch
{
throw new UnauthorizedAccessException("The program failed to create the backup folders in the specified directory. Please check security settings.");
}
tbxOutput.Text += "Folder structure established \r\n";
}
/* *************** */
/* Generate Script */
/* *************** */
try //Wrap in try statement to catch incorrect server errors
{
tbxOutput.Text += "Connecting to server " + srvname + "...\r\n";
Server srv;
srv = new Server(srvname);
srv.ConnectionContext.LoginSecure = true;
if (!srv.Databases.Contains(dbName))
{
RemoveFolders();//Clean out folders creating during this run
throw new ArgumentException("The specified database could not be found.");
}
Database db = new Database();
db = srv.Databases[dbName];
Scripter scr = new Scripter(srv);
Scripter scrFullScript = new Scripter(srv);
srv.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
/* Create Options for the scr Scripter */
ScriptingOptions options = new ScriptingOptions();
options.IncludeHeaders = true;
options.AppendToFile = false;
options.ToFileOnly = true;
options.DriAll = true;
options.IncludeDatabaseContext = true;
//options.ScriptDrops = true;
scr.Options = options; //Assign options to scr
/* Create options for the scrFullScript Scripter */
ScriptingOptions scopFull = new ScriptingOptions();
scopFull.IncludeHeaders = true;
scopFull.AppendToFile = true;
scopFull.ToFileOnly = true;
scopFull.DriAll = true;
scopFull.IncludeDatabaseContext = true;
scopFull.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\"
+ dbName + "_FULL.sql";
scrFullScript.Options = scopFull; //Assign options to scrFullScript
/* ******************* */
/* CREATE SCRIPT FILES */
/* ******************* */
List<string> lstErrors = new List<string>();
//SCRIPT DATABASE
Microsoft.SqlServer.Management.Smo.Database[] dbs = new Microsoft.SqlServer.Management.Smo.Database[1];
tbxOutput.Text += "Scripting Database: " + db + "\r\n";
dbs[0] = db;
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\"
+ dbName + ".sql";
scr.Script(dbs);
scrFullScript.Script(dbs);
tbxOutput.Text += "Scripting Database Complete.\r\n";
//SCRIPT TABLES
Microsoft.SqlServer.Management.Smo.Table[] tbl = new Microsoft.SqlServer.Management.Smo.Table[1];
tbxOutput.Text += "Scripting Tables...\r\n";
for (int idx = 0; idx < db.Tables.Count; idx++)
{
if (!db.Tables[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting Table: " + db.Tables[idx] + "\r\n";
tbl[0] = db.Tables[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"
+ tbl[0].Name + ".sql";
scr.Script(tbl);
scrFullScript.Script(tbl);
}
}
tbxOutput.Text += "Scripting Tables Complete.\r\n";
//SCRIPT VIEWS
Microsoft.SqlServer.Management.Smo.View[] vw = new Microsoft.SqlServer.Management.Smo.View[1];
tbxOutput.Text += "Scripting Views...\r\n";
for (int idx = 0; idx < db.Views.Count; idx++)
{
if (!db.Views[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting View: " + db.Views[idx] + "\r\n";
vw[0] = db.Views[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"
+ vw[0].Name + ".sql";
scr.Script(vw);
scrFullScript.Script(vw);
}
}
tbxOutput.Text += "Scripting Views Complete.\r\n";
//SCRIPT STORED PROCEDURES
Microsoft.SqlServer.Management.Smo.StoredProcedure[] proc = new Microsoft.SqlServer.Management.Smo.StoredProcedure[1];
tbxOutput.Text += "Scripting Stored Procedures...\r\n";
for (int idx = 0; idx < db.StoredProcedures.Count; idx++)
{
if (!db.StoredProcedures[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting Stored Procedure: " + db.StoredProcedures[idx] + "\r\n";
proc[0] = db.StoredProcedures[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"
+ proc[0].Name + ".sql";
scr.Script(proc);
scrFullScript.Script(proc);
}
}
tbxOutput.Text += "Scripting Stored Procedures Complete.\r\n";
//SCRIPT FUNCTIONS
Microsoft.SqlServer.Management.Smo.UserDefinedFunction[] udf = new Microsoft.SqlServer.Management.Smo.UserDefinedFunction[1];
tbxOutput.Text += "Scripting User Defined Functions...\r\n";
for (int idx = 0; idx < db.UserDefinedFunctions.Count; idx++)
{
if (!db.UserDefinedFunctions[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting User Defined Function: " + db.UserDefinedFunctions[idx] + "\r\n";
udf[0] = db.UserDefinedFunctions[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\"
+ udf[0].Name + ".sql";
scr.Script(udf);
scrFullScript.Script(udf);
}
}
tbxOutput.Text += "Scripting User Defined Functions complete.\r\n";
tbxOutput.Text += "Scripting master file...\r\n";
try
{
String strFullScript = "";
String strFullOutput = "";
tbxOutput.Text += "Retrieving full script...\r\n";
using (StreamReader sr = new StreamReader(scopFull.FileName))
{
strFullScript = sr.ReadToEnd();
}
tbxOutput.Text += "Full script retrieved.\r\n";
//strFullOutput = strFullScript;//Temporary
string[] arrFullScript = Regex.Split(strFullScript, "GO");
foreach (string line in arrFullScript)
{
if(!line.StartsWith("\r\nALTER TABLE"))
strFullOutput += line + "GO\r\n";
}
foreach (string line in arrFullScript)
{
if (line.StartsWith("\r\nALTER TABLE"))
strFullOutput += line + "GO\r\n";
}
string strConditionalDrop = "\r\n\r\nIF DB_ID('" + dbName + "') IS NOT NULL\r\nBEGIN\r\n"
+ " ALTER DATABASE " + dbName + "\r\n"
+ " SET SINGLE_USER\r\n"
+ " WITH ROLLBACK IMMEDIATE;\r\n"
+ " DROP DATABASE " + dbName + ";\r\n"
+ "END\r\n";
strFullOutput = strFullOutput.Insert(strFullOutput.IndexOf("GO") + 2, strConditionalDrop);
tbxOutput.Text += "Writing corrected full script...\r\n";
using (StreamWriter sw = new StreamWriter(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\" + dbName + "_FULL.sql"))
{
sw.Write(strFullOutput);
}
tbxOutput.Text += "Full script successfully written.\r\n";
tbxOutput.Text += "Scripting master file complete.\r\n";
}
catch
{
tbxOutput.Text += "ERROR Scripting Master File Failed.\r\n";
lstErrors.Add("Scripting Master File Failed.");
}
tbxOutput.Text += "=================================\r\n";
if (lstErrors.Count == 0)
tbxOutput.Text += "SCRIPTING COMPLETED SUCCESSFULLY.\r\n";
else
{
tbxOutput.Text += "SCRIPTING COMPLETED WITH ERRORS.\r\n";
tbxOutput.Text += String.Format("The following {0} errors occurred:\r\n", lstErrors.Count);
foreach (string error in lstErrors)
{
tbxOutput.Text += error + "\r\n";
}
}
ActiveControl = btnClose;
}
catch (ConnectionFailureException) //Error type thrown by attempt to bind invalid server name
{
//throw new ConnectionFailureException("A connection to the specified server could not be made. Please check the supplied server name and try again.");
tbxOutput.Text += "Connection to server failed.\r\n";
RemoveFolders();//Clean out folders creating during this run
tbxOutput.Text += "A connection to the specified server could not be made. Please check the supplied server name and try again.\r\n";
}
catch //General Catch-All re-throws error without further handling
{
RemoveFolders();//Clean out folders creating during this run
throw;
}
}
private MetadataTable BuildMetadataView(MetadataDatabase mdb, Microsoft.SqlServer.Management.Smo.View view, bool PrimaryKeyIndexOnly = true, bool SelfJoin = false)
{
MetadataTable mt = null;
List <VirtualForeignKey> VirtualKeys = new List <VirtualForeignKey>();
view.Refresh();
if (mdb.Tables.TryGetValue(view.Name, out mt))
{
return(mt);
}
mt = new MetadataTable()
{
ID = view.ID,
Schema = view.Schema,
Name = view.Name,
//Parent = mdb
};
mt.TitleColumn = GetExtendedProperty("TitleColumn", view.ExtendedProperties);
string[] values = GetExtendedProperty("DisplayName", view.ExtendedProperties, new char[] { '\r', '\n' });
if (values != null)
{
foreach (string value in values)
{
string[] v = value.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
mt.DisplayNames.TryAdd(Convert.ToInt32(v[0]), v[1]);
}
}
values = GetExtendedProperty("Lists", view.ExtendedProperties, new char[] { '\r', '\n' });
if (values != null)
{
foreach (string value in values)
{
string[] v = value.Split(new char[] { ':' }, StringSplitOptions.RemoveEmptyEntries);
List <string> v2 = v[1].Split(',').ToList();
if (!mt.ListDefinitions.TryAdd(v[0].Trim(), v2))
{
throw new InvalidOperationException(string.Format("The TinySql.Lists extended property is invalid for the table '{0}'", view.Name));
}
}
}
foreach (Microsoft.SqlServer.Management.Smo.Column column in view.Columns)
{
try
{
MetadataColumn col = new MetadataColumn()
{
ID = column.ID,
Parent = mt,
//Database = mdb,
Name = column.Name,
Collation = column.Collation,
Default = column.Default,
IsComputed = column.Computed,
ComputedText = column.ComputedText,
IsPrimaryKey = column.InPrimaryKey,
IsIdentity = column.Identity,
IsForeignKey = column.IsForeignKey,
IdentityIncrement = column.IdentityIncrement,
IdentitySeed = column.IdentitySeed,
Nullable = column.Nullable,
IsRowGuid = column.RowGuidCol
};
BuildColumnDataType(col, column);
values = GetExtendedProperty("DisplayName", column.ExtendedProperties, new char[] { '\r', '\n' });
if (values != null)
{
foreach (string value in values)
{
if (!value.Contains("="))
{
col.DisplayNames.TryAdd(SqlBuilder.DefaultCulture.LCID, value);
}
else
{
string[] v = value.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
col.DisplayNames.TryAdd(Convert.ToInt32(v[0]), v[1]);
}
}
}
col.IncludeColumns = GetExtendedProperty("IncludeColumns", column.ExtendedProperties, new char[] { ',' });
values = GetExtendedProperty("FK", column.ExtendedProperties, new char[] { '\r', '\n' });
if (values != null)
{
VirtualKeys.Add(new VirtualForeignKey()
{
Column = col, values = values
});
col.IsForeignKey = true;
}
mt.Columns.AddOrUpdate(col.Name, col, (k, v) => { return(col); });
}
catch (Exception exColumn)
{
throw new InvalidOperationException(string.Format("Unable to generate the column {0}", column.Name), exColumn);
}
}
if (view.IsIndexable)
{
foreach (Index idx in view.Indexes)
{
if (!PrimaryKeyIndexOnly || idx.IndexKeyType == IndexKeyType.DriPrimaryKey)
{
Key key = new Key()
{
ID = idx.ID,
Parent = mt,
Database = mdb,
Name = idx.Name,
IsUnique = idx.IsUnique,
IsPrimaryKey = idx.IndexKeyType == IndexKeyType.DriPrimaryKey
};
foreach (IndexedColumn c in idx.IndexedColumns)
{
key.Columns.Add(mt[c.Name]);
}
mt.Indexes.AddOrUpdate(key.Name, key, (k, v) => { return(key); });
}
}
}
//if (!SelfJoin)
//{
// foreach (ForeignKey FK in view.ForeignKeys)
// {
// MetadataForeignKey mfk = new MetadataForeignKey()
// {
// ID = FK.ID,
// Parent = mt,
// Database = mdb,
// Name = FK.Name,
// ReferencedKey = FK.ReferencedKey,
// ReferencedSchema = FK.ReferencedTableSchema,
// ReferencedTable = FK.ReferencedTable
// };
// MetadataTable mtref = null;
// if (!mdb.Tables.TryGetValue(mfk.ReferencedSchema + "." + mfk.ReferencedTable, out mtref))
// {
// bool self = false;
// if ((mfk.ReferencedSchema == mt.Schema && mfk.ReferencedTable == mt.Name) || TablesInProgress.Contains(mfk.ReferencedSchema + "." + mfk.ReferencedTable))
// {
// self = true;
// }
// TablesInProgress.Add(mfk.ReferencedSchema + "." + mfk.ReferencedTable);
// mtref = BuildMetadata(mdb, mfk.ReferencedTable, mfk.ReferencedSchema, PrimaryKeyIndexOnly, self);
// }
// foreach (ForeignKeyColumn cc in FK.Columns)
// {
// mfk.ColumnReferences.Add(new MetadataColumnReference()
// {
// Name = cc.Name,
// Column = mt[cc.Name],
// ReferencedColumn = mtref[cc.ReferencedColumn]
// });
// }
// mt.ForeignKeys.AddOrUpdate(mfk.Name, mfk, (key, existing) =>
// {
// return mfk;
// });
// }
//}
if (VirtualKeys.Count > 0)
{
BuildVirtualKeys(VirtualKeys, mt, mdb, PrimaryKeyIndexOnly);
}
mdb.Tables.AddOrUpdate(mt.Schema + "." + mt.Name, mt, (key, existing) =>
{
return(mt);
});
return(mt);
}
private static SQLInformation.Data.ApplicationDataSet.DBViewsRow Add(Guid databaseID, MSMO.View view, ExpandMask.ViewExpandSetting viewExpandSetting)
{
SQLInformation.Data.ApplicationDataSet.DBViewsRow dataRow = null;
try
{
dataRow = Common.ApplicationDataSet.DBViews.NewDBViewsRow();
dataRow.ID = Guid.NewGuid();
dataRow.Name_View = view.Name;
dataRow.View_ID = view.ID;
dataRow.Database_ID = databaseID; // From above
dataRow.Owner = view.Owner;
dataRow.ExpandColumns = viewExpandSetting.ExpandColumns;
dataRow.CreateDate = view.CreateDate;
//newView.DataSpaceUsed = int.Parse(viewH.DataSpaceUsed); // This is not supported yet.
try
{
dataRow.DateLastModified = view.DateLastModified;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 7);
#endif
}
dataRow.SnapShotDate = DateTime.Now;
dataRow.SnapShotError = "";
Common.ApplicationDataSet.DBViews.AddDBViewsRow(dataRow);
Common.ApplicationDataSet.DBViewsTA.Update(Common.ApplicationDataSet.DBViews);
//viewID = viewRow.ID;
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 8);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
return(dataRow);
}
public void Script(string srvname, string dbName, string destination)
{
tbxOutput.Text = "Scripting the " + dbName + " database." + "\r\n";
if (destination == "")
{
destination = Environment.CurrentDirectory + "\\";
}
else
{
if (destination[destination.Length - 1] != Convert.ToChar("\\"))
{
destination += "\\";
}
}
tbxOutput.Text += "Output directory set to " + destination + "\r\n";
/* *************************** */
/* CHECK FOR VALID DESTINATION */
/* *************************** */
tbxOutput.Text += "Checking for valid destination directory...\r\n";
if (!Directory.Exists(destination))
{
throw new DirectoryNotFoundException("The specified destination directory does not exist.");
}
else
{
tbxOutput.Text += "Destination directory is valid.\r\n";
/* *********************** */
/* CREATE FOLDER STRUCTURE */
/* *********************** */
tbxOutput.Text += "Establishing folder structure...\r\n";
newFolders.Clear();
try
{
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd")))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"));
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"));
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + @"\Programmability\Functions\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\" + "\r\n";
}
}
catch
{
throw new UnauthorizedAccessException("The program failed to create the backup folders in the specified directory. Please check security settings.");
}
tbxOutput.Text += "Folder structure established \r\n";
}
/* *************** */
/* Generate Script */
/* *************** */
try //Wrap in try statement to catch incorrect server errors
{
tbxOutput.Text += "Connecting to server " + srvname + "...\r\n";
Server srv;
srv = new Server(srvname);
srv.ConnectionContext.LoginSecure = true;
if (!srv.Databases.Contains(dbName))
{
RemoveFolders();//Clean out folders creating during this run
throw new ArgumentException("The specified database could not be found.");
}
Database db = new Database();
db = srv.Databases[dbName];
Scripter scr = new Scripter(srv);
Scripter scrFullScript = new Scripter(srv);
srv.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
/* Create Options for the scr Scripter */
ScriptingOptions options = new ScriptingOptions();
options.IncludeHeaders = true;
options.AppendToFile = false;
options.ToFileOnly = true;
options.DriAll = true;
options.IncludeDatabaseContext = true;
//options.ScriptDrops = true;
scr.Options = options; //Assign options to scr
/* Create options for the scrFullScript Scripter */
ScriptingOptions scopFull = new ScriptingOptions();
scopFull.IncludeHeaders = true;
scopFull.AppendToFile = true;
scopFull.ToFileOnly = true;
scopFull.DriAll = true;
scopFull.IncludeDatabaseContext = true;
scopFull.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\"
+ dbName + "_FULL.sql";
scrFullScript.Options = scopFull; //Assign options to scrFullScript
/* ******************* */
/* CREATE SCRIPT FILES */
/* ******************* */
List <string> lstErrors = new List <string>();
//SCRIPT DATABASE
Microsoft.SqlServer.Management.Smo.Database[] dbs = new Microsoft.SqlServer.Management.Smo.Database[1];
tbxOutput.Text += "Scripting Database: " + db + "\r\n";
dbs[0] = db;
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\"
+ dbName + ".sql";
scr.Script(dbs);
scrFullScript.Script(dbs);
tbxOutput.Text += "Scripting Database Complete.\r\n";
//SCRIPT TABLES
Microsoft.SqlServer.Management.Smo.Table[] tbl = new Microsoft.SqlServer.Management.Smo.Table[1];
tbxOutput.Text += "Scripting Tables...\r\n";
for (int idx = 0; idx < db.Tables.Count; idx++)
{
if (!db.Tables[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting Table: " + db.Tables[idx] + "\r\n";
tbl[0] = db.Tables[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"
+ tbl[0].Name + ".sql";
scr.Script(tbl);
scrFullScript.Script(tbl);
}
}
tbxOutput.Text += "Scripting Tables Complete.\r\n";
//SCRIPT VIEWS
Microsoft.SqlServer.Management.Smo.View[] vw = new Microsoft.SqlServer.Management.Smo.View[1];
tbxOutput.Text += "Scripting Views...\r\n";
for (int idx = 0; idx < db.Views.Count; idx++)
{
if (!db.Views[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting View: " + db.Views[idx] + "\r\n";
vw[0] = db.Views[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"
+ vw[0].Name + ".sql";
scr.Script(vw);
scrFullScript.Script(vw);
}
}
tbxOutput.Text += "Scripting Views Complete.\r\n";
//SCRIPT STORED PROCEDURES
Microsoft.SqlServer.Management.Smo.StoredProcedure[] proc = new Microsoft.SqlServer.Management.Smo.StoredProcedure[1];
tbxOutput.Text += "Scripting Stored Procedures...\r\n";
for (int idx = 0; idx < db.StoredProcedures.Count; idx++)
{
if (!db.StoredProcedures[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting Stored Procedure: " + db.StoredProcedures[idx] + "\r\n";
proc[0] = db.StoredProcedures[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"
+ proc[0].Name + ".sql";
scr.Script(proc);
scrFullScript.Script(proc);
}
}
tbxOutput.Text += "Scripting Stored Procedures Complete.\r\n";
//SCRIPT FUNCTIONS
Microsoft.SqlServer.Management.Smo.UserDefinedFunction[] udf = new Microsoft.SqlServer.Management.Smo.UserDefinedFunction[1];
tbxOutput.Text += "Scripting User Defined Functions...\r\n";
for (int idx = 0; idx < db.UserDefinedFunctions.Count; idx++)
{
if (!db.UserDefinedFunctions[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting User Defined Function: " + db.UserDefinedFunctions[idx] + "\r\n";
udf[0] = db.UserDefinedFunctions[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\"
+ udf[0].Name + ".sql";
scr.Script(udf);
scrFullScript.Script(udf);
}
}
tbxOutput.Text += "Scripting User Defined Functions complete.\r\n";
tbxOutput.Text += "Scripting master file...\r\n";
try
{
String strFullScript = "";
String strFullOutput = "";
tbxOutput.Text += "Retrieving full script...\r\n";
using (StreamReader sr = new StreamReader(scopFull.FileName))
{
strFullScript = sr.ReadToEnd();
}
tbxOutput.Text += "Full script retrieved.\r\n";
//strFullOutput = strFullScript;//Temporary
string[] arrFullScript = Regex.Split(strFullScript, "GO");
foreach (string line in arrFullScript)
{
if (!line.StartsWith("\r\nALTER TABLE"))
{
strFullOutput += line + "GO\r\n";
}
}
foreach (string line in arrFullScript)
{
if (line.StartsWith("\r\nALTER TABLE"))
{
strFullOutput += line + "GO\r\n";
}
}
string strConditionalDrop = "\r\n\r\nIF DB_ID('" + dbName + "') IS NOT NULL\r\nBEGIN\r\n"
+ " ALTER DATABASE " + dbName + "\r\n"
+ " SET SINGLE_USER\r\n"
+ " WITH ROLLBACK IMMEDIATE;\r\n"
+ " DROP DATABASE " + dbName + ";\r\n"
+ "END\r\n";
strFullOutput = strFullOutput.Insert(strFullOutput.IndexOf("GO") + 2, strConditionalDrop);
tbxOutput.Text += "Writing corrected full script...\r\n";
using (StreamWriter sw = new StreamWriter(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\" + dbName + "_FULL.sql"))
{
sw.Write(strFullOutput);
}
tbxOutput.Text += "Full script successfully written.\r\n";
tbxOutput.Text += "Scripting master file complete.\r\n";
}
catch
{
tbxOutput.Text += "ERROR Scripting Master File Failed.\r\n";
lstErrors.Add("Scripting Master File Failed.");
}
tbxOutput.Text += "=================================\r\n";
if (lstErrors.Count == 0)
{
tbxOutput.Text += "SCRIPTING COMPLETED SUCCESSFULLY.\r\n";
}
else
{
tbxOutput.Text += "SCRIPTING COMPLETED WITH ERRORS.\r\n";
tbxOutput.Text += String.Format("The following {0} errors occurred:\r\n", lstErrors.Count);
foreach (string error in lstErrors)
{
tbxOutput.Text += error + "\r\n";
}
}
ActiveControl = btnClose;
}
catch (ConnectionFailureException) //Error type thrown by attempt to bind invalid server name
{
//throw new ConnectionFailureException("A connection to the specified server could not be made. Please check the supplied server name and try again.");
tbxOutput.Text += "Connection to server failed.\r\n";
RemoveFolders();//Clean out folders creating during this run
tbxOutput.Text += "A connection to the specified server could not be made. Please check the supplied server name and try again.\r\n";
}
catch //General Catch-All re-throws error without further handling
{
RemoveFolders(); //Clean out folders creating during this run
throw;
}
}
private static SQLInformation.Data.ApplicationDataSet.DBViewsRow GetInfoFromSMO(Guid databaseID, MSMO.View view, ExpandMask.ViewExpandSetting viewExpandSetting)
{
#if TRACE
long startTicks = VNC.AppLog.Trace4(string.Format("Enter {0}", view.Name), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 4);
#endif
Guid viewID = Guid.Empty;
SQLInformation.Data.ApplicationDataSet.DBViewsRow dataRow = null;
try
{
var vs = from v in Common.ApplicationDataSet.DBViews
where v.Database_ID == databaseID
select v;
var vs2 = from v2 in vs
where v2.Name_View == view.Name
select v2;
if (vs2.Count() > 0)
{
dataRow = vs2.First();
Update(view, dataRow);
}
else
{
dataRow = Add(databaseID, view, viewExpandSetting);
}
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 5);
}
#if TRACE
VNC.AppLog.Trace4(string.Format("Exit {0}", view.Name), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 6, startTicks);
#endif
return(dataRow);
}
internal View(Microsoft.SqlServer.Management.Smo.View View)
{
_view = View;
}
public void ExtractScript(SQLObjectName oname, SQLScripts SQLScriptsCollection, bool Verbose)
{
// Store extracted scripts. Each extract may include multiple scripts.
StringCollection OutputScripts = new StringCollection();
string FinalScript = String.Empty;
switch (oname.ObjectType)
{
case SQLObjectType.Table:
Microsoft.SqlServer.Management.Smo.Table scriptTable = connDatabase.Tables[oname.Name, oname.Schema];
if (scriptTable != null)
{
StringCollection CheckScripts = new StringCollection(); // Store scripts to be checked
String TableScript = String.Empty; // Stores individual script for output collection.
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.DriAll = true;
scriptOptions.Statistics = true;
scriptOptions.ClusteredIndexes = true;
scriptOptions.NonClusteredIndexes = true;
scriptOptions.DriAllConstraints = true;
scriptOptions.WithDependencies = false;
// Get table and related scripts
CheckScripts = scriptTable.Script(scriptOptions);
// Check scripts so we can remove invalide SQL 2012 column store options from the script.
// (Why doesn't the target server version remove this?
// This is a crappy place to do this, and it's version specific.
// Need to implement the new versioning code to check target model.
foreach (string CheckCCI in CheckScripts)
{
if (CheckCCI.Contains(", DATA_COMPRESSION = COLUMNSTORE"))
{
TableScript = CheckCCI.Replace(", DATA_COMPRESSION = COLUMNSTORE", "");
}
else
{
TableScript = CheckCCI;
}
// Add the script into the OutputScripts collection.
OutputScripts.Add(TableScript);
}
}
break;
case SQLObjectType.View:
Microsoft.SqlServer.Management.Smo.View scriptView = connDatabase.Views[oname.Name, oname.Schema];
if (scriptView != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.DriAll = true;
scriptOptions.ClusteredIndexes = true;
scriptOptions.NonClusteredIndexes = true;
scriptOptions.WithDependencies = false;
// Must specify tables seperatly, but safer to do so
// to avoid having duplicate table names in the model.
OutputScripts = scriptView.Script(scriptOptions);
}
break;
case SQLObjectType.StoredProcedure:
Microsoft.SqlServer.Management.Smo.StoredProcedure scriptStoredProcedure = connDatabase.StoredProcedures[oname.Name, oname.Schema];
if (scriptStoredProcedure != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptStoredProcedure.Script(scriptOptions);
}
break;
case SQLObjectType.PartitionScheme:
{
Microsoft.SqlServer.Management.Smo.PartitionScheme scriptPScheme = connDatabase.PartitionSchemes[oname.Name];
if (scriptPScheme != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptPScheme.Script(scriptOptions);
}
}
break;
case SQLObjectType.PartitionFunction:
{
Microsoft.SqlServer.Management.Smo.PartitionFunction scriptPFunction = connDatabase.PartitionFunctions[oname.Name];
if (scriptPFunction != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptPFunction.Script(scriptOptions);
}
}
break;
case SQLObjectType.Schema:
{
Microsoft.SqlServer.Management.Smo.Schema scriptSchema = connDatabase.Schemas[oname.Name];
if (scriptSchema != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
scriptOptions.ScriptOwner = true; // This includes the "with authorize" part.
OutputScripts = scriptSchema.Script(scriptOptions);
}
}
break;
case SQLObjectType.FileGroup:
{
Microsoft.SqlServer.Management.Smo.FileGroup scriptFG = connDatabase.FileGroups[oname.Name];
if (scriptFG != null)
{
// Create manual script for FileGroups
OutputScripts.Add("ALTER DATABASE [$(DatabaseName)] ADD FILEGROUP " + scriptFG.Name);
}
}
break;
case SQLObjectType.User:
{
Microsoft.SqlServer.Management.Smo.User scriptUser = connDatabase.Users[oname.Name];
if (scriptUser != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptUser.Script(scriptOptions);
}
}
break;
case SQLObjectType.Function:
Microsoft.SqlServer.Management.Smo.UserDefinedFunction userDefinedFunction = connDatabase.UserDefinedFunctions[oname.Name, oname.Schema];
if (userDefinedFunction != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = userDefinedFunction.Script(scriptOptions);
}
break;
}
if (OutputScripts.Count > 0)
{
Console.WriteLine("Extracted SQL script: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
foreach (string script in OutputScripts)
{
// Add the script to the script collection.
FinalScript = FinalScript + script + Environment.NewLine + "GO" + Environment.NewLine;
}
}
else
{
Console.WriteLine("Warning - Could not retrieve: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
FinalScript = String.Empty;
}
if (FinalScript != String.Empty)
{
SQLScriptsCollection.Scripts.Add(FinalScript);
}
else
{
SQLScriptsCollection.MissingScripts.Add("Missing SQL object: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
}
// Print script(s) if verbose is on.
if (Verbose)
{
Console.WriteLine(FinalScript);
}
}
private void cmdEncryptObjects_Click(object sender, EventArgs e)
{
try
{
DialogResult dlgRes = MessageBox.Show("Are you sure you want to encrypt -> " + cboComboBox.Text + "\n\n\nPlease confirm if a backup has been taken before this operation!", "Please Confirm", MessageBoxButtons.YesNo);
if (dlgRes == DialogResult.Yes)
{
dlgRes = MessageBox.Show("Please note that the encryption is not reversible. Please confirm that you want to encrypt -> " + cboComboBox.Text + "\n\n\nPlease confirm if a backup has been taken before this operation!", "Please Confirm", MessageBoxButtons.YesNo);
}
if (dlgRes == DialogResult.No)
{
return;
}
SqlConnectionInfo sci = new SqlConnectionInfo(txtServerName.Text);
if (!chkWindowsAuth.Checked)
{
sci.UseIntegratedSecurity = false;
sci.UserName = txtUserID.Text;
sci.Password = txtPassword.Text;
}
else
{
sci.UseIntegratedSecurity = true;
}
var serverConnection = new ServerConnection(sci);
var srv = new Server(serverConnection);
try // Check to see if server connection details are ok.
{
var db = new Database();
db = srv.Databases[cboComboBox.Text.TrimStart('[').TrimEnd(']')];
if (db == null)
{
throw new Exception("Error! Unable to select");
}
Console.WriteLine("Encrypted stored procedures: ");
var sp = new StoredProcedure();
for (int i = 0; i < db.StoredProcedures.Count; i++)
{
sp = db.StoredProcedures[i];
if (!sp.IsSystemObject) // Exclude System stored procedures
{
if (!sp.IsEncrypted) // Exclude already encrypted stored procedures
{
sp.TextMode = false;
sp.IsEncrypted = true;
sp.TextMode = true;
try
{
sp.Alter();
}
catch (Exception excep)
{
_logger.Info(excep, "Encryption failed for -> {0}, error- -> {1}", sp.Name, excep.Message);
}
Console.WriteLine(" " + sp.Name); // display name of the SP.
_logger.Info("Encrypting Proc =>{0}", sp.Name);
}
}
}
var udfs = new UserDefinedFunction();
for (int i = 0; i < db.UserDefinedFunctions.Count; i++)
{
udfs = db.UserDefinedFunctions[i];
if (!udfs.IsSystemObject)
{
if (!udfs.IsEncrypted)
{
udfs.TextMode = false;
udfs.IsEncrypted = true;
udfs.TextMode = true;
try
{
udfs.Alter();
}
catch (Exception excep)
{
_logger.Info(excep, "Encryption failed for -> {0}, error- -> {1}", udfs.Name, excep.Message);
}
Console.WriteLine(" " + udfs.Name);
_logger.Info("Encrypting UDF =>{0}", udfs.Name);
}
}
}
Microsoft.SqlServer.Management.Smo.View viewobj = null;
for (int i = 0; i < db.Views.Count; i++)
{
viewobj = db.Views[i];
if (!viewobj.IsSystemObject)
{
if (!viewobj.IsEncrypted)
{
viewobj.TextMode = false;
viewobj.IsEncrypted = true;
viewobj.TextMode = true;
try
{
viewobj.Alter();
}
catch (Exception excep)
{
_logger.Info(excep, "Encryption failed for -> {0}, error- -> {1}", viewobj.Name, excep.Message);
}
Console.WriteLine(" " + viewobj.Name); // display name of the SP.
_logger.Info("Encrypting View =>{0}", viewobj.Name);
}
}
}
MessageBox.Show("Process has completed succssfully. Please refer to the application logs for more information", "Operation Complete", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception InnerEx)
{
_logger.Info("Server details are incorrect;"
+ " please retry with proper details");
MessageBox.Show("Error\n" + "Server details are incorrect;"
+ "\nPlease retry with proper details.\n\n" + InnerEx.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
_logger.Fatal(ex, "Unable to connect to the DB");
MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void InitializeDsDwMap()
{
if (DwRebuildDsDwTableMap)
{
AppendLogLine("Rebuilding table map...");
DsDwMapChanged = true;
DsDwMap = null;
}
if (DsDwMap == null)
{
DsDwMap = new DsDwMap_M();
}
else
{
SycDwTableViewMapList();
}
foreach (TableView_M aTableView in DwTableViewList)
{
if ((aTableView != null) && (!DsDwMap.DsDwTableMapList.Any(tm => tm.DsObjectId == aTableView.ObjectId)))
{
DsDwMapChanged = true;
AppendLogLine(string.Format("Processing table/view: {0}...", aTableView.Name));
DsDwTableMap_M aDsDwTableMap = new DsDwTableMap_M
{
DsIsTable = aTableView.IsTable,
DsTableName = aTableView.Name,
DsSchemaName = aTableView.Schema,
DwTableName = aTableView.Name,
DwSchemaName = aTableView.Schema,
DsRowCount = aTableView.RowCount,
DsObjectId = aTableView.ObjectId,
DsDatabaseName = DsDbName,
DsServerName = DsDbServerName
};
DsDwMap.DsDwTableMapList.Add(aDsDwTableMap);
TableViewBase aTableViewBase;
if (aTableView.IsTable)
{
aTableViewBase = _DsDb.Tables.Cast <Table>().FirstOrDefault(t => t.ID == aTableView.ObjectId);
}
else
{
aTableViewBase =
_DsDb.Views.Cast <Microsoft.SqlServer.Management.Smo.View>()
.FirstOrDefault(t => t.ID == aTableView.ObjectId);
}
if (aTableViewBase != null)
{
foreach (Column aColumn in aTableViewBase.Columns)
{
DsDwColumnMap_M aDsDwColumnMap = new DsDwColumnMap_M
{
DsColumn = new DsDwColumn_M(aColumn),
DwColumn = new DsDwColumn_M(aColumn),
Include = true
};
if (aColumn.InPrimaryKey)
{
aDsDwColumnMap.Transformation = DsDwColumnTransformation.BusinesKey;
aDsDwTableMap.DsDwColumnMapList.Insert(0, aDsDwColumnMap);
}
else
{
aDsDwColumnMap.Transformation = DsDwColumnTransformation.Scd1;
aDsDwTableMap.DsDwColumnMapList.Add(aDsDwColumnMap);
}
}
if (aTableView.IsTable)
{
AddExistingForeignKeysTable((Table)aTableViewBase, aDsDwTableMap);
}
if (!aTableView.IsTable) //this is a view
{
aTableViewBase =
_DsDb.Views.Cast <Microsoft.SqlServer.Management.Smo.View>()
.FirstOrDefault(t => t.ID == aTableView.ObjectId);
ParseViewSql aParseViewSql = new ParseViewSql();
Microsoft.SqlServer.Management.Smo.View aView =
(aTableViewBase as Microsoft.SqlServer.Management.Smo.View);
if (aParseViewSql.ParseView(aView.TextHeader + aView.TextBody))
{
FillColumnAndForeignKeyInfoView(aParseViewSql.ColumnList, aDsDwTableMap);
}
}
AddDwSupportColumns(aDsDwTableMap);
}
}
else
{
//Update DbName and ServerName
DsDwTableMap_M aUpdateDsDwTableMap = DsDwMap.DsDwTableMapList.FirstOrDefault(tm => tm.DsObjectId == aTableView.ObjectId);
if (aUpdateDsDwTableMap != null)
{
aUpdateDsDwTableMap.DsDatabaseName = DsDbName;
aUpdateDsDwTableMap.DsServerName = DsDbServerName;
}
}
}
if (DsDwMapChanged || true)
{
AppendLogLine(string.Format("Always rebuilding foreign key links."));
DsDwMap.ConfigureForeignKeyTransformations();
}
else
{
AppendLogLine(string.Format("Map not changed, not rebuilding foreign key links."));
}
DsDwMapChanged = false;
NotifyPropertyChanged("DsDwMap");
}
