public List <QuerySourceField> LoadCustomQuerySourceFields(string connectionString, string customQueryDefinition)
{
var result = new List <QuerySourceField>();
var dataTypeAdaptor = new DB2SupportDataType();
try
{
using (var transaction = new TransactionScope(TransactionScopeOption.RequiresNew))
{
using (var conn = new DB2Connection(connectionString))
{
conn.Open();
var command = new DB2Command(customQueryDefinition, conn);
command.CommandType = CommandType.Text;
var reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
var schema = reader.GetSchemaTable();
for (int i = 0; i < schema.Rows.Count; i++)
{
string dataType = reader.GetDataTypeName(i);
result.Add(
new QuerySourceField
{
Name = schema.Rows[i]["ColumnName"].ToString() ?? "",
DataType = dataType,
IzendaDataType = dataTypeAdaptor.GetIzendaDataType(dataType),
AllowDistinct = dataTypeAdaptor.GetAllowDistinct(dataType),
ExtendedProperties = "",
Position = Convert.ToInt32(schema.Rows[0]["ColumnOrdinal"].ToString())
}
);
}
}
}
}
catch (DB2Exception ex)
{
var errorMsgBuilder = new StringBuilder();
var modelErrors = new ModelErrors();
for (int i = 0; i < ex.Errors.Count; i++)
{
var error = ex.Errors[i];
errorMsgBuilder.AppendLine(error.Message);
}
modelErrors.AddError("CustomDefinition", errorMsgBuilder.ToString());
throw new IzendaModelException(modelErrors);
}
return(result);
}
private List <QuerySourceField> LoadFieldsFromTable(string connectionString, string schemaName = null, string tableName = null)
{
var result = new List <QuerySourceField>();
var dataTypeAdaptor = new DB2SupportDataType();
using (var conn = new DB2Connection(connectionString))
{
conn.Open();
var builder = new SchemaRestrictionsBuilder(true);
if (!string.IsNullOrEmpty(schemaName))
{
builder = builder.TableSchema(schemaName);
}
if (!string.IsNullOrEmpty(tableName))
{
builder = builder.Table(tableName);
}
string[] restrictions = builder.Build();
var columns = conn.GetSchema(DB2MetaDataCollectionNames.Columns, restrictions);
//var columns = conn.GetSchema("Columns");
foreach (var row in columns.Rows.OfType <DataRow>())
{
var fieldDataType = string.Empty + row["DATA_TYPE_NAME"];
var izendaDataType = dataTypeAdaptor.GetIzendaDataType(fieldDataType);
if (string.IsNullOrEmpty(izendaDataType))
{
continue;
}
result.Add(
new QuerySourceField
{
Name = row["COLUMN_NAME"].ToString(),
DataType = fieldDataType,
IzendaDataType = izendaDataType,
AllowDistinct = dataTypeAdaptor.GetAllowDistinct(fieldDataType),
//ExtendedProperties = string.IsNullOrEmpty("" + row["column_key"]) ? "" : new FieldExtendedProperty { PrimaryKey = true }.ToJson(),//TODO FieldExtendedProperty ?
Position = Convert.ToInt32(row["ORDINAL_POSITION"]),
QuerySourceName = row["TABLE_NAME"].ToString(),
CategoryName = row["TABLE_SCHEMA"].ToString()
});
}
}
return(result);
}
public List <QuerySourceParameter> GetQuerySourceParameters(string connectionString, string specificSchema, string specificName)
{
var dataTypeAdaptor = new DB2SupportDataType();
using (var conn = new DB2Connection(connectionString))
{
const string sqlFormat = @"SELECT PARMNAME, ROUTINENAME, ROUTINESCHEMA, TYPENAME, ROWTYPE, ORDINAL
FROM SYSIBM.SYSROUTINEPARMS
WHERE ROUTINESCHEMA NOT IN({0})";
var sql = string.Format(sqlFormat, ExcludeSchemas);
if (!string.IsNullOrEmpty(specificSchema) && !string.IsNullOrEmpty(specificName))
{
sql += string.Format(" AND ROUTINESCHEMA = '{0}' AND ROUTINENAME = '{1}';", specificSchema, specificName);
}
else
{
sql += ";";
}
var parametes =
conn.Query <dynamic>(sql)
.Select(s => new QuerySourceParameter
{
Name = string.Empty + s.PARMNAME,
QuerySourceName = s.ROUTINENAME,
Category = s.ROUTINESCHEMA,
DataType = s.TYPENAME,
IzendaDataType = dataTypeAdaptor.GetIzendaDataType(s.TYPENAME),
InputMode = s.ROWTYPE.Equals("P", StringComparison.OrdinalIgnoreCase),
Result = s.ROWTYPE.Equals("R", StringComparison.OrdinalIgnoreCase),
Position = s.ORDINAL,
Value = DBNull.Value,
AllowDistinct = dataTypeAdaptor.GetAllowDistinct(s.TYPENAME)
}).ToList();
return(parametes);
}
}
/// <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;
}
}
}
}
}