Esempio n. 1
0
        public void fillcomboDB(ComboBox c, string query)
        {
            try
            {
                connect.Open();
                c.Items.Clear();
                DB2Command    cmd = new DB2Command(query, connect);
                DB2DataReader reader;
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    string tName = reader.GetString(0);
                    c.Items.Add(tName);
                }
                reader.Close();
                connect.Close();
            } catch (DB2Exception e)
            {
                System.Windows.Forms.MessageBox.Show(e.ToString());
                connect.Close();
            }
        }
Esempio n. 2
0
        private void ModificarCheck_Load(object sender, EventArgs e)
        {
            textBox1.Text = arbol.SelectedNode.Text;
            nombre_real   = arbol.SelectedNode.Text;
            PantallaPrincipal pn         = new PantallaPrincipal();
            DB2Connection     connection = pn.obtenerConexion(arbol.SelectedNode.Parent.Parent.Text);

            try {
                connection.Open();
                DB2Command    cmd    = new DB2Command(@"SELECT TEXT FROM SYSIBM.SYSCHECKS WHERE TBCREATOR='DB2ADMIN' AND NAME ='" + arbol.SelectedNode.Text + "';", connection);
                DB2DataReader buffer = cmd.ExecuteReader();
                while (buffer.Read())
                {
                    var function = buffer ["TEXT"].ToString();
                    richTextBox1.Text = function;
                    checkviejo        = function;
                    break;
                }
            } catch (DB2Exception ex) {
                MessageBox.Show("Error al mostrar Check\n" + ex.Message);
            }
            connection.Close();
        }
        private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
        {
            comboBox4.Items.Clear();
            PantallaPrincipal pn         = new PantallaPrincipal();
            DB2Connection     connection = pn.obtenerConexion(arbol.SelectedNode.Parent.Text);

            try {
                connection.Open();
                int           posicion     = comboBox3.SelectedIndex;
                string        nombre_tabla = comboBox3.Items [posicion].ToString();
                DB2Command    cmd          = new DB2Command(@"SELECT NAME FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = '" + nombre_tabla + "';", connection);
                DB2DataReader buffer       = cmd.ExecuteReader();
                while (buffer.Read())
                {
                    var nombre_campo = buffer ["NAME"].ToString();
                    comboBox4.Items.Add(nombre_campo);
                }
                buffer.Close();
            } catch (DB2Exception ex) {
                MessageBox.Show("Ha ocurrido un error\n" + ex.Message);
            }
            connection.Close();
        }
Esempio n. 4
0
        public static List <DatosDeConsultaCondenadoProcesado> ConsultaCondenadoProcesado(decimal cuil)
        {
            log.InfoFormat("Se ejecuta el método YH_ConsultaCondenadoProcesado con el parámetro {0}", cuil);
            List <DatosDeConsultaCondenadoProcesado> response = new List <DatosDeConsultaCondenadoProcesado>();
            DB2Command cmd = AdministradorDeConexion.ObtenerComando();

            using (DB2Connection conexion = AdministradorDeConexion.ObtenerConnexion())
            {
                try
                {
                    cmd.CommandText = string.Format("{0}.YH_ConsultaCondenadoProcesado", AdministradorDeConexion.GetSchema());
                    cmd.Parameters.Add(new DB2Parameter("@CUIL", cuil));
                    cmd.Connection = conexion;
                    conexion.Open();
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        response.Add(Adapter.toConsultaCondenadoProcesadoResponse(reader));
                    }
                }
                catch (Exception ex)
                {
                    log.ErrorFormat("Se produjo un error en el método YH_ConsultaCondenadoProcesado {0} - {1} - {2}", ex.Message, ex.Source, ex.StackTrace);
                    throw ex;
                }
                finally
                {
                    if (conexion.IsOpen)
                    {
                        conexion.Close();
                    }
                    conexion.Dispose();
                    cmd.Dispose();
                }
            }
            return(response);
        }
        protected override CustomListColumnCollection GetColumnsOverride(CustomListData data)
        {
            data.Properties.TryGetValue("SQL Statement", StringComparison.OrdinalIgnoreCase, out var SQLStatement);

            var cols        = new CustomListColumnCollection();
            var con         = GetConnection(data);
            var command     = new DB2Command(SQLStatement, con);
            var reader      = command.ExecuteReader();
            var schemaTable = reader.GetSchemaTable();

            foreach (DataRow db2col in schemaTable.Rows)
            {
                var columnName     = (string)db2col["ColumnName"];
                var dataType       = (Type)db2col["DataType"];
                var listColumnType = CustomListColumnTypes.String;

                // We convert the types to one of the three Peakboard types (string, number or boolean)
                if (dataType == typeof(string))
                {
                    listColumnType = CustomListColumnTypes.String;
                }
                else if (dataType == typeof(bool))
                {
                    listColumnType = CustomListColumnTypes.Boolean;
                }
                else
                {
                    listColumnType = DataTypeHelper.IsNumericType(dataType) ? CustomListColumnTypes.Number : CustomListColumnTypes.String;
                }

                cols.Add(new CustomListColumn(columnName, listColumnType));
            }

            con.Close();

            return(cols);
        }
Esempio n. 6
0
        private DBReaderController ExecuteQuery(DBRequest request)
        {
            //Controllo
            if (!registered.Contains(request.Controller))
            {
                throw new Exception("Il Controller non è registrato all'inizio dell'esecuzione");
            }

            //Inizio
            DBReaderController result;
            DB2DataReader      reader;
            DB2Transaction     transaction = databaseConnection.GetTransaction();
            DB2Command         command     = databaseConnection.GetCommand();

            command.CommandText = request.Command;
            command.Transaction = transaction;

            //Esecuzione
            try {
                reader = command.ExecuteReader();
            }
            catch (Exception e) {
                throw new Exception("Errore in lettura: " + e.Message);
            }

            //Controllo
            if (!registered.Contains(request.Controller))
            {
                transaction.Rollback();
                throw new Exception("Il Controller non è registrato alla fine dell'esecuzione");
            }

            //Fine
            transaction.Commit();
            return(result = new DBReaderController(reader));
        }
