public void Fill(Database database, string connectionString)
{
ICode code = null;
try
{
if ((database.Options.Ignore.FilterStoreProcedure) || (database.Options.Ignore.FilterView) || (database.Options.Ignore.FilterFunction) || (database.Options.Ignore.FilterTrigger))
{
root.RaiseOnReading(new ProgressEventArgs("Reading Text Objects...", Constants.READING_TEXTOBJECTS));
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQL(database.Options), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
code = null;
root.RaiseOnReadingOne(reader["name"]);
string type = reader["Type"].ToString().Trim();
string name = reader["name"].ToString();
string definition = reader["Text"].ToString();
int id = (int)reader["object_id"];
if (type.Equals("V"))
{
code = (ICode)database.Views.Find(id);
}
if (type.Equals("TR"))
{
code = (ICode)database.Find(id);
}
if (type.Equals("P"))
{
((ICode)database.Procedures.Find(id)).Text = GetObjectDefinition(type, name, definition);
}
if (type.Equals("IF") || type.Equals("FN") || type.Equals("TF"))
{
code = (ICode)database.Functions.Find(id);
}
if (code != null)
{
code.Text = reader["Text"].ToString();
}
}
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public void Fill(Database database, string connectionString)
{
//not supported in azure yet
if (database.Info.Version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10)
{
return;
}
int parentId = 0;
bool change = false;
Table parent = null;
root.RaiseOnReading(new ProgressEventArgs("Reading FullText Index...", Constants.READING_INDEXES));
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(FullTextIndexSQLCommand.Get(database.Info.Version), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
FullTextIndex item = null;
while (reader.Read())
{
root.RaiseOnReadingOne(reader["Name"]);
if (parentId != (int)reader["object_id"])
{
parentId = (int)reader["object_id"];
parent = database.Tables.Find(parentId);
change = true;
}
else
{
change = false;
}
if (change)
{
item = new FullTextIndex(parent);
item.Name = reader["Name"].ToString();
item.Owner = parent.Owner;
item.FullText = reader["FullTextCatalogName"].ToString();
item.Index = reader["IndexName"].ToString();
item.IsDisabled = !(bool)reader["is_enabled"];
item.ChangeTrackingState = reader["ChangeTracking"].ToString();
if (database.Info.Version == DatabaseInfo.VersionTypeEnum.SQLServer2008)
{
item.FileGroup = reader["FileGroupName"].ToString();
}
((Table)parent).FullTextIndex.Add(item);
}
FullTextIndexColumn ccon = new FullTextIndexColumn();
ccon.ColumnName = reader["ColumnName"].ToString();
ccon.Language = reader["LanguageName"].ToString();
item.Columns.Add(ccon);
}
}
}
}
}
public void FillCheck(Database database, string connectionString)
{
int parentId = 0;
ISchemaBase table = null;
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(ConstraintSQLCommand.GetCheck(database.Info.Version), conn))
{
root.RaiseOnReading(new ProgressEventArgs("Reading constraint...", Constants.READING_CONSTRAINTS));
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
Constraint item = null;
while (reader.Read())
{
root.RaiseOnReadingOne(reader["Name"]);
if (parentId != (int)reader["parent_object_id"])
{
parentId = (int)reader["parent_object_id"];
if (reader["ObjectType"].ToString().Trim().Equals("U"))
{
table = database.Tables.Find(parentId);
}
else
{
table = database.TablesTypes.Find(parentId);
}
}
item = new Constraint(table);
item.Id = (int)reader["id"];
item.Name = reader["Name"].ToString();
item.Type = Constraint.ConstraintType.Check;
item.Definition = reader["Definition"].ToString();
item.WithNoCheck = (bool)reader["WithCheck"];
item.IsDisabled = (bool)reader["is_disabled"];
item.Owner = reader["Owner"].ToString();
if (database.Options.Ignore.FilterNotForReplication)
{
item.NotForReplication = (bool)reader["is_not_for_replication"];
}
if (reader["ObjectType"].ToString().Trim().Equals("U"))
{
((Table)table).Constraints.Add(item);
}
else
{
((TableType)table).Constraints.Add(item);
}
}
}
}
}
}
public void Fill(Database database, string connectionString)
{
if ((database.Options.Ignore.FilterStoreProcedure) || (database.Options.Ignore.FilterCLRStoreProcedure))
{
root.RaiseOnReading(new ProgressEventArgs("Reading Store Procedures...", Constants.READING_PROCEDURES));
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQL(database.Info.Version), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
InitIndex(reader);
root.RaiseOnReadingOne(reader[NameIndex]);
if ((reader[typeIndex].ToString().Trim().Equals("P")) && (database.Options.Ignore.FilterStoreProcedure))
{
StoreProcedure item = new StoreProcedure(database);
item.Id = (int)reader[object_idIndex];
item.Name = (string)reader[NameIndex];
item.Owner = (string)reader[ownerIndex];
item.CreateDate = (DateTime)reader[createDateIndex];
item.ModifyDate = (DateTime)reader[modifyDateIndex];
database.Procedures.Add(item);
}
if ((reader[typeIndex].ToString().Trim().Equals("PC")) && (database.Options.Ignore.FilterCLRStoreProcedure))
{
CLRStoreProcedure item = new CLRStoreProcedure(database);
item.Id = (int)reader[object_idIndex];
item.Name = reader[NameIndex].ToString();
item.Owner = reader[ownerIndex].ToString();
item.IsAssembly = true;
item.AssemblyId = (int)reader["assembly_id"];
item.AssemblyName = reader["assembly_name"].ToString();
item.AssemblyClass = reader["assembly_class"].ToString();
item.AssemblyExecuteAs = reader["ExecuteAs"].ToString();
item.AssemblyMethod = reader["assembly_method"].ToString();
database.CLRProcedures.Add(item);
}
}
}
}
}
if (database.CLRProcedures.Count > 0)
{
FillParameters(database, connectionString);
}
}
}
private void FillView(Database database, string connectionString)
{
int lastViewId = 0;
using (var conn = new SqlConnection(connectionString))
{
using (var command = new SqlCommand(ViewSQLCommand.GetView(database.Info.Version), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
View item = null;
while (reader.Read())
{
root.RaiseOnReadingOne(reader["name"]);
if (lastViewId != (int)reader["object_id"])
{
item = new View(database)
{
Id = (int)reader["object_id"],
Name = reader["name"].ToString(),
Owner = reader["owner"].ToString(),
IsSchemaBinding = reader["IsSchemaBound"].ToString().Equals("1"),
CreateDate = (DateTime)reader["create_date"],
ModifyDate = (DateTime)reader["modify_date"]
};
database.Views.Add(item);
lastViewId = item.Id;
}
if (item.IsSchemaBinding)
{
if (!reader.IsDBNull(reader.GetOrdinal("referenced_major_id")))
{
database.Dependencies.Add(database, (int)reader["referenced_major_id"], item);
}
if (!String.IsNullOrEmpty(reader["TableName"].ToString()))
{
item.DependenciesIn.Add(reader["TableName"].ToString());
}
if (!String.IsNullOrEmpty(reader["DependOut"].ToString()))
{
item.DependenciesOut.Add(reader["DependOut"].ToString());
}
}
}
}
}
}
}
public void Fill(Database database, string connectionString)
{
//TODO XML_SCHEMA_NAMESPACE function not supported in Azure, is there a workaround?
//not supported in azure yet
if (database.Info.Version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10)
{
return;
}
if (database.Options.Ignore.FilterXMLSchema)
{
root.RaiseOnReading(new ProgressEventArgs("Reading XML Schema...", Constants.READING_XMLSCHEMAS));
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQLXMLSchema(), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
root.RaiseOnReadingOne(reader["name"]);
XMLSchema item = new XMLSchema(database);
item.Id = (int)reader["ID"];
item.Name = reader["name"].ToString();
item.Owner = reader["owner"].ToString();
item.Text = reader["Text"].ToString();
database.XmlSchemas.Add(item);
}
}
}
}
if (database.Options.Ignore.FilterTable)
{
FillColumnsDependencies(database.XmlSchemas, connectionString);
}
}
}
public void Fill(Database database, string connectionString)
{
int indexid = 0;
int parentId = 0;
bool change = false;
string type;
ISchemaBase parent = null;
root.RaiseOnReading(new ProgressEventArgs("Reading Index...", Constants.READING_INDEXES));
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(IndexSQLCommand.Get(database.Info.Version), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
Index item = null;
while (reader.Read())
{
root.RaiseOnReadingOne(reader["Name"]);
type = reader["ObjectType"].ToString().Trim();
if (parentId != (int)reader["object_id"])
{
parentId = (int)reader["object_id"];
if (type.Equals("V"))
{
parent = database.Views.Find(parentId);
}
else
{
parent = database.Tables.Find(parentId);
}
change = true;
}
else
{
change = false;
}
if ((indexid != (int)reader["Index_id"]) || (change))
{
item = new Index(parent);
item.Name = reader["Name"].ToString();
item.Owner = parent.Owner;
item.Type = (Index.IndexTypeEnum)(byte) reader["type"];
item.Id = (int)reader["Index_id"];
item.IgnoreDupKey = (bool)reader["ignore_dup_key"];
item.IsAutoStatistics = (bool)reader["NoAutomaticRecomputation"];
item.IsDisabled = (bool)reader["is_disabled"];
item.IsPrimaryKey = (bool)reader["is_primary_key"];
item.IsUniqueKey = (bool)reader["is_unique"];
if (database.Options.Ignore.FilterIndexRowLock)
{
item.AllowPageLocks = (bool)reader["allow_page_locks"];
item.AllowRowLocks = (bool)reader["allow_row_locks"];
}
if (database.Options.Ignore.FilterIndexFillFactor)
{
item.FillFactor = (byte)reader["fill_factor"];
item.IsPadded = (bool)reader["is_padded"];
}
if ((database.Options.Ignore.FilterTableFileGroup) && (item.Type != Index.IndexTypeEnum.XML))
{
item.FileGroup = reader["FileGroup"].ToString();
}
if ((database.Info.Version == DatabaseInfo.VersionTypeEnum.SQLServer2008) && (database.Options.Ignore.FilterIndexFilter))
{
item.FilterDefintion = reader["FilterDefinition"].ToString();
}
indexid = (int)reader["Index_id"];
if (type.Equals("V"))
{
((View)parent).Indexes.Add(item);
}
else
{
((Table)parent).Indexes.Add(item);
}
}
IndexColumn ccon = new IndexColumn(item.Parent);
ccon.Name = reader["ColumnName"].ToString();
ccon.IsIncluded = (bool)reader["is_included_column"];
ccon.Order = (bool)reader["is_descending_key"];
ccon.Id = (int)reader["column_id"];
ccon.KeyOrder = (byte)reader["key_ordinal"];
ccon.DataTypeId = (int)reader["user_type_id"];
if ((!ccon.IsIncluded) || (ccon.IsIncluded && database.Options.Ignore.FilterIndexIncludeColumns))
{
item.Columns.Add(ccon);
}
}
}
}
}
}
public void Fill(Database database, string connectionString, List <MessageLog> messages)
{
//not supported in azure yet http://msdn.microsoft.com/en-us/library/ee336233.aspx
if (database.Info.Version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10)
{
return;
}
try
{
if (database.Options.Ignore.FilterUserDataType)
{
root.RaiseOnReading(new ProgressEventArgs("Reading UDT...", Constants.READING_UDT));
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(UserDataTypeCommand.Get(database.Info.Version), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
root.RaiseOnReadingOne(reader["Name"]);
UserDataType item = new UserDataType(database);
item.Id = (int)reader["tid"];
item.AllowNull = (bool)reader["is_nullable"];
item.Size = (short)reader["max_length"];
item.Name = reader["Name"].ToString();
item.Owner = reader["owner"].ToString();
item.Precision = int.Parse(reader["precision"].ToString());
item.Scale = int.Parse(reader["scale"].ToString());
if (!String.IsNullOrEmpty(reader["defaultname"].ToString()))
{
item.Default.Name = reader["defaultname"].ToString();
item.Default.Owner = reader["defaultowner"].ToString();
}
if (!String.IsNullOrEmpty(reader["rulename"].ToString()))
{
item.Rule.Name = reader["rulename"].ToString();
item.Rule.Owner = reader["ruleowner"].ToString();
}
item.Type = reader["basetypename"].ToString();
item.IsAssembly = (bool)reader["is_assembly_type"];
item.AssemblyId = (int)reader["assembly_id"];
item.AssemblyName = reader["assembly_name"].ToString();
item.AssemblyClass = reader["assembly_class"].ToString();
database.UserTypes.Add(item);
}
}
}
}
if (database.Options.Ignore.FilterTable)
{
FillColumnsDependencies(database.UserTypes, connectionString);
}
}
}
catch (Exception ex)
{
messages.Add(new MessageLog(ex.Message, ex.StackTrace, MessageLog.LogType.Error));
}
}
public void Fill(Database database, string connectionString, List <MessageLog> messages)
{
int parentId = 0;
ISchemaBase parent = null;
string type;
try
{
if (database.Options.Ignore.FilterTrigger)
{
root.RaiseOnReading(new ProgressEventArgs("Reading Triggers...", Constants.READING_TRIGGERS));
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQL(database.Info.Version, database.Options), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
root.RaiseOnReadingOne(reader["Name"]);
type = reader["ObjectType"].ToString().Trim();
if (parentId != (int)reader["parent_id"])
{
parentId = (int)reader["parent_id"];
if (type.Equals("V"))
{
parent = database.Views.Find(parentId);
}
else
{
parent = database.Tables.Find(parentId);
}
}
if (reader["type"].Equals("TR"))
{
Trigger item = new Trigger(parent);
item.Id = (int)reader["object_id"];
item.Name = reader["Name"].ToString();
item.InsteadOf = (bool)reader["is_instead_of_trigger"];
item.IsDisabled = (bool)reader["is_disabled"];
item.IsDDLTrigger = false;
item.Owner = reader["Owner"].ToString();
if (database.Options.Ignore.FilterNotForReplication)
{
item.NotForReplication = (bool)reader["is_not_for_replication"];
}
if (type.Equals("V"))
{
((View)parent).Triggers.Add(item);
}
else
{
((Table)parent).Triggers.Add(item);
}
}
else
{
CLRTrigger item = new CLRTrigger(parent);
item.Id = (int)reader["object_id"];
item.Name = reader["Name"].ToString();
item.IsDelete = (bool)reader["IsDelete"];
item.IsUpdate = (bool)reader["IsUpdate"];
item.IsInsert = (bool)reader["IsInsert"];
item.Owner = reader["Owner"].ToString();
item.IsAssembly = true;
item.AssemblyId = (int)reader["assembly_id"];
item.AssemblyName = reader["assembly_name"].ToString();
item.AssemblyClass = reader["assembly_class"].ToString();
item.AssemblyExecuteAs = reader["ExecuteAs"].ToString();
item.AssemblyMethod = reader["assembly_method"].ToString();
if (type.Equals("V"))
{
((View)parent).CLRTriggers.Add(item);
}
else
{
((Table)parent).CLRTriggers.Add(item);
}
/*if (!database.Options.Ignore.FilterIgnoreNotForReplication)
* trigger.NotForReplication = (bool)reader["is_not_for_replication"];*/
}
}
}
}
}
}
}
catch (Exception ex)
{
messages.Add(new MessageLog(ex.Message, ex.StackTrace, MessageLog.LogType.Error));
}
}
private void FillTables(Database database, string connectionString)
{
int textInRow;
Boolean largeValues;
Boolean varDecimal;
int lastObjectId = 0;
bool isTable = true;
ISchemaBase item = null;
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(TableSQLCommand.GetTableDetail(database.Info.Version), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
InitTableIndex(database, reader);
root.RaiseOnReadingOne(reader[TableNameIndex]);
if (lastObjectId != (int)reader[TableIdIndex])
{
lastObjectId = (int)reader[TableIdIndex];
isTable = reader["ObjectType"].ToString().Trim().Equals("U");
if (isTable)
{
item = new Table(database);
item.Id = (int)reader[TableIdIndex];
item.Name = (string)reader[TableNameIndex];
item.Owner = (string)reader[TableOwnerIndex];
item.CreateDate = (DateTime)reader[TableCreateDateIndex];
item.ModifyDate = (DateTime)reader[TableModifyDateIndex];
((Table)item).HasClusteredIndex = (int)reader[HasClusteredIndexIndex] == 1;
textInRow = (int)reader[Text_In_Row_limitIndex];
largeValues = (Boolean)reader[large_value_types_out_of_rowIndex];
varDecimal = ((int)reader[HasVarDecimalIndex]) == 1;
if (database.Options.Ignore.FilterTableFileGroup)
{
((Table)item).FileGroup = (string)reader[FileGroupIndex];
((Table)item).FileGroupText = (string)reader[FileGroupTextIndex];
if (database.Info.Version == DatabaseInfo.VersionTypeEnum.SQLServer2008)
{
if (database.Options.Ignore.FilterTableChangeTracking)
{
((Table)item).FileGroupStream = (string)reader[FileGroupStreamIndex];
((Table)item).HasChangeTracking = ((int)reader[TableHasChangeTracking]) == 1;
((Table)item).HasChangeTrackingTrackColumn = ((int)reader[TableHasChangeTrackingTrackColumn]) == 1;
}
}
}
if (database.Options.Ignore.FilterTableOption)
{
if (textInRow > 0)
{
((Table)item).Options.Add(new TableOption("TextInRow", textInRow.ToString(CultureInfo.InvariantCulture), item));
}
if (largeValues)
{
((Table)item).Options.Add(new TableOption("LargeValues", "1", item));
}
if (varDecimal)
{
((Table)item).Options.Add(new TableOption("VarDecimal", "1", item));
}
}
if ((database.Options.Ignore.FilterTableLockEscalation) && (database.Info.Version == DatabaseInfo.VersionTypeEnum.SQLServer2008))
{
((Table)item).Options.Add(new TableOption("LockEscalation", (string)reader[TableLockEscalation], item));
}
else
{
((Table)item).Options.Add(new TableOption("LockEscalation", "TABLE", item));
}
database.Tables.Add((Table)item);
}
else
{
item = new TableType(database)
{
Id = (int)reader[TableIdIndex],
Name = (string)reader[TableNameIndex],
Owner = (string)reader[TableOwnerIndex]
};
database.TablesTypes.Add((TableType)item);
}
}
if (isTable)
{
if (database.Options.Ignore.FilterTable)
{
FillColumn((ITable <Table>)item, reader);
}
}
else
{
if (database.Options.Ignore.FilterUserDataType)
{
FillColumn((ITable <TableType>)item, reader);
}
}
}
}
}
}
//tables.ToSQL();
}
public void Fill(Database database, string connectionString)
{
int lastViewId = 0;
if ((database.Options.Ignore.FilterFunction) || (database.Options.Ignore.FilterCLRFunction))
{
root.RaiseOnReading(new ProgressEventArgs("Reading functions...", Constants.READING_FUNCTIONS));
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(FunctionSQLCommand.Get(database.Info.Version), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
Function itemF = null;
CLRFunction itemC = null;
while (reader.Read())
{
root.RaiseOnReadingOne(reader["name"]);
if ((!reader["type"].ToString().Trim().Equals("FS")) && (database.Options.Ignore.FilterFunction))
{
if (lastViewId != (int)reader["object_id"])
{
itemF = new Function(database);
itemF.Id = (int)reader["object_id"];
itemF.Name = reader["name"].ToString();
itemF.Owner = reader["owner"].ToString();
itemF.IsSchemaBinding = reader["IsSchemaBound"].ToString().Equals("1");
itemF.CreateDate = (DateTime)reader["create_date"];
itemF.ModifyDate = (DateTime)reader["modify_date"];
database.Functions.Add(itemF);
lastViewId = itemF.Id;
}
if (itemF.IsSchemaBinding)
{
if (!reader.IsDBNull(reader.GetOrdinal("referenced_major_id")))
{
database.Dependencies.Add(database, (int)reader["referenced_major_id"], itemF);
}
if (!String.IsNullOrEmpty(reader["TableName"].ToString()))
{
itemF.DependenciesIn.Add(reader["TableName"].ToString());
}
if (!String.IsNullOrEmpty(reader["DependOut"].ToString()))
{
itemF.DependenciesOut.Add(reader["DependOut"].ToString());
}
}
}
if ((reader["type"].ToString().Trim().Equals("FS")) && (database.Options.Ignore.FilterCLRFunction))
{
itemC = new CLRFunction(database);
if (lastViewId != (int)reader["object_id"])
{
itemC.Id = (int)reader["object_id"];
itemC.Name = reader["name"].ToString();
itemC.Owner = reader["owner"].ToString();
itemC.IsAssembly = true;
itemC.AssemblyId = (int)reader["assembly_id"];
itemC.AssemblyName = reader["assembly_name"].ToString();
itemC.AssemblyClass = reader["assembly_class"].ToString();
itemC.AssemblyExecuteAs = reader["ExecuteAs"].ToString();
itemC.AssemblyMethod = reader["assembly_method"].ToString();
itemC.ReturnType.Type = reader["ReturnType"].ToString();
itemC.ReturnType.Size = (short)reader["max_length"];
itemC.ReturnType.Scale = (byte)reader["Scale"];
itemC.ReturnType.Precision = (byte)reader["precision"];
if (itemC.ReturnType.Type.Equals("nchar") || itemC.ReturnType.Type.Equals("nvarchar"))
{
if (itemC.ReturnType.Size != -1)
{
itemC.ReturnType.Size = itemC.ReturnType.Size / 2;
}
}
database.CLRFunctions.Add(itemC);
lastViewId = itemC.Id;
}
}
}
}
}
}
}
if (database.CLRFunctions.Count > 0)
{
FillParameters(database, connectionString);
}
}
