Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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);
        }
Exemplo n.º 3
0
        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);
            }
        }
Exemplo n.º 4
0
        /// <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;
                        }
                    }
                }
            }
        }
Exemplo n.º 5
0
        public override List <DatabaseDataType> GetBaseDataTypes()
        {
            var dataTypeAdapter = new DB2SupportDataType();

            return(dataTypeAdapter.GetBaseDataTypes());
        }