public void SqlTables(
[Argument(Help = "Identificação da fonte de dados")]
string dataSource,
[Argument(Help = "String de ligação ao servidor de SQL, e.g. 'Server=SqlServerTestMachine;Trusted_Connection=True;'")]
string connectionString,
[Argument(Help = "Identificador da base de dados")]
string catalogName,
[Argument(Help = "Identificador(es) da(s) tabela(s)", IsRequired = false)]
string[] tableNameCollection = null)
{
try
{
App.Console.WriteWithColor("A enviar solicitação ao servidor...", false, ConsoleColor.Yellow);
var producerFacade = ServiceProvider.GetService <IRelationalDatabasesProducerService>();
RelationalDatabaseDataSet results =
tableNameCollection != null
? producerFacade.GetTables(dataSource, connectionString,
CatalogName.FromArray(new[] { catalogName }), TableName.FromArray(tableNameCollection))
: producerFacade.GetTables(dataSource, connectionString,
CatalogName.FromArray(new[] { catalogName }));
App.Console.WriteWithColor("A receber resposta do servidor.", false, ConsoleColor.Yellow);
string output = TableViewExtensions.ToTableView(results.Table).Build().ToString();
App.Console.Write(output, true);
}
catch
{
// ignored
}
}
public void SqlCatalog(
[Argument(Help = "identificação da fonte de dados")]
string dataSource,
[Argument(Help = "String de ligação ao servidor de SQL, e.g. 'Server=SqlServerTestMachine;Trusted_Connection=True;'")]
string connectionString,
[Argument(Help = "Identificador da base de dados")]
string catalogName)
{
try
{
App.Console.BreakLineInNextWrite = false;
App.Console.WriteWithColor("A enviar solicitação ao servidor...", false, ConsoleColor.Yellow);
var producerFacade = ServiceProvider.GetService <IRelationalDatabasesProducerService>();
RelationalDatabaseDataSet results = producerFacade.GetCatalogs(dataSource, connectionString,
new[] { new CatalogName(catalogName) });
App.Console.WriteWithColor("A receber resposta do servidor.", false, ConsoleColor.Yellow);
string output = TableViewExtensions.ToTableView(results.Catalog).Build().ToString();
App.Console.Write(output, true);
}
catch
{
// ignored
}
}
public void FillBy(RelationalDatabaseDataSet dataSet, IDomainGateway domainGateway, CatalogName[] catalogNameCollection)
{
_logger.Trace(Strings.TableModuleCall);
Guard.Against.Null(domainGateway, nameof(domainGateway));
Guard.Against.NullOrEmptyCollection(catalogNameCollection, nameof(catalogNameCollection));
domainGateway.FillDomainsBy(dataSet, catalogNameCollection);
}
public RelationalDatabaseDataSet FindAllCatalogs()
{
var ds = new RelationalDatabaseDataSet();
//// 1. Open connection
//using (SqlConnection connection = new SqlConnection(_context.ConnectionString))
//{
// connection.Open();
// // 2. Create new adapter
// using (SqlDataAdapter adapter =
// new SqlDataAdapter(CatalogStatements.SELECT_ALL_CATALOGS, connection))
// {
// adapter.TableMappings.Add(BuildTableMapping());
// // 3. Use DataAdapter to fill typed datatable
// adapter.Fill(ds);
// }
//}
using (var adapter = new SqlDataAdapter(CatalogStatements.SELECT_ALL_CATALOGS, _context.Connection))
{
adapter.TableMappings.Add(BuildTableMapping(ds));
adapter.Fill(ds);
return(ds);
}
}
public RelationalDatabaseDataSet FindTablesBy(CatalogName[] catalogNameCollection)
{
Guard.Against.NullOrEmptyCollection(catalogNameCollection, nameof(catalogNameCollection));
RelationalDatabaseDataSet ds = new RelationalDatabaseDataSet();
ds.EnforceConstraints = false;
Dsl.ConnectToMsSqlServer()
.UsingThisConnectionString(_connection.ConnectionString)
.ForEachCatalog(CatalogName.ToArray(catalogNameCollection))
.SelectAllColumns()
.From(TableStatements.From)
.Adapt()
.UsingThisMapping(BuildTableMapping(ds))
.FillDataset(ds)
;
/*using (SqlDataAdapter adapter = new SqlDataAdapter(TableStatments.SELECT_ALL_TABLES, Connection))
* {
* adapter.TableMappings.Add(CreateTableMapping(RdmsDataSet.SchemaStructure.Table.TableName));
*
* foreach (string catalog in catalogs)
* {
* Connection.ChangeDatabase(catalog);
*
* adapter.Fill(ds);
* }
* }*/
return(ds);
}
public RelationalDatabaseDataSet GetSchema(string dataSource, string connectionString, CatalogName[] catalogNameCollection)
{
Guard.Against.NullOrEmpty(dataSource, nameof(dataSource));
Guard.Against.NullOrEmpty(connectionString, nameof(connectionString));
try
{
IRelationalDatabaseGateway gateway = _tableGatewayFactoryFunc(dataSource, connectionString);
RelationalDatabaseDataSet dataSet = new RelationalDatabaseDataSet();
// Activa mecanismo que suspende a validação das relações estrangeiras
dataSet.EnforceConstraints = false;
_tableModule.Server.FillBy(dataSet, gateway);
_tableModule.Catalog.FillBy(dataSet, gateway, catalogNameCollection);
_tableModule.Table.FillBy(dataSet, gateway, catalogNameCollection);
_tableModule.Domain.FillBy(dataSet, gateway, catalogNameCollection);
_tableModule.Column.FillBy(dataSet, gateway, catalogNameCollection);
// Activa regras de relacionamento entre tabelas
dataSet.EnforceConstraints = true;
return(dataSet);
}
catch (Exception ex)
{
_logger.LogError(ex.Message);
throw;
}
}
// Mapeamento da tabela para a estrutura lógica do ADO.Net: https://msdn.microsoft.com/en-us/library/ms810286.aspx (consultado a 25.7.2018)
private DataTableMapping BuildTableMapping(RelationalDatabaseDataSet ds)
{
var dtm = new DataTableMapping
{
SourceTable = "Table",
DataSetTable = ds.Column.TableName
};
dtm.ColumnMappings.Add("TABLE_SERVER_NAME", ds.Column.ServerNameColumn.ColumnName);
dtm.ColumnMappings.Add("TABLE_CATALOG_ID", ds.Column.CatalogIdentifierColumn.ColumnName);
dtm.ColumnMappings.Add("TABLE_CATALOG", ds.Column.CatalogNameColumn.ColumnName);
dtm.ColumnMappings.Add("TABLE_SCHEMA", ds.Column.SchemaNameColumn.ColumnName);
dtm.ColumnMappings.Add("TABLE_NAME", ds.Column.TableNameColumn.ColumnName);
dtm.ColumnMappings.Add("COLUMN_NAME", ds.Column.ColumnNameColumn.ColumnName);
dtm.ColumnMappings.Add("ORDINAL_POSITION", ds.Column.OrdinalPositionColumn.ColumnName);
dtm.ColumnMappings.Add("DOMAIN_SCHEMA", ds.Column.DomainSchemaNameColumn.ColumnName);
dtm.ColumnMappings.Add("DOMAIN_NAME", ds.Column.DomainNameColumn.ColumnName);
dtm.ColumnMappings.Add("DATA_TYPE", ds.Column.DataTypeColumn.ColumnName);
dtm.ColumnMappings.Add("CHARACTER_MAXIMUM_LENGTH", ds.Column.CharacterMaximumLengthColumn.ColumnName);
dtm.ColumnMappings.Add("NUMERIC_PRECISION", ds.Column.NumericPrecisionColumn.ColumnName);
dtm.ColumnMappings.Add("NUMERIC_SCALE", ds.Column.NumericScaleColumn.ColumnName);
dtm.ColumnMappings.Add("COLUMN_DEFAULT", ds.Column.ColumnDefaultColumn.ColumnName);
dtm.ColumnMappings.Add("IS_NULLABLE", ds.Column.IsNullableColumn.ColumnName);
dtm.ColumnMappings.Add("IS_PRIMARY_KEY", ds.Column.IsPrimaryKeyColumn.ColumnName);
dtm.ColumnMappings.Add("IS_FOREIGN_KEY", ds.Column.IsForeignKeyColumn.ColumnName);
return(dtm);
}
public RelationalDatabaseDataSet FindServer()
{
var ds = new RelationalDatabaseDataSet();
ds.Server.AddServerRow(_context.Connection.DataSource, _context.Connection.ServerVersion);
return(ds);
}
public async Task <RelationalDatabaseDataSet> GetSchemaAsync(string dataSource, string connectionString,
CatalogName[] catalogNameCollection)
{
Guard.Against.NullOrEmpty(dataSource, nameof(dataSource));
Guard.Against.NullOrEmpty(connectionString, nameof(connectionString));
try
{
// simula a execução assíncrona do componente de acesso aos dados
//
RelationalDatabaseDataSet dataSet = await Task.Run(async() =>
{
await Task.Delay(1000);
IRelationalDatabaseGateway gateway = _tableGatewayFactoryFunc(dataSource, connectionString);
RelationalDatabaseDataSet ds = new RelationalDatabaseDataSet();
ds.EnforceConstraints = false;
_tableModule.Server.FillBy(ds, gateway);
_tableModule.Catalog.FillBy(ds, gateway, catalogNameCollection);
_tableModule.Table.FillBy(ds, gateway, catalogNameCollection);
_tableModule.Domain.FillBy(ds, gateway, catalogNameCollection);
_tableModule.Column.FillBy(ds, gateway, catalogNameCollection);
return(ds);
});
return(dataSet);
}
catch (Exception ex)
{
_logger.LogError(ex.Message);
throw;
}
}
public RelationalDatabaseDataSet FindColumnsBy(CatalogName[] catalogNameCollection)
{
Guard.Against.NullOrEmptyCollection(catalogNameCollection, nameof(catalogNameCollection));
RelationalDatabaseDataSet results = new RelationalDatabaseDataSet();
FillColumnsBy(results, catalogNameCollection);
return(results);
}
public void FillBy(RelationalDatabaseDataSet dataSet, ICatalogGateway catalogGateway, CatalogName[] catalogNameCollection)
{
_logger.Trace(Strings.TableModuleCall);
Guard.Against.Null(catalogGateway, nameof(catalogGateway));
Guard.Against.NullOrEmptyCollection(catalogNameCollection, nameof(catalogNameCollection));
RelationalDatabaseDataSet results = catalogGateway.FindCatalogBy(catalogNameCollection);
dataSet.Merge(results);
}
public void FillBy(RelationalDatabaseDataSet dataSet,
IServerGateway gateway)
{
_logger.Trace(Strings.TableModuleCall);
Guard.Against.Null(dataSet, nameof(dataSet));
Guard.Against.Null(gateway, nameof(gateway));
RelationalDatabaseDataSet results = gateway.FindServer();
dataSet.Merge(results);
}
public RelationalDatabaseDataSet GetBy(ICatalogGateway catalogGateway, CatalogName[] catalogNameCollection)
{
_logger.Trace(Strings.TableModuleCall);
Guard.Against.Null(catalogGateway, nameof(catalogGateway));
Guard.Against.NullOrEmptyCollection(catalogNameCollection, nameof(catalogNameCollection));
var dataSet = new RelationalDatabaseDataSet();
FillBy(dataSet, catalogGateway, catalogNameCollection);
return(dataSet);
}
// Mapeamento da tabela para a estrutura lógica do ADO.Net.
// https://msdn.microsoft.com/en-us/library/ms810286.aspx
// https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dataadapter-datatable-and-datacolumn-mappings
private DataTableMapping BuildTableMapping(RelationalDatabaseDataSet dataSet)
{
var dtm = new DataTableMapping("Table", dataSet.Catalog.TableName);
// Este mapeamento permite alinhar com a estrutura de dados tipada do data set.
dtm.ColumnMappings.Add("SERVER_NAME" /* campo da consulta */, dataSet.Catalog.ServerNameColumn.ColumnName);
dtm.ColumnMappings.Add("CATALOG_ID", dataSet.Catalog.CatalogIdentifierColumn.ColumnName);
dtm.ColumnMappings.Add("CATALOG_NAME", dataSet.Catalog.CatalogNameColumn.ColumnName);
dtm.ColumnMappings.Add("CATALOG_CREATE_DATE", dataSet.Catalog.CatalogCreateDateColumn.ColumnName);
dtm.ColumnMappings.Add("CATALOG_MODIFY_DATE", dataSet.Catalog.CatalogModifyDateColumn.ColumnName);
return(dtm);
}
public RelationalDatabaseDataSet GetBy(ITableGateway tableGateway, CatalogName[] catalogNameCollection, TableName[] tableNameCollection)
{
_logger.Trace(Strings.TableModuleCall);
Guard.Against.Null(tableGateway, nameof(tableGateway));
Guard.Against.NullOrEmptyCollection(catalogNameCollection, nameof(catalogNameCollection));
RelationalDatabaseDataSet results = tableGateway.FindTablesBy(catalogNameCollection[0], tableNameCollection);
for (int i = 1; i < catalogNameCollection.Length; i++)
{
results.Merge(tableGateway.FindTablesBy(catalogNameCollection[i], tableNameCollection));
}
return(results);
}
public RelationalDatabaseDataSet FindTablesBy(CatalogName catalog, TableName[] tableNameCollection)
{
RelationalDatabaseDataSet ds = new RelationalDatabaseDataSet();
ds.EnforceConstraints = false;
Dsl.ConnectToMsSqlServer()
.UsingThisConnectionString(_connection.ConnectionString)
.ForThisCatalog(catalog.Name)
.SelectAllColumns()
.From(TableStatements.From)
.Where("TABLE_NAME").In(TableName.ToArray(tableNameCollection))
.Adapt().UsingThisMapping(BuildTableMapping(ds))
.FillDataset(ds);
return(ds);
}
public void FillColumnsBy(RelationalDatabaseDataSet dataSet, CatalogName[] catalogNameCollection)
{
var dsl = Dsl.ConnectToMsSqlServer().UsingThisConnectionString(_context.ConnectionString);
dsl.ForEachCatalog(catalogNameCollection.Select(c => c.Name).ToArray())
.SelectAllColumns()
.From(ColumnStatements.FromStatement)
//.Where("TABLE_CATALOG").IsEqualTo(catalogName)
//.And("TABLE_NAME").In(tableNameCollection)
.OrderBy("TABLE_CATALOG")
.ThenBy("TABLE_SCHEMA").Asc()
.ThenBy("TABLE_NAME").Asc()
.ThenBy("ORDINAL_POSITION").Asc()
.Adapt()
.UsingThisMapping(BuildTableMapping(dataSet))
.FillDataset(dataSet);
}
private DataTableMapping BuildTableMapping(RelationalDatabaseDataSet ds)
{
var dtm = new DataTableMapping
{
SourceTable = "Table",
DataSetTable = ds.Table.TableName
};
dtm.ColumnMappings.Add("TABLE_SERVER_NAME", ds.Table.ServerNameColumn.ColumnName);
dtm.ColumnMappings.Add("TABLE_CATALOG_ID", ds.Table.CatalogIdentifierColumn.ColumnName);
dtm.ColumnMappings.Add("TABLE_CATALOG", ds.Table.CatalogNameColumn.ColumnName);
dtm.ColumnMappings.Add("TABLE_SCHEMA", ds.Table.SchemaNameColumn.ColumnName);
dtm.ColumnMappings.Add("TABLE_NAME", ds.Table.TableNameColumn.ColumnName);
dtm.ColumnMappings.Add("TABLE_CREATE_DATE", ds.Table.TableCreateDateColumn.ColumnName);
dtm.ColumnMappings.Add("TABLE_MODIFY_DATE", ds.Table.TableModifyDateColumn.ColumnName);
dtm.ColumnMappings.Add("TABLE_TYPE", ds.Table.TableTypeColumn.ColumnName);
return(dtm);
}
public RelationalDatabaseDataSet FindCatalogBy(CatalogName[] catalogNameCollection)
{
var ds = new RelationalDatabaseDataSet();
using (var adapter = new SqlDataAdapter(CatalogStatements.SELECT_SINGLE_CATALOG, _context.Connection))
{
adapter.TableMappings.Add(BuildTableMapping(ds));
foreach (CatalogName cat in catalogNameCollection)
{
// Muda a base de dados actual associada à conexão
_context.Connection.ChangeDatabase(cat.Name);
adapter.Fill(ds);
}
}
return(ds);
}
private DataTableMapping BuildTableMapping(RelationalDatabaseDataSet ds)
{
var dtm = new DataTableMapping
{
SourceTable = "Table",
DataSetTable = ds.Domain.TableName
};
dtm.ColumnMappings.Add("DOMAIN_CATALOG_ID", ds.Domain.CatalogIdentifierColumn.ColumnName);
dtm.ColumnMappings.Add("DOMAIN_CATALOG", ds.Domain.CatalogNameColumn.ColumnName);
dtm.ColumnMappings.Add("DOMAIN_SCHEMA", ds.Domain.SchemaNameColumn.ColumnName);
dtm.ColumnMappings.Add("DOMAIN_NAME", ds.Domain.DomainNameColumn.ColumnName);
dtm.ColumnMappings.Add("DATA_TYPE", ds.Domain.DataTypeColumn.ColumnName);
dtm.ColumnMappings.Add("NUMERIC_PRECISION", ds.Domain.NumericPrecisionColumn.ColumnName);
dtm.ColumnMappings.Add("NUMERIC_SCALE", ds.Domain.NumericScaleColumn.ColumnName);
return(dtm);
}
public async Task <RelationalDatabaseDataSet> GetCatalogsAsync(string dataSource, string connectionString)
{
Guard.Against.NullOrEmpty(dataSource, nameof(dataSource));
Guard.Against.NullOrEmpty(connectionString, nameof(connectionString));
try
{
// simula a execução assíncrona do componente de acesso aos dados
//
RelationalDatabaseDataSet dataSet = await Task.Run(async() =>
{
await Task.Delay(1);
IRelationalDatabaseGateway gateway = _tableGatewayFactoryFunc(dataSource, connectionString);
return(_tableModule.Catalog.GetAll(gateway));
});
return(dataSet);
}
catch (Exception ex)
{
_logger.LogError(ex.Message);
throw;
}
}
public void FillDomainsBy(RelationalDatabaseDataSet dataSet, CatalogName[] catalogNameCollection)
{
foreach (var catalog in catalogNameCollection)
{
Dsl.ConnectToMsSqlServer().UsingThisConnectionString(_context.ConnectionString)
.ForThisCatalog(catalog)
.SelectAllColumns()
.From(DomainStatements.FromStatement)
.Where("DOMAIN_SCHEMA").IsNotEqualTo("sys")
.OrderBy("DOMAIN_SCHEMA").Asc().ThenBy("DOMAIN_NAME").Asc()
.Adapt().UsingThisMapping(BuildTableMapping(dataSet))
.FillDataset(dataSet);
}
Dsl.ConnectToMsSqlServer().UsingThisConnectionString(_context.ConnectionString)
.ForThisCatalog(catalogNameCollection.First())
.SelectAllColumns()
.From(DomainStatements.FromStatement)
.Where("DOMAIN_SCHEMA").IsEqualTo("sys")
.OrderBy("DOMAIN_SCHEMA").Asc().ThenBy("DOMAIN_NAME").Asc()
.Adapt().UsingThisMapping(BuildTableMapping(dataSet))
.FillDataset(dataSet);
}
public void SqlSchema(
[Argument(Help = "Identificação da fonte de dados")]
string dataSource,
[Argument(Help = "String de ligação ao servidor de SQL, e.g. 'Server=SqlServerTestMachine;Trusted_Connection=True;'")]
string connectionString,
[Argument(Help = "Nome da base de dados")]
string[] catalogNameCollection,
[Argument(Help = "Formato do ficheiro (Xml ou Json)'", IsRequired = false)]
DataSetStreamFormat fileFormat = DataSetStreamFormat.Xml
)
{
// Cria animação de espera em back-ground, obtendo um token para cancelar a animação logo que os dados cheguem.
CancellationTokenSource animation = Animation.Spinner(ConsoleColor.Yellow);
try
{
App.Console.WriteWithColor("A enviar solicitação ao servidor...", false, ConsoleColor.Yellow);
var producerFacade = ServiceProvider.GetService <IRelationalDatabasesProducerServiceAsync>();
var task = producerFacade.GetSchemaAsync(dataSource, connectionString,
CatalogName.FromArray(catalogNameCollection));
task.Wait();
// Os dados já chegaram, pára a animação e mostra os resultados.
animation.Cancel();
App.Console.WriteWithColor("A receber resposta do servidor.", false, ConsoleColor.Yellow);
RelationalDatabaseDataSet dataSet = task.Result;
// Cria nome do ficheiro
IDataSetStreamWriter dataSetWriter =
ServiceProvider.GetRequiredService <DataSetStreamFormatWriterFunc>()(fileFormat);
var sqlSchemaFileTemplate =
ServiceProvider.GetRequiredService <IOptions <ProducerOptions> >().Value.SqlSchemaFileTemplate;
var fileNameBuilder = new SchemaFileNameBuilder(sqlSchemaFileTemplate,
BridgeElement.Category, fileFormat.ToString().ToLower());
string filePath = fileNameBuilder.Build();
using (FileStream fs = File.Create(filePath))
{
dataSetWriter.Write(dataSet, fs);
fs.Close();
}
string output =
$"O ficheiro '{filePath}' foi criado com {dataSet.Catalog.Count} catálogo(s) e {dataSet.Table.Count} tabela(s).";
Logger.LogInformation(output);
}
catch (Exception e)
{
if (!animation.IsCancellationRequested)
{
animation.Cancel();
}
Logger.LogError(e.Message);
}
}
void IDomainGateway.FillDomainsBy(RelationalDatabaseDataSet dataSet, CatalogName[] catalogNameCollection)
{
_logger.Trace(Strings.TableGatewayCall);
_domainGateway.FillDomainsBy(dataSet, catalogNameCollection);
}
public void FillDomainsBy(RelationalDatabaseDataSet dataSet, CatalogName[] catalogNameCollection)
{
throw new NotImplementedException();
}