Esempio n. 7
0
        private void eliminarCheckToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DB2Connection connection = obtenerConexion(arbol_conexiones.SelectedNode.Parent.Parent.Text);

            try {
                connection.Open();
                DB2Command    cmd    = new DB2Command("SELECT TBNAME FROM SYSIBM.SYSCHECKS WHERE TBCREATOR='DB2ADMIN' AND NAME='" + arbol_conexiones.SelectedNode.Text + "'", connection);
                DB2DataReader buffer = cmd.ExecuteReader();

                while (buffer.Read())
                {
                    var nombre_tabla = buffer ["TBNAME"].ToString();
                    buffer.Close();
                    cmd.CommandText = "ALTER TABLE " + nombre_tabla + " DROP CHECK " + arbol_conexiones.SelectedNode.Text + ";";
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Check Borrado!");
                    arbol_conexiones.SelectedNode.Remove();
                    break;
                }
            } catch (DB2Exception ex) {
                MessageBox.Show("Error al borrar Check!\n" + ex.Message);
            }
            connection.Close();
        }
Esempio n. 8
0
        public DB2DataReader DataReader(string sql)
        {
            DB2Command oc = new DB2Command();

            try
            {
                AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
                oc.CommandText = sql;
                DB2Connection con = OpenConnection();
                connection    = con;
                oc.Connection = con;
                DB2DataReader dr = oc.ExecuteReader();
                CloseConnection(con);

                return(dr);
            }
            catch (Exception e)
            {
                exceptions  = "Error ejecutando SQL." + cambioLinea + cambioLinea;
                exceptions += e.ToString();
                AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, sql, e, exceptions);
                return(null);
            }
        }
Esempio n. 9
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;
                        }
                    }
                }
            }
        }
        private void extract_document_locations(String fileNumber, String consolNumber, String hbNumber, String masterNumber)
        {
            String ConString      = "Database = edoc; User ID = inetsoft; Password = etl5boxes; server = edoc.khi.ei:50002; Max Pool Size = 100; Persist security info = False; Pooling = True ";
            String masterFileType = "";
            String houseFileType  = "";
            string docQuery       = @"SELECT DISTINCT

                                EDOC.FOLDER.KEY_,
                                EDOC.FOLDER.DESCRIPTION,
                                EDOC.DOCUMENT.DESCRIPTION,
                                EDOC.DOCUMENT.CREATION_TIME,
                                EDOC.DOCUMENT.FILE_NAME_IMAGE,
                                EDOC.FOLDER.KEY_TYPE,
                                EDOC.DOCUMENT.DOC_TYPE,
                                EDOC.DOCUMENT.IMAGE_FILE_TYPE,
                                EDOC.DOCUMENT.CREATED_BY_NAME

                                from(EDOC.FOLDER INNER JOIN EDOC.FOLDER_DOCUMENT ON EDOC.FOLDER.G_U_I_D = EDOC.FOLDER_DOCUMENT.FOLDER__P_K)
                                LEFT OUTER JOIN EDOC.DOCUMENT ON EDOC.DOCUMENT.G_U_I_D = EDOC.FOLDER_DOCUMENT.DOCUMENT__P_K and EDOC.DOCUMENT.DOC_TYPE IN('HOU', 'HOR', 'MOR', 'MOB')
                                where EDOC.FOLDER.KEY_ IN('" + fileNumber + "','" + consolNumber + "','" + hbNumber + "')AND EDOC.DOCUMENT.FILE_NAME_IMAGE IS NOT NULL";

            //List<String> columnData = new List<String>();
            //Connecting to DB
            using (DB2Connection myconnection = new DB2Connection(ConString))
            {
                myconnection.Open();
                DB2Command cmd = myconnection.CreateCommand();
                cmd.CommandText = docQuery;
                //DB2DataReader rd = cmd.ExecuteReader();
                using (DB2DataReader reader = cmd.ExecuteReader())
                {
                    string host        = "ftp://edoc.khi.ei//";
                    string user        = "******";
                    string pass        = "******";
                    bool   checkHouse  = false;
                    bool   checkMaster = false;


                    while (reader.Read())
                    {
                        string rf = folder + reader.GetString(4);


                        //MetroFramework.MetroMessageBox.Show(this,reader.GetString(6));

                        string lf = "";
                        if ((reader.GetString(6) == "HOU") && ((reader.GetString(7) == "TIFF") && (!checkHouse)))
                        {
                            lf = downLocation + "/" + hbNumber + ".tiff";
                            download(rf, lf, host, user, pass);
                            checkHouse    = true;
                            houseFileType = ".tiff";
                        }
                        if ((reader.GetString(6) == "HOR") && ((reader.GetString(7) == "TIFF") && (!checkHouse)))
                        {
                            lf = downLocation + "/" + hbNumber + ".tiff";
                            download(rf, lf, host, user, pass);
                            checkHouse    = true;
                            houseFileType = ".tiff";
                        }
                        if ((reader.GetString(6) == "HOU") && ((reader.GetString(7) == "PDF") && (!checkHouse)))
                        {
                            lf = downLocation + "/" + hbNumber + ".pdf";
                            download(rf, lf, host, user, pass);
                            checkHouse    = true;
                            houseFileType = ".pdf";
                        }
                        if ((reader.GetString(6) == "HOR") && ((reader.GetString(7) == "PDF") && (!checkHouse)))
                        {
                            lf = downLocation + "/" + hbNumber + ".pdf";
                            download(rf, lf, host, user, pass);
                            checkHouse    = true;
                            houseFileType = ".pdf";
                        }

                        if ((reader.GetString(6) == "MOR") && ((reader.GetString(7) == "PDF") && (!checkMaster)))
                        {
                            lf = downLocation + "/" + masterNumber + ".pdf";
                            download(rf, lf, host, user, pass);
                            checkMaster    = true;
                            masterFileType = ".pdf";
                        }
                        if ((reader.GetString(6) == "MOR") && ((reader.GetString(7) == "TIFF") && (!checkMaster)))
                        {
                            lf = downLocation + "/" + masterNumber + ".tiff";
                            download(rf, lf, host, user, pass);
                            checkMaster    = true;
                            masterFileType = ".tiff";
                        }
                        if ((reader.GetString(6) == "MOB") && ((reader.GetString(7) == "PDF") && (!checkMaster)))
                        {
                            lf = downLocation + "/" + masterNumber + ".pdf";
                            download(rf, lf, host, user, pass);
                            checkMaster    = true;
                            masterFileType = ".pdf";
                        }
                        if ((reader.GetString(6) == "MOB") && ((reader.GetString(7) == "TIFF") && (!checkMaster)))
                        {
                            lf = downLocation + "/" + masterNumber + ".tiff";
                            download(rf, lf, host, user, pass);
                            checkMaster    = true;
                            masterFileType = ".tiff";
                        }
                    }
                }
            }
            create_Email_Draft(downLocation + "/" + masterNumber + ".xlsx", downLocation + "/" + masterNumber + masterFileType, downLocation + "/" + hbNumber + houseFileType);
            //Show
        }
