private List <UserDefProcedures> GetUserDefViews(string DBName)
{
ExecuteStoredProcedure _esp = new ExecuteStoredProcedure();
List <UserDefProcedures> _UserDefViews = new List <UserDefProcedures>();
string CommandText =
"SELECT "
+ " Object_id, "
+ " Name "
+ "FROM sys.views "
+ "WHERE is_ms_shipped = 0 "
+ "ORDER BY Name ";
SqlConnection conn = GetDBConnection(DBName);
if (conn == null)
{
return(null);
}
List <UserDefProcedures> _UserDefStProcedures = new List <UserDefProcedures>();
DataSet UserDefViews = _esp.ExecuteSQLTextDS(CommandText, conn, "UserDefViews");
_UserDefViews = Db.FillGenObjectDS <UserDefProcedures, DataSet>(new UserDefProcedures(), UserDefViews);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
return(_UserDefViews);
}
public void List_TableColumns(TreeNode Table)
{
ExecuteStoredProcedure _esp = new ExecuteStoredProcedure();
SqlConnection conn;
string TableName = Table.Parent.Text;
string CommandText =
"SELECT DISTINCT"
+ " so.Name AS TableName, " // TableName
+ " sc.Name AS ColumnName, " // Columns
+ " st.name AS DataType, " // Data Type
+ " sc.[Length] AS Data_Length " // Data Lengths
+ "FROM sys.sysobjects AS SO "
+ "JOIN sys.syscolumns AS SC "
+ " ON so.ID = SC.ID "
+ "JOIN sys.systypes AS ST "
+ " ON sc.xtype = st.xtype "
+ "WHERE sc.ID = OBJECT_ID('" + TableName + "')"
+ " AND st.Name != 'sysname';";
List <TableColumnInfo> _TableColumns = new List <TableColumnInfo>();
conn = GetDBConnection(SelectedDB, true);
DataSet TableColumns = _esp.ExecuteSQLTextDS(CommandText, conn, "TableColumns");
_TableColumns = Db.FillGenObjectDS <TableColumnInfo, DataSet>(new TableColumnInfo(), TableColumns);
foreach (TableColumnInfo Column in _TableColumns)
{
Table.Nodes.Add(Column.ColumnName);
Debug.WriteLine(Column.ColumnName);
}
}
private List <UserDefViews> GetUserDefViews(string DBName)
{
ExecuteStoredProcedure _esp = new ExecuteStoredProcedure();
SqlConnection conn; // = GetDBConnection(DBName);
string CommandText =
"SELECT "
+ " Object_id, "
+ " Name "
+ "FROM sys.views "
+ "WHERE is_ms_shipped = 0 "
+ "ORDER BY Name ";
//if (!(SelectedDatabaseInstance == null))
//{
// conn = SelectedDatabaseInstance.ConnectionContext.SqlConnectionObject;
//}
//else
//{
conn = GetDBConnection(DBName, true);
//}
if (conn == null)
{
return(null);
}
List <UserDefViews> _UserDefViews = new List <UserDefViews>();
DataSet UserDefViewsDS = _esp.ExecuteSQLTextDS(CommandText, conn, "UserDefViews");
_UserDefViews = Db.FillGenObjectDS <UserDefViews, DataSet>(new UserDefViews(), UserDefViewsDS);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
return(_UserDefViews);
}
public string GetRowCount(string TableName)
{
ExecuteStoredProcedure _esp = new ExecuteStoredProcedure();
List <TableRowCount> _TableRowCount = new List <TableRowCount>();
string CommandText =
"SELECT "
+ " SCHEMA_NAME(schema_id) AS [SchemaName], "
+ " [Tables].name AS [TableName], "
+ " CAST(SUM([Partitions].[rows]) AS INT) AS [TotalRowCount] "
+ "FROM sys.tables AS [Tables] "
+ " JOIN sys.partitions AS [Partitions] "
+ " ON [Tables].[object_id] = [Partitions].[object_id] "
+ " AND [Partitions].index_id IN (0, 1) "
+ "WHERE [Tables].name = '" + TableName + "' "
+ "GROUP BY "
+ " SCHEMA_NAME(schema_id), "
+ " [Tables].name";
SqlConnection conn = GetDBConnection(SelectedDB, true);
if (conn == null)
{
return(null);
}
DataSet DS_TableRowCount = _esp.ExecuteSQLTextDS(CommandText, conn, "TableRowCount");
_TableRowCount = Db.FillGenObjectDS <TableRowCount, DataSet>(new TableRowCount(), DS_TableRowCount);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
return(string.Format("Table: {0} Rows: {1}", _TableRowCount[0].TableName.ToString(), _TableRowCount[0].TotalRowCount.ToString()));
}