public void Fill(Database database, string connectionString)
{
if (database.Options.Ignore.FilterRules)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Rule item = new Rule(database);
item.Id = (int)reader["object_id"];
item.Name = reader["Name"].ToString();
item.Owner = reader["Owner"].ToString();
item.Text = reader["Definition"].ToString();
database.Rules.Add(item);
}
}
}
}
}
}
public DatabaseInfo(string connectionString, Database database)
{
Version = VersionNumber.SQLServer2005;
this._connectionString = connectionString;
this._database = database;
//Initialise all the properties of this database
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
//Set the version number
this.Version = DBDiff.Schema.SQLServer.Util.GetVersionNumber(conn);
using (SqlCommand command = new SqlCommand(DatabaseSQLCommand.GetDatabaseProperties(this.Version, this._database), conn))
{
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
this.Collation = reader["Collation"].ToString();
this.HasFullTextEnabled = ((int)reader["IsFulltextEnabled"]) == 1;
this.CompatibilityLevel = reader["CompatibilityLevel"].ToString();
}
}
}
}
}
public void Fill(Database database, string connectioString)
{
if (database.Options.Ignore.FilterSchema)
{
using (SqlConnection conn = new SqlConnection(connectioString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Model.Schema item = new Model.Schema(database);
item.Id = (int)reader["schema_id"];
item.Name = reader["name"].ToString();
item.Owner = reader["owner"].ToString();
database.Schemas.Add(item);
}
}
}
}
}
}
//private static string GetSQL()
//{
// string sql = "SELECT obj.object_id, Name, SCHEMA_NAME(obj.schema_id) AS Owner, ISNULL(smobj.definition, ssmobj.definition) AS [Definition] from sys.objects obj ";
// sql += "LEFT OUTER JOIN sys.sql_modules AS smobj ON smobj.object_id = obj.object_id ";
// sql += "LEFT OUTER JOIN sys.system_sql_modules AS ssmobj ON ssmobj.object_id = obj.object_id ";
// sql += "where obj.type='D'";
// return sql;
//}
public void Fill(Database database, string connectionString)
{
if (database.Options.Ignore.FilterRules)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(DefaultSQLCommand.Get(database.Info.Version), conn))
{
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Default item = new Default(database);
item.Id = (int)reader["object_id"];
item.Name = reader["Name"].ToString();
item.Owner = reader["Owner"].ToString();
item.Value = reader["Definition"].ToString();
database.Defaults.Add(item);
}
}
}
}
}
}
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)
{
if (database.Info.Version == DatabaseInfo.VersionNumber.SQLServer2000) return;
if (database.Options.Ignore.FilterDDLTriggers)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Trigger trigger = new Trigger(database);
trigger.Text = reader["Text"].ToString();
trigger.Name = reader["Name"].ToString();
trigger.InsteadOf = (bool)reader["is_instead_of_trigger"];
trigger.IsDisabled = (bool)reader["is_disabled"];
trigger.IsDDLTrigger = true;
trigger.NotForReplication = (bool)reader["is_not_for_replication"];
trigger.Owner = "";
database.DDLTriggers.Add(trigger);
}
}
}
}
}
}
private void ProgressForm_Activated(object sender, EventArgs e)
{
if (!IsProcessing)
{
this.Refresh();
IsProcessing = false;
genData1.OnProgress += new DBDiff.Schema.Events.ProgressEventHandler.ProgressHandler(genData1_OnProgress);
genData2.OnProgress += new DBDiff.Schema.Events.ProgressEventHandler.ProgressHandler(genData2_OnProgress);
/*Thread t1 = new Thread(delegate()
{*/
origen = genData1.Process();
/*});
Thread t2 = new Thread(delegate()
{*/
destino = genData2.Process();
origenClone = (Database)origen.Clone(null);
/*});
t1.Start();
t2.Start();
t1.Join();
t2.Join();
*/
destino = Generate.Compare(origen, destino);
origen = origenClone;
databaseProgressControl1.Message = "Complete";
databaseProgressControl2.Message = "Complete";
databaseProgressControl1.Value = Generate.MaxValue;
databaseProgressControl2.Value = Generate.MaxValue;
this.Dispose();
}
}
//private static string GetSQLFile(Database database,FileGroup filegroup)
//{
// string sql;
// sql = "select file_id,";
// sql += "type,";
// sql += "name,";
// sql += "physical_name,";
// sql += "size,";
// sql += "max_size,";
// sql += "growth,";
// sql += "is_sparse,";
// sql += "is_percent_growth ";
// sql += "from sys.database_files WHERE data_space_id = " + filegroup.Id.ToString();
// return sql;
//}
private static void FillFiles(Database database,FileGroup filegroup, string connectionString)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(FileGroupSQLCommand.GetFilesInFileGroup(database.Info.Version,filegroup), conn))
{
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
FileGroupFile item = new FileGroupFile(filegroup);
item.Id = (int)reader["file_id"];
item.Name = reader["name"].ToString();
item.Owner = "";
item.Growth = (int)reader["growth"];
item.IsPercentGrowth = (bool)reader["is_percent_growth"];
item.IsSparse = (bool)reader["is_sparse"];
item.MaxSize = (int)reader["max_size"];
item.PhysicalName = reader["physical_name"].ToString();
item.Size = (int)reader["size"];
item.Type = (byte)reader["type"];
filegroup.Files.Add(item);
}
}
}
}
}
public void Fill(Database database, string connectionString)
{
if (database.Options.Ignore.FilterFullText)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
FullText item = new FullText(database);
item.Id = (int)reader["fulltext_catalog_id"];
item.Name = reader["Name"].ToString();
item.Owner = reader["Owner"].ToString();
item.IsAccentSensity = (bool)reader["is_accent_sensitivity_on"];
item.IsDefault = (bool)reader["is_default"];
if (!reader.IsDBNull(reader.GetOrdinal("path")))
item.Path = reader["path"].ToString().Substring(0, reader["path"].ToString().Length - item.Name.Length);
if (!reader.IsDBNull(reader.GetOrdinal("FileGroupName")))
item.FileGroupName = reader["FileGroupName"].ToString();
database.FullText.Add(item);
}
}
}
}
}
}
public void Fill(Database database, string connectionString)
{
try
{
if (database.Options.Ignore.FilterTableFileGroup)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
FileGroup item = new FileGroup(database);
item.Id = (int)reader["ID"];
item.Name = reader["name"].ToString();
item.Owner = "";
item.IsDefaultFileGroup = (bool)reader["is_default"];
item.IsReadOnly = (bool)reader["is_read_only"];
item.IsFileStream = reader["type"].Equals("FD");
FillFiles(item,connectionString);
database.FileGroups.Add(item);
}
}
}
}
}
}
catch
{
throw;
}
}
private static void FillFiles(Database database, string connectionString)
{
if (database.Options.Ignore.FilterAssemblies)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQLFiles(), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (((int)reader["FileId"]) != 1)
{
Assembly assem = database.Assemblies[reader["Name"].ToString()];
AssemblyFile file = new AssemblyFile(assem,reader["FileName"].ToString(), ToHex((byte[])reader["FileContent"]));
assem.Files.Add(file);
}
}
}
}
}
}
}
public void Fill(Database database, string connectioString)
{
int lastObjectId = 0;
PartitionScheme item = null;
if (database.Info.Version == DatabaseInfo.VersionNumber.SQLServer2000) return;
if (database.Options.Ignore.FilterPartitionScheme)
{
using (SqlConnection conn = new SqlConnection(connectioString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (lastObjectId != (int)reader["ID"])
{
lastObjectId = (int)reader["ID"];
item = new PartitionScheme(database);
item.Id = (int)reader["ID"];
item.Name = reader["name"].ToString();
item.PartitionFunction = reader["FunctionName"].ToString();
database.PartitionSchemes.Add(item);
}
item.FileGroups.Add(reader["FileGroupName"].ToString());
}
}
}
}
}
}
private static void FillParameters(Database database, string connectionString)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQLParameters(), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Parameter param = new Parameter();
param.Name = reader["Name"].ToString();
param.Type = reader["TypeName"].ToString();
param.Size = (short)reader["max_length"];
param.Scale = (byte)reader["scale"];
param.Precision = (byte)reader["precision"];
param.Output = (bool)reader["is_output"];
if (param.Type.Equals("nchar") || param.Type.Equals("nvarchar"))
{
if (param.Size != -1)
param.Size = param.Size / 2;
}
database.CLRProcedures[reader["ObjectName"].ToString()].Parameters.Add(param);
}
}
}
}
}
public void Fill(Database database, string connectionString)
{
if (database.Info.Version == DatabaseInfo.VersionNumber.SQLServer2000) return;
if (database.Options.Ignore.FilterXMLSchema)
{
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())
{
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);
root.RaiseOnReading(new ProgressEventArgs("Reading XML Schema " + item.Name + "...", Constants.READING_XMLSCHEMAS));
}
}
}
}
if (database.Options.Ignore.FilterTable)
FillColumnsDependencies(database.XmlSchemas, connectionString);
}
}
public void Fill(Database database, string connectionString)
{
if (database.Options.Ignore.FilterSynonyms)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Synonym item = new Synonym(database);
item.Id = (int)reader["object_id"];
item.Name = reader["Name"].ToString();
item.Owner = reader["Owner"].ToString();
item.Value = reader["base_object_name"].ToString();
database.Synonyms.Add(item);
}
}
}
}
}
}
private static string GetAzure(Database databaseSchema)
{
string sql;
//DATABASEPROPERTYEX('IsFullTextEnabled') is deprecated http://technet.microsoft.com/en-us/library/cc646010(SQL.110).aspx
sql = "SELECT 0 AS IsFullTextEnabled, DATABASEPROPERTYEX('" + databaseSchema.Name + "','Collation') AS Collation";
return sql;
}
public static string Get(DatabaseInfo.VersionTypeEnum version, Database databaseSchema)
{
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2005) return Get2005(databaseSchema);
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2008) return Get2008(databaseSchema);
if (version == DatabaseInfo.VersionTypeEnum.SQLServer2008R2) return Get2008R2(databaseSchema);
if (version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10) return GetAzure(databaseSchema);
return "";
}
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)
{
if (database.Options.Ignore.FilterConstraintPK)
FillPrimaryKey(database, connectionString);
if (database.Options.Ignore.FilterConstraintFK)
FillForeignKey(database, connectionString);
if (database.Options.Ignore.FilterConstraintUK)
FillUniqueKey(database, connectionString);
if (database.Options.Ignore.FilterConstraintCheck)
FillCheck(database, connectionString);
}
//private static string GetSQLForeignKey()
//{
// StringBuilder sql = new StringBuilder();
// sql.Append("SELECT FK.object_id, C.user_type_id ,FK.parent_object_id,S.Name AS Owner, S2.Name AS ReferenceOwner, C2.Name AS ColumnName, C2.column_id AS ColumnId, C.name AS ColumnRelationalName, C.column_id AS ColumnRelationalId, T.object_id AS TableRelationalId, FK.Parent_object_id AS TableId, T.Name AS TableRelationalName, FK.Name, FK.is_disabled, FK.is_not_for_replication, FK.is_not_trusted, FK.delete_referential_action, FK.update_referential_action ");
// sql.Append("FROM sys.foreign_keys FK ");
// sql.Append("INNER JOIN sys.tables T ON T.object_id = FK.referenced_object_id ");
// sql.Append("INNER JOIN sys.schemas S2 ON S2.schema_id = T.schema_id ");
// sql.Append("INNER JOIN sys.foreign_key_columns FKC ON FKC.constraint_object_id = FK.object_id ");
// sql.Append("INNER JOIN sys.columns C ON C.object_id = FKC.referenced_object_id AND C.column_id = referenced_column_id ");
// sql.Append("INNER JOIN sys.columns C2 ON C2.object_id = FKC.parent_object_id AND C2.column_id = parent_column_id ");
// sql.Append("INNER JOIN sys.schemas S ON S.schema_id = FK.schema_id ");
// sql.Append("ORDER BY FK.parent_object_id, FK.Name, ColumnId");
// return sql.ToString();
//}
private static void FillForeignKey(Database database, string connectionString)
{
int lastid = 0;
int parentId = 0;
Table table = null;
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(ForeignKeySQLCommand.Get(database.Info.Version), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
Constraint con = null;
while (reader.Read())
{
if (parentId != (int)reader["parent_object_id"])
{
parentId = (int)reader["parent_object_id"];
table = database.Tables.Find(parentId);
}
if (lastid != (int)reader["object_id"])
{
con = new Constraint(table);
con.Id = (int)reader["object_id"];
con.Name = reader["Name"].ToString();
con.Type = Constraint.ConstraintType.ForeignKey;
con.WithNoCheck = (bool)reader["is_not_trusted"];
con.RelationalTableFullName = "[" + reader["ReferenceOwner"].ToString() + "].[" + reader["TableRelationalName"].ToString() + "]";
con.RelationalTableId = (int)reader["TableRelationalId"];
con.Owner = reader["Owner"].ToString();
con.IsDisabled = (bool)reader["is_disabled"];
con.OnDeleteCascade = (byte)reader["delete_referential_action"];
con.OnUpdateCascade = (byte)reader["update_referential_action"];
if (database.Options.Ignore.FilterNotForReplication)
con.NotForReplication = (bool)reader["is_not_for_replication"];
lastid = (int)reader["object_id"];
table.Constraints.Add(con);
}
ConstraintColumn ccon = new ConstraintColumn(con);
ccon.Name = reader["ColumnName"].ToString();
ccon.ColumnRelationalName = reader["ColumnRelationalName"].ToString();
ccon.ColumnRelationalId = (int)reader["ColumnRelationalId"];
ccon.Id = (int)reader["ColumnId"];
ccon.KeyOrder = con.Columns.Count;
ccon.ColumnRelationalDataTypeId = (int)reader["user_type_id"];
//table.DependenciesCount++;
con.Columns.Add(ccon);
}
}
}
}
}
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 static string GetDatabaseProperties(DatabaseInfo.VersionNumber version, Database databaseSchema)
{
switch (version)
{
case DatabaseInfo.VersionNumber.SQLServer2000:
case DatabaseInfo.VersionNumber.SQLServer2005:
case DatabaseInfo.VersionNumber.SQLServer2008:
return string.Format("SELECT DATABASEPROPERTYEX('{0}','IsFulltextEnabled') AS IsFullTextEnabled, DATABASEPROPERTYEX('{0}','Collation') AS Collation, cmptlevel AS CompatibilityLevel from master..sysdatabases where name='{0}'", databaseSchema.Name);
break;
default:
return string.Empty;
break;
}
}
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, List<MessageLog> messages)
{
try
{
root.RaiseOnReading(new ProgressEventArgs("Reading views...", Constants.READING_VIEWS));
if (database.Options.Ignore.FilterView)
{
FillView(database,connectionString);
}
}
catch (Exception ex)
{
messages.Add(new MessageLog(ex.Message, ex.StackTrace, MessageLog.LogType.Error));
}
}
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];
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);
}
}
public void Fill(Database database, string connectioString)
{
string type;
if ((database.Options.Ignore.FilterUsers) || (database.Options.Ignore.FilterRoles))
{
using (SqlConnection conn = new SqlConnection(connectioString))
{
using (SqlCommand command = new SqlCommand(UserSQLCommand.Get(database.Info.Version), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
type = reader["type"].ToString();
if (database.Options.Ignore.FilterUsers && (type.Equals("S") || type.Equals("U")))
{
User item = new User(database);
item.Id = (int)reader["principal_id"];
item.Name = reader["name"].ToString();
item.Login = reader["Login"].ToString();
item.Owner = reader["default_schema_name"].ToString();
database.Users.Add(item);
}
if (database.Options.Ignore.FilterRoles && (type.Equals("A") || type.Equals("R")))
{
Role item = new Role(database);
item.Id = (int)reader["principal_id"];
item.Name = reader["name"].ToString();
item.Owner = reader["default_schema_name"].ToString();
item.Password = "";
item.IsSystem = (Boolean)reader["is_fixed_role"];
if (type.Equals("A"))
item.Type = Role.RoleTypeEnum.ApplicationRole;
else
item.Type = Role.RoleTypeEnum.DatabaseRole;
database.Roles.Add(item);
}
}
}
}
}
}
}
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)
{
int lastViewId = 0;
if (database.Info.Version == DatabaseInfo.VersionNumber.SQLServer2000) return;
if (database.Options.Ignore.FilterAssemblies)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
Assembly item = null;
while (reader.Read())
{
if (lastViewId != (int)reader["assembly_id"])
{
item = new Assembly(database)
{
Id = (int) reader["assembly_id"],
Name = reader["Name"].ToString(),
Owner = reader["Owner"].ToString(),
CLRName = reader["clr_name"].ToString(),
PermissionSet = reader["permission_set_desc"].ToString(),
Text = ToHex((byte[]) reader["content"]),
Visible = (bool) reader["is_visible"]
};
lastViewId = item.Id;
database.Assemblies.Add(item);
}
if (!String.IsNullOrEmpty(reader["Dependency"].ToString()))
item.DependenciesOut.Add(reader["Dependency"].ToString());
if (!String.IsNullOrEmpty(reader["ObjectDependency"].ToString()))
item.DependenciesOut.Add(reader["ObjectDependency"].ToString());
if (!String.IsNullOrEmpty(reader["UDTName"].ToString()))
item.DependenciesOut.Add(reader["UDTName"].ToString());
}
}
}
FillFiles(database, connectionString);
}
}
}
public void Fill(Database database, string connectioString)
{
int lastObjectId = 0;
PartitionFunction item = null;
if (database.Info.Version == DatabaseInfo.VersionNumber.SQLServer2000) return;
if (database.Options.Ignore.FilterPartitionFunction)
{
using (SqlConnection conn = new SqlConnection(connectioString))
{
using (SqlCommand command = new SqlCommand(GetSQL(), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (lastObjectId != (int)reader["function_id"])
{
lastObjectId = (int)reader["function_id"];
item = new PartitionFunction(database);
item.Id = (int)reader["function_id"];
item.Name = reader["name"].ToString();
item.IsBoundaryRight = (bool)reader["IsRight"];
item.Precision = (byte)reader["precision"];
item.Scale = (byte)reader["scale"];
item.Size = (short)reader["max_length"];
item.Type = reader["TypeName"].ToString();
database.PartitionFunctions.Add(item);
}
if (item.Type.Equals("binary") || item.Type.Equals("varbinary"))
item.Values.Add(ToHex((byte[])reader["value"]));
else
item.Values.Add(reader["value"].ToString());
}
}
}
}
}
}
private void FillView(Database database, string connectionString)
{
int lastViewId = 0;
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand 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);
item.Id = (int)reader["object_id"];
item.Name = reader["name"].ToString();
item.Owner = reader["owner"].ToString();
item.IsSchemaBinding = reader["IsSchemaBound"].ToString().Equals("1");
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());
}
}
}
}
}
}