Esempio n. 11
0
        public Task <IList <Column> > GetTableDetails(Table table, string owner)
        {
            IList <Column> columns = new List <Column>();

            using (var sqlCon = new DB2Connection(_connectionStr))
            {
                try
                {
                    sqlCon.Open();

                    using (DB2Command tableDetailsCommand = sqlCon.CreateCommand())
                    {
                        tableDetailsCommand.CommandText = $@"
select c.colno, c.colname, c.coltype
     , cast(case when c.collength >= 256 then null else c.collength end as int) collength
     , cast(case when c.collength >= 256 then c.collength / 256 else null end as int) colprecision 
     , cast(case when c.collength >= 256 then c.collength - (cast(c.collength / 256 as int) * 256) else null end as int) colscale 
--     , cast(case when c.collength >= 256 then c.collength / 256 else c.collength end as int) collength
--     , cast(case when c.collength >= 256 then c.collength / 256 else c.collength end as int) colprecision 
--     , cast(case when c.collength >= 256 then c.collength - (cast(c.collength / 256 as int) * 256) else 0 end as int) colscale 
     , case when pkidx.idxtype is not null then 'P' else '-' end ispk
     , nvl(nl.constrtype, '-') isnullable
     , (select nvl(min(uidx.idxtype), '-') uniq
         from sysindexes uidx
        where uidx.tabid = c.tabid and uidx.idxtype = 'U'
          and c.colno in (uidx.part1, uidx.part2, uidx.part3, uidx.part4, uidx.part5, uidx.part6, uidx.part7, uidx.part8,
                          uidx.part9, uidx.part10, uidx.part11, uidx.part12, uidx.part13, uidx.part14, uidx.part15, uidx.part16)
        ) isunique
     , case when c.coltype in (6, 18, 262, 274) then 'I' else '-' end isidentity
from syscolumns c
  inner join systables t on c.tabid = t.tabid
  left outer join syscoldepend d on d.tabid = c.tabid and d.colno = c.colno
  left outer join sysconstraints nl on nl.constrid = d.constrid and nl.constrtype = 'N'
  left outer join sysconstraints pk on pk.tabid = c.tabid and pk.constrtype = 'P'
  left outer join sysindexes pkidx on pkidx.tabid = c.tabid and pkidx.idxname = pk.idxname
                                  and c.colno in (pkidx.part1, pkidx.part2, pkidx.part3, pkidx.part4, pkidx.part5, pkidx.part6, pkidx.part7, pkidx.part8,
                                                  pkidx.part9, pkidx.part10, pkidx.part11, pkidx.part12, pkidx.part13, pkidx.part14, pkidx.part15, pkidx.part16)
where t.owner = '{owner}'
  and t.tabname = '{table.Name}'
";
                        using (DB2DataReader reader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default))
                        {
                            var m = new DataTypeMapper();

                            while (reader.Read())
                            {
                                string dataType      = GetColumnType(reader.Get <short>("coltype"));
                                int?   dataLength    = reader.Get <int?>("collength");
                                int?   dataPrecision = reader.Get <int?>("colprecision");
                                int?   dataScale     = reader.Get <int?>("colscale");

                                columns.Add(new Column
                                {
                                    Name           = reader.GetString("colname"),
                                    DataType       = dataType,
                                    DataLength     = dataLength,
                                    DataPrecision  = dataPrecision,
                                    DataScale      = dataScale,
                                    IsNullable     = reader.GetString("isnullable") != "N",
                                    IsPrimaryKey   = reader.GetString("ispk") == "P",
                                    IsUnique       = reader.GetString("isunique") == "U",
                                    IsIdentity     = reader.GetString("isidentity") == "I",
                                    MappedDataType = m.MapFromDBType(ServerType.Informix, dataType, dataLength, dataPrecision, dataScale),
                                });
                            }
                        }
                    }

                    table.Owner   = owner;
                    table.Columns = columns;
                }
                finally
                {
                    sqlCon.Close();
                }
            }

            table.PrimaryKey           = DeterminePrimaryKeys(table);
            table.ForeignKeys          = DetermineForeignKeyReferences(table);
            table.HasManyRelationships = DetermineHasManyRelationships(table);

            return(Task.FromResult(columns));
        }
Esempio n. 12
0
        public string Read(string jsonQuery)
        {
            string selectSQL    = this._sqlParser.ReadSqlParser(jsonQuery);
            string objName      = this._sqlParser.ObjName;
            bool   isReadToList = this._sqlParser.IsReadToList;

            string jsonDataSet = string.Empty;

            if (!string.IsNullOrEmpty(selectSQL))
            {
                DB2Command dbCmd = new DB2Command(selectSQL, this._connection);
                this.OpenConnection();
                DB2DataReader dbReader = dbCmd.ExecuteReader();
                while (dbReader.Read())
                {
                    // add LIST seperator jsonDataSet
                    if (isReadToList && !string.IsNullOrEmpty(jsonDataSet))
                    {
                        jsonDataSet += ",";
                    }

                    int fieldCount = dbReader.FieldCount;

                    // open json row
                    jsonDataSet += "{";
                    for (int fieldIndex = 0; fieldIndex < fieldCount; fieldIndex++)
                    {
                        // GET NAME
                        string fieldName   = dbReader.GetName(fieldIndex);
                        string JOIN_PREFIX = "JOIN_";

                        bool isJoinField = false;
                        if (fieldName.ToUpper().StartsWith("JOIN_"))
                        {
                            isJoinField = true;
                            fieldName   = fieldName.Substring(fieldName.IndexOf(JOIN_PREFIX) + JOIN_PREFIX.Length);
                        }
                        fieldName = $"\"{fieldName}\"";

                        // GET VALUE
                        string fieldValue = dbReader.IsDBNull(fieldIndex) ? "\"\"" : dbReader.GetString(fieldIndex);
                        if (!isJoinField)
                        {
                            fieldValue = $"\"{fieldValue}\"";
                        }

                        jsonDataSet += $"{fieldName}:{fieldValue}";
                        if (fieldIndex < fieldCount - 1)
                        {
                            jsonDataSet += ",";
                        }
                    }
                    // close json row
                    jsonDataSet += "}";
                }

                //close
                dbReader.Close();
                dbReader.Dispose();
                this.CloseConnection();
            }
            return(StringHelper.Simpler(string.IsNullOrEmpty(jsonDataSet) ? null : ("{" + $"\"{objName}\"" + ":" + ((isReadToList) ? "[" + jsonDataSet + "]" : jsonDataSet) + "}"), Pattern.PATTERN_SPECIAL_CHARS));
        }
