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;
}
