using System;
using System.Collections;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data;
using System.Data.SqlClient;
using ObjectHelper.DBObjectType;
namespace ObjectHelper
{
public delegate void ObjectFetchedEventHandler(object sender, FetchEventArgs e);
public class ObjectDb
{
readonly string _connString;
private SqlDatabase _sqlDatabase;
Hashtable _hsResultSets = new Hashtable();
public ObjectDb(string connString)
{
_connString = connString;
_tables = new List<Table>();
}
public event ObjectFetchedEventHandler ObjectFetched;
protected virtual void OnObjectFetched(FetchEventArgs e)
{
if (ObjectFetched != null)
{
ObjectFetched(this,e);
}
}
private List<Table> _tables = new List<Table>();
private List<Index> _indexes = new List<Index>();
private List<Trigger> _ddlTriggers = new List<Trigger>();
private List<Trigger> _dmlTriggers = new List<Trigger>();
private List<ClrTrigger> _clrTriggers = new List<ClrTrigger>();
private List<StoredProcedure> _storedProcedures = new List<StoredProcedure>();
private List<View> _views = new List<View>();
private List<Principal> _appRoles = new List<Principal>();
private List<Principal> _databaseRoles = new List<Principal>();
private List<Principal> _users = new List<Principal>();
private List<Assembly> _assemblies = new List<Assembly>();
private List<Aggregate> _aggregates = new List<Aggregate>();
private List<Default> _defaults = new List<Default>();
private List<Synonym> _synonyms = new List<Synonym>();
private List<XmlSchemaCollection> _xmlSchemaCollections = new List<XmlSchemaCollection>();
private List<MessageType> _messageTypes = new List<MessageType>();
private List<Contract> _contracts = new List<Contract>();
private List<PartitionFunction> _partitionFunctions = new List<PartitionFunction>();
private List<ServiceQueue> _serviceQueues = new List<ServiceQueue>();
private List<FullTextCatalog> _fullTextCatalogs = new List<FullTextCatalog>();
private List<FullTextStopList> _fullTextStopLists = new List<FullTextStopList>();
private List<Service> _services = new List<Service>();
private List<BrokerPriority> _brokerPriorities = new List<BrokerPriority>();
private List<PartitionScheme> _partitionSchemes = new List<PartitionScheme>();
private List<RemoteServiceBinding> _remoteServiceBindings = new List<RemoteServiceBinding>();
private List<DBObjectType.Rule> _rules = new List<DBObjectType.Rule>();
private List<Route> _routes = new List<Route>();
private List<Schema> _schemas = new List<Schema>();
private List<SqlUserDefinedFunction> _sqlUserDefinedFunctions = new List<SqlUserDefinedFunction>();
private List<ClrUserDefinedFunction> _clrUserDefinedFunctions = new List<ClrUserDefinedFunction>();
private List<UserDefinedDataType> _userDefinedDataTypes = new List<UserDefinedDataType>();
private List<UserDefinedType> _userDefinedTypes = new List<UserDefinedType>();
private List<UserDefinedTableType> _userDefinedTableTypes = new List<UserDefinedTableType>();
private List<Dependency> _dependencies =new List<Dependency>();
private List<FullTextIndex> _fullTextIndexes = new List<FullTextIndex>();
public List<FullTextIndex> FullTextIndexes
{
get { return _fullTextIndexes; }
}
public List<Dependency> Dependencies
{
get
{
return _dependencies;
}
}
public List<Assembly> Assemblies
{
get
{
return _assemblies;
}
}
public List<Aggregate> Aggregates
{
get
{
return _aggregates;
}
}
public List<Principal> Users
{
get
{
return _users;
}
}
public List<Principal> ApplicationRoles
{
get
{
return _appRoles;
}
}
public List<Principal> DatabaseRoles
{
get
{
return _databaseRoles;
}
}
public List<Table> Tables
{
get
{
return _tables;
}
}
public List<Index> Indexes
{
get
{
return _indexes;
}
}
public List<Trigger> DdlTriggers
{
get
{
return _ddlTriggers;
}
}
public List<Trigger> DmlTriggers
{
get
{
return _dmlTriggers;
}
}
public List<ClrTrigger> ClrTriggers
{
get
{
return _clrTriggers;
}
}
public List<StoredProcedure> StoredProcedures
{
get
{
return _storedProcedures;
}
}
public List<View> Views
{
get
{
return _views;
}
}
public List<Synonym> Synonyms
{
get
{
return _synonyms;
}
}
public List<Default> Defaults
{
get
{
return _defaults;
}
}
public List<XmlSchemaCollection> XmlSchemaCollections {
get
{
return _xmlSchemaCollections;
}
}
public List<MessageType> MessageTypes
{
get
{
return _messageTypes;
}
}
public List<Contract> Contracts
{
get
{
return _contracts;
}
}
public List<PartitionFunction> PartitionFunctions
{
get {
return _partitionFunctions;
}
}
public List<ServiceQueue> ServiceQueues
{
get {
return _serviceQueues;
}
}
public List<FullTextCatalog> FullTextCatalogs
{
get {
return _fullTextCatalogs;
}
}
public List<FullTextStopList> FullTextStopLists
{
get
{
return _fullTextStopLists;
}
}
public List<Service> Services {
get
{
return _services;
}
}
public List<BrokerPriority> BrokerPriorities {
get {
return _brokerPriorities;
}
}
public List<PartitionScheme> PartitionSchemes
{
get {
return _partitionSchemes;
}
}
public List<RemoteServiceBinding> RemoteServiceBindings
{
get
{
return _remoteServiceBindings;
}
}
public List<DBObjectType.Rule> Rules
{
get
{
return _rules;
}
}
public List<Route> Routes
{
get
{
return _routes;
}
}
public List<Schema> Schemas
{
get
{
return _schemas;
}
}
public List<SqlUserDefinedFunction> SqlUserDefinedFunctions
{
get
{
return _sqlUserDefinedFunctions;
}
}
public List<ClrUserDefinedFunction> ClrUserDefinedFunctions
{
get
{
return _clrUserDefinedFunctions;
}
}
public List<UserDefinedDataType> UserDefinedDataTypes
{
get
{
return _userDefinedDataTypes;
}
}
public List<UserDefinedType> UserDefinedTypes
{
get
{
return _userDefinedTypes;
}
}
public List<UserDefinedTableType> UserDefinedTableTypes
{
get
{
return _userDefinedTableTypes;
}
}
public void FetchObjects(ScriptingOptions so)
{
var generator = new ScriptGenerator();
string sql = generator.GenerateScript(so);
_hsResultSets = generator.ResultSets;
_sqlDatabase = new SqlDatabase(_connString);
var sqlCommand = _sqlDatabase.GetSqlStringCommand(sql) as SqlCommand;
DataSet ds = _sqlDatabase.ExecuteDataSet(sqlCommand);
if (so.ScriptDependencies)
{
DataTable dtDependencies = ds.Tables[int.Parse(_hsResultSets["DependenciesCollection"].ToString())];
_dependencies = GetDependencies(dtDependencies);
}
if (so.Tables)
{
DataTable dtTables = ds.Tables[int.Parse(_hsResultSets["TableCollection"].ToString())];
foreach (DataRow drTable in dtTables.Rows)
{
var table = new Table();
table.AnsiNullsStatus = bool.Parse(drTable["AnsiNullsStatus"].ToString());
table.ChangeTrackingEnabled = bool.Parse(drTable["ChangeTrackingEnabled"].ToString());
table.Description = drTable["Description"].ToString();
table.Filegroup = drTable["FileGroup"].ToString();
table.FileStreamGroup = drTable["FileStreamGroup"].ToString();
table.FileStreamPartitionScheme = drTable["FileStreamPartitionScheme"].ToString();
table.Name = drTable["Name"].ToString();
table.ObjectId = long.Parse(drTable["Object_Id"].ToString());
table.PartitionedColumn = drTable["PartitionedColumn"].ToString();
table.PartitionScheme = drTable["PartitionScheme"].ToString();
table.QuotedIdentifierStatus = bool.Parse(drTable["QuotedIdentifierStatus"].ToString());
table.Schema = drTable["Schema"].ToString();
table.TextFileGroup = drTable["TextFileGroup"].ToString();
table.TrackColumnsUpdatedEnabled = bool.Parse(drTable["TrackColumnsUpdatedEnabled"].ToString());
table.CreateDate = DateTime.Parse(drTable["create_date"].ToString());
table.ModifyDate = DateTime.Parse(drTable["modify_date"].ToString());
var dtColumns = ds.Tables[int.Parse(_hsResultSets["ColumnCollection"].ToString())];
table.Columns = GetColumns(dtColumns, table);
_tables.Add(table);
if (so.DataCompression)
{
if (so.ServerMajorVersion >= 10)
{
var dtTableDataCompression = ds.Tables[int.Parse(_hsResultSets["TableDataCompressionCollection"].ToString())];
table.Partitions = GetTableDataCompression(dtTableDataCompression, table.ObjectId);
}
}
if (so.DefaultConstraints)
{
var dtDefaultConstraints = ds.Tables[int.Parse(_hsResultSets["DefaultConstraintCollection"].ToString())];
table.DefaultConstraints = GetDefaultConstraints(dtDefaultConstraints, table.ObjectId);
}
if (so.CheckConstraints)
{
var dtCheckConstraints = ds.Tables[int.Parse(_hsResultSets["CheckConstraintCollection"].ToString())];
table.CheckConstraints = GetCheckConstraints(dtCheckConstraints, table.ObjectId);
}
DataTable dtIndexColumns=null;
if (so.PrimaryKeys || so.UniqueConstraints || so.ClusteredIndexes || so.NonClusteredIndexes)
{
dtIndexColumns = ds.Tables[int.Parse(_hsResultSets["IndexColumnCollection"].ToString())];
}
if (so.PrimaryKeys)
{
var dtPrimaryKey = ds.Tables[int.Parse(_hsResultSets["IndexCollection"].ToString())];
table.PrimaryKeyConstraint = GetPrimaryKeyConstraint(dtPrimaryKey, dtIndexColumns, table.ObjectId);
}
if (so.UniqueConstraints)
{
var dtUniqueConstraints = ds.Tables[int.Parse(_hsResultSets["IndexCollection"].ToString())];
table.UniqueConstraints = GetUniqueConstraints(dtUniqueConstraints, dtIndexColumns, table.ObjectId);
}
if (so.ForeignKeys)
{
var dtForeignKeyConstraints = ds.Tables[int.Parse(_hsResultSets["ForeignKeyCollection"].ToString())];
var dtForeignKeyColumns = ds.Tables[int.Parse(_hsResultSets["ForeignKeyColumnCollection"].ToString())];
table.ForeignKeys = GetForeignKeys(dtForeignKeyConstraints,dtForeignKeyColumns, table);
}
OnObjectFetched( new FetchEventArgs(table));
}
}
if (so.FullTextIndexes)
{
var dtFulltextIndexes = ds.Tables[int.Parse(_hsResultSets["FullTextIndexCollection"].ToString())];
var dtFulltextIndexColumns = ds.Tables[int.Parse(_hsResultSets["FullTextIndexColumnCollection"].ToString())];
//table.FullTextIndexes = GetFullTextIndexes(dtFulltextIndexes, dtFulltextIndexColumns,table.ObjectId);
_fullTextIndexes.AddRange(GetFullTextIndexes(dtFulltextIndexes, dtFulltextIndexColumns));
}
if (so.MessageTypes)
{
var dtMessageTypes = ds.Tables[int.Parse(_hsResultSets["MessageTypeCollection"].ToString())];
_messageTypes = GetMessageTypes(dtMessageTypes);
}
if (so.Contracts)
{
var dtContracts = ds.Tables[int.Parse(_hsResultSets["ContractCollection"].ToString())];
var dtMsgTypes = ds.Tables[int.Parse(_hsResultSets["ContractMessageTypeCollection"].ToString())];
_contracts = GetContracts(dtContracts,dtMsgTypes);
}
if (so.XmlSchemaCollections)
{
var dtXmlSchemaCollections = ds.Tables[int.Parse(_hsResultSets["XMLSchemaCollection"].ToString())];
_xmlSchemaCollections = GetXmlSchemaCollections(dtXmlSchemaCollections);
}
if (so.ClusteredIndexes || so.NonClusteredIndexes)
{
var dtIndexColumns = ds.Tables[int.Parse(_hsResultSets["IndexColumnCollection"].ToString())];
var dtIndexes = ds.Tables[int.Parse(_hsResultSets["IndexCollection"].ToString())];
_indexes = new List<Index>();
if(so.ClusteredIndexes)
{
var idxs = GetIndexes(dtIndexes, dtIndexColumns, "CLUSTERED");
foreach(var idx in idxs)
{
_indexes.Add(idx);
}
}
if (so.NonClusteredIndexes)
{
var idxs = GetIndexes(dtIndexes, dtIndexColumns, "NONCLUSTERED");
foreach (var idx in idxs)
{
_indexes.Add(idx);
}
}
}
if (so.DdlTriggers)
{
var dtTriggers = ds.Tables[int.Parse(_hsResultSets["TriggerCollection"].ToString())];
var dtTriggerEvents = ds.Tables[int.Parse(_hsResultSets["TriggerEventCollection"].ToString())];
_ddlTriggers = GetTriggers(dtTriggers,dtTriggerEvents, true);
}
if (so.DmlTriggers)
{
var dtTriggers = ds.Tables[int.Parse(_hsResultSets["TriggerCollection"].ToString())];
var dtTriggerEvents = ds.Tables[int.Parse(_hsResultSets["TriggerEventCollection"].ToString())];
_dmlTriggers = GetTriggers(dtTriggers,dtTriggerEvents, false);
}
DataTable dtParameters = null;
if (so.Aggregates || so.StoredProcedures || so.SqlUserDefinedFunctions||so.ClrUserDefinedFunctions )
{
dtParameters = ds.Tables[int.Parse(_hsResultSets["ParameterCollection"].ToString())];
}
if (so.StoredProcedures)
{
var dtSPs = ds.Tables[int.Parse(_hsResultSets["SPCollection"].ToString())];
_storedProcedures = GetStoredProcedures(dtSPs, dtParameters);
}
if (so.Views)
{
var dtViews = ds.Tables[int.Parse(_hsResultSets["ViewCollection"].ToString())];
var dtViewColumns = ds.Tables[int.Parse(_hsResultSets["ViewColumnCollection"].ToString())];
_views = GetViews(dtViews, dtViewColumns);
}
if (so.ClrTriggers)
{
var dtClrTriggers = ds.Tables[int.Parse(_hsResultSets["CLRTriggerCollection"].ToString())];
var dtClrTriggerEvents = ds.Tables[int.Parse(_hsResultSets["TriggerEventCollection"].ToString())];
_clrTriggers = GetClrTriggers(dtClrTriggers,dtClrTriggerEvents);
}
if (so.ApplicationRoles)
{
var dtPrincipals = ds.Tables[int.Parse(_hsResultSets["PrincipalCollection"].ToString())];
_appRoles = GetPrincipals(dtPrincipals,"A");
}
if (so.DatabaseRoles)
{
var dtPrincipals = ds.Tables[int.Parse(_hsResultSets["PrincipalCollection"].ToString())];
_databaseRoles = GetPrincipals(dtPrincipals, "R");
}
if (so.Users)
{
var dtPrincipals = ds.Tables[int.Parse(_hsResultSets["PrincipalCollection"].ToString())];
_users = GetPrincipals(dtPrincipals, "S");
}
if (so.Assemblies)
{
var dtAssemblies = ds.Tables[int.Parse(_hsResultSets["AssemblyCollection"].ToString())];
_assemblies = GetAssemblies(dtAssemblies);
}
if (so.Aggregates)
{
var dtAggregates = ds.Tables[int.Parse(_hsResultSets["AggregateCollection"].ToString())];
_aggregates = GetAggregates(dtAggregates, dtParameters);
}
if (so.Defaults)
{
var dtDefaults = ds.Tables[int.Parse(_hsResultSets["DefaultCollection"].ToString())];
_defaults = GetDefaults(dtDefaults);
}
if (so.Synonyms)
{
var dtSynonyms = ds.Tables[int.Parse(_hsResultSets["SynonymCollection"].ToString())];
_synonyms = GetSynonyms(dtSynonyms);
}
if (so.PartitionFunctions)
{
var dtPartitionFunctions = ds.Tables[int.Parse(_hsResultSets["PartitionFunctionCollection"].ToString())];
var dtPartitionFunctionsRangeValues = ds.Tables[int.Parse(_hsResultSets["PartitionFunctionRangeValuesCollection"].ToString())];
_partitionFunctions = GetPartitionFunctions(dtPartitionFunctions,dtPartitionFunctionsRangeValues);
}
if (so.ServiceQueues)
{
var dtQueues = ds.Tables[int.Parse(_hsResultSets["QueueCollection"].ToString())];
_serviceQueues = GetServiceQueues(dtQueues);
}
if (so.FullTextCatalogs)
{
DataTable dtFtCatalogs = ds.Tables[int.Parse(_hsResultSets["FullTextCatalogCollection"].ToString())];
_fullTextCatalogs = GetFulltextCatalogs(dtFtCatalogs);
}
if (so.FullTextStopLists)
{
if (so.ServerMajorVersion >= 10)
{
DataTable dtStopLists = ds.Tables[int.Parse(_hsResultSets["FullTextStopListCollection"].ToString())];
DataTable dtStopWords = ds.Tables[int.Parse(_hsResultSets["FullTextStopWordCollection"].ToString())];
_fullTextStopLists = GetFullTextStopLists(dtStopLists, dtStopWords);
}
}
if (so.Services)
{
DataTable dtServices = ds.Tables[int.Parse(_hsResultSets["ServiceCollection"].ToString())];
DataTable dtServiceContracts = ds.Tables[int.Parse(_hsResultSets["ServiceContractCollection"].ToString())];
_services = GetServices(dtServices,dtServiceContracts);
}
if (so.BrokerPriorities)
{
if (so.ServerMajorVersion > 9)
{
DataTable dtBrokerPriorities = ds.Tables[int.Parse(_hsResultSets["BrokerPriorityCollection"].ToString())];
_brokerPriorities = GetBrokerPriorities(dtBrokerPriorities);
}
}
if (so.PartitionSchemes)
{
DataTable dtPartitionSchemes = ds.Tables[int.Parse(_hsResultSets["PartitionSchemeCollection"].ToString())];
DataTable dtPartitionSchemeFileGroups = ds.Tables[int.Parse(_hsResultSets["PartitionSchemeFileGroupCollection"].ToString())];
_partitionSchemes = GetPartitionSchemes(dtPartitionSchemes, dtPartitionSchemeFileGroups);
}
if (so.RemoteServiceBindings)
{
DataTable dtBindings = ds.Tables[int.Parse(_hsResultSets["RemoteServiceBindingCollection"].ToString())];
_remoteServiceBindings = GetRemoteServiceBindings(dtBindings);
}
if (so.Rules)
{
DataTable dtRules = ds.Tables[int.Parse(_hsResultSets["RuleCollection"].ToString())];
_rules = GetRules(dtRules);
}
if (so.Routes)
{
DataTable dtRoutes = ds.Tables[int.Parse(_hsResultSets["RouteCollection"].ToString())];
_routes = GetRoutes(dtRoutes);
}
if (so.Schemas)
{
DataTable dtSchemas = ds.Tables[int.Parse(_hsResultSets["SchemaCollection"].ToString())];
_schemas = GetSchemas(dtSchemas);
}
if (so.SqlUserDefinedFunctions)
{
DataTable dtScalarValuedFunctions = ds.Tables[int.Parse(_hsResultSets["UserDefinedFunctionCollection"].ToString())];
DataTable dtSqlFunctionColumns = ds.Tables[int.Parse(_hsResultSets["UserDefinedFunctionColumnCollection"].ToString())];
_sqlUserDefinedFunctions = GetSqlUserDefinedFunctions(dtScalarValuedFunctions, dtParameters, dtSqlFunctionColumns);
}
if(so.ClrUserDefinedFunctions)
{
DataTable dtClrFunctions = ds.Tables[int.Parse(_hsResultSets["CLRUserDefinedFunctionCollection"].ToString())];
DataTable dtClrFunctionColumns = ds.Tables[int.Parse(_hsResultSets["CLRUserDefinedFunctionColumnCollection"].ToString())];
_clrUserDefinedFunctions = GetClrUserDefinedFunctions(dtClrFunctions, dtParameters, dtClrFunctionColumns);
}
if (so.UserDefinedDataTypes)
{
DataTable dtUddts = ds.Tables[int.Parse(_hsResultSets["UserDefinedDataTypeCollection"].ToString())];
_userDefinedDataTypes = GetUserDefinedDataTypes(dtUddts);
}
if (so.UserDefinedTypes)
{
DataTable dtUdts = ds.Tables[int.Parse(_hsResultSets["UserDefinedTypeCollection"].ToString())];
_userDefinedTypes = GetUserDefinedTypes(dtUdts);
}
if (so.UserDefinedTableTypes)
{
DataTable dtUdtts = ds.Tables[int.Parse(_hsResultSets["UserDefinedTableTypeCollection"].ToString())];
DataTable dtUdttsCols = ds.Tables[int.Parse(_hsResultSets["UserDefinedTableTypeColumnCollection"].ToString())];
DataTable dtUdttsIndexes = ds.Tables[int.Parse(_hsResultSets["UserDefinedTableTypeIndexCollection"].ToString())];
DataTable dtUdttsIndexColumns = ds.Tables[int.Parse(_hsResultSets["UserDefinedTableTypeIndexColumnCollection"].ToString())];
DataTable dtUdttsIndexCheckConstraints = ds.Tables[int.Parse(_hsResultSets["UserDefinedTableTypeCheckConstraintCollection"].ToString())];
_userDefinedTableTypes = GetUserDefinedTableTypes(dtUdtts, dtUdttsCols, dtUdttsIndexes, dtUdttsIndexColumns, dtUdttsIndexCheckConstraints);
}
}
private List<Dependency> GetDependencies(DataTable dtTypes)
{
var deps = new List<Dependency>();
var dwDeps = new DataView(dtTypes, "", "Name", DataViewRowState.CurrentRows);
for (var i = 0; i < dwDeps.Count; i++)
{
var dep = new Dependency();
dep.Name = dwDeps[i].Row["Name"].ToString();
dep.Schema = dwDeps[i].Row["Schema"].ToString();
dep.ObjectId = long.Parse(dwDeps[i].Row["ObjectId"].ToString());
dep.Type = dwDeps[i].Row["Type"].ToString();
dep.TypeDescription = dwDeps[i].Row["TypeDescription"].ToString();
dep.ReferencedName = dwDeps[i].Row["ReferencedName"].ToString();
dep.ReferencedSchema = dwDeps[i].Row["ReferencedSchema"].ToString();
dep.ReferencedObjectId = int.Parse(dwDeps[i].Row["ReferencedObjectId"].ToString());
dep.ReferencedType = dwDeps[i].Row["ReferencedType"].ToString();
dep.ReferencedTypeDescription = dwDeps[i].Row["ReferencedTypeDescription"].ToString();
deps.Add(dep);
OnObjectFetched(new FetchEventArgs(dep));
}
return deps;
}
private List<UserDefinedType> GetUserDefinedTypes(DataTable dtTypes)
{
var udts = new List<UserDefinedType>();
var dwUdts = new DataView(dtTypes, "", "Name", DataViewRowState.CurrentRows);
for (var i = 0; i < dwUdts.Count; i++)
{
var udt = new UserDefinedType();
udt.AssemblyClass = dwUdts[i].Row["AssemblyClass"].ToString();
udt.AssemblyName = dwUdts[i].Row["AssemblyName"].ToString();
udt.Name = dwUdts[i].Row["Name"].ToString();
udt.ObjectId = long.Parse(dwUdts[i].Row["ObjectId"].ToString());
udt.Schema = dwUdts[i].Row["Schema"].ToString();
udt.AssemblyId = int.Parse(dwUdts[i].Row["AssemblyId"].ToString());
var dep = new Dependency
{
ObjectId = udt.ObjectId,
Name = udt.Name,
Type = "TYPE",
Schema = udt.Schema,
ReferencedObjectId = udt.AssemblyId,
ReferencedName = udt.AssemblyName,
ReferencedType = "AS",
ReferencedTypeDescription = "ASSEMBLY"
};
_dependencies.Add(dep);
udts.Add(udt);
OnObjectFetched(new FetchEventArgs(udt));
}
return udts;
}
private List<UserDefinedTableType> GetUserDefinedTableTypes(DataTable dtTypes, DataTable dtColumns,
DataTable dtIndexes, DataTable dtIndexColumns, DataTable dtCheckConstraints)
{
var udtts = new List<UserDefinedTableType>();
var dwUdtts = new DataView(dtTypes, "", "Name", DataViewRowState.CurrentRows);
for (var i = 0; i < dwUdtts.Count; i++)
{
var udtt = new UserDefinedTableType();
udtt.Name = dwUdtts[i].Row["Name"].ToString();
udtt.ObjectId = long.Parse(dwUdtts[i].Row["ObjectId"].ToString());
udtt.Schema = dwUdtts[i].Row["Schema"].ToString();
udtt.Description = dwUdtts[i].Row["Description"].ToString();
var dwColumns = new DataView(dtColumns, "ObjectId='" + udtt.ObjectId + "'", "ColumnId", DataViewRowState.CurrentRows);
for (var j = 0; j < dwColumns.Count; j++)
{
var column = new UserDefinedTableTypeColumn();
column.ColumnId = int.Parse(dwColumns[j].Row["ColumnId"].ToString());
column.Collation = dwColumns[j].Row["Collation"].ToString();
column.DataType = dwColumns[j].Row["DataType"].ToString();
column.DataTypeId = int.Parse(dwColumns[j].Row["DataTypeId"].ToString());
column.DefaultValue = dwColumns[j].Row["DefaultValue"].ToString();
column.Definition = dwColumns[j].Row["Definition"].ToString();
column.Identity = bool.Parse(dwColumns[j].Row["Identity"].ToString());
column.IdentityIncrement = int.Parse(dwColumns[j].Row["IdentityIncrement"].ToString());
column.IdentitySeed = int.Parse(dwColumns[j].Row["IdentitySeed"].ToString());
column.IsComputed = bool.Parse(dwColumns[j].Row["IsComputed"].ToString());
column.IsNullable = bool.Parse(dwColumns[j].Row["IsNullable"].ToString());
column.IsPersisted = bool.Parse(dwColumns[j].Row["IsPersisted"].ToString());
column.IsRowGuidCol = bool.Parse(dwColumns[j].Row["IsRowGuidCol"].ToString());
column.IsColumnSet = bool.Parse(dwColumns[j].Row["IsColumnSet"].ToString());
column.Length = int.Parse(dwColumns[j].Row["Length"].ToString());
column.Name = dwColumns[j].Row["Name"].ToString();
column.Precision = int.Parse(dwColumns[j].Row["Precision"].ToString());
column.Scale = int.Parse( dwColumns[j].Row["Scale"].ToString());
if (column.DataType != dwColumns[j].Row["SystemType"].ToString())
{
var dep = new Dependency();
dep.ObjectId = udtt.ObjectId;
dep.Name = udtt.Name;
dep.Schema = udtt.Schema;
dep.Type = "TT";
dep.TypeDescription = "TYPE";
dep.ReferencedObjectId = column.DataTypeId;
dep.ReferencedName = column.DataType;
dep.ReferencedType = "TY";
dep.ReferencedTypeDescription = "TYPE";
_dependencies.Add(dep);
}
udtt.Columns.Add(column);
}
var dwIndexes = new DataView(dtIndexes, "objectid='" + udtt.ObjectId + "'", "indexid", DataViewRowState.CurrentRows);
for (int j = 0; j < dwIndexes.Count; j++)
{
var index = new UserDefinedTableTypeIndex();
index.FilterDefinition = dwIndexes[j].Row["FilterDefinition"].ToString();
index.IgnoreDupKey = bool.Parse(dwIndexes[j].Row["IgnoreDupKey"].ToString());
index.IndexId = int.Parse(dwIndexes[j].Row["IndexId"].ToString());
index.IsPrimaryKey = bool.Parse(dwIndexes[j].Row["IsPrimaryKey"].ToString());
index.IsUnique = bool.Parse(dwIndexes[j].Row["IsUnique"].ToString());
index.Name = dwIndexes[j].Row["Name"].ToString();
index.ObjectId = long.Parse(dwIndexes[j].Row["ObjectId"].ToString());
index.TypeDescription = dwIndexes[j].Row["TypeDescription"].ToString();
var dwIndexColumns = new DataView(dtIndexColumns, "objectid='" + udtt.ObjectId + "' AND indexid=" + index.IndexId, "", DataViewRowState.CurrentRows);
for (int k = 0; k < dwIndexColumns.Count; k++)
{
var idxColumn = new IndexColumn();
idxColumn.Name = dwIndexColumns[k].Row["name"].ToString();
idxColumn.IsDescendingKey = bool.Parse(dwIndexColumns[k].Row["IsDescendingKey"].ToString());
index.Columns.Add(idxColumn);
}
udtt.Indexes.Add(index);
}
var dwCheckConstraints = new DataView(dtCheckConstraints, "parentobjectid='" + udtt.ObjectId + "'","", DataViewRowState.CurrentRows);
for (int j = 0; j < dwCheckConstraints.Count; j++)
{
var checkConstraint = new UserDefinedTableTypeCheckConstraint();
checkConstraint.Name = dwCheckConstraints[j].Row["Name"].ToString();
checkConstraint.Definition = dwCheckConstraints[j].Row["Definition"].ToString();
checkConstraint.ObjectId = long.Parse(dwCheckConstraints[j].Row["objectid"].ToString());
checkConstraint.ParentObjectId = int.Parse(dwCheckConstraints[j].Row["parentobjectid"].ToString());
udtt.CheckConstraints.Add(checkConstraint);
}
udtts.Add(udtt);
OnObjectFetched(new FetchEventArgs(udtt));
}
return udtts;
}
private List<UserDefinedDataType> GetUserDefinedDataTypes(DataTable dtTypes)
{
var uddts = new List<UserDefinedDataType>();
var dwUddts = new DataView(dtTypes, "", "Name", DataViewRowState.CurrentRows);
for (int i = 0; i < dwUddts.Count; i++)
{
var uddt = new UserDefinedDataType();
uddt.IsNullable = bool.Parse(dwUddts[i].Row["Nullable"].ToString());
uddt.Length = int.Parse(dwUddts[i].Row["Length"].ToString());
uddt.MaxLength = int.Parse(dwUddts[i].Row["MaxLength"].ToString());
uddt.Name = dwUddts[i].Row["Name"].ToString();
uddt.ObjectId = long.Parse(dwUddts[i].Row["ObjectId"].ToString());
uddt.Precision = int.Parse(dwUddts[i].Row["Precision"].ToString());
uddt.Scale = int.Parse(dwUddts[i].Row["Scale"].ToString());
uddt.Schema = dwUddts[i].Row["Schema"].ToString();
uddt.SystemType = dwUddts[i].Row["SystemType"].ToString();
uddt.Description = dwUddts[i].Row["Description"].ToString();
uddts.Add(uddt);
OnObjectFetched(new FetchEventArgs(uddt));
}
return uddts;
}
private List<Schema> GetSchemas(DataTable dtSchemas)
{
var schemas = new List<Schema>();
var dwSchemas = new DataView(dtSchemas, "", "Name", DataViewRowState.CurrentRows);
for (var i = 0; i < dwSchemas.Count; i++)
{
var schema = new Schema();
schema.Name = dwSchemas[i].Row["Name"].ToString();
schema.ObjectId = long.Parse(dwSchemas[i].Row["schema_id"].ToString());
schema.Principal = dwSchemas[i].Row["principal"].ToString();
schema.Description = dwSchemas[i].Row["Description"].ToString();
schemas.Add(schema);
OnObjectFetched(new FetchEventArgs(schema));
}
return schemas;
}
private List<Route> GetRoutes(DataTable dtRoutes)
{
var routes = new List<Route>();
var dwRoutes = new DataView(dtRoutes, "", "Name", DataViewRowState.CurrentRows);
for (int i = 0; i < dwRoutes.Count; i++)
{
var route = new Route();
route.Address = dwRoutes[i].Row["Address"].ToString();
route.BrokerInstance = dwRoutes[i].Row["brokerinstance"].ToString();
string lifetime = dwRoutes[i].Row["lifetime"].ToString();
if (lifetime == "")
{
route.LifeTime = 0;
}
else
{
var dtLifeTime = DateTime.Parse(lifetime);
var diffResult = dtLifeTime - DateTime.Now;
route.LifeTime = (int)diffResult.TotalSeconds;
}
route.MirrorAddress = dwRoutes[i].Row["mirroraddress"].ToString();
route.Name = dwRoutes[i].Row["name"].ToString();
route.ObjectId = long.Parse(dwRoutes[i].Row["routeid"].ToString());
route.Principal = dwRoutes[i].Row["principal"].ToString();
route.RemoteService = dwRoutes[i].Row["remoteservicename"].ToString();
routes.Add(route);
OnObjectFetched(new FetchEventArgs(route));
}
return routes;
}
private List<DBObjectType.Rule> GetRules(DataTable dtRules)
{
var rules = new List<DBObjectType.Rule>();
var dwRules = new DataView(dtRules, "", "Name", DataViewRowState.CurrentRows);
for (int i = 0; i < dwRules.Count; i++)
{
var rule = new DBObjectType.Rule();
rule.Definition = dwRules[i].Row["definition"].ToString();
rule.ObjectId = long.Parse(dwRules[i].Row["objectid"].ToString());
rule.Name = dwRules[i].Row["name"].ToString();
rule.Schema = dwRules[i].Row["schema"].ToString();
rules.Add(rule);
OnObjectFetched(new FetchEventArgs(rule));
}
return rules;
}
private List<RemoteServiceBinding> GetRemoteServiceBindings(DataTable dtBindings)
{
var bindings = new List<RemoteServiceBinding>();
var dwBindings = new DataView(dtBindings, "", "Name", DataViewRowState.CurrentRows);
for (int i = 0; i < dwBindings.Count; i++)
{
var binding = new RemoteServiceBinding();
binding.Name = dwBindings[i].Row["name"].ToString();
binding.ObjectId = long.Parse(dwBindings[i].Row["RemoteServiceBindingId"].ToString());
binding.IsAnonymousOn = bool.Parse(dwBindings[i].Row["isanonymouson"].ToString());
binding.Principal = dwBindings[i].Row["Principal"].ToString();
binding.RemoteService = dwBindings[i].Row["remoteservicename"].ToString();
//binding.ServiceContractId = int.Parse(dwBindings[i].Row["servicecontractid"].ToString());
bindings.Add(binding);
OnObjectFetched(new FetchEventArgs(binding));
}
return bindings;
}
private List<PartitionScheme> GetPartitionSchemes(DataTable dtSchemes, DataTable dtFileGroups)
{
var schemes = new List<PartitionScheme>();
var dwSchemes = new DataView(dtSchemes, "", "Name", DataViewRowState.CurrentRows);
for (int i = 0; i < dwSchemes.Count; i++)
{
var scheme = new PartitionScheme();
scheme.Name = dwSchemes[i].Row["Name"].ToString();
scheme.PartitionFunction = dwSchemes[i].Row["PartitionFunction"].ToString();
scheme.PartitionFunctinoId = int.Parse(dwSchemes[i].Row["PartitionFunctionId"].ToString());
var dep = new Dependency();
dep.Name = scheme.Name;
dep.ObjectId = scheme.ObjectId;
dep.Type = "PS";
dep.ReferencedName = scheme.PartitionFunction;
dep.ReferencedType = "PF";
dep.ReferencedObjectId = scheme.PartitionFunctinoId;
_dependencies.Add(dep);
var dwFileGRoups = new DataView(dtFileGroups, "name='" + scheme.Name+"'", "ID", DataViewRowState.CurrentRows);
for (var j = 0; j < dwFileGRoups.Count; j++)
{
scheme.FileGroups.Add(dwFileGRoups[j].Row["FileGroup"].ToString());
}
schemes.Add(scheme);
OnObjectFetched(new FetchEventArgs(scheme));
}
return schemes;
}
private List<BrokerPriority> GetBrokerPriorities(DataTable dtPriorities)
{
var priorities = new List<BrokerPriority>();
var dwPriorities = new DataView(dtPriorities, "", "Name", DataViewRowState.CurrentRows);
for (int i = 0; i < dwPriorities.Count; i++)
{
var priority = new BrokerPriority();
priority.Name = dwPriorities[i].Row["name"].ToString();
priority.ObjectId = long.Parse(dwPriorities[i].Row["ObjectId"].ToString());
priority.Priority = int.Parse(dwPriorities[i].Row["priority"].ToString());
priority.RemoteService = dwPriorities[i].Row["RemoteService"].ToString();
priority.LocalService = dwPriorities[i].Row["LocalService"].ToString();
priority.Contract = dwPriorities[i].Row["Contract"].ToString();
if (dwPriorities[i].Row["LocalServiceId"]!=DBNull.Value)
{
priority.LocalServiceId = int.Parse(dwPriorities[i].Row["LocalServiceId"].ToString());
var dep = new Dependency();
dep.ObjectId = priority.ObjectId;
dep.Name = priority.Name;
dep.Type = "BP";
dep.ReferencedObjectId = (int)priority.LocalServiceId;
dep.ReferencedName = priority.LocalService;
dep.ReferencedType = "SE";
_dependencies.Add(dep);
}
if (dwPriorities[i].Row["ContractId"] != DBNull.Value)
{
priority.ContractId = int.Parse(dwPriorities[i].Row["ContractId"].ToString());
var dep = new Dependency();
dep.ObjectId = priority.ObjectId;
dep.Name = priority.Name;
dep.Type = "BP";
dep.ReferencedObjectId = (int)priority.ContractId;
dep.ReferencedName = priority.Contract;
dep.ReferencedType = "SC";
_dependencies.Add(dep);
}
priorities.Add(priority);
OnObjectFetched(new FetchEventArgs(priority));
}
return priorities;
}
private List<FullTextStopList> GetFullTextStopLists(DataTable dtStopLists, DataTable dtStopWords)
{
var fullTextStopLists = new List<FullTextStopList>();
var dwStopLists = new DataView(dtStopLists, "", "Name", DataViewRowState.CurrentRows);
for (int i = 0; i < dwStopLists.Count; i++)
{
var fullTextStopList = new FullTextStopList();
fullTextStopList.Name = dwStopLists[i].Row["name"].ToString();
fullTextStopList.ObjectId = long.Parse(dwStopLists[i].Row["ObjectId"].ToString());
fullTextStopList.Principal = dwStopLists[i].Row["Principal"].ToString();
var dwStopWords = new DataView(dtStopWords, "stoplistid=" + fullTextStopList.ObjectId, "", DataViewRowState.CurrentRows);
for (int j = 0; j < dwStopWords.Count; j++)
{
var word = new FullTextStopWord();
word.Language = dwStopWords[j].Row["Language"].ToString();
word.StopListId = int.Parse(dwStopWords[j].Row["stoplistid"].ToString());
word.StopWord = dwStopWords[j].Row["stopword"].ToString();
fullTextStopList.StopWords.Add(word);
}
fullTextStopLists.Add(fullTextStopList);
OnObjectFetched(new FetchEventArgs(fullTextStopList));
}
return fullTextStopLists;
}
private List<Service> GetServices(DataTable dtServices, DataTable dtServiceContracts)
{
var services = new List<Service>();
var dwServices = new DataView(dtServices, "", "Name", DataViewRowState.CurrentRows);
for (var i = 0; i < dwServices.Count; i++)
{
var service = new Service();
service.Name = dwServices[i].Row["name"].ToString();
service.ObjectId = long.Parse(dwServices[i].Row["ObjectId"].ToString());
service.Principal = dwServices[i].Row["principal"].ToString();
service.ServiceQueue = dwServices[i].Row["ServiceQueue"].ToString();
service.ServiceQueueSchema = dwServices[i].Row["ServiceQueueSchema"].ToString();
service.ServiceQueueId = int.Parse(dwServices[i].Row["ServiceQueueId"].ToString());
var dwContracts = new DataView(dtServiceContracts, "serviceid=" + service.ObjectId , "", DataViewRowState.CurrentRows);
for (var j = 0; j < dwContracts.Count; j++)
{
service.Contracts.Add(dwContracts[j].Row["name"].ToString());
}
var dep = new Dependency();
dep.ObjectId = service.ObjectId;
dep.Name = service.Name;
dep.Type = "SE";
dep.Description = "SERVICE";
dep.ReferencedObjectId = service.ServiceQueueId;
dep.ReferencedName = service.ServiceQueue;
dep.ReferencedType = "SQ";
dep.ReferencedTypeDescription = "SERVICE_QUEUE";
_dependencies.Add(dep);
services.Add(service);
OnObjectFetched(new FetchEventArgs(service));
}
return services;
}
private List<ServiceQueue> GetServiceQueues(DataTable dtQueues)
{
var queues = new List<ServiceQueue>();
var dwQueues = new DataView(dtQueues);
for (var i = 0; i < dwQueues.Count; i++)
{
var sq = new ServiceQueue();
sq.ActivationProcedure = dwQueues[i].Row["activationprocedure"].ToString();
sq.IsActivationEnabled = bool.Parse(dwQueues[i].Row["isactivationenabled"].ToString());
sq.IsEnqueueEnabled = bool.Parse(dwQueues[i].Row["isenqueueenabled"].ToString());
sq.IsReceiveEnabled = bool.Parse(dwQueues[i].Row["isreceiveenabled"].ToString());
sq.IsRetentionEnabled = bool.Parse(dwQueues[i].Row["isretentionenabled"].ToString());
sq.MaxReaders = int.Parse(dwQueues[i].Row["maxreaders"].ToString());
sq.Name = dwQueues[i].Row["name"].ToString();
sq.ObjectId = long.Parse(dwQueues[i].Row["objectid"].ToString());
sq.Principal = dwQueues[i].Row["Principal"].ToString();
sq.ExecuteAsPrincipalId = dwQueues[i].Row["ExecuteAsPrincipalId"].ToString();
sq.Schema = dwQueues[i].Row["Schema"].ToString();
sq.FileGroup = dwQueues[i].Row["FileGroup"].ToString();
sq.ActivationProcedureSchema = dwQueues[i].Row["ActivationProcedureSchema"].ToString();
if (dwQueues[i].Row["activationprocedureid"] != DBNull.Value)
{
if (dwQueues[i].Row["activationprocedureid"] != DBNull.Value)
{
sq.ActivationProcedureId = int.Parse(dwQueues[i].Row["activationprocedureid"].ToString());
var dep = new Dependency();
dep.ObjectId = sq.ObjectId;
dep.Name = sq.Name;
dep.Type = "SQ";
dep.TypeDescription = "SERVICE_QUEUE";
dep.ReferencedObjectId = sq.ActivationProcedureId;
dep.ReferencedName = sq.ActivationProcedure;
dep.ReferencedType = "P";
dep.ReferencedSchema = dwQueues[i].Row["activationprocedureschema"].ToString();
dep.ReferencedTypeDescription = "SQL_STORED_PROCEDURE";
_dependencies.Add(dep);
}
}
queues.Add(sq);
OnObjectFetched(new FetchEventArgs(sq));
}
return queues;
}
private List<FullTextCatalog> GetFulltextCatalogs(DataTable dtFtCatalogs)
{
var ftCatalogs = new List<FullTextCatalog>();
var dwFtCatalogs = new DataView(dtFtCatalogs);
for (int i = 0; i < dwFtCatalogs.Count; i++)
{
var ftCatalog = new FullTextCatalog();
ftCatalog.FileGroup = dwFtCatalogs[i].Row["FileGroup"].ToString();
ftCatalog.IsAccentSensitive = bool.Parse(dwFtCatalogs[i].Row["Is_Accent_Sensitivity_on"].ToString());
ftCatalog.IsDefault = bool.Parse(dwFtCatalogs[i].Row["Is_Default"].ToString());
ftCatalog.Name = dwFtCatalogs[i].Row["Name"].ToString();
ftCatalog.ObjectId = long.Parse(dwFtCatalogs[i].Row["fulltext_catalog_id"].ToString());
ftCatalog.Path = dwFtCatalogs[i].Row["Path"].ToString();
ftCatalog.Principal = dwFtCatalogs[i].Row["Principal"].ToString();
ftCatalogs.Add(ftCatalog);
OnObjectFetched(new FetchEventArgs(ftCatalog));
}
return ftCatalogs;
}
private List<PartitionFunction> GetPartitionFunctions(DataTable dtPartitionFunctions, DataTable dtPartitionFunctionsRangeValues)
{
var partitionFunctions = new List<PartitionFunction>();
var dwFunctions = new DataView(dtPartitionFunctions, "", "Name", DataViewRowState.CurrentRows);
for (var i = 0; i < dwFunctions.Count; i++)
{
var partitionFunction = new PartitionFunction();
partitionFunction.Name = dwFunctions[i].Row["name"].ToString();
partitionFunction.ObjectId = long.Parse(dwFunctions[i].Row["function_id"].ToString());
partitionFunction.Type = dwFunctions[i].Row["Type"].ToString(); //boundary_value_on_right
partitionFunction.BoundaryValueOnRight = bool.Parse(dwFunctions[i].Row["boundary_value_on_right"].ToString());
partitionFunction.MaxLength = int.Parse(dwFunctions[i].Row["max_length"].ToString());
partitionFunction.Precision = int.Parse(dwFunctions[i].Row["precision"].ToString());
partitionFunction.Scale = int.Parse(dwFunctions[i].Row["scale"].ToString());
var dwRAngeValues = new DataView(dtPartitionFunctionsRangeValues, "function_id=" + partitionFunction.ObjectId + "", "boundary_id", DataViewRowState.CurrentRows);
for (var j = 0; j < dwRAngeValues.Count; j++)
{
partitionFunction.RangeValues.Add(int.Parse(dwRAngeValues[j].Row["boundary_Id"].ToString()),dwRAngeValues[j].Row["value"].ToString());
}
partitionFunctions.Add(partitionFunction);
OnObjectFetched(new FetchEventArgs(partitionFunction));
}
return partitionFunctions;
}
private List<FullTextIndex> GetFullTextIndexes(DataTable dtFtIndexes, DataTable columns)
{
var ftIndexes = new List<FullTextIndex>();
var dwFtIndexes = new DataView(dtFtIndexes, "" ,"", DataViewRowState.CurrentRows);
for (var i = 0; i < dwFtIndexes.Count; i++)
{
var ftIndex = new FullTextIndex();
ftIndex.ChangeTrackingState = dwFtIndexes[i].Row["change_tracking_state_desc"].ToString();
ftIndex.Filegroup = dwFtIndexes[i].Row["FileGroup"].ToString();
ftIndex.FullTextCatalog = dwFtIndexes[i].Row["FullTextCatalog"].ToString();
ftIndex.KeyIndex = dwFtIndexes[i].Row["IndexName"].ToString();
ftIndex.ObjectId = //long.Parse(dwFtIndexes[i].Row["ParentObjectId"].ToString());
long.Parse(dwFtIndexes[i].Row["ParentObjectId"] + "" + dwFtIndexes[i].Row["Index_Id"]);
ftIndex.IndexId = int.Parse(dwFtIndexes[i].Row["index_id"].ToString());
//index_id
ftIndex.ParentObjectName = dwFtIndexes[i].Row["ParentObjectName"].ToString();
ftIndex.Schema = dwFtIndexes[i].Row["Schema"].ToString();
ftIndex.Name = dwFtIndexes[i].Row["IndexName"].ToString();
ftIndex.ParentObjectId = int.Parse(dwFtIndexes[i].Row["ParentObjectId"].ToString());
var dwColumns = new DataView(columns, "object_id=" + ftIndex.ParentObjectId , "", DataViewRowState.CurrentRows);
var dep = new Dependency();
if (dwFtIndexes[i].Row["FullTextCatalogId"]!=DBNull.Value)
{
ftIndex.FullTextCatalogId = int.Parse(dwFtIndexes[i].Row["FullTextCatalogId"].ToString());
dep.ObjectId = ftIndex.ObjectId;
dep.Name = ftIndex.Name;
dep.Schema = ftIndex.Schema;
dep.Type = "FI";
dep.ReferencedName = ftIndex.FullTextCatalog;
dep.ReferencedObjectId = ftIndex.FullTextCatalogId;
dep.ReferencedType = "FC";
_dependencies.Add(dep);
}
if (dwFtIndexes[i].Row["StopList"] != DBNull.Value)
{
ftIndex.StopListId = int.Parse(dwFtIndexes[i].Row["StoplistId"].ToString());
ftIndex.StopList = dwFtIndexes[i].Row["StopList"].ToString();
dep.ObjectId = ftIndex.ObjectId;
dep.Name = ftIndex.Name;
dep.Schema = ftIndex.Schema;
dep.Type = "FI";
dep.ReferencedName = ftIndex.StopList;
dep.ReferencedObjectId = ftIndex.StopListId;
dep.ReferencedType = "SL";
_dependencies.Add(dep);
}
dep = new Dependency();
dep.ObjectId = ftIndex.ObjectId;
dep.Name = ftIndex.Name;
dep.Schema = ftIndex.Schema;
dep.Type = "FI";
dep.ReferencedName = ftIndex.ParentObjectName;
dep.ReferencedObjectId = ftIndex.ParentObjectId;
dep.ReferencedSchema = ftIndex.Schema;
dep.ReferencedType = "U";
_dependencies.Add(dep);
for (var j = 0; j < dwColumns.Count; j++)
{
var column = new FulltextIndexColumn();
column.ColumnName = dwColumns[j].Row["ColumnName"].ToString();
column.LanguageId = int.Parse(dwColumns[j].Row["language_id"].ToString());
column.ObjectId = int.Parse(dwColumns[j].Row["object_id"].ToString());
column.TypeColumnName = dwColumns[j].Row["TypeColumnName"].ToString();
ftIndex.Columns.Add(column);
}
ftIndexes.Add(ftIndex);
OnObjectFetched(new FetchEventArgs(ftIndex));
}
return ftIndexes;
}
private static List<DBObjectType.ForeignKeyConstraint> GetForeignKeys(DataTable dtFk, DataTable columns, Table table)
{
var fks = new List<DBObjectType.ForeignKeyConstraint>();
var dwFKs = new DataView(dtFk, "parent_object_id=" + table.ObjectId + "", "Name", DataViewRowState.CurrentRows);
for (var i = 0; i < dwFKs.Count; i++)
{
var fk = new DBObjectType.ForeignKeyConstraint();
fk.Name = dwFKs[i].Row["name"].ToString();
fk.ObjectId = long.Parse(dwFKs[i].Row["object_id"].ToString());
fk.Schema = dwFKs[i].Row["schemaname"].ToString();
fk.ParentObjectId = int.Parse(dwFKs[i].Row["parent_object_id"].ToString());
fk.IsDisabled = bool.Parse(dwFKs[i].Row["is_disabled"].ToString());
fk.IsNotForReplication = bool.Parse(dwFKs[i].Row["is_not_for_replication"].ToString());
fk.IsNotTrusted = bool.Parse(dwFKs[i].Row["is_not_trusted"].ToString());
fk.DeleteAction = dwFKs[i].Row["delete_referential_action_desc"].ToString();
fk.UpdateAction = dwFKs[i].Row["update_referential_action_desc"].ToString();
fk.Description = dwFKs[i].Row["Description"].ToString();
var dwColumns = new DataView(columns, "constraint_object_id=" + fk.ObjectId + "", "", DataViewRowState.CurrentRows);
for (var j = 0; j < dwColumns.Count; j++)
{
var fkc = new ForeingKeyColumns();
fkc.ConstraintColumnId = int.Parse(dwColumns[j].Row["constraint_column_id"].ToString());
fkc.ConstraintObjectId = int.Parse(dwColumns[j].Row["constraint_object_id"].ToString());
fkc.ParentColumnName = dwColumns[j].Row["ParentColumnName"].ToString();
fkc.ReferenceColumnName = dwColumns[j].Row["ReferencedColumnName"].ToString();
fkc.ReferencedObjectName = dwColumns[j].Row["ReferencedObjectName"].ToString();
fkc.ReferencedObjectId = int.Parse( dwColumns[j].Row["ReferencedObjectId"].ToString());
fkc.ReferencedObjectSchema = dwColumns[j].Row["ReferencedObjectSchema"].ToString();
var dep = new Dependency();
dep.Name = "";
dep.ObjectId = table.ObjectId;
dep.Schema = table.Schema;
dep.Type = "U";
dep.ReferencedName = fkc.ReferencedObjectName;
dep.ReferencedObjectId = fkc.ReferencedObjectId;
dep.ReferencedSchema = fkc.ReferencedObjectSchema;
dep.ReferencedType = "U";
fk.Columns.Add(fkc);
}
fks.Add(fk);
//OnObjectFetched(new FetchEventArgs(fk));
}
return fks;
}
private List<Contract> GetContracts(DataTable dtContracts, DataTable dtMsgTypes)
{
var contracts = new List<Contract>();
var dwContracts = new DataView(dtContracts, "", "Name", DataViewRowState.CurrentRows);
for (var i = 0; i < dwContracts.Count; i++)
{
var contract = new Contract();
contract.Name = dwContracts[i].Row["name"].ToString();
contract.ObjectId = long.Parse(dwContracts[i].Row["service_contract_id"].ToString());
contract.PrincipalName = dwContracts[i].Row["PrincipalName"].ToString();
var dwContractMessageTypes = new DataView(dtMsgTypes, "service_contract_id=" + contract.ObjectId + "", "", DataViewRowState.CurrentRows);
for (var j = 0; j < dwContractMessageTypes.Count; j++)
{
var cmt = new ContractMessageType();
cmt.ServiceMessageTypeId = int.Parse(dwContractMessageTypes[j].Row["message_type_id"].ToString());
cmt.ServiceContractId = int.Parse(dwContractMessageTypes[j].Row["service_contract_id"].ToString());
cmt.Name = dwContractMessageTypes[j].Row["Name"].ToString();
cmt.IsSentByInitiator = bool.Parse(dwContractMessageTypes[j].Row["is_sent_by_initiator"].ToString());
cmt.IsSentByTarget = bool.Parse(dwContractMessageTypes[j].Row["is_sent_by_target"].ToString());
var dep = new Dependency();
dep.Name = contract.Name;
dep.ObjectId = contract.ObjectId;
dep.Type = "SC";
dep.ReferencedName = cmt.Name;
dep.ReferencedObjectId = cmt.ServiceMessageTypeId;
dep.ReferencedType = "MT";
_dependencies.Add(dep);
contract.MessageTypes.Add(cmt);
}
contracts.Add(contract);
OnObjectFetched(new FetchEventArgs(contract));
}
return contracts;
}
private List<MessageType> GetMessageTypes(DataTable dtMessageTypes)
{
var messageTypes = new List<MessageType>();
var dwMessageTypes = new DataView(dtMessageTypes);
for (var i = 0; i < dwMessageTypes.Count; i++)
{
var messageType = new MessageType();
messageType.Name = dwMessageTypes[i].Row["Name"].ToString();
messageType.PrincipalName = dwMessageTypes[i].Row["PrincipalName"].ToString();
messageType.ObjectId = long.Parse(dwMessageTypes[i].Row["message_type_id"].ToString());
messageType.Validation = dwMessageTypes[i].Row["validation_desc"].ToString();
messageType.XmlSchemaCollection = dwMessageTypes[i].Row["XmlSchemaCollection"].ToString();
messageType.XmlSchemaCollectionSchema = dwMessageTypes[i].Row["XmlSchemaCollectionSchema"].ToString();
if (dwMessageTypes[i].Row["XmlSchemaCollection"] != DBNull.Value)
{
messageType.XmlSchemaCollectionId = int.Parse(dwMessageTypes[i].Row["XmlSchemaCollectionId"].ToString());
var dep = new Dependency();
dep.ObjectId = messageType.ObjectId;
dep.Name = messageType.Name;
dep.Type = "MT";
dep.ReferencedName = messageType.XmlSchemaCollection;
dep.ReferencedObjectId = messageType.XmlSchemaCollectionId;
dep.ReferencedSchema = messageType.XmlSchemaCollectionSchema;
dep.ReferencedType = "XS";
_dependencies.Add(dep);
}
messageTypes.Add(messageType);
OnObjectFetched(new FetchEventArgs(messageType));
}
return messageTypes;
}
private List<XmlSchemaCollection> GetXmlSchemaCollections(DataTable dtXmlSchemaCollections)
{
var xmlSchemaCollections = new List<XmlSchemaCollection>();
var dwXmlSchemaCollections = new DataView(dtXmlSchemaCollections);
for (var i = 0; i < dwXmlSchemaCollections.Count; i++)
{
var xmlSchemaCollection = new XmlSchemaCollection();
xmlSchemaCollection.Name = dwXmlSchemaCollections[i].Row["Name"].ToString();
xmlSchemaCollection.Definition = dwXmlSchemaCollections[i].Row["text"].ToString();
xmlSchemaCollection.Schema = dwXmlSchemaCollections[i].Row["Schema"].ToString();
xmlSchemaCollection.ObjectId = long.Parse(dwXmlSchemaCollections[i].Row["id"].ToString());
xmlSchemaCollections.Add(xmlSchemaCollection);
OnObjectFetched(new FetchEventArgs(xmlSchemaCollection));
}
return xmlSchemaCollections;
}
private List<Synonym> GetSynonyms(DataTable dtSynonyms)
{
var synonyms = new List<Synonym>();
var dwSynonyms = new DataView(dtSynonyms);
for (var i = 0; i < dwSynonyms.Count; i++)
{
var synonym = new Synonym();
synonym.Name = dwSynonyms[i].Row["Name"].ToString();
synonym.Definition = dwSynonyms[i].Row["baseobjectname"].ToString();
synonym.Schema = dwSynonyms[i].Row["Schema"].ToString();
synonym.ObjectId = long.Parse(dwSynonyms[i].Row["objectid"].ToString());
synonym.CreateDate = DateTime.Parse(dwSynonyms[i].Row["CreateDate"].ToString());
synonym.ModifyDate = DateTime.Parse(dwSynonyms[i].Row["ModifyDate"].ToString());
synonyms.Add(synonym);
OnObjectFetched(new FetchEventArgs(synonym));
}
return synonyms;
}
private List<Default> GetDefaults(DataTable dtDefaults)
{
var defaults = new List<Default>();
var dwDefaults = new DataView(dtDefaults);
for (int i = 0; i < dwDefaults.Count; i++)
{
var def = new Default();
def.Definition = dwDefaults[i].Row["Definition"].ToString();
def.Name = dwDefaults[i].Row["Name"].ToString();
def.ObjectId = long.Parse( dwDefaults[i].Row["object_id"].ToString());
def.ModifyDate = DateTime.Parse(dwDefaults[i].Row["Modify_date"].ToString());
def.CreateDate = DateTime.Parse(dwDefaults[i].Row["Create_date"].ToString());
def.Schema = dwDefaults[i].Row["SchemaName"].ToString();
def.Description = dwDefaults[i].Row["Description"].ToString();
defaults.Add(def);
OnObjectFetched(new FetchEventArgs(def));
}
return defaults;
}
private List<Aggregate> GetAggregates(DataTable dtAggregates, DataTable dtParameters)
{
var aggregates = new List<Aggregate>();
var dwAggregates = new DataView(dtAggregates);
for (int i = 0; i < dwAggregates.Count; i++)
{
var aggregate = new Aggregate();
aggregate.AssemblyClass = dwAggregates[i].Row["AssemblyClass"].ToString();
aggregate.AssemblyName = dwAggregates[i].Row["AssemblyName"].ToString();
aggregate.Name = dwAggregates[i].Row["Name"].ToString();
aggregate.ObjectId = long.Parse( dwAggregates[i].Row["Objectid"].ToString());
aggregate.Schema = dwAggregates[i].Row["Schema"].ToString();
aggregate.ModifyDate = DateTime.Parse(dwAggregates[i].Row["ModifyDate"].ToString());
aggregate.CreateDate = DateTime.Parse(dwAggregates[i].Row["CreateDate"].ToString());
aggregate.AssemblyId = int.Parse(dwAggregates[i].Row["Assemblyid"].ToString());
var dep = new Dependency();
dep.ObjectId = aggregate.ObjectId;
dep.Name = aggregate.Name;
dep.Schema = aggregate.Schema;
dep.Type = "AF";
dep.ReferencedObjectId = aggregate.AssemblyId;
dep.ReferencedName = aggregate.AssemblyName;
dep.ReferencedType = "AS";
_dependencies.Add(dep);
//List<ObjectHelper.DBObjectType.UniqueConstraint> constraints = new List<ObjectHelper.DBObjectType.UniqueConstraint>();
string rowFilter = "Object_Id=" + aggregate.ObjectId + " and is_output=0";
var dwParameters = new DataView(dtParameters, rowFilter, "Parameter_Id", DataViewRowState.CurrentRows);
for (int j = 0; j < dwParameters.Count; j++)
{
var parameter = new Parameter();
parameter.DefaultValue = dwParameters[j].Row["Default_Value"].ToString();
parameter.IsOutput = bool.Parse(dwParameters[j].Row["is_output"].ToString());
parameter.IsReadOnly = bool.Parse(dwParameters[j].Row["is_readonly"].ToString());
parameter.IsXmlDocument = bool.Parse(dwParameters[j].Row["is_xml_document"].ToString());
parameter.MaxLength = int.Parse(dwParameters[j].Row["max_length"].ToString());
parameter.Name = dwParameters[j].Row["Name"].ToString();
parameter.ObjectId = long.Parse(dwParameters[j].Row["object_id"].ToString());
parameter.ParameterId = int.Parse(dwParameters[j].Row["parameter_id"].ToString());
parameter.Precision = int.Parse(dwParameters[j].Row["precision"].ToString());
parameter.Scale = int.Parse(dwParameters[j].Row["scale"].ToString());
parameter.DataType = dwParameters[j].Row["Type"].ToString();
parameter.XmlCollectionId = int.Parse(dwParameters[j].Row["xml_collection_id"].ToString());
parameter.SystemDataTypeId = int.Parse(dwParameters[j].Row["SystemDataTypeId"].ToString());
if (parameter.DataTypeId != parameter.SystemDataTypeId)
{
dep = new Dependency();
dep.ObjectId = aggregate.ObjectId;
dep.Name = aggregate.Name;
dep.Schema = aggregate.Schema;
dep.Type = "AF";
dep.ReferencedName = parameter.DataType;
dep.ReferencedObjectId = parameter.DataTypeId;
dep.ReferencedType = "TY";
_dependencies.Add(dep);
}
if (dwParameters[j].Row["xmlcollection"] != DBNull.Value)
{
parameter.XmlCollection = dwParameters[j].Row["xmlcollection"].ToString();
parameter.XmlCollectionSchema = dwParameters[j].Row["XmlSchemaCollectionSchema"].ToString();
dep = new Dependency();
dep.ObjectId = aggregate.ObjectId;
dep.Name = aggregate.Name;
dep.Schema = aggregate.Schema;
dep.Type = "AF";
dep.ReferencedObjectId = parameter.XmlCollectionId;
dep.ReferencedName = parameter.XmlCollection;
dep.ReferencedSchema = parameter.XmlCollectionSchema;
dep.ReferencedType = "XS";
_dependencies.Add(dep);
}
//parameter.Description = dwParameters[j].Row["Description"].ToString();
aggregate.Parameters.Add(parameter);
}
rowFilter = "Object_Id=" + aggregate.ObjectId + " and is_output=1";
dwParameters = new DataView(dtParameters, rowFilter, "Parameter_Id", DataViewRowState.CurrentRows);
for (int j = 0; j < dwParameters.Count; j++)
{
var parameter = new Parameter();
parameter.DefaultValue = dwParameters[j].Row["Default_Value"].ToString();
parameter.IsOutput = bool.Parse(dwParameters[j].Row["is_output"].ToString());
parameter.IsReadOnly = bool.Parse(dwParameters[j].Row["is_readonly"].ToString());
parameter.IsXmlDocument = bool.Parse(dwParameters[j].Row["is_xml_document"].ToString());
parameter.MaxLength = int.Parse(dwParameters[j].Row["max_length"].ToString());
parameter.Name = dwParameters[j].Row["Name"].ToString();
parameter.ObjectId = long.Parse(dwParameters[j].Row["object_id"].ToString());
parameter.ParameterId = int.Parse(dwParameters[j].Row["parameter_id"].ToString());
parameter.Precision = int.Parse(dwParameters[j].Row["precision"].ToString());
parameter.Scale = int.Parse(dwParameters[j].Row["scale"].ToString());
parameter.DataType = dwParameters[j].Row["Type"].ToString();
parameter.XmlCollectionId = int.Parse(dwParameters[j].Row["xml_collection_id"].ToString());
//parameter.Description = dwParameters[j].Row["Description"].ToString();
parameter.SystemDataTypeId = int.Parse(dwParameters[j].Row["SystemDataTypeId"].ToString());
if (parameter.DataTypeId != parameter.SystemDataTypeId)
{
dep = new Dependency();
dep.ObjectId = aggregate.ObjectId;
dep.Name = aggregate.Name;
dep.Schema = aggregate.Schema;
dep.Type = "AF";
dep.ReferencedName = parameter.DataType;
dep.ReferencedObjectId = parameter.DataTypeId;
dep.ReferencedType = "TY";
_dependencies.Add(dep);
}
if (dwParameters[j].Row["xmlcollection"] != DBNull.Value)
{
parameter.XmlCollectionId = int.Parse(dwParameters[j].Row["xml_collection_id"].ToString());
parameter.XmlCollection = dwParameters[j].Row["xmlcollection"].ToString();
parameter.XmlCollectionSchema = dwParameters[j].Row["XmlSchemaCollectionSchema"].ToString();
dep = new Dependency();
dep.ObjectId = aggregate.ObjectId;
dep.Name = aggregate.Name;
dep.Schema = aggregate.Schema;
dep.Type = "AF";
dep.ReferencedObjectId = parameter.XmlCollectionId;
dep.ReferencedName = parameter.XmlCollection;
dep.ReferencedSchema = parameter.XmlCollectionSchema;
dep.ReferencedType = "XS";
_dependencies.Add(dep);
}
aggregate.OutputParameter = parameter;
}
//DataTable dtParameters = ds.Tables[int.Parse(hsResultSets["SPCollection"].ToString())];
aggregates.Add(aggregate);
OnObjectFetched(new FetchEventArgs(aggregate));
}
return aggregates;
}
private List<Assembly> GetAssemblies(DataTable dtAssemblies)
{
List<Assembly> assemblies = new List<Assembly>();
DataView dwAssemblies = new DataView(dtAssemblies,"", "Name", DataViewRowState.CurrentRows);
for (int i = 0; i < dwAssemblies.Count; i++)
{
Assembly assembly = new Assembly();
assembly.Name = dwAssemblies[i].Row["name"].ToString();
assembly.Filename = dwAssemblies[i].Row["Filename"].ToString();
assembly.Content = dwAssemblies[i].Row["Content"].ToString().ToUpper();
switch (int.Parse(dwAssemblies[i].Row["permission_set"].ToString()))
{
case 1:
assembly.PermissionSet = "SAFE";
break;
case 2:
assembly.PermissionSet = "EXTERNAL_ACCESS";
break;
case 3:
assembly.PermissionSet = "UNSAFE";
break;
}
assembly.Principal = dwAssemblies[i].Row["principalname"].ToString();
assembly.ObjectId = long.Parse(dwAssemblies[i].Row["assembly_id"].ToString());
assembly.Description = dwAssemblies[i].Row["Description"].ToString();
assemblies.Add(assembly);
OnObjectFetched(new FetchEventArgs(assembly));
}
return assemblies;
}
private List<Principal> GetPrincipals(DataTable dtPrincipals, string type)
{
List<Principal> principals = new List<Principal>();
DataView dwPrincipals = new DataView(dtPrincipals, "Type='"+type+"'", "Name", DataViewRowState.CurrentRows);
for (int i = 0; i < dwPrincipals.Count; i++)
{
Principal principal = new Principal();
principal.Name = dwPrincipals[i].Row["name"].ToString();
principal.OwningPrincipalName = dwPrincipals[i].Row["OwningPrincipalName"].ToString();
principal.ObjectId = long.Parse(dwPrincipals[i].Row["principal_id"].ToString());
principal.Schema = dwPrincipals[i].Row["default_schema_name"].ToString();
principal.Type = dwPrincipals[i].Row["type"].ToString();
principal.Login = dwPrincipals[i].Row["login"].ToString();
principal.AsymmetricKey = dwPrincipals[i].Row["AsymmetricKey"].ToString();
principal.Certificate = dwPrincipals[i].Row["Certificate"].ToString();
principal.Description = dwPrincipals[i].Row["Description"].ToString();
principals.Add(principal);
OnObjectFetched(new FetchEventArgs(principal));
}
return principals;
}
private List<ClrTrigger> GetClrTriggers(DataTable dtTriggers, DataTable dtEvents)
{
var triggers = new List<ClrTrigger>();
var dwTriggers = new DataView(dtTriggers, "", "Name", DataViewRowState.CurrentRows);
DataView dwEvents = null;
for (var i = 0; i < dwTriggers.Count; i++)
{
var trigger = new ClrTrigger();
trigger.AssemblyClass = dwTriggers[i].Row["assembly_class"].ToString();
trigger.AssemblyMethod = dwTriggers[i].Row["assembly_method"].ToString();
trigger.AssemblyName = dwTriggers[i].Row["assemblyname"].ToString();
trigger.ExecuteAs = dwTriggers[i].Row["ExecuteAs"].ToString();
trigger.IsAfter = bool.Parse(dwTriggers[i].Row["IsAfter"].ToString());
trigger.IsDisabled = bool.Parse(dwTriggers[i].Row["Is_Disabled"].ToString());
trigger.IsInsteadOf = bool.Parse(dwTriggers[i].Row["is_instead_of_trigger"].ToString());
trigger.IsNotForReplication = bool.Parse(dwTriggers[i].Row["is_not_for_replication"].ToString());
trigger.Name = dwTriggers[i].Row["name"].ToString();
trigger.ObjectId =long.Parse( dwTriggers[i].Row["Object_Id"].ToString());
trigger.ParentClass =int.Parse( dwTriggers[i].Row["parent_class"].ToString());
trigger.ParentObjectName=dwTriggers[i].Row["ParentObjectName"].ToString();
trigger.ParentObjectSchema = dwTriggers[i].Row["ParentObjectSchema"].ToString();
trigger.ParentObjectId = int.Parse(dwTriggers[i].Row["ParentObjectId"].ToString());
trigger.Schema = dwTriggers[i].Row["Schema"].ToString();
trigger.IsDatabaseTrigger = bool.Parse(dwTriggers[i].Row["IsDatabaseTrigger"].ToString());
trigger.Description = dwTriggers[i].Row["Description"].ToString();
trigger.AssemblyId = int.Parse(dwTriggers[i].Row["Assembly_id"].ToString());
var dep = new Dependency();
dep.ObjectId = trigger.ObjectId;
dep.Name = trigger.Name;
dep.Schema = trigger.Schema;
dep.Type = "TA";
dep.ReferencedObjectId = trigger.AssemblyId;
dep.ReferencedName = trigger.AssemblyName;
dep.ReferencedType = "AS";
_dependencies.Add(dep);
if (!trigger.IsDatabaseTrigger)
{
dep = new Dependency();
dep.ObjectId = trigger.ObjectId;
dep.Name = trigger.Name;
dep.Schema = trigger.Schema;
dep.Type = "TA";
dep.ReferencedObjectId = trigger.ParentObjectId;
dep.ReferencedName = trigger.ParentObjectName;
dep.ReferencedSchema = trigger.ParentObjectSchema;
dep.ReferencedType = "U";
_dependencies.Add(dep);
}
dwEvents = new DataView(dtEvents,"object_id=" + trigger.ObjectId,"type_desc",DataViewRowState.CurrentRows);
for (int j = 0; j < dwEvents.Count; j++)
{
trigger.Events.Add(dwEvents[j].Row["type_desc"].ToString());
}
triggers.Add(trigger);
OnObjectFetched(new FetchEventArgs(trigger));
}
return triggers;
}
private List<Trigger> GetTriggers(DataTable dtTriggers, DataTable dtTriggerEvents,bool ddlTrigger)
{
var triggers = new List<Trigger>();
string rowFilter;
if (ddlTrigger)
{
rowFilter = "parent_class=0";
}
else
{
rowFilter = "parent_class<>0";
}
var dwTriggers = new DataView(dtTriggers, rowFilter, "Name", DataViewRowState.CurrentRows);
DataView dwEvents = null;
for (var i = 0; i < dwTriggers.Count; i++)
{
var trigger = new Trigger();
trigger.ObjectId = long.Parse(dwTriggers[i].Row["Object_Id"].ToString());
trigger.Definition = dwTriggers[i].Row["Definition"].ToString();
trigger.IsDisabled = bool.Parse(dwTriggers[i].Row["Is_Disabled"].ToString());
trigger.IsInsteadOf = bool.Parse(dwTriggers[i].Row["is_instead_of_trigger"].ToString());
trigger.IsNotForReplication = bool.Parse(dwTriggers[i].Row["is_not_for_replication"].ToString());
trigger.Name = dwTriggers[i].Row["Name"].ToString();
trigger.ParentClass = int.Parse(dwTriggers[i].Row["Parent_Class"].ToString());
trigger.IsAfter = bool.Parse(dwTriggers[i].Row["IsAfter"].ToString());
trigger.ParentObjectName = dwTriggers[i].Row["ParentObjectName"].ToString();
trigger.ParentObjectSchema = dwTriggers[i].Row["ParentObjectSchema"].ToString();
trigger.ParentObjectId = int.Parse(dwTriggers[i].Row["ParentObjectId"].ToString());
trigger.Schema = dwTriggers[i].Row["Schema"].ToString();
trigger.IsDatabaseTrigger = bool.Parse(dwTriggers[i].Row["IsDatabaseTrigger"].ToString());
trigger.Description = dwTriggers[i].Row["Description"].ToString();
if (!trigger.IsDatabaseTrigger)
{
var dep = new Dependency();
dep.ObjectId = trigger.ObjectId;
dep.Name = trigger.Name;
dep.Schema = trigger.Schema;
dep.Type = "TR";
dep.ReferencedObjectId = trigger.ParentObjectId;
dep.ReferencedName = trigger.ParentObjectName;
dep.ReferencedSchema = trigger.ParentObjectSchema;
dep.ReferencedType = "U";
_dependencies.Add(dep);
}
dwEvents = new DataView(dtTriggerEvents, "object_id=" + trigger.ObjectId, "type_desc", DataViewRowState.CurrentRows);
for (int j = 0; j < dwEvents.Count; j++)
{
trigger.Events.Add(dwEvents[j].Row["type_desc"].ToString());
}
triggers.Add(trigger);
OnObjectFetched(new FetchEventArgs(trigger));
}
return triggers;
}
private List<Column> GetColumns(DataTable dtColumns, Table table)
{
var columns = new List<Column>();
var rowFilter = "Object_Id=" + table.ObjectId;
var dwColumns = new DataView(dtColumns, rowFilter, "Column_Id", DataViewRowState.CurrentRows);
for (var i = 0; i < dwColumns.Count; i++)
{
var column = new Column();
column.ColumnId = int.Parse(dwColumns[i].Row["Column_Id"].ToString());
column.Description = dwColumns[i].Row["Description"].ToString();
column.Name = dwColumns[i].Row["Name"].ToString();
column.IsComputed = bool.Parse(dwColumns[i].Row["Is_Computed"].ToString());
column.IsFileStream = bool.Parse(dwColumns[i].Row["Is_Filestream"].ToString());
column.IsIdentity = bool.Parse(dwColumns[i].Row["Is_Identity"].ToString());
column.IsNullable = bool.Parse(dwColumns[i].Row["Is_Nullable"].ToString());
column.IsReplicated = bool.Parse(dwColumns[i].Row["Is_Replicated"].ToString());
column.IsRowGuidCol = bool.Parse(dwColumns[i].Row["Is_rowguidcol"].ToString());
column.IsSparse = bool.Parse(dwColumns[i].Row["Is_Sparse"].ToString());
column.ObjectId = long.Parse(dwColumns[i].Row["Object_Id"].ToString());
column.Precision = int.Parse(dwColumns[i].Row["Precision"].ToString());
column.Scale = int.Parse(dwColumns[i].Row["Scale"].ToString());
column.DataType = dwColumns[i].Row["Type"].ToString();
column.DataTypeId = int.Parse(dwColumns[i].Row["DataTypeId"].ToString());
column.SystemDataTypeId = int.Parse(dwColumns[i].Row["SystemDataTypeId"].ToString());
column.IsComputed = bool.Parse(dwColumns[i].Row["Is_Computed"].ToString());
column.Definition = dwColumns[i].Row["Definition"].ToString();
column.Collation = dwColumns[i].Row["Collation"].ToString();
column.Scale =int.Parse(dwColumns[i].Row["Scale"].ToString());
column.Description = dwColumns[i].Row["Description"].ToString();
column.IdentityIncrement = int.Parse(dwColumns[i].Row["IdentityIncrement"].ToString());
column.IdentitySeed = int.Parse(dwColumns[i].Row["IdentitySeed"].ToString());
column.IsPersisted = bool.Parse(dwColumns[i].Row["IsPersisted"].ToString());
column.IsUserDefinedDataType = bool.Parse(dwColumns[i].Row["IsUserDefinedDataType"].ToString());
if (column.DataTypeId != column.SystemDataTypeId)
{
Dependency dep = new Dependency();
dep.ObjectId = table.ObjectId;
dep.Name = table.Name;
dep.Schema = table.Schema;
dep.Type = "U";
dep.ReferencedName = column.DataType;
dep.ReferencedObjectId = column.DataTypeId;
dep.ReferencedType = "TY";
_dependencies.Add(dep);
}
int isUnique = int.Parse(dwColumns[i].Row["IsUnique"].ToString());
if (isUnique > 0)
{
column.IsUnique = true;
}
else
{
column.IsUnique = false;
}
column.IsColumnSet = bool.Parse(dwColumns[i].Row["is_column_set"].ToString());
columns.Add(column);
}
return columns;
}
private List<Column> GetViewColumns(DataTable dtColumns, long viewId)
{
var columns = new List<Column>();
string rowFilter = "Object_Id=" + viewId;
var dwColumns = new DataView(dtColumns, rowFilter, "Column_Id", DataViewRowState.CurrentRows);
for (var i = 0; i < dwColumns.Count; i++)
{
var column = new Column();
column.ColumnId = int.Parse(dwColumns[i].Row["Column_Id"].ToString());
column.Description = dwColumns[i].Row["Description"].ToString();
column.Name = dwColumns[i].Row["Name"].ToString();
column.IsComputed = bool.Parse(dwColumns[i].Row["Is_Computed"].ToString());
column.IsFileStream = bool.Parse(dwColumns[i].Row["Is_Filestream"].ToString());
column.IsIdentity = bool.Parse(dwColumns[i].Row["Is_Identity"].ToString());
column.IsNullable = bool.Parse(dwColumns[i].Row["Is_Nullable"].ToString());
column.IsReplicated = bool.Parse(dwColumns[i].Row["Is_Replicated"].ToString());
column.IsRowGuidCol = bool.Parse(dwColumns[i].Row["Is_rowguidcol"].ToString());
column.IsSparse = bool.Parse(dwColumns[i].Row["Is_Sparse"].ToString());
column.ObjectId = long.Parse(dwColumns[i].Row["Object_Id"].ToString());
column.Precision = int.Parse(dwColumns[i].Row["Precision"].ToString());
column.Scale = int.Parse(dwColumns[i].Row["Scale"].ToString());
column.DataType = dwColumns[i].Row["Type"].ToString();
column.DataTypeId = int.Parse(dwColumns[i].Row["DataTypeId"].ToString());
column.IsComputed = bool.Parse(dwColumns[i].Row["Is_Computed"].ToString());
column.Definition = dwColumns[i].Row["Definition"].ToString();
column.Collation = dwColumns[i].Row["Collation"].ToString();
column.Scale = int.Parse(dwColumns[i].Row["Scale"].ToString());
column.Description = dwColumns[i].Row["Description"].ToString();
column.IdentityIncrement = int.Parse(dwColumns[i].Row["IdentityIncrement"].ToString());
column.IdentitySeed = int.Parse(dwColumns[i].Row["IdentitySeed"].ToString());
column.IsPersisted = bool.Parse(dwColumns[i].Row["IsPersisted"].ToString());
column.IsUserDefinedDataType = bool.Parse(dwColumns[i].Row["IsUserDefinedDataType"].ToString());
int isUnique = int.Parse(dwColumns[i].Row["IsUnique"].ToString());
if (isUnique > 0)
{
column.IsUnique = true;
}
else
{
column.IsUnique = false;
}
column.IsColumnSet = bool.Parse(dwColumns[i].Row["is_column_set"].ToString());
columns.Add(column);
}
return columns;
}
private List<DefaultConstraint> GetDefaultConstraints(DataTable dtConstraints, long tableId)
{
var constraints = new List<DefaultConstraint>();
string rowFilter = "Parent_Object_Id=" + tableId;
var dwConstraints = new DataView(dtConstraints, rowFilter, "Name", DataViewRowState.CurrentRows);
for (var i = 0; i < dwConstraints.Count; i++)
{
var constraint = new DefaultConstraint();
constraint.Name = dwConstraints[i].Row["Name"].ToString();
constraint.ObjectId = long.Parse(dwConstraints[i].Row["Object_id"].ToString());
constraint.ParentObjectId = int.Parse(dwConstraints[i].Row["Parent_Object_id"].ToString());
constraint.Column = dwConstraints[i].Row["ColumnName"].ToString();
constraint.Definition = dwConstraints[i].Row["Definition"].ToString();
constraint.Table = dwConstraints[i].Row["TableName"].ToString();
constraint.Schema = dwConstraints[i].Row["Schema"].ToString();
constraint.Description = dwConstraints[i].Row["Description"].ToString();
constraints.Add(constraint);
OnObjectFetched(new FetchEventArgs(constraint));
}
return constraints;
}
private List<CheckConstraint> GetCheckConstraints(DataTable dtConstraints, long tableId)
{
var constraints = new List<CheckConstraint>();
string rowFilter = "Parent_Object_Id=" + tableId;
var dwConstraints = new DataView(dtConstraints, rowFilter, "Name", DataViewRowState.CurrentRows);
for (int i = 0; i < dwConstraints.Count; i++)
{
var constraint = new CheckConstraint();
constraint.Name = dwConstraints[i].Row["Name"].ToString();
constraint.ObjectId = long.Parse(dwConstraints[i].Row["Object_id"].ToString());
constraint.ParentObjectId = int.Parse(dwConstraints[i].Row["Parent_Object_id"].ToString());
constraint.Column = dwConstraints[i].Row["ColumnName"].ToString();
constraint.Definition = dwConstraints[i].Row["Definition"].ToString();
constraint.Table = dwConstraints[i].Row["TableName"].ToString();
constraint.Schema = dwConstraints[i].Row["Schema"].ToString();
constraint.Description = dwConstraints[i].Row["Description"].ToString();
constraints.Add(constraint);
}
return constraints;
}
private List<PrimaryKeyConstraint> GetPrimaryKeyConstraint(DataTable dtConstraints, DataTable dtIndexColumns, long tableId)
{
var constraints = new List<PrimaryKeyConstraint>();
string rowFilter = "ObjectId='" + tableId + "' AND is_primary_key=1";
var dwConstraints = new DataView(dtConstraints, rowFilter, "Name", DataViewRowState.CurrentRows);
for (var i = 0; i < dwConstraints.Count; i++)
{
var constraint = new PrimaryKeyConstraint();
constraint.ObjectId = long.Parse(dwConstraints[i].Row["Objectid"].ToString());
constraint.Name = dwConstraints[i].Row["Name"].ToString();
constraint.IndexId = int.Parse(dwConstraints[i].Row["Index_id"].ToString());
constraint.Type = dwConstraints[i].Row["Type_Desc"].ToString();
constraint.IsUnique = bool.Parse(dwConstraints[i].Row["is_unique"].ToString());
constraint.IgnoreDupKey = bool.Parse(dwConstraints[i].Row["ignore_dup_key"].ToString());
constraint.IsPrimary = bool.Parse(dwConstraints[i].Row["is_primary_key"].ToString());
constraint.IsUniqueConstraint = bool.Parse(dwConstraints[i].Row["is_unique_constraint"].ToString());
constraint.FillFactor = int.Parse(dwConstraints[i].Row["fill_factor"].ToString());
constraint.IsPadded = bool.Parse(dwConstraints[i].Row["is_padded"].ToString());
constraint.IsDisabled = bool.Parse(dwConstraints[i].Row["is_disabled"].ToString());
constraint.IsHypothetical = bool.Parse(dwConstraints[i].Row["is_hypothetical"].ToString());
constraint.AllowRowLocks = bool.Parse(dwConstraints[i].Row["allow_row_locks"].ToString());
constraint.AllowPageLocks = bool.Parse(dwConstraints[i].Row["allow_page_locks"].ToString());
//constraint.HasFilter = bool.Parse(dwConstraints[i].Row["has_filter"].ToString());
//constraint.FilterDefinition = dwConstraints[i].Row["filter_definition"].ToString();
constraint.DataSpaceType = dwConstraints[i].Row["DataSpaceType"].ToString();
constraint.DataSpace = dwConstraints[i].Row["DataSpace"].ToString();
constraint.PartitionedColumn = dwConstraints[i].Row["PartitionedColumn"].ToString();
constraint.StatisticsNoRecompute = bool.Parse(dwConstraints[i].Row["no_recompute"].ToString());
constraint.Columns = GetIndexColumns(tableId, constraint.IndexId, dtIndexColumns);
constraint.Description = dwConstraints[i].Row["ConstraintDescription"].ToString();
constraints.Add(constraint);
}
return constraints;
}
private List<Index> GetIndexes(DataTable dtIndexes, DataTable dtIndexColumns, string type)
{
var constraints = new List<Index>();
string rowFilter = "is_primary_key=0 and is_unique_constraint=0 and [type_desc]='"+type+"'";
var dwIndexes = new DataView(dtIndexes, rowFilter, "Name", DataViewRowState.CurrentRows);
Index constraint;
for (var i = 0; i < dwIndexes.Count; i++)
{
constraint= new Index();
constraint.ObjectId = long.Parse(dwIndexes[i].Row["ObjectId"]+""+ dwIndexes[i].Row["Index_Id"]);
constraint.ReferencedObjecId = int.Parse(dwIndexes[i].Row["ParentObjectid"].ToString());
constraint.Name = dwIndexes[i].Row["Name"].ToString();
constraint.IndexId = int.Parse(dwIndexes[i].Row["Index_id"].ToString());
constraint.Type = dwIndexes[i].Row["Type_Desc"].ToString();
constraint.IsUnique = bool.Parse(dwIndexes[i].Row["is_unique"].ToString());
constraint.IgnoreDupKey = bool.Parse(dwIndexes[i].Row["ignore_dup_key"].ToString());
constraint.IsPrimary = bool.Parse(dwIndexes[i].Row["is_primary_key"].ToString());
constraint.IsUniqueConstraint = bool.Parse(dwIndexes[i].Row["is_unique_constraint"].ToString());
constraint.FillFactor = int.Parse(dwIndexes[i].Row["fill_factor"].ToString());
constraint.IsPadded = bool.Parse(dwIndexes[i].Row["is_padded"].ToString());
constraint.IsDisabled = bool.Parse(dwIndexes[i].Row["is_disabled"].ToString());
constraint.IsHypothetical = bool.Parse(dwIndexes[i].Row["is_hypothetical"].ToString());
constraint.AllowRowLocks = bool.Parse(dwIndexes[i].Row["allow_row_locks"].ToString());
constraint.AllowPageLocks = bool.Parse(dwIndexes[i].Row["allow_page_locks"].ToString());
constraint.HasFilter = bool.Parse(dwIndexes[i].Row["has_filter"].ToString());
constraint.FilterDefinition = dwIndexes[i].Row["filter_definition"].ToString();
constraint.DataSpaceType = dwIndexes[i].Row["DataSpaceType"].ToString();
constraint.DataSpace = dwIndexes[i].Row["DataSpace"].ToString();
constraint.PartitionedColumn = dwIndexes[i].Row["PartitionedColumn"].ToString();
constraint.StatisticsNoRecompute = bool.Parse(dwIndexes[i].Row["no_recompute"].ToString());
constraint.Columns = GetIndexColumns(long.Parse(dwIndexes[i].Row["ObjectId"].ToString()), constraint.IndexId, dtIndexColumns);
constraint.FileStreamFileGroup = dwIndexes[i].Row["FileStreamFileGroup"].ToString();
constraint.ObjectName = dwIndexes[i].Row["ObjectName"].ToString();
constraint.ObjectSchema = dwIndexes[i].Row["ObjectSchema"].ToString();
constraint.Description = dwIndexes[i].Row["Description"].ToString();
constraint.ReferencedObjectType = dwIndexes[i].Row["ReferencedObjectType"].ToString();
var dep = new Dependency();
dep.ObjectId = long.Parse(constraint.ObjectId.ToString());
dep.Name = constraint.Name;
dep.Type = "IX";
dep.ReferencedObjectId = constraint.ReferencedObjecId;
dep.ReferencedName = constraint.ObjectName;
dep.ReferencedSchema = constraint.ObjectSchema;
dep.ReferencedType = constraint.ReferencedObjectType;
constraints.Add(constraint);
OnObjectFetched(new FetchEventArgs(constraint));
}
return constraints;
}
private List<DBObjectType.UniqueConstraint> GetUniqueConstraints(DataTable dtConstraints, DataTable dtIndexColumns, long tableId)
{
var constraints = new List<DBObjectType.UniqueConstraint>();
string rowFilter = "ObjectId='" + tableId + "' AND is_unique_constraint=1";
var dwConstraints = new DataView(dtConstraints, rowFilter, "Name", DataViewRowState.CurrentRows);
for (var i = 0; i < dwConstraints.Count; i++)
{
var constraint = new DBObjectType.UniqueConstraint();
constraint.ObjectId = long.Parse(dwConstraints[i].Row["Objectid"].ToString());
constraint.Name = dwConstraints[i].Row["Name"].ToString();
constraint.IndexId = int.Parse(dwConstraints[i].Row["Index_id"].ToString());
constraint.Type = dwConstraints[i].Row["Type_Desc"].ToString();
constraint.IsUnique = bool.Parse(dwConstraints[i].Row["is_unique"].ToString());
constraint.IgnoreDupKey = bool.Parse(dwConstraints[i].Row["ignore_dup_key"].ToString());
constraint.IsPrimary = bool.Parse(dwConstraints[i].Row["is_primary_key"].ToString());
constraint.IsUniqueConstraint = bool.Parse(dwConstraints[i].Row["is_unique_constraint"].ToString());
constraint.FillFactor = int.Parse(dwConstraints[i].Row["fill_factor"].ToString());
constraint.IsPadded = bool.Parse(dwConstraints[i].Row["is_padded"].ToString());
constraint.IsDisabled = bool.Parse(dwConstraints[i].Row["is_disabled"].ToString());
constraint.IsHypothetical = bool.Parse(dwConstraints[i].Row["is_hypothetical"].ToString());
constraint.AllowRowLocks = bool.Parse(dwConstraints[i].Row["allow_row_locks"].ToString());
constraint.AllowPageLocks = bool.Parse(dwConstraints[i].Row["allow_page_locks"].ToString());
constraint.HasFilter = bool.Parse(dwConstraints[i].Row["has_filter"].ToString());
constraint.FilterDefinition = dwConstraints[i].Row["filter_definition"].ToString();
constraint.DataSpaceType = dwConstraints[i].Row["DataSpaceType"].ToString();
constraint.DataSpace = dwConstraints[i].Row["DataSpace"].ToString();
constraint.PartitionedColumn = dwConstraints[i].Row["PartitionedColumn"].ToString();
constraint.StatisticsNoRecompute = bool.Parse(dwConstraints[i].Row["no_recompute"].ToString());
constraint.Columns = GetIndexColumns(tableId, constraint.IndexId, dtIndexColumns);
constraint.Description = dwConstraints[i].Row["ConstraintDescription"].ToString();
constraints.Add(constraint);
OnObjectFetched(new FetchEventArgs(constraint));
}
return constraints;
}
private List<IndexColumn> GetIndexColumns(long tableId, int indexId, DataTable dtIndexColumns)
{
var columns = new List<IndexColumn>();
string rowFilter = "Object_Id=" + tableId + " AND index_Id=" + indexId;
var dwColumns = new DataView(dtIndexColumns, rowFilter, "Index_Column_Id", DataViewRowState.CurrentRows);
for (var i = 0; i < dwColumns.Count; i++)
{
var column = new IndexColumn();
column.ObjectId = long.Parse(dwColumns[i].Row["Object_id"].ToString());
column.Name = dwColumns[i].Row["Name"].ToString();
column.IndexId = int.Parse(dwColumns[i].Row["Index_id"].ToString());
column.IsDescendingKey = bool.Parse(dwColumns[i].Row["is_descending_key"].ToString());
column.IsIncluded = bool.Parse(dwColumns[i].Row["is_descending_key"].ToString());
column.IndexColumnId = int.Parse(dwColumns[i].Row["Index_Column_Id"].ToString());
column.ColumnId = int.Parse(dwColumns[i].Row["Column_Id"].ToString());
columns.Add(column);
}
return columns;
}
private List<ClrUserDefinedFunction> GetClrUserDefinedFunctions(DataTable dtFunctions, DataTable dtParameters, DataTable dtColumns)
{
var functions = new List<ClrUserDefinedFunction>();
var dwFunctions = new DataView(dtFunctions);
for (int i = 0; i < dwFunctions.Count; i++)
{
var function = new ClrUserDefinedFunction();
function.AssemblyClass = dwFunctions[i].Row["Assembly_class"].ToString();
function.AssemblyMethod = dwFunctions[i].Row["Assembly_method"].ToString();
function.AssemblyName = dwFunctions[i].Row["Assembly_name"].ToString();
function.ExecuteAsPrincipal = dwFunctions[i].Row["execute_as_principal_id_name"].ToString();
function.ExecuteAsPrincipalId = int.Parse(dwFunctions[i].Row["execute_as_principal_id"].ToString());
function.IsTableValued = bool.Parse(dwFunctions[i].Row["Is_Table_Valued"].ToString());
function.Name = dwFunctions[i].Row["Name"].ToString();
function.ObjectId = long.Parse(dwFunctions[i].Row["object_id"].ToString());
function.Schema = dwFunctions[i].Row["Schema"].ToString();
function.Description = dwFunctions[i].Row["Description"].ToString();
function.AssemblyId = int.Parse(dwFunctions[i].Row["Assembly_Id"].ToString());
var dwParameters = new DataView(dtParameters, "object_id=" + function.ObjectId, "Parameter_id", DataViewRowState.CurrentRows);
for (int j = 0; j < dwParameters.Count; j++)
{
var parameter = new Parameter();
parameter.DefaultValue = dwParameters[j].Row["Default_Value"].ToString();
parameter.IsOutput = bool.Parse(dwParameters[j].Row["is_output"].ToString());
parameter.IsReadOnly = bool.Parse(dwParameters[j].Row["is_readonly"].ToString());
parameter.IsXmlDocument = bool.Parse(dwParameters[j].Row["is_xml_document"].ToString());
parameter.MaxLength = int.Parse(dwParameters[j].Row["max_length"].ToString());
parameter.Name = dwParameters[j].Row["Name"].ToString();
parameter.ObjectId = long.Parse(dwParameters[j].Row["object_id"].ToString());
parameter.ParameterId = int.Parse(dwParameters[j].Row["parameter_id"].ToString());
parameter.Precision = int.Parse(dwParameters[j].Row["precision"].ToString());
parameter.Scale = int.Parse(dwParameters[j].Row["scale"].ToString());
parameter.DataType = dwParameters[j].Row["Type"].ToString();
parameter.DataTypeId = int.Parse(dwParameters[j].Row["DataTypeId"].ToString());
parameter.SystemDataTypeId = int.Parse(dwParameters[j].Row["SystemDataTypeId"].ToString());
if (parameter.DataTypeId!=parameter.SystemDataTypeId)
{
var dep = new Dependency();
dep.ObjectId = function.ObjectId;
dep.Name = function.Name;
dep.Schema = function.Schema;
if (function.IsTableValued)
{
dep.Type = "FS";
}
else
{
dep.Type = "FT";
}
dep.ReferencedName = parameter.DataType;
dep.ReferencedObjectId = parameter.DataTypeId;
dep.ReferencedType = "TY";
_dependencies.Add(dep);
}
if (dwParameters[j].Row["xmlcollection"] != DBNull.Value)
{
parameter.XmlCollectionId = int.Parse(dwParameters[j].Row["xml_collection_id"].ToString());
parameter.XmlCollection = dwParameters[j].Row["xmlcollection"].ToString();
parameter.XmlCollectionSchema = dwParameters[j].Row["xmlschemacollectionschema"].ToString();
var dep = new Dependency();
dep.ObjectId = function.ObjectId;
dep.Schema = function.Schema;
dep.Name = function.Name;
if (function.IsTableValued)
{
dep.Type = "FS";
}
else
{
dep.Type = "FT";
}
dep.ReferencedObjectId = parameter.XmlCollectionId;
dep.ReferencedName = parameter.XmlCollection;
dep.ReferencedType = "XS";
dep.ReferencedSchema = parameter.XmlCollectionSchema;
_dependencies.Add(dep);
}
parameter.Description = dwParameters[j].Row["Description"].ToString();
if (parameter.IsOutput)
{
function.Output = parameter;
}
else
{
function.Parameters.Add(parameter);
}
}
var dwColumns = new DataView(dtColumns,"object_id="+function.ObjectId,"column_id",DataViewRowState.CurrentRows);
for (int j = 0; j < dwColumns.Count; j++)
{
var column = new Column();
column.ObjectId = long.Parse(dwColumns[j].Row["object_id"].ToString());
column.Name = dwColumns[j].Row["name"].ToString();
column.ColumnId = int.Parse(dwColumns[j].Row["column_id"].ToString());
column.DataType = dwColumns[j].Row["DataType"].ToString();
column.DataTypeId = int.Parse(dwColumns[j].Row["DataTypeId"].ToString());
column.Scale = int.Parse(dwColumns[j].Row["Scale"].ToString());
column.Precision = int.Parse(dwColumns[j].Row["Precision"].ToString());
column.IsSparse = bool.Parse(dwColumns[j].Row["is_sparse"].ToString());
column.DataTypeId = int.Parse(dwColumns[j].Row["user_type_id"].ToString());
function.Columns.Add(column);
}
functions.Add(function);
OnObjectFetched(new FetchEventArgs(function));
}
return functions;
}
private List<SqlUserDefinedFunction> GetSqlUserDefinedFunctions(DataTable dtFs, DataTable dtParameters, DataTable dtColumns)
{
var functions = new List<SqlUserDefinedFunction>();
var dwFunctions = new DataView(dtFs);
for (var i = 0; i < dwFunctions.Count; i++)
{
var function = new SqlUserDefinedFunction();
function.IsDeterministic = bool.Parse(dwFunctions[i].Row["IsDeterministic"].ToString());
function.Definition = dwFunctions[i].Row["Definition"].ToString();
function.Name = dwFunctions[i].Row["Name"].ToString();
function.ObjectId = long.Parse(dwFunctions[i].Row["Object_Id"].ToString());
function.QuotedIdentifiers = bool.Parse(dwFunctions[i].Row["QuotedIdentifierStatus"].ToString());
function.Schema = dwFunctions[i].Row["Schema"].ToString();
function.IsTableValued = bool.Parse(dwFunctions[i].Row["Is_Table_Valued"].ToString());
function.Description = dwFunctions[i].Row["Description"].ToString();
string rowFilter = "Object_Id=" + function.ObjectId;
var dwParameters = new DataView(dtParameters, rowFilter, "Parameter_Id", DataViewRowState.CurrentRows);
for (var j = 0; j < dwParameters.Count; j++)
{
var parameter = new Parameter();
parameter.DefaultValue = dwParameters[j].Row["Default_Value"].ToString();
parameter.IsOutput = bool.Parse(dwParameters[j].Row["is_output"].ToString());
parameter.IsReadOnly = bool.Parse(dwParameters[j].Row["is_readonly"].ToString());
parameter.IsXmlDocument = bool.Parse(dwParameters[j].Row["is_xml_document"].ToString());
parameter.MaxLength = int.Parse(dwParameters[j].Row["max_length"].ToString());
parameter.Name = dwParameters[j].Row["Name"].ToString();
parameter.ObjectId = long.Parse(dwParameters[j].Row["object_id"].ToString());
parameter.ParameterId = int.Parse(dwParameters[j].Row["parameter_id"].ToString());
parameter.Precision = int.Parse(dwParameters[j].Row["precision"].ToString());
parameter.Scale = int.Parse(dwParameters[j].Row["scale"].ToString());
parameter.DataType = dwParameters[j].Row["Type"].ToString();
parameter.DataTypeId = int.Parse(dwParameters[j].Row["DataTypeId"].ToString());
parameter.SystemDataTypeId = int.Parse(dwParameters[j].Row["SystemDataTypeId"].ToString());
if (parameter.DataTypeId != parameter.SystemDataTypeId)
{
var dep = new Dependency();
dep.ObjectId = function.ObjectId;
dep.Name = function.Name;
dep.Schema = function.Schema;
if (function.IsTableValued)
{
dep.Type = "FN";
}
else
{
dep.Type = "IF";
}
dep.ReferencedName = parameter.DataType;
dep.ReferencedObjectId = parameter.DataTypeId;
dep.ReferencedType = "TY";
_dependencies.Add(dep);
}
if (dwParameters[j].Row["XmlCollection"]!=DBNull.Value)
{
parameter.XmlCollectionId = int.Parse(dwParameters[j].Row["xml_collection_id"].ToString());
parameter.XmlCollection = dwParameters[j].Row["xmlcollection"].ToString();
parameter.XmlCollectionSchema = dwParameters[j].Row["XmlSchemaCollectionSchema"].ToString();
var dep = new Dependency();
dep.ObjectId = function.ObjectId;
dep.Schema = function.Schema;
dep.Name = function.Name;
if (function.IsTableValued)
{
dep.Type = "FN";
}
else
{
dep.Type = "IF";
}
dep.ReferencedObjectId = parameter.XmlCollectionId;
dep.ReferencedName = parameter.XmlCollection;
dep.ReferencedType = "XS";
dep.ReferencedSchema = parameter.XmlCollectionSchema;
_dependencies.Add(dep);
}
parameter.Description = dwParameters[j].Row["Description"].ToString();
if (parameter.IsOutput)
{
function.Output = parameter;
}
else
{
function.Parameters.Add(parameter);
}
}
var dwColumns = new DataView(dtColumns, "object_id=" + function.ObjectId, "column_id", DataViewRowState.CurrentRows);
for (var j = 0; j < dwColumns.Count; j++)
{
var column = new Column();
column.ObjectId = long.Parse(dwColumns[j].Row["object_id"].ToString());
column.Name = dwColumns[j].Row["name"].ToString();
column.ColumnId = int.Parse(dwColumns[j].Row["column_id"].ToString());
column.DataType = dwColumns[j].Row["DataType"].ToString();
column.DataTypeId = int.Parse(dwColumns[j].Row["DataTypeId"].ToString());
column.Scale = int.Parse(dwColumns[j].Row["Scale"].ToString());
column.Precision = int.Parse(dwColumns[j].Row["Precision"].ToString());
column.IsSparse = bool.Parse(dwColumns[j].Row["is_sparse"].ToString());
function.Columns.Add(column);
}
functions.Add(function);
OnObjectFetched(new FetchEventArgs(function));
}
return functions;
}
private List<StoredProcedure> GetStoredProcedures(DataTable dtSPs, DataTable dtParameters)
{
var procedures = new List<StoredProcedure>();
var dwProcedures = new DataView(dtSPs);
for (var i = 0; i < dwProcedures.Count; i++)
{
var sp = new StoredProcedure();
sp.Definition = dwProcedures[i].Row["Definition"].ToString();
sp.IsAutoExecuted = bool.Parse(dwProcedures[i].Row["is_auto_executed"].ToString());
sp.IsExecutionReplicated = bool.Parse(dwProcedures[i].Row["is_execution_replicated"].ToString());
sp.IsReplicationSerializableOnly = bool.Parse(dwProcedures[i].Row["is_repl_serializable_only"].ToString());
sp.ObjectId = long.Parse(dwProcedures[i].Row["object_id"].ToString());
sp.Name = dwProcedures[i].Row["Name"].ToString();
sp.Schema = dwProcedures[i].Row["SchemaName"].ToString();
sp.UsesAnsiNull = bool.Parse(dwProcedures[i].Row["uses_ansi_nulls"].ToString());
sp.UsesQuotedIdentifier = bool.Parse(dwProcedures[i].Row["uses_quoted_identifier"].ToString());
sp.ModifyDate = DateTime.Parse(dwProcedures[i].Row["modify_date"].ToString());
sp.CreateDate = DateTime.Parse(dwProcedures[i].Row["create_date"].ToString());
sp.Description = dwProcedures[i].Row["description"].ToString();
//List<ObjectHelper.DBObjectType.UniqueConstraint> constraints = new List<ObjectHelper.DBObjectType.UniqueConstraint>();
string rowFilter = "Object_Id=" + sp.ObjectId;
var dwParameters = new DataView(dtParameters, rowFilter, "Parameter_Id", DataViewRowState.CurrentRows);
for (int j = 0; j < dwParameters.Count; j++)
{
var parameter = new Parameter();
parameter.DefaultValue = dwParameters[j].Row["Default_Value"].ToString();
parameter.IsOutput = bool.Parse(dwParameters[j].Row["is_output"].ToString());
parameter.IsReadOnly=bool.Parse(dwParameters[j].Row["is_readonly"].ToString());
parameter.IsXmlDocument = bool.Parse(dwParameters[j].Row["is_xml_document"].ToString());
parameter.MaxLength = int.Parse(dwParameters[j].Row["max_length"].ToString());
parameter.Name = dwParameters[j].Row["Name"].ToString();
parameter.ObjectId = long.Parse(dwParameters[j].Row["object_id"].ToString());
parameter.ParameterId = int.Parse(dwParameters[j].Row["parameter_id"].ToString());
parameter.Precision = int.Parse(dwParameters[j].Row["precision"].ToString());
parameter.Scale = int.Parse(dwParameters[j].Row["scale"].ToString());
parameter.DataType = dwParameters[j].Row["Type"].ToString();
parameter.DataTypeId = int.Parse(dwParameters[j].Row["DataTypeId"].ToString());
parameter.SystemDataTypeId = int.Parse(dwParameters[j].Row["SystemDataTypeId"].ToString());
if (parameter.DataTypeId != parameter.SystemDataTypeId)
{
var dep = new Dependency();
dep.ObjectId = sp.ObjectId;
dep.Name = sp.Name;
dep.Schema = sp.Schema;
dep.Type = "P";
dep.ReferencedName = parameter.DataType;
dep.ReferencedObjectId = parameter.DataTypeId;
dep.ReferencedType = "TY";
_dependencies.Add(dep);
}
if (dwParameters[j].Row["XmlCollection"] != DBNull.Value)
{
parameter.XmlCollectionId = int.Parse(dwParameters[j].Row["xml_collection_id"].ToString());
parameter.XmlCollection = dwParameters[j].Row["xmlcollection"].ToString();
parameter.XmlCollectionSchema = dwParameters[j].Row["XmlSchemaCollectionSchema"].ToString();
var dep = new Dependency();
dep.ObjectId = sp.ObjectId;
dep.Schema = sp.Schema;
dep.Name = sp.Name;
dep.Type = "P";
dep.ReferencedObjectId = parameter.XmlCollectionId;
dep.ReferencedName = parameter.XmlCollection;
dep.ReferencedType = "XS";
dep.ReferencedSchema = parameter.XmlCollectionSchema;
_dependencies.Add(dep);
}
parameter.Description = dwParameters[j].Row["Description"].ToString();
sp.Parameters.Add(parameter);
}
//DataTable dtParameters = ds.Tables[int.Parse(hsResultSets["SPCollection"].ToString())];
procedures.Add(sp);
OnObjectFetched(new FetchEventArgs(sp));
}
return procedures;
}
private List<View> GetViews(DataTable dtViews, DataTable dtColumns)
{
var views = new List<View>();
var dwViews = new DataView(dtViews);
for (var i = 0; i < dwViews.Count; i++)
{
var view = new View();
view.Definition = dwViews[i].Row["Definition"].ToString();
view.WithCheckOption = bool.Parse(dwViews[i].Row["with_check_option"].ToString());
view.IsRecompiled = bool.Parse(dwViews[i].Row["is_recompiled"].ToString());
view.IsSchemaBound = bool.Parse(dwViews[i].Row["is_schema_bound"].ToString());
view.ObjectId = long.Parse(dwViews[i].Row["object_id"].ToString());
view.Name = dwViews[i].Row["Name"].ToString();
view.Schema = dwViews[i].Row["SchemaName"].ToString();
view.UsesAnsiNull = bool.Parse(dwViews[i].Row["uses_ansi_nulls"].ToString());
view.UsesQuotedIdentifier = bool.Parse(dwViews[i].Row["uses_quoted_identifier"].ToString());
view.Description = dwViews[i].Row["Description"].ToString();
view.Columns = GetViewColumns(dtColumns, view.ObjectId);
views.Add(view);
OnObjectFetched(new FetchEventArgs(view));
}
return views;
}
private static SortedList<int, string> GetTableDataCompression(DataTable dtTableDataCompression, long tableId)
{
var dataCompression = new SortedList<int, string>();
var rowFilter = "Object_Id=" + tableId;
var dwTableDataCompression = new DataView(dtTableDataCompression, rowFilter, "Partition_Number", DataViewRowState.CurrentRows);
for (var i = 0; i < dwTableDataCompression.Count; i++)
{
dataCompression.Add(int.Parse(dwTableDataCompression[i].Row["partition_number"].ToString()), dwTableDataCompression[i].Row["data_compression_desc"].ToString());
}
return dataCompression;
}
}
}
Submit an article or tip