Esempio n. 13
0
        public IList <ForeignKey> DetermineForeignKeyReferences(Table table)
        {
            var foreignKeys = new List <ForeignKey>();

            using (var sqlCon = new DB2Connection(_connectionStr))
            {
                try
                {
                    sqlCon.Open();

                    using (DB2Command tableDetailsCommand = sqlCon.CreateCommand())
                    {
                        tableDetailsCommand.CommandText = $@"
select co.constrname, t.tabname, c.colno, c.colname, t2.tabname tabname2, c2.colno colno2, c2.colname colname2
  from sysreferences r
  inner join sysconstraints co on co.constrid = r.constrid and co.constrtype = 'R'
  inner join systables t on t.tabid = co.tabid
  inner join sysindexes i on i.idxname = co.idxname
  inner join syscolumns c on c.tabid = co.tabid

  inner join sysconstraints co2 on co2.constrid = r.primary
  inner join systables t2 on t2.tabid = co2.tabid
  inner join sysindexes i2 on i2.idxname = co2.idxname
  inner join syscolumns c2 on c2.tabid = co2.tabid
 
where (
       (c.colno = i.part1 and c2.colno = i2.part1)
    or (c.colno = i.part2 and c2.colno = i2.part2)
    or (c.colno = i.part3 and c2.colno = i2.part3)
    or (c.colno = i.part4 and c2.colno = i2.part4)
    or (c.colno = i.part5 and c2.colno = i2.part5)
    or (c.colno = i.part6 and c2.colno = i2.part6)
    or (c.colno = i.part7 and c2.colno = i2.part7)
    or (c.colno = i.part8 and c2.colno = i2.part8)
    or (c.colno = i.part9 and c2.colno = i2.part9)
    or (c.colno = i.part10 and c2.colno = i2.part10)
    or (c.colno = i.part11 and c2.colno = i2.part11)
    or (c.colno = i.part12 and c2.colno = i2.part12)
    or (c.colno = i.part13 and c2.colno = i2.part13)
    or (c.colno = i.part14 and c2.colno = i2.part14)
    or (c.colno = i.part15 and c2.colno = i2.part15)
    or (c.colno = i.part16 and c2.colno = i2.part16)
    )
  and t.owner = '{table.Owner}'
  and t.tabname = '{table.Name}'
";
                        using (DB2DataReader reader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default))
                        {
                            ForeignKey foreignKey = null;
                            while (reader.Read())
                            {
                                string constraintName = reader.GetString("constrname");
                                if (foreignKey != null && foreignKey.Name != constraintName)
                                {
                                    foreignKeys.Add(foreignKey);
                                    foreignKey = null;
                                }
                                if (foreignKey == null)
                                {
                                    foreignKey = new ForeignKey
                                    {
                                        Name               = constraintName,
                                        References         = reader.GetString("tabname2"),
                                        Columns            = new List <Column>(),
                                        UniquePropertyName = null,
                                        IsNullable         = true
                                    };
                                }

                                Column column = FindTableColumn(table, reader.GetString("colname"));
                                column.IsForeignKey         = true;
                                column.ConstraintName       = foreignKey.Name;
                                column.ForeignKeyTableName  = foreignKey.References;
                                column.ForeignKeyColumnName = reader.GetString("colname2");
                                foreignKey.Columns.Add(column);
                            }

                            if (foreignKey != null)
                            {
                                foreignKeys.Add(foreignKey);
                            }
                        }
                    }
                }
                catch
                {
                    sqlCon.Close();
                    throw;
                }
            }

            Table.SetUniqueNamesForForeignKeyProperties(foreignKeys);

            return(foreignKeys);
        }
