public IEnumerable <string> GetDatabaseObjectText(string value1, string value2, string value3)
{
var objectName = value1;
var objectType = value2;
var databaseName = value3;
var objectText = string.Empty;
if (!string.IsNullOrEmpty(databaseName) && (objectType == "p" || objectType == "ip"))
{
var sql = " EXEC sp_helptext N'" + objectName + "'";
var oDT = new Framework.Components.DataAccess.DBDataTable("ProcedureText", sql, databaseName);
if (oDT.DBTable != null && oDT.DBTable.Rows.Count > 0)
{
foreach (DataRow dr in oDT.DBTable.Rows)
{
objectText += dr[0].ToString();
}
}
}
var result = new List <string>();
result.Add(objectText);
return(result);
}
public IEnumerable <string> GetDatabaseObjects(string value1, string value2, string value3)
{
var objectName = new JavaScriptSerializer().Deserialize <string>(value1);
var objectType = value2;
var databaseName = value3;
var sql = string.Empty;
var result = new List <string>();
if (objectType == "p")
{
sql = " SELECT * FROM sysobjects"
+ " WHERE type = '"+ objectType + "' "
+ " AND name LIKE '%"+ objectName + "%'";
}
else
{
sql = " SELECT o.name " +
" from sys.sql_expression_dependencies ed " +
" JOIN sys.objects o on ed.referencing_id = o.object_id " +
" WHERE ed.referenced_id is NULL AND ed.referenced_database_name IS NULL" +
" AND o.name LIKE '%"+ objectName + "%' " +
" AND LEFT(o.name, 3) NOT IN ('sp_', 'xp_', 'ms_') " +
" ORDER BY o.name";
}
var oDT = new Framework.Components.DataAccess.DBDataTable("Search", sql, databaseName);
result = oDT.DBTable.AsEnumerable().Select(a => a["Name"].ToString()).ToList();
return(result);
}
void objTree_MouseDoubleClick(object sender, EventArgs e)
{
if (objTree.treeViewObjects.ActiveNode != null && Convert.ToString(objTree.treeViewObjects.ActiveNode.Tag) == "proc")
{
try
{
//tabControlQueryEditors.
var objectName = objTree.treeViewObjects.ActiveNode.Key;
if (!tabControlQueryEditors.TabPages.ContainsKey("obj_" + objectName))
{
var sql = "sp_helptext 'dbo." + objectName + "'";
var oDT = new Framework.Components.DataAccess.DBDataTable("Get List", sql, Database);
var procText = new StringBuilder();
if (oDT.DBTable != null)
{
foreach (DataRow dr in oDT.DBTable.Rows)
{
procText.Append(Convert.ToString(dr["Text"]));
}
}
var tabTitle = "dbo." + objectName;
TabPageEx myTabPage = new TabPageEx(tabTitle);
myTabPage.Name = "obj_" + objectName;
myTabPage.UseVisualStyleBackColor = true;
myTabPage.Menu = null;
var txtProcText = new ScintillaNET.Scintilla();
txtProcText.ConfigurationManager.Language = "mssql";
txtProcText.ConfigurationManager.Configure();
if (ShowLineNumbers)
{
txtProcText.Margins[0].Width = 35;
}
else
{
txtProcText.Margins[0].Width = 0;
}
txtProcText.Text = procText.ToString();
txtProcText.Dock = DockStyle.Fill;
txtProcText.AllowDrop = true;
txtProcText.DragEnter += txtQueryEditor_DragEnter;
txtProcText.BackColor = Color.FromName(LoadSettingsByKeyName("TextEditorBackgroundColor"));
myTabPage.Controls.Add(txtProcText);
tabControlQueryEditors.TabPages.Add(myTabPage);
tabControlQueryEditors.SelectedTab = myTabPage;
}
else
{
tabControlQueryEditors.SelectedTab = tabControlQueryEditors.TabPages["obj_" + objectName];
}
CheckAndCreateResult();
}
catch { }
}
}
private int GetChildCount(string objType, int?categoryId)
{
int count = 0;
var sql = string.Empty;
if (objType != "group")
{
if (objType == "table")
{
sql = "select * from sysobjects where xtype='u' ORDER BY name";
}
else if (objType == "proc")
{
sql = "select * from sysobjects where xtype='p' ORDER BY name";
}
else if (objType == "view")
{
sql = "select * from sysobjects where xtype='v' ORDER BY name";
}
else if (objType == "function")
{
sql = "select * from sysobjects where xtype='fn' ORDER BY name";
}
////Framework.Components.DataAccess.StartUp.EntryPoint(path, timeout, ConnectionString);
var oDT = new Framework.Components.DataAccess.DBDataTable("Get List", sql, DataStoreKey);
if (oDT.DBTable != null)
{
count = oDT.DBTable.Rows.Count;
}
if (count > 0)
{
return(count);
}
}
if (categoryId != null)
{
// Get Child Category Count
var data = new Category.Data();
data.ParentCategoryId = categoryId;
var dtCategory = Category.Search(data);
if (dtCategory != null && dtCategory.Rows.Count > 0)
{
count = dtCategory.Rows.Count;
return(count);
}
// Get Child Object Items Count
var dataItem = new CategoryItem.Data();
dataItem.CategoryId = categoryId;
var dtCategoryItem = CategoryItem.Search(dataItem);
if (dtCategoryItem != null && dtCategoryItem.Rows.Count > 0)
{
count = dtCategoryItem.Rows.Count;
}
}
return(count);
}
private int GetTableRowCount(string tableName)
{
var result = 0;
var sql = "SELECT so.name, [RowCount] = MAX(si.rows) FROM sysobjects so, sysindexes si WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name) and so.name = 'category' GROUP BY so.name ORDER BY 2 DESC";
var oDT = new Framework.Components.DataAccess.DBDataTable("Get List", sql, Database);
if (oDT.DBTable != null && oDT.DBTable.Rows.Count > 0)
{
result = Convert.ToInt32(oDT.DBTable.Rows[0]["RowCount"]);
}
return(result);
}
public IEnumerable <string> GetDatabaseObjectList()
{
var result = new List <string>();
var sql = "SELECT top 5 name as 'Name' FROM sysobjects"
+ " WHERE type = 'P' ";
var oDT = new Framework.Components.DataAccess.DBDataTable("Search", sql, "Configuration");
result = oDT.DBTable.AsEnumerable().Select(a => a["Name"].ToString()).ToList();
return(result);
}
private void toolStripMenuItemClass_Click(object sender, EventArgs e)
{
var strHelperClass = string.Empty;
var strClass = string.Empty;
if (tNodeNewGrpClicked != null)
{
var tableName = tNodeNewGrpClicked.Text.Split(new char[] { '(' })[0];
if (!string.IsNullOrEmpty(tableName))
{
var sql = "SELECT column_Name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" + tableName + "' ";
Framework.Components.DataAccess.StartUp.EntryPoint(path, timeout, Database);
var oDT = new Framework.Components.DataAccess.DBDataTable("Get Columns", sql, Database);
if (oDT != null && oDT.DBTable.Rows.Count > 0)
{
GeneratedHelperClass = ClassGenerator.ConstructHelperClass(tableName, oDT.DBTable);
GeneratedClass = ClassGenerator.ConstructClass(tableName, oDT.DBTable);
ClassGenerated(this, new EventArgs());
}
}
}
}
private string GetDragText(string objName, string objType)
{
var resultText = objName;
if (objType == "table")
{
resultText = "SELECT * FROM dbo." + objName;
}
else if (objType == "proc")
{
var sql = "sp_helptext 'dbo." + objName + "'";
var oDT = new Framework.Components.DataAccess.DBDataTable("Get List", sql, Database);
var procText = new StringBuilder();
if (oDT.DBTable != null)
{
foreach (DataRow dr in oDT.DBTable.Rows)
{
procText.Append(Convert.ToString(dr["Text"]));
}
}
resultText = procText.ToString();
}
return(resultText);
}
private void CreatChildNodes(UltraTreeNode tNode)
{
try
{
var sql = string.Empty;
var nodeTag = string.Empty;
var isRootNode = false;
var imgIndex = 0;
if (tNode.Tag == "tables")
{
//sql = "select name, (SELECT SUM (row_count) FROM sys.dm_db_partition_stats WHERE object_id=s.id) as 'rowcount' from sysobjects s WHERE xtype='u' ORDER BY name";
sql = "SELECT so.name, [RowCount] = MAX(si.rows) FROM sysobjects so, sysindexes si WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name) GROUP BY so.name ORDER BY 1";
nodeTag = "table";
isRootNode = true;
imgIndex = 2;
}
else if (tNode.Tag == "procs")
{
sql = "select name from sysobjects where xtype='p' ORDER BY name";
nodeTag = "proc";
isRootNode = true;
imgIndex = 3;
}
else if (tNode.Tag == "views")
{
sql = "select name from sysobjects where xtype='v' ORDER BY name";
nodeTag = "view";
isRootNode = true;
imgIndex = 4;
}
else if (tNode.Tag == "functions")
{
sql = "select name from sysobjects where xtype='fn' ORDER BY name";
nodeTag = "function";
isRootNode = true;
imgIndex = 5;
}
var isCleared = false;
var initCount = tNode.Nodes.Count;
if (isRootNode && tNode.Nodes.Count == 1)
{
tNode.Nodes.Clear();
isCleared = true;
Framework.Components.DataAccess.StartUp.EntryPoint(path, timeout, Database);
var oDT = new Framework.Components.DataAccess.DBDataTable("Get List", sql, Database);
if (oDT.DBTable != null && oDT.DBTable.Rows.Count > 0)
{
var categoryItemList = CategoryItem.GetList();
foreach (DataRow dr in oDT.DBTable.Rows)
{
bool isExist = false;
if (categoryItemList != null && categoryItemList.Rows.Count > 0)
{
var rows = categoryItemList.Select(" Name = '" + Convert.ToString(dr["name"]) + "'");
if (rows.Length > 0)
{
isExist = true;
}
}
if (!tNode.Nodes.Exists(Convert.ToString(dr["name"])) && !isExist)
{
var nodeName = Convert.ToString(dr["name"]);
if (tNode.Tag == "tables")
{
nodeName += "(" + Convert.ToString(dr["rowcount"]) + ")";
}
var tmpNode = tNode.Nodes.Add(Convert.ToString(dr["name"]), nodeName);
tmpNode.Tag = nodeTag;
tmpNode.AllowCellEdit = AllowCellEdit.Disabled;
tmpNode.Override.NodeAppearance.Image = GetImageIndex(nodeTag);
}
}
}
}
// For Group also and for Root Nodes Also.
if (initCount == 1)
{
if (!isCleared)
{
tNode.Nodes.Clear();
}
// Get Child Category
var data = new Category.Data();
if (tNode.Tag.ToString() == "group")
{
var tmpId = GetKey(tNode.Key);
data.ParentCategoryId = Convert.ToInt32(tmpId);
}
else
{
data.ParentCategoryId = Convert.ToInt32(tNode.Key);
}
var dtCategory = Category.Search(data);
if (dtCategory != null && dtCategory.Rows.Count > 0)
{
foreach (DataRow dr in dtCategory.Rows)
{
if (!tNode.Nodes.Exists(Convert.ToString(dr["name"])))
{
var tmpNode = tNode.Nodes.Add(Convert.ToString(dr["CategoryId"]) + "$$group", Convert.ToString(dr["Name"]));
tmpNode.Tag = "group";
tmpNode.Override.NodeAppearance.Image = GetImageIndex(tmpNode.Tag.ToString());
int childCount = GetChildCount("group", Convert.ToInt32(dr["CategoryId"]));
if (childCount > 0)
{
tmpNode.Nodes.Add("Sample Group" + tmpNode.Key, "Sample Group");
}
}
}
}
// Get Child Object Items
var dataItem = new CategoryItem.Data();
if (tNode.Tag.ToString() == "group")
{
var tmpId = GetKey(tNode.Key);
dataItem.CategoryId = Convert.ToInt32(tmpId);
}
else
{
dataItem.CategoryId = Convert.ToInt32(tNode.Key);
}
var dtCategoryItem = CategoryItem.Search(dataItem);
if (dtCategoryItem != null && dtCategoryItem.Rows.Count > 0)
{
foreach (DataRow dr in dtCategoryItem.Rows)
{
var tag = Convert.ToString(dr["ItemType"]).ToLower();
var name = Convert.ToString(dr["Name"]);
if (tag == "table")
{
name = name + "(" + GetTableRowCount(name) + ")";
}
var tmpNode = tNode.Nodes.Add(Convert.ToString(dr["CategoryItemId"]) + "$$" + tag, name);
tmpNode.Tag = tag;
tmpNode.Override.NodeAppearance.Image = GetImageIndex(Convert.ToString(dr["ItemType"]).ToLower());
}
}
}
//toolStripStatusLabelExecute.Text = "Objects Listed Successfully";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private int GetChildCount(string objType, int?categoryId)
{
int count = 0;
var sql = string.Empty;
if (objType != "group")
{
if (objType == "table")
{
sql = "select * from sysobjects where xtype='u' ORDER BY name";
}
else if (objType == "proc")
{
sql = "select * from sysobjects where xtype='p' ORDER BY name";
}
else if (objType == "view")
{
sql = "select * from sysobjects where xtype='v' ORDER BY name";
}
else if (objType == "function")
{
sql = "select * from sysobjects where xtype='fn' ORDER BY name";
}
var localConnectionString = ConfigurationManager.ConnectionStrings[Database];
if (localConnectionString == null)
{
AddNewConnectionString(Server, Database, User, Password);
}
var oDT = new Framework.Components.DataAccess.DBDataTable("Get List", sql, Database);
if (oDT.DBTable != null)
{
count = oDT.DBTable.Rows.Count;
}
if (count > 0)
{
return(count);
}
}
if (categoryId != null)
{
// Get Child Category Count
var data = new Category.Data();
data.ParentCategoryId = categoryId;
var dtCategory = Category.Search(data);
if (dtCategory != null && dtCategory.Rows.Count > 0)
{
count = dtCategory.Rows.Count;
return(count);
}
// Get Child Object Items Count
var dataItem = new CategoryItem.Data();
dataItem.CategoryId = categoryId;
var dtCategoryItem = CategoryItem.Search(dataItem);
if (dtCategoryItem != null && dtCategoryItem.Rows.Count > 0)
{
count = dtCategoryItem.Rows.Count;
}
}
return(count);
}
