private void WfloadTree(string pAdm02, UltraTree pUltraTree, UltraTreeNode pUltraTreeNode)
{
StringBuilder sbSql;
DataTable dtAdm;
UltraTreeNode utn;
string ado02, ado07, adm01, adm02;
try
{
sbSql = new StringBuilder();
sbSql.AppendLine("SELECT * FROM adm_tb");
sbSql.AppendLine("LEFT JOIN ado_tb ON adm02=ado01");
sbSql.AppendLine(string.Format("WHERE adm01='{0}'", pAdm02));
//sbSql.AppendLine(" AND ado07='P'");
sbSql.AppendLine("ORDER BY adm03");
dtAdm = BoSecurity.OfGetDataTable(sbSql.ToString(), null);
if (dtAdm == null || dtAdm.Rows.Count == 0)
{
return;
}
foreach (DataRow drAdm in dtAdm.Rows)
{
ado02 = GlobalFn.isNullRet(drAdm["ado02"], "");
ado07 = GlobalFn.isNullRet(drAdm["ado07"], "");
adm01 = GlobalFn.isNullRet(drAdm["adm01"], "");
adm02 = GlobalFn.isNullRet(drAdm["adm02"], "");
if (pUltraTreeNode == null)
{
utn = pUltraTree.Nodes.Add();
}
else
{
utn = pUltraTreeNode.Nodes.Add();
}
utn.Key = adm02;
if (ado07.ToLower() == "m")//menu
{
utn.Text = ado02;
utn.Override.ActiveNodeAppearance.Image = ImgList.Images[GlobalPictuer.MENU_TREE_FOLDER_ACTIVE];
utn.Override.NodeAppearance.Image = ImgList.Images[GlobalPictuer.MENU_TREE_FOLDER];
WfloadTree(adm02, pUltraTree, utn);
}
else
{
utn.Text = ado02 + " -" + adm02;
utn.Override.ActiveNodeAppearance.Image = ImgList.Images[GlobalPictuer.MENU_TREE_FORM_ACTIVE];
utn.Override.NodeAppearance.Image = ImgList.Images[GlobalPictuer.MENU_TREE_FORM];
}
}
}
catch (Exception ex)
{
throw ex;
}
}
//todo:目前是以proc運作,需考量改用程式碼來執行
private void WfRefreshView()
{
StringBuilder sbSql, sbViewSql;
string lsSql;
string viewName;
DataTable dtAta, dtView;
string ata01;
CommonBLL boRefresh;
try
{
if (BoSecurity == null)
{
WfShowErrorMsg("未設定主要連線!");
return;
}
//先更新主要連線的view YR
sbViewSql = new StringBuilder();
sbViewSql.AppendLine(string.Format("SELECT name "));
sbViewSql.AppendLine(string.Format("FROM sys.objects"));
sbViewSql.AppendLine(string.Format("WHERE type = 'V'"));
dtView = BoSecurity.OfGetDataTable(sbViewSql.ToString());
foreach (DataRow ldr in dtView.Rows)
{
viewName = ldr["name"] == DBNull.Value ? "" : ldr["name"].ToString();
lsSql = string.Format("EXEC sp_refreshview '{0}' ", viewName);
BoSecurity.OfExecuteNonquery(lsSql.ToString());
lsSql = string.Format("EXEC sp_gen_aza '{0}' ", viewName);
BoSecurity.OfExecuteNonquery(lsSql.ToString());
}
sbSql = new StringBuilder();
sbSql.AppendLine("SELECT * FROM ata_tb");
dtAta = BoSecurity.OfGetDataTable(sbSql.ToString(), null);
if (dtAta != null && dtAta.Rows.Count > 0)
{
foreach (DataRow drTemp in dtAta.Rows)
{
ata01 = drTemp["ata01"] == DBNull.Value ? "" : drTemp["ata01"].ToString();
boRefresh = new CommonBLL(ata01, "", "", "");
//boRefresh.TRAN = admBll.TRAN;
dtView = boRefresh.OfGetDataTable(sbViewSql.ToString());
foreach (DataRow ldr in dtView.Rows)
{
viewName = ldr["name"] == DBNull.Value ? "" : ldr["name"].ToString();
lsSql = string.Format("EXEC sp_refreshview '{0}' ", viewName);
boRefresh.OfExecuteNonquery(lsSql.ToString());
}
}
}
//admBll.TRAN.Commit();
}
catch (System.Exception ex)
{
BoSecurity.TRAN.Rollback();
throw ex;
}
}
private void WfLoadMenu()
{
StringBuilder sbSql;
DataTable dtAdm;
Infragistics.Win.UltraWinExplorerBar.UltraExplorerBarContainerControl uebcc;
Infragistics.Win.UltraWinTree.UltraTree ut;
ImageList imgModules;
string imgModuleKey;
try
{
imgModules = YR.Util.GlobalPictuer.LoadModuleImage();
sbSql = new StringBuilder();
sbSql.AppendLine("SELECT * FROM adm_tb");
sbSql.AppendLine("LEFT JOIN ado_tb ON adm02=ado01");
sbSql.AppendLine("WHERE adm01='menu'");
sbSql.AppendLine(" AND ado07='M'");
sbSql.AppendLine("ORDER BY adm03");
dtAdm = BoSecurity.OfGetDataTable(sbSql.ToString(), null);
if (dtAdm == null || dtAdm.Rows.Count == 0)
{
return;
}
var i = 0;
foreach (DataRow drAdm in dtAdm.Rows)
{
UltraExplorerBarGroup uebg = new UltraExplorerBarGroup();
uebcc = new UltraExplorerBarContainerControl();
ut = new UltraTree();
ut.BorderStyle = Infragistics.Win.UIElementBorderStyle.None;
ut.ShowLines = true;
//ut.Appearance.BackColor = GetStyleLibrary.TreeBackGroundColor;
ut.ImageList = ImgList;
//ut.Font = new Font(ut.Font.FontFamily, 11);
ut.Font = GetStyleLibrary.FontControlNormal;
ut.DoubleClick += new EventHandler(UltraTree_DoubleClick);
ut.AfterActivate += ut_AfterActivate;
ut.KeyDown += new System.Windows.Forms.KeyEventHandler(UltraTree_KeyDown);
uebg.Key = drAdm["adm02"].ToString();
uebg.Text = drAdm["ado02"].ToString();
if (i == 0)//第一筆時要顯示流程圖
{
WfShowFlowByAdo01(uebg.Key);
}
uebg.Settings.Style = Infragistics.Win.UltraWinExplorerBar.GroupStyle.ControlContainer;
UebMenu.Groups.Add(uebg);
//設定header圖檔,先簡單處理
imgModuleKey = "module_" + uebg.Key + "_32";
try
{
uebg.Settings.AppearancesSmall.HeaderAppearance.Image = imgModules.Images[imgModuleKey];
}
catch
{
}
WfloadTree(uebg.Key, ut, null);
if (ut != null)
{
uebg.Container = uebcc;
uebg.Settings.ItemAreaInnerMargins.Bottom = 0;
uebg.Settings.ItemAreaInnerMargins.Left = 0;
uebg.Settings.ItemAreaInnerMargins.Right = 0;
uebg.Settings.ItemAreaInnerMargins.Top = 0;
uebg.Settings.ItemAreaOuterMargins.Bottom = 5;
uebg.Settings.ItemAreaOuterMargins.Left = 5;
uebg.Settings.ItemAreaOuterMargins.Right = 5;
uebg.Settings.ItemAreaOuterMargins.Top = 5;
ut.Dock = DockStyle.Fill;
uebcc.Controls.Add(ut);
UebMenu.Controls.Add(uebcc);
UebMenu.Groups[0].ExplorerBar.Appearance.BackColor = Color.Red;
}
i++;
}
}
catch (Exception ex)
{
throw ex;
}
}
//*****************************表單自訂Fuction****************************************
#region WfRegenView
//todo:效能安全姓及來源DB連線還要調整
private void WfReGenView()
{
try
{
StringBuilder sbDDL = new StringBuilder();
var urns = new List <Urn>();
System.Collections.Specialized.StringCollection strDropCollection;
System.Collections.Specialized.StringCollection strCreateCollection;
StringBuilder sbSql;
DataTable dtAta;
string ata03; //資料庫名稱
if (BoSecurity == null)
{
return;
}
// Connect to the local, default instance of SQL Server.
SqlConnection sc = new SqlConnection(YR.ERP.Shared.GlobalVar.SQLCA_SecConSTR);
Microsoft.SqlServer.Management.Common.ServerConnection sqlConn = new Microsoft.SqlServer.Management.Common.ServerConnection(sc);
Server server = new Server(sqlConn);
// Reference the database.
Database dbSecurity = server.Databases[sqlConn.DatabaseName];
//先刪除
Scripter scrpDrop = new Scripter(server);
scrpDrop.Options.ScriptDrops = true;
scrpDrop.Options.WithDependencies = false;
scrpDrop.Options.Indexes = false; // To include indexes
scrpDrop.Options.DriAllConstraints = true; // to include referential constraints in the script
scrpDrop.Options.Triggers = false;
scrpDrop.Options.FullTextIndexes = false;
scrpDrop.Options.NoCollation = false;
scrpDrop.Options.Bindings = false;
scrpDrop.Options.IncludeIfNotExists = false;
scrpDrop.Options.ScriptBatchTerminator = true;
scrpDrop.Options.ExtendedProperties = true;
scrpDrop.PrefetchObjects = true; // some sources suggest this may speed things up
//再建立
Scripter scrpCreate = new Scripter(server);
scrpCreate.Options.ScriptDrops = false;
scrpCreate.Options.WithDependencies = false;
scrpCreate.Options.Indexes = false; // To include indexes
scrpCreate.Options.DriAllConstraints = true; // to include referential constraints in the script
scrpCreate.Options.Triggers = false;
scrpCreate.Options.FullTextIndexes = false;
scrpCreate.Options.NoCollation = false;
scrpCreate.Options.Bindings = false;
scrpCreate.Options.IncludeIfNotExists = false;
scrpCreate.Options.ScriptBatchTerminator = true;
scrpCreate.Options.ExtendedProperties = true;
scrpCreate.PrefetchObjects = true; // some sources suggest this may speed things up
// Iterate through the views in database and script each one. Display the script.
foreach (Microsoft.SqlServer.Management.Smo.View view in dbSecurity.Views)
{
// check if the view is not a system object
if (view.IsSystemObject == false)
{
urns.Add(view.Urn);
}
}
strCreateCollection = scrpCreate.Script(urns.ToArray());
//foreach (string st in strCreateCollection)
//{
// // It seems each string is a sensible batch, and putting GO after it makes it work in tools like SSMS.
// // Wrapping each string in an 'exec' statement would work better if using SqlCommand to run the script.
// sbDDL.AppendLine(st);
// sbDDL.AppendLine("GO");
//}
if (strCreateCollection != null)
{
sbSql = new StringBuilder();
sbSql.AppendLine("SELECT * FROM ata_tb");
dtAta = BoSecurity.OfGetDataTable(sbSql.ToString(), null);
if (dtAta != null && dtAta.Rows.Count > 0)
{
foreach (DataRow drTemp in dtAta.Rows)
{
//這裡要建立刪除的VIEW
ata03 = drTemp["ata03"] == DBNull.Value ? "" : drTemp["ata03"].ToString();
Database dbTemp = server.Databases[ata03];
if (dbTemp == null)
{
continue;
}
//假如主連線與要刪除view的db是相同的,刪不做處理
if (dbSecurity.Name.ToUpper() == dbTemp.Name.ToUpper())
{
continue;
}
urns.Clear();
// Iterate through the views in database and script each one. Display the script.
foreach (Microsoft.SqlServer.Management.Smo.View view in dbTemp.Views)
{
// check if the view is not a system object
if (view.IsSystemObject == false)
{
urns.Add(view.Urn);
}
}
strDropCollection = scrpDrop.Script(urns.ToArray());
if (strDropCollection != null && strDropCollection.Count > 0)
{
dbTemp.ExecuteNonQuery(strDropCollection);
strDropCollection.RemoveAt(0);//執行過後會在第一筆加入 USE DATABASE...因此在這裡移除,避免錯誤
}
dbTemp.ExecuteNonQuery(strCreateCollection);
strCreateCollection.RemoveAt(0);//執行過後會在第一筆加入 USE DATABASE...因此在這裡移除,避免錯誤
}
}
}
}
catch (System.Exception ex)
{
throw ex;
}
}