Esempio n. 14
0
        IList <HasMany> DetermineHasManyRelationships(Table table)
        {
            var hasManys = new List <HasMany>();

            using (var sqlCon = new DB2Connection(_connectionStr))
            {
                try
                {
                    sqlCon.Open();

                    using (DB2Command tableDetailsCommand = sqlCon.CreateCommand())
                    {
                        tableDetailsCommand.CommandText = $@"
select co.constrname, t.tabname, c.colno, c.colname, t2.tabname tabname2, c2.colno colno2, c2.colname colname2
  from sysreferences r
  inner join sysconstraints co on co.constrid = r.constrid and co.constrtype = 'R'
  inner join systables t on t.tabid = co.tabid
  inner join sysindexes i on i.idxname = co.idxname
  inner join syscolumns c on c.tabid = co.tabid

  inner join sysconstraints co2 on co2.constrid = r.primary
  inner join systables t2 on t2.tabid = co2.tabid
  inner join sysindexes i2 on i2.idxname = co2.idxname
  inner join syscolumns c2 on c2.tabid = co2.tabid
 
where (
       (c.colno = i.part1 and c2.colno = i2.part1)
    or (c.colno = i.part2 and c2.colno = i2.part2)
    or (c.colno = i.part3 and c2.colno = i2.part3)
    or (c.colno = i.part4 and c2.colno = i2.part4)
    or (c.colno = i.part5 and c2.colno = i2.part5)
    or (c.colno = i.part6 and c2.colno = i2.part6)
    or (c.colno = i.part7 and c2.colno = i2.part7)
    or (c.colno = i.part8 and c2.colno = i2.part8)
    or (c.colno = i.part9 and c2.colno = i2.part9)
    or (c.colno = i.part10 and c2.colno = i2.part10)
    or (c.colno = i.part11 and c2.colno = i2.part11)
    or (c.colno = i.part12 and c2.colno = i2.part12)
    or (c.colno = i.part13 and c2.colno = i2.part13)
    or (c.colno = i.part14 and c2.colno = i2.part14)
    or (c.colno = i.part15 and c2.colno = i2.part15)
    or (c.colno = i.part16 and c2.colno = i2.part16)
    )
  and t2.owner = '{table.Owner}'
  and t2.tabname = '{table.Name}'
";
                        using (DB2DataReader reader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default))
                        {
                            HasMany hasMany = null;
                            while (reader.Read())
                            {
                                string constraintName = reader.GetString("constrname");
                                if (hasMany != null && hasMany.ConstraintName != constraintName)
                                {
                                    hasManys.Add(hasMany);
                                    hasMany = null;
                                }
                                if (hasMany == null)
                                {
                                    hasMany = new HasMany
                                    {
                                        ConstraintName = constraintName,
                                        Reference      = reader.GetString("tabname2"),
                                        PKTableName    = reader.GetString("tabname")
                                    };
                                }

                                hasMany.AllReferenceColumns.Add(reader.GetString("colname"));
                            }

                            if (hasMany != null)
                            {
                                hasManys.Add(hasMany);
                            }
                        }
                    }
                }
                catch
                {
                    sqlCon.Close();
                    throw;
                }
            }

            return(hasManys);
        }
        protected override string GenerateSelectSql(IList <ProviderPropertyExpression> properties,
                                                    ExpressionTreeToSqlCompilerBase <TOid> compiler)
        {
            _dataPageNumber = GetProviderPropertyValue <DataPageNumberExpression, int>(properties, -1);
            _dataPageSize   = GetProviderPropertyValue <DataPageSizeExpression, int>(properties, 0);

            string sql = "";

            //string orderByCols = String.Join(",",
            //                                 Enumerable.ToArray(Processor.Select(
            //                                                        GetProviderPropertyValue
            //                                                            <OrderByCollectionExpression,
            //                                                            CollectionExpression<OrderByExpression>>(
            //                                                            properties,
            //                                                            new CollectionExpression<OrderByExpression>(
            //                                                                new OrderByExpression[] {})),
            //                                                        delegate(OrderByExpression o) { return o.ToString("\"{0}\""); })));


            string orderByClause = string.IsNullOrEmpty(compiler.OrderByClause) ? "" : " ORDER BY " + compiler.OrderByClause;

            string mainQueryColumns = string.Join(",", Enumerable.ToArray(
                                                      FormatColumnNames(true, true,
                                                                        compiler.ProjectedColumns.Count > 0
                                                                                 ? compiler.ProjectedColumns
                                                                                 : SelectAllColumnNames())));

            sql = String.Format("\nSELECT {0}\nFROM {1}\n{2}\n{3} {4}\n{5};",
                                mainQueryColumns,
                                QualifiedTableName + " AS \"" + Table + "\"",
                                compiler.SqlJoinClauses,
                                string.IsNullOrEmpty(compiler.SqlWhereClause) ? "" : " WHERE ",
                                compiler.SqlWhereClause,
                                orderByClause);
            //}
#if DEBUG && EXPLAIN
            if (sql.StartsWith("SELECT"))
            {
                using (DB2Connection cn = new DB2Connection(ConnectionString))
                {
                    cn.Open();
                    DB2Command cm = new DB2Command(String.Format("EXPLAIN ANALYZE {0}", sql), cn);
                    foreach (IDataParameter par in compiler.ParameterCache.Values)
                    {
                        cm.Parameters.Add(par);
                    }

                    Debug.WriteLine("");
                    DB2DataReader dr = cm.ExecuteReader();
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            Debug.WriteLine(dr.GetString(0));
                        }
                    }
                    Debug.WriteLine("");
                }
            }
#endif
            return(sql);
        }
Esempio n. 16
0
    protected void DB2Connection()
    {
        string Text1        = TextBox1.Text;
        string inputPattern = @"^[\s\;]*\w+[^\;]*[\;\s]*$";
        Regex  input        = new Regex(inputPattern);
        Match  inputmatch   = input.Match(TextBox1.Text);

        if (inputmatch.Success)
        {
            string         s            = @"Server=localhost:50000;Database=" + DropDownList2.SelectedItem.Text + ";UID=db2admin;PWD=***;CurrentSchema=db2admin";
            DB2Connection  myConnection = new DB2Connection(s);
            DB2Command     cmd          = new DB2Command();
            DB2DataReader  r            = null;
            DB2DataAdapter adapter      = new DB2DataAdapter(Text1, myConnection);

            //string usertype = User.Identity.Name.Substring(4);
            string usertype = Session["Role"].ToString().ToLower();
            string pattern  = "";
            switch (usertype)
            {
            case "level1":
                pattern = @"((^|\;)\s*(?i)(\binsert\b|\bupdate\b|\bdelete\b|\bmerge\b|\bcreate\b|\balter\b|\bdrop\b)(?i))|(?i)\binto\b(?i)";
                break;

            case "level2":
                pattern = @"(^|;|\*\/)\s*(?i)(\bcreate\b|\balter\b|\bdrop\b)(?i)";
                break;

            default:
                pattern = @"((^|;|\*\/)\s*(?i)(\bdrop\b\s*database)(?i))";
                break;
            }
            Regex rgx   = new Regex(pattern);
            Match match = rgx.Match(TextBox1.Text);
            if (match.Success)
            {
                msg.Text = "Permission was denied on database " + DropDownList2.SelectedItem.Text;
            }
            else
            {
                try
                {
                    cmd.CommandText = TextBox1.Text;
                    cmd.Connection  = myConnection;
                    myConnection.Open();
                    string firstWord = Regex.Match(TextBox1.Text, @"\w+\b").ToString().ToLower();
                    if (firstWord == "select")
                    {
                        DataTable dt = new DataTable();
                        adapter.Fill(dt);
                        GridView1.DataSource = dt;

                        r = cmd.ExecuteReader();
                        int rowCount = 0;
                        if (r.HasRows) //results>0
                        {
                            while (r.Read())
                            {
                                rowCount++;
                            }
                            msg.Text = "Result: " + rowCount + " row(s) found";
                        }
                        else
                        {
                            msg.Text = "Result: 0 row(s) found.<br /><span style='background-color:#339933; color:black; font-size: 15pt'>";
                            for (int i = 0; i < r.FieldCount; i++)
                            {
                                msg.Text += r.GetName(i) + " |";
                            }
                            msg.Text += "</span><br />";
                        }
                        r.Close();
                    }
                    else
                    {
                        int numberOfRecords = cmd.ExecuteNonQuery();
                        msg.Text = "Result: " + numberOfRecords + " row(s) affected.";
                    }
                }
                catch (Exception ex)
                {
                    msg.Text = ex.Message;
                }
                finally
                {
                    myConnection.Close();
                    GridView1.DataBind();
                    FormatView();
                }
            }
        }
        else
        {
            //msg.Text = "Only one statement allowed";
            Header.Controls.Add(new LiteralControl("<script type=\"text/javascript\">alert('Only one statement allowed');</script>"));
        }
    }
        public void GetData()
        {
            myAggLabels    = new ObservableCollection <AggregatedLabel>();
            myContactLists = new ObservableCollection <ContactList>();
            DB2Connection conn = null;

            try
            {
                conn = new DB2Connection("XXXX;");
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show(ex.Message + " " + ex.InnerException);
            }
            //get all contactLists and their labels
            DB2Command command = new DB2Command("SQL SELECT statement");

            command.Connection = conn;
            conn.Open();
            //get all labels from database
            using (DB2DataReader dr = command.ExecuteReader())
            {
                while (dr.Read())
                {
                    AggregatedLabel aggLabel = new AggregatedLabel();
                    aggLabel.ID   = Convert.ToInt32(dr["LABEL_ID"]);
                    aggLabel.Name = dr["LABEL_NAME"].ToString();
                    myAggLabels.Add(aggLabel);
                }
            }
            //Add unique contactLists to dictionary
            Dictionary <int, ContactList> myContactDictionary = new Dictionary <int, ContactList>();

            using (DB2DataReader dr = command.ExecuteReader())
            {
                while (dr.Read())
                {
                    int id = Convert.ToInt32(dr["CONTACT_LIST_ID"]);
                    if (!myContactDictionary.ContainsKey(id))
                    {
                        ContactList contactList = new ContactList();
                        contactList.ContactListID   = id;
                        contactList.ContactListName = dr["CONTACT_LIST_NAME"].ToString();
                        contactList.AggLabels       = new ObservableCollection <AggregatedLabel>()
                        {
                            new AggregatedLabel()
                            {
                                ID   = Convert.ToInt32(dr["LABEL_ID"]),
                                Name = dr["LABEL_NAME"].ToString()
                            }
                        };
                        myContactDictionary.Add(id, contactList);
                    }
                    else
                    {
                        //populate existing contact lists with remaining labels
                        ContactList contactList = myContactDictionary[id];
                        contactList.AggLabels.Add
                        (
                            new AggregatedLabel()
                        {
                            ID   = Convert.ToInt32(dr["LABEL_ID"]),
                            Name = dr["LABEL_NAME"].ToString()
                        }
                        );
                    }
                }
            }
            //add to observable collection
            foreach (KeyValuePair <int, ContactList> contactKeyValue in myContactDictionary)
            {
                ContactList contactList = contactKeyValue.Value;
                myContactLists.Add(contactList);
            }
            conn.Close();
        }
