/// <summary>
/// Executes for schema.
/// </summary>
/// <param name="connectionString">The connection string.</param>
/// <param name="type">The type.</param>
/// <param name="categoryName">Name of the schema.</param>
/// <param name="querySourceName">Name of the specific.</param>
/// <param name="parameters">The parameters.</param>
/// <returns>
/// list of QuerySourceField
/// </returns>
private List <QuerySourceField> LoadFieldsFromProcedure(string connectionString, string type, string categoryName, string querySourceName, List <QuerySourceParameter> parameters = null, bool ignoreError = true, BI.Logging.ILog log = null)
{
var result = new List <QuerySourceField>();
var dataTypeAdaptor = new DB2SupportDataType();
if (parameters == null)
{
parameters = GetQuerySourceParameters(connectionString, categoryName, querySourceName);
}
parameters = parameters.OrderBy(x => x.Position).ToList();
using (var transaction = new TransactionScope(TransactionScopeOption.RequiresNew))
{
using (var connection = new DB2Connection(connectionString))
{
connection.Open();
string sql = String.Format("{0}.{1}", categoryName, querySourceName);
DB2Command cmd = new DB2Command(sql, connection);
if (type == SQLQuerySourceType.Procedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (parameters != null && parameters.Count() > 0)
{
foreach (var parameter in parameters)
{
var db2Parameter = cmd.Parameters.Add(parameter.Name, parameter.Value);
}
}
try
{
var reader = cmd.ExecuteReader();
DataTable schema = reader.GetSchemaTable();
if (schema != null)
{
var colNames = schema.Rows.OfType <DataRow>().FirstOrDefault().Table.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToList();
var colStr = string.Join(", ", colNames.ToArray());
result.AddRange(schema
.Rows
.OfType <DataRow>()
.Select
(
(c, i) => new QuerySourceField
{
Id = Guid.NewGuid(),
GroupPosition = 1,
Position = i,
Name = c["ColumnName"].ToString(),
DataType = reader.GetDataTypeName(int.Parse(c["ColumnOrdinal"].ToString())),
IzendaDataType = dataTypeAdaptor.GetIzendaDataType(reader.GetDataTypeName(int.Parse(c["ColumnOrdinal"].ToString()))),
AllowDistinct = dataTypeAdaptor.GetAllowDistinct(reader.GetDataTypeName(int.Parse(c["ColumnOrdinal"].ToString()))),
Type = (int)QuerySourceFieldType.Field
}
)
.Where(x => !string.IsNullOrEmpty(x.IzendaDataType)));
}
reader.Close();
return(result);
}
catch (Exception ex)
{
log?.Debug(ex);
// ignore error when execute stored proc from customer connectionString
if (ignoreError)
{
return(result);
}
else
{
throw;
}
}
}
}
}
public List <QuerySourceField> LoadFields(string connectionString, string type, string categoryName, string querySourceName, bool rollbackSP, List <QuerySourceParameter> parameters = null, bool ignoreError = true, int commandTimeout = 500, BI.Logging.ILog log = null)
{
var result = new List <QuerySourceField>();
if (type.EqualsIgnoreCase(SQLQuerySourceType.Table) || type.EqualsIgnoreCase(SQLQuerySourceType.View))
{
result = LoadFieldsFromTable(connectionString, categoryName, querySourceName);
}
else if (type.EqualsIgnoreCase(SQLQuerySourceType.Procedure))
{
result = LoadFieldsFromProcedure(connectionString, type, categoryName, querySourceName, parameters, ignoreError, log);
}
return(result);
}
