public IEnumerable<DatabaseSchemaDto> GetDatabaseSchema(ConnectionOptions connectionOptions, string providerName)
{
var sqlQuery = new StringBuilder();
sqlQuery.AppendLine("WITH T AS (");
sqlQuery.AppendLine(" SELECT TABLE_CATALOG AS TableCatalog, TABLE_SCHEMA AS TableSchema, TABLE_NAME AS TableName, TABLE_TYPE AS TableType ");
sqlQuery.AppendLine(" FROM INFORMATION_SCHEMA.TABLES ");
sqlQuery.AppendLine(" UNION ALL ");
sqlQuery.AppendLine(" SELECT TABLE_CATALOG AS TableCatalog, TABLE_SCHEMA AS TableSchema, TABLE_NAME AS TableName, 'VIEW' AS TableType ");
sqlQuery.AppendLine(" FROM INFORMATION_SCHEMA.VIEWS ");
sqlQuery.AppendLine(") ");
sqlQuery.AppendLine(" SELECT TableCatalog, TableSchema, TableName, TableType ");
sqlQuery.AppendLine(" ROM T ");
return DatabaseReader.Create(connectionOptions, providerName).Reader.Select<DatabaseSchemaDto>(sqlQuery.ToString());
}
public IEnumerable<TableSchemaDto> GetTableSchema(ConnectionOptions connectionOptions, string providerName, string schema, string table)
{
var sqlQuery = new StringBuilder();
sqlQuery.AppendLine(" SELECT TABLE_CATALOG AS TableCatalog, TABLE_SCHEMA AS TableSchema, TABLE_NAME AS TableName ");
sqlQuery.AppendLine(" ,COLUMN_NAME AS ColunmName, DATA_TYPE AS OriginalDataType, ORDINAL_POSITION AS OrdinalPosition, CHARACTER_MAXIMUM_LENGTH AS MaximumLength ");
sqlQuery.AppendLine(" ,CASE DATA_TYPE");
sqlQuery.AppendLine(" WHEN 'int' THEN 'integer' ");
sqlQuery.AppendLine(" WHEN 'bit' THEN 'integer' ");
sqlQuery.AppendLine(" WHEN 'float' THEN 'number' ");
sqlQuery.AppendLine(" WHEN 'datetime' THEN 'date' ");
sqlQuery.AppendLine(" ELSE 'string' ");
sqlQuery.AppendLine(" END AS DataType ");
sqlQuery.AppendLine(" FROM INFORMATION_SCHEMA.COLUMNS ");
sqlQuery.AppendLine(" WHERE TABLE_SCHEMA = @TableSchema AND TABLE_NAME = @TableName ");
return DatabaseReader.Create(connectionOptions, providerName).Reader.Select<TableSchemaDto>(sqlQuery.ToString(),
new { TableSchema = schema, TableName = table });
}
private IDataReader GetDataReaderOfAttachment()
{
var report = this.GetReport(this._attachmentTopic.ReportId);
if (report == null)
{
this.SetErrorStatus("The report does not find.");
return null;
}
var connectOpt = new ConnectionOptions
{
DataSource = report.Rdbms.Server,
InitialCatalog = report.Rdbms.Catalog,
UserId = report.Rdbms.UserId,
Password = report.Rdbms.Password,
ReadOnly = report.Rdbms.ReadOnly
};
var provider = report.Rdbms.Provider;
var sqlStatement = this._attachmentTopic.SqlStatement;
var parameters = this._attachmentTopic.Parameter;
if (String.IsNullOrWhiteSpace(parameters))
return StorageManager.CreateInstance(connectOpt, provider).GetDataReader(sqlStatement);
var parms = StorageParameter.ConvertStringToParameter(parameters);
return StorageManager.CreateInstance(connectOpt, provider).GetDataReader(sqlStatement, parms);
}
public static DatabaseReader Create(ConnectionOptions connectionOptions, string providerName)
{
return new DatabaseReader(connectionOptions, providerName);
}
private DatabaseReader(ConnectionOptions connectionOptions, string providerName)
{
this._connectionConfig = Tuple.Create(connectionOptions, providerName);
}
private Tuple<ConnectionOptions, string> GetConnectionOption()
{
var rdbms = this.report.Rdbms;
var connectionOpt = new ConnectionOptions
{
DataSource = rdbms.Server,
InitialCatalog = rdbms.Catalog,
UserId = rdbms.UserId,
Password = rdbms.Password,
ReadOnly = rdbms.ReadOnly
};
return Tuple.Create(connectionOpt, rdbms.Provider);
}
// 显示 Table / View 的信息(字段名及类型等)
private IEnumerable<TableSchemaDto> GetTableSchema(RdbmsDto rdbms, string table)
{
var connOpt = new ConnectionOptions
{
DataSource = rdbms.Server,
InitialCatalog = rdbms.Catalog,
UserId = CryptoFactory.AES.Decrypt(rdbms.UserId),
Password = CryptoFactory.AES.Decrypt(rdbms.Password),
ReadOnly = rdbms.ReadOnly
};
var schemaService = ServiceLocator.Instance.Resolve<IReportSchemaQueryService>();
return schemaService.GetTableSchema(connOpt, rdbms.Provider, "dbo", table);
}
private bool TestConnection(RdbmsDto rdbms)
{
var connectionOpt = new ConnectionOptions
{
DataSource = rdbms.Server,
InitialCatalog = rdbms.Catalog,
UserId = rdbms.UserId,
Password = rdbms.Password,
ReadOnly = rdbms.ReadOnly
};
var connTest = StorageManager.ConnectionTest(connectionOpt, rdbms.Provider);
return connTest.Test();
}
