private static List<Entry> ExtractArticles(SqlConnection connection, int id) { var command = connection.CreateCommand(); command.CommandText = "SELECT sysarticles.artid, sysarticles.name, schemas.name AS schema_name, objects.name AS object_name, sysarticles.status, creation_script, " + " del_cmd, filter, filter_clause, ins_cmd, upd_cmd, description, dest_owner, dest_table, status, CAST(schema_option AS BIGINT) AS schema_option " + "FROM dbo.sysarticles " + "INNER JOIN sys.objects " + " ON sysarticles.objid = objects.object_id " + "INNER JOIN sys.schemas " + " ON objects.schema_id = schemas.schema_id " + "WHERE pubid = " + id + " " + "ORDER BY objects.name"; var reader = command.ExecuteReader(); var result = new List<Entry>(); while (reader.Read()) { var entry = new Entry { Element = new Element { Type = "SqlPublicationArticle", Id = reader.GetInt32("artid").Value, Name = string.Format("[{0}].[{1}]", reader.GetString("schema_name"), reader.GetString("object_name")), Properties = new List<Property> { new Property { Name = "CreationScript", Value = reader.GetString("creation_script") }, new Property { Name = "DeleteCommand", Value = reader.GetString("del_cmd") }, new Property { Name = "Description", Value = reader.GetString("description") }, new Property { Name = "DestinationOwner", Value = reader.GetString("dest_owner") }, new Property { Name = "DestinationTable", Value = reader.GetString("dest_table") }, new Property { Name = "Filter", Value = string.Empty }, new Property { Name = "FilterClause", Value = string.Empty }, new Property { Name = "InsertCommand", Value = reader.GetString("ins_cmd") }, new Property { Name = "Name", Value = reader.GetString("name") }, new Property { Name = "StatusActive", Value = TranslateStatusFlag(reader.GetByte(reader.GetOrdinal("status")), 1) }, new Property { Name = "StatusIncludeColumnNameInInsert", Value = TranslateStatusFlag(reader.GetByte(reader.GetOrdinal("status")), 8) }, new Property { Name = "StatusUseParameterizedStatements", Value = TranslateStatusFlag(reader.GetByte(reader.GetOrdinal("status")), 16) }, new Property { Name = "UpdateCommand", Value = reader.GetString("upd_cmd") } }, Relationships = new List<Relationship> { new Relationship { Name = "Table", Entries = new List<Entry> { new Entry { References = new References { Name = string.Format("[{0}].[{1}]", reader.GetString("schema_name"), reader.GetString("object_name")) } } } }, new Relationship { Name = "Columns", Entries = ExtractColumns(connection, reader.GetInt32("artid").Value) } } } }; entry.Element.Properties = entry.Element.Properties.Add(ExtractSchemaOptionsProperties(reader.GetInt64("schema_option"))).OrderBy(x => x.Name).ToList(); result.Add(entry); } return result; }
private static List<Entry> ExtractColumns(SqlConnection connection, int articleId) { var command = connection.CreateCommand(); command.CommandText = "SELECT sysarticles.artid, schemas.name AS schema_name, tables.name AS table_name, columns.name AS column_name " + "FROM sysarticlecolumns " + "INNER JOIN sysarticles " + " ON sysarticlecolumns.artid = sysarticles.artid " + " AND sysarticles.artid = " + articleId + " " + "INNER JOIN sys.tables " + " ON sysarticles.objid = tables.object_id " + "INNER JOIN sys.schemas " + " ON tables.schema_id = schemas.schema_id " + "INNER JOIN sys.columns " + " ON sysarticles.objid = columns.object_id " + " AND sysarticlecolumns.colid = columns.column_id " + "ORDER BY columns.name"; var reader = command.ExecuteReader(); var result = new List<Entry>(); while (reader.Read()) { var entry = new Entry { References = new References { Name = string.Format("[{0}].[{1}].[{2}]", reader.GetString("schema_name"), reader.GetString("table_name"), reader.GetString("column_name")) } }; result.Add(entry); } return result; }