Esempio n. 18
0
        static void Main(string[] args)
        {
            try
            {
                DB2Connection con = new DB2Connection("Server = /*IP*/; UID=/*Instanz*/; PWD=*****; DATABASE=TPCH");
                con.Open();
                Console.Write("Land (in Großbuchstaben angeben) ? ");
                String input = "";
                while (input.Length == 0 || input == null)
                {
                    input = Console.In.ReadLine();
                }
                Console.WriteLine();

                /***********************************************************************
                **
                ** Aufgabe 1.1 (a)
                **
                ***********************************************************************/
                Console.WriteLine("***************");
                Console.WriteLine("Aufgabe 1.1 (a)");
                Console.WriteLine("***************");
                Console.WriteLine();

                DB2Command cmd = con.CreateCommand();
                cmd.CommandText = "SELECT c_name FROM customer, nation WHERE n_nationkey = c_nationkey AND n_name = '" + input + "' ";
                DB2DataReader reader1 = cmd.ExecuteReader();

                while (reader1.Read())
                {
                    Console.WriteLine("Kunde: " + reader1.GetString(0));
                }
                reader1.Close();

                Console.WriteLine();
                Console.WriteLine();


                /***********************************************************************
                **
                ** Aufgabe 1.1 (b)
                **
                ***********************************************************************/
                Console.WriteLine("***************");
                Console.WriteLine("Aufgabe 1.1 (b)");
                Console.WriteLine("***************");
                Console.WriteLine();

                cmd.CommandText = "SELECT c_name FROM customer, nation WHERE n_nationkey = c_nationkey AND n_name = ? ";
                cmd.Prepare();
                cmd.Parameters.Add("n_name", DB2Type.VarChar, 25);
                cmd.Parameters["n_name"].Value = input;
                DB2DataReader reader2 = cmd.ExecuteReader();

                while (reader2.Read())
                {
                    Console.WriteLine("Kunde: " + reader2.GetString(0));
                }
                reader2.Close();

                Console.WriteLine();
                Console.WriteLine();


                /***********************************************************************
                **
                ** Aufgabe 1.2
                **
                ***********************************************************************/
                Console.WriteLine("***********");
                Console.WriteLine("Aufgabe 1.2");
                Console.WriteLine("***********");
                Console.WriteLine();

                cmd.CommandText = "SELECT c_name, c_custkey, COUNT(o_totalprice) FROM customer, nation, orders WHERE n_nationkey = c_nationkey AND c_custkey = o_custkey AND n_name = '" + input + "' GROUP BY c_name, c_custkey";

                DB2DataReader reader3 = cmd.ExecuteReader();

                while (reader3.Read())
                {
                    DB2Command SPCMD = con.CreateCommand();
                    SPCMD.CommandText = "orders_of";                    //Geb ich den namen der SP an
                    SPCMD.CommandType = CommandType.StoredProcedure;    //Man muss noch zeigen das es SP ist
                    SPCMD.Parameters.Add("IN_table", DB2Type.Integer).Value    = reader3.GetDB2Int32(1);
                    SPCMD.Parameters.Add("OUT_sql", DB2Type.Integer).Direction = ParameterDirection.Output;
                    DB2DataReader dr = SPCMD.ExecuteReader();
                    Console.WriteLine("Kunde:               " + reader3.GetString(0));
                    Console.WriteLine("Kundenr.:            " + reader3.GetString(1));
                    Console.WriteLine("AnzahlBestellungen:  " + SPCMD.Parameters["OUT_sql"].Value);

                    decimal summe = 0;
                    bool    first = true;
                    int     days  = 0;

                    while (dr.Read())
                    {
                        summe += Convert.ToDecimal(dr["TOTALPRICE"]);
                    }
                    Console.WriteLine("Gesamtumsatz:        " + summe + " $ (USD)");
                    dr.Close();
                }
                reader1.Close();

                Console.WriteLine();
                Console.WriteLine();

                /***********************************************************************
                **
                ** Aufgabe 2.1
                **
                ***********************************************************************/
                Console.WriteLine("***********");
                Console.WriteLine("Aufgabe 2.1");
                Console.WriteLine("***********");
                Console.WriteLine();

                Console.WriteLine("ServerType:    " + con.ServerType);                                              //Typ des Servers
                Console.WriteLine("ServerVersion: " + con.ServerVersion);                                           //Version des Servers
                Console.WriteLine("Version:       " + con.ServerMajorVersion + "." + con.ServerMinorVersion);       //übergeordnete Version und untergeordnete Version des Servers
                Console.WriteLine("Verbindung:    " + con.ConnectionString);                                        //Ruft die Zeichenfolge ab, die zum Öffnen einer Datenbankverbindung verwendet wird
                Console.WriteLine("Datenbankname: " + con.Database);                                                //Ruft den Namen der aktuellen Datenbank ab
                Console.WriteLine("InProperty1:   " + con.InternalProperty1);
                Console.WriteLine("InProperty7:   " + con.InternalProperty7);
                Console.WriteLine("BuildVersion:  " + con.ServerBuildVersion);                                      //Buildversion des Servers
                Console.WriteLine("RevVersion:    " + con.ServerRevisionVersion);                                   //Überarbeitungsversion des Servers
                Console.WriteLine("Status:        " + con.State);                                                   //aktueller Status der Verbindung
                Console.WriteLine("UserId:        " + con.UserId);                                                  //UserId

                Console.WriteLine();
                Console.WriteLine();

                /***********************************************************************
                **
                ** Aufgabe 2.2
                **
                ***********************************************************************/
                Console.WriteLine("***********");
                Console.WriteLine("Aufgabe 2.2");
                Console.WriteLine("***********");
                Console.WriteLine();

                cmd.CommandText = "SELECT n_nationkey, n_name, n_regionkey FROM nation";
                DB2DataReader reader5 = cmd.ExecuteReader();
                printResult(reader5, 3);
                reader5.Close();

                Console.WriteLine();
                Console.WriteLine();

                /***********************************************************************
                **
                ** Aufgabe 3
                **
                ***********************************************************************/
                Console.WriteLine("***********");
                Console.WriteLine("Aufgabe 3");
                Console.WriteLine("***********");
                Console.WriteLine();

                DataSet           ds = new DataSet();
                DB2DataAdapter    da = new DB2DataAdapter("SELECT o_orderkey, o_custkey, o_orderpriority, o_totalprice FROM orders", con);      //benötigt zum Füllen des ds
                DB2CommandBuilder cb = new DB2CommandBuilder(da);

                Console.Write("Kundennummer(o_custkey) eingeben: ");
                String input2 = "";
                while (input2.Length == 0 || input == null)
                {
                    input2 = Console.In.ReadLine();
                }
                Console.WriteLine();
                con.Close();

                Console.WriteLine();
                Console.WriteLine();
                Console.ReadKey();
            }
            catch (DB2Exception e)
            {
                Console.Error.WriteLine(e.Message);
                Console.ReadKey();
            }
        }
Esempio n. 19
0
        private void metroButton2_Click(object sender, EventArgs e)
        {
            string ConStr = "Database = " + metroTextBox3.Text.Trim() + ";User ID=inetsoft;Password = etl5boxes; server = " + metroTextBox3.Text.Trim().ToLower() + "." + metroTextBox3.Text.Trim().ToLower() + ".ei:50000; Max Pool Size=100;Persist security info =False;Pooling = True ";
            string Query  = @"Select 
                sh.SHPMNT_REF,
                ship.NAME,
                ship.ADDR_LINE1,
                ship.ADDR_LINE2,
                CONCAT(CONCAT(ship.CITY, ','), ship.COUNTRY),
                cons.NAME,
                cons.ADDR_LINE1,
                cons.ADDR_LINE2,
                CONCAT(CONCAT(cons.CITY, ','), cons.COUNTRY),
                hdr.ORIGIN_NAME_AWB,
                hdr.DESTIN_NAME_AWB,
                hdr.FLIGHT_TO_AWB_1,
                hdr.FLIGHT_BY_AWB_1,
                hdr.CHRG_CODE_FRGHT_1,
                hdr.CHRG_CODE_OTHER_1,
                sh.PCS_GRS,
                CAST(ROUND(sh.WGT_GRS_2 / 10000, 0) AS DECIMAL(18, 1)),
                CAST(ROUND(sh.WGT_CHRG_2 / 10000, 0) AS DECIMAL(18, 1)),
                hdr.FLIGHT_NUM_AWB_1,
                hdr.HNDL_INFO_AWB_1,
                hdr.HNDL_INFO_AWB_2,
                SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( '!',concat(concat(itm.REMARKS_AWB_PFX,' '),itm.REMARKS_AWB)))) as VARCHAR(1024)), 3),
                sl.MAWB_BL_NO
                                    

                From
                EXPORT.SHPMNT_HDR sh
                inner join IASDB.SHIP_LOG sl on sl.INVOICE_NO = sh.SHPMNT_REF
                inner join HELPDB.CLIENT ship on ship.CLIENT_NO = sh.SHIPPER_ID
                inner join HELPDB.CLIENT cons on cons.CLIENT_NO = sh.CONSIGN_ID
                inner join EXPORT.AIR_FRGHT_HDR hdr on hdr.INVOICE_REF = sh.SHPMNT_REF
                inner join EXPORT.AIR_FRGHT_ITEM itm on itm.CARRIER_REF = hdr.CARRIER_REF
                where sh.SHPMNT_REF = '" + metroTextBox1.Text.Trim() + @"'
                Group by
                ship.NAME,
                ship.ADDR_LINE1,
                ship.ADDR_LINE2,
                ship.CITY,
                ship.COUNTRY,
                sh.SHPMNT_REF,
                cons.NAME,
                cons.ADDR_LINE1,
                cons.ADDR_LINE2,
                cons.city,
                cons.COUNTRY,
                CAST(ROUND(sh.WGT_GRS_2 / 10000, 0) AS DECIMAL(18, 1)),
                CAST(ROUND(sh.WGT_CHRG_2 / 10000, 0) AS DECIMAL(18, 1)),
                hdr.ORIGIN_NAME_AWB,
                hdr.DESTIN_NAME_AWB,
                hdr.FLIGHT_TO_AWB_1,
                hdr.FLIGHT_BY_AWB_1,
                hdr.CHRG_CODE_FRGHT_1,
                hdr.CHRG_CODE_OTHER_1,
                hdr.FLIGHT_NUM_AWB_1,
                sh.PCS_GRS,
                hdr.HNDL_INFO_AWB_1,
                hdr.HNDL_INFO_AWB_2,
                sl.MAWB_BL_NO
                ";

            using (DB2Connection myconnection = new DB2Connection(ConStr))
            {
                myconnection.Open();
                DB2Command cmd = myconnection.CreateCommand();
                cmd.CommandText = Query;
                DB2DataReader rd = cmd.ExecuteReader();

                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        //richTextBox1.Text = richTextBox1.Text + rd.GetValue(0).ToString() + "   " + rd.GetValue(1).ToString();


                        Excel.Application app = new Excel.Application();
                        //string filepath = @"HAWBTemp.xlsx";
                        //Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                        Excel.Workbook  workbook  = app.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + "/HAWBTemplate.xlsx", ReadOnly: false, Editable: true);
                        Excel.Worksheet worksheet = workbook.Worksheets.Item[1];
                        //worksheet.Cells[5, 7].Value = rd.GetValue(1).ToString();
                        //Shipper Update
                        worksheet.Range["D1"].Value  = rd.GetValue(22).ToString();
                        worksheet.Range["N1"].Value  = rd.GetValue(0).ToString();
                        worksheet.Range["M51"].Value = rd.GetValue(0).ToString();
                        worksheet.Range["B3"].Value  = rd.GetValue(1).ToString();
                        worksheet.Range["B4"].Value  = rd.GetValue(2).ToString();
                        worksheet.Range["B5"].Value  = rd.GetValue(3).ToString();
                        worksheet.Range["B6"].Value  = rd.GetValue(4).ToString();

                        //Consignee Update

                        worksheet.Range["B8"].Value  = rd.GetValue(5).ToString();
                        worksheet.Range["B9"].Value  = rd.GetValue(6).ToString();
                        worksheet.Range["B10"].Value = rd.GetValue(7).ToString();
                        worksheet.Range["B11"].Value = rd.GetValue(8).ToString();

                        //Origin-Destination
                        worksheet.Range["B18"].Value = rd.GetValue(9).ToString();
                        worksheet.Range["B21"].Value = rd.GetValue(10).ToString();

                        //Dest-First Flight
                        worksheet.Range["B19"].Value = rd.GetValue(11).ToString();
                        worksheet.Range["C19"].Value = rd.GetValue(12).ToString();

                        //Prepaid-Collect

                        if (rd.GetValue(13).ToString() == "P")
                        {
                            worksheet.Range["K19"].Value = " P";
                        }
                        else
                        {
                            worksheet.Range["K19"].Value = "     C";
                        }

                        if (rd.GetValue(14).ToString() == "P")
                        {
                            worksheet.Range["L19"].Value = "P";
                        }
                        else
                        {
                            worksheet.Range["L19"].Value = "    C";
                        }

                        //Weight-Pieces
                        worksheet.Range["B27"].Value = rd.GetValue(15).ToString();
                        worksheet.Range["C27"].Value = rd.GetValue(16).ToString();
                        worksheet.Range["G27"].Value = rd.GetValue(17).ToString();
                        worksheet.Range["B37"].Value = rd.GetValue(15).ToString();
                        worksheet.Range["C37"].Value = rd.GetValue(16).ToString();
                        //Flight
                        worksheet.Range["F21"].Value = rd.GetValue(18).ToString();

                        //Handling Information
                        worksheet.Range["C23"].Value = rd.GetValue(19).ToString();
                        worksheet.Range["C24"].Value = rd.GetValue(20).ToString();
                        worksheet.Range["O46"].Value = metroTextBox2.Text;

                        //Data
                        DateTime dt = DateTime.Now;
                        worksheet.Range["I49"].Value = dt.ToShortDateString();

                        //Description

                        String description = rd.GetValue(21).ToString();

                        List <string> descResult = description.Split('!').ToList();

                        descResult = descResult.Where(s => !string.IsNullOrWhiteSpace(s)).Distinct().ToList();

                        //richTextBox1.Text = richTextBox1.Text + descResult[0] +"///////" + descResult[1]+ "//////" + descResult[2];

                        for (int i = descResult.Count; i <= 7; i++)
                        {
                            descResult.Add("");
                        }

                        worksheet.Range["B28"].Value = descResult[0];
                        worksheet.Range["B29"].Value = descResult[1];
                        worksheet.Range["B30"].Value = descResult[2];
                        worksheet.Range["B31"].Value = descResult[3];
                        worksheet.Range["B32"].Value = descResult[4];
                        worksheet.Range["B33"].Value = descResult[5];
                        worksheet.Range["B34"].Value = descResult[6];
                        worksheet.Range["B35"].Value = descResult[7];



                        /*String remarks = rd.GetValue(22).ToString();
                         * List<string> remarksResult = remarks.Split('!').ToList();
                         * remarksResult = remarksResult.Where(s => !string.IsNullOrWhiteSpace(s)).Distinct().ToList();
                         * for (int i = remarksResult.Count; i <= 7; i++)
                         * {
                         *  remarksResult.Add("");
                         * }
                         *
                         * MetroMessageBox.Show(this,remarksResult[1]);
                         * MetroMessageBox.Show(this, remarksResult[2]);
                         * MetroMessageBox.Show(this, remarksResult[3]);
                         * MetroMessageBox.Show(this, remarksResult[4]);
                         * MetroMessageBox.Show(this, remarksResult[5]);
                         *
                         * worksheet.Range["M28"].Value = remarksResult[0];
                         * worksheet.Range["M29"].Value = remarksResult[1];
                         * worksheet.Range["M30"].Value = remarksResult[2];
                         * worksheet.Range["M31"].Value = remarksResult[3];
                         * worksheet.Range["M32"].Value = remarksResult[4];
                         * worksheet.Range["M33"].Value = remarksResult[5];
                         * worksheet.Range["M34"].Value = remarksResult[6];
                         * worksheet.Range["M35"].Value = remarksResult[7];*/

                        workbook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "/" + metroTextBox1.Text.Trim() + ".pdf");
                        app.Application.ActiveWorkbook.Save();
                        workbook.Close(0);
                        app.Application.Quit();
                        app.Quit();

                        MetroFramework.MetroMessageBox.Show(this, "House Created!");
                    }
                }
            }
        }