Пример #1
0
        public DataSet Request(DataSet ds, IncludeSchema isEnum, string nombreProcedimiento, IDictionaryEnumerator parametros)
        {
            DB2Connection lc = OpenConnection();

            try
            {
                DB2Command comm = new DB2Command(nombreProcedimiento, lc);
                comm.CommandType = System.Data.CommandType.StoredProcedure;
                if (parametros != null)
                {
                    while (parametros.MoveNext())
                    {
                        comm.Parameters.Add(parametros.Key.ToString(), parametros.Value);
                    }
                }
                DB2DataAdapter adapter = new DB2DataAdapter(comm);
                if (isEnum == IncludeSchema.YES)
                {
                    adapter.FillSchema(ds, SchemaType.Mapped);
                    adapter.Fill(ds);
                }
                else
                {
                    adapter.Fill(ds, "result_ " + ds.Tables.Count.ToString());
                }
            }
            catch (Exception e)
            {
                exceptions  = "Error ejecutando SQL." + cambioLinea + cambioLinea;
                exceptions += e.ToString();
                AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, nombreProcedimiento, e, exceptions);
            }
            CloseConnection(lc);
            return(ds);
        }
Пример #2
0
        public DataTable Getda_excel(string sql, string path)
        {
            string        constr = "Database=cig;Password=Shaorong01;User ID=db2admin";
            DB2Connection conn   = new DB2Connection(constr);

            try
            {
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {
                    DB2Command     sqlcon = new DB2Command(sql, conn);
                    DB2DataAdapter ds     = new DB2DataAdapter(sqlcon);
                    DataTable      dt     = new DataTable();
                    ds.Fill(dt);
                    return(dt);
                }
                else
                {
                    return(null);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return(null);
            }
            finally
            {
                conn.Close();
            }
        }
Пример #3
0
        //********************************//
        // Desbloquear item de Cuarentena //
        //********************************//
        public static bool UnlockQuarantine(string itemId)
        {
            string     updQuarantine = "UPDATE CGS.\"QUARANTINE_RULE\" SET QUARANTINE_RULE_ENABLED='N' WHERE ITEM_ID='" + itemId + "'";
            DB2Command cmdcgs        = new DB2Command();

            cmdcgs.Connection  = ConnectDB2CGS;
            cmdcgs.CommandText = updQuarantine;
            ConnectDB2CGS.Open();
            if (cmdcgs.ExecuteNonQuery() == 1)
            {
                string[] itemInfo = WS.getContainerInfo(itemId);
                ConnectDB2CGS.Close();
                string         getQuarantine = "SELECT QUARANTINE_RULE_KEY FROM CGS.\"QUARANTINE_RULE\" WHERE ITEM_ID='" + itemId + "'";
                DataTable      Qid           = new DataTable();
                DB2DataAdapter da            = new DB2DataAdapter(getQuarantine, ConnectDB2CGS);
                da.Fill(Qid);
                ConnectDB2CGSDW.Open();
                //Inserto en tabla QUARANTINE_RULE_HIST
                if (insertQRhistory(Qid.Rows[0][0].ToString(), "DESBLOQUEO DE MATERIAL", "Material validado por " + Global.vrm.QCUser, itemInfo[0], itemId, "N", Global.vrm.QCUser))
                {
                    //Inserto en tabla ITEM_HISTORY_025
                    insertIhistory(itemId, "QUARANTINE UNLOCK", Global.vrm.QCUser);
                }

                ConnectDB2CGSDW.Close();
                return(true);
            }
            else
            {
                ConnectDB2CGS.Close();
                return(false);
            }
        }
Пример #4
0
        public override void Update(System.Data.DataTable data, string selectsql)
        {
            DB2Command cmd;

            using (cmd = new DB2Command(selectsql, conn))
            {
                if (this._s == DBStatus.Begin_Trans)
                {
                    cmd.Transaction = this.tran;
                }

                DB2DataAdapter    adt     = new DB2DataAdapter(cmd);
                DB2CommandBuilder builder = new DB2CommandBuilder(adt);

                try
                {
                    adt.UpdateCommand = builder.GetUpdateCommand();
                    adt.Update(data);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Cancel();
                    cmd = null;
                }
            }
        }
Пример #5
0
        public TableInfoList(string connectionString, string schema = null)
        {
            //_connectionString = connectionString;

            using (var cn = new DB2Connection(connectionString))
            {
                cn.Open();

                using (var cm = cn.CreateCommand())
                {
                    cm.CommandType = CommandType.Text;

                    if (string.IsNullOrWhiteSpace(schema))
                    {
                        cm.CommandText = SQL_WO_SCHEMA;
                    }
                    else
                    {
                        cm.CommandText = string.Format(SQL_W_SCHEMA, schema);
                    }

                    using (var a = new DB2DataAdapter(cm))
                    {
                        _data = new DataTable();
                        a.Fill(_data);
                    }
                }
            }
        }
Пример #6
0
        public ArrayList RequestGlobalAsCollection(string sql)
        {
            ArrayList result = null;

            AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
            DB2DataAdapter adapter = new DB2DataAdapter(sql, connection);
            DataSet        ds      = new DataSet();

            adapter.Fill(ds);

            if (ds.Tables.Count > 1)
            {
                throw new Exception("Consulta inválida");
            }

            ArrayList list = new ArrayList(ds.Tables[0].Rows.Count);

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                Hashtable table = new Hashtable();

                for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                {
                    DataColumn col  = ds.Tables[0].Columns[j];
                    Object     data = ds.Tables[0].Rows[i][j];
                    table.Add(col.ColumnName, data);
                }
                list.Add(table);
            }

            result = list;

            return(result);
        }
        /// <summary>
        /// Function that executes the given command on the database and returns the result.
        /// </summary>
        /// <param name="myquery">the query (stored procedure) to execute on the database.</param>
        /// <returns>A dataset which is filled with the table(s) with results from the stored procedure.</returns>
        public DataSet Query(string myquery)
        {
            DataSet TempDataSet = new DataSet();

            try
            {
                DB2DataAdapter DataAdapter = new DB2DataAdapter(myquery, ConnString);
                DataAdapter.Fill(TempDataSet);
            }
            catch (SqlException e_dbconn_sql)
            {
                //SocketClass.LogError(e_dbconn_sql, "LogServer.txt");
                //Create an error message
                TempDataSet = new DataSet("Replies");
                TempDataSet.Tables.Add("Reply");
                TempDataSet.Tables[0].Columns.Add("INT");
                DataRow newRow = TempDataSet.Tables[0].NewRow();
                newRow[0] = -404;
                TempDataSet.Tables[0].Rows.Add(newRow);
            }
            catch (TimeoutException e_dbconn_conn)
            {
                //SocketClass.LogError(e_dbconn_conn, "LogServer.txt");
                //Create an error message
                TempDataSet = new DataSet("Replies");
                TempDataSet.Tables.Add("Reply");
                TempDataSet.Tables[0].Columns.Add("INT");
                DataRow newRow = TempDataSet.Tables[0].NewRow();
                newRow[0] = -404;
                TempDataSet.Tables[0].Rows.Add(newRow);
            }
            return(TempDataSet);
        }
Пример #8
0
        /// <summary>
        /// 获取DataSet数据列表
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="ctype">类型</param>
        /// <param name="dataname">内存表</param>
        /// <param name="param">参数</param>
        /// <returns>返回自定义内存表</returns>
        public DataSet GetDataSet(string sql, CommandType ctype, string dataname, params IDataParameter[] param)
        {
            Open();
            var cmd = new DB2Command();

            PrepareCommand(cmd, _connSql, null, ctype, sql, param);
            using (var dap = new DB2DataAdapter(cmd))
            {
                var ds = new DataSet();
                try
                {
                    dap.Fill(ds, dataname);
                    dap.Dispose();
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                    return(ds);
                }
                catch (DB2Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    Close();
                }
            }
        }
Пример #9
0
        private DataTable innerFillDataTable(DB2Command command, int timeout, bool inTransaction)
        {
            try
            {
                var table = new DataTable();
                command.Connection = inTransaction
                    ? (DB2Connection)TransConnection
                    : (DB2Connection)Connection;
                if (!IsValidTimeout(command, timeout))
                {
                    throw new ArgumentException("Invalid CommandTimeout value", nameof(timeout));
                }

                if (inTransaction)
                {
                    command.Transaction = (DB2Transaction)Transaction;
                }
                using (var da = new DB2DataAdapter(command))
                {
                    da.Fill(table);
                }
                return(table);
            }
            catch (Exception ex)
            {
                Logger?.LogError(ex, $"Error at FillDataTable; command text: {command.CommandText}");
                throw new DbDataException(ex, command.CommandText);
            }
        }
Пример #10
0
        public DataSet RequestGlobal(DataSet ds, IncludeSchema isEnum, string dataBase, string sql)
        {
            DB2Connection lc;

            if (dataBase == "")
            {
                lc = OpenConnectionGlobal();
            }
            else
            {
                lc = OpenConnectionGlobal(dataBase);
            }

            try
            {
                AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
                DB2DataAdapter adapter = new DB2DataAdapter(sql, lc);

                if (isEnum == IncludeSchema.YES)
                {
                    adapter.FillSchema(ds, SchemaType.Mapped);
                    adapter.Fill(ds);
                }
                else
                {
                    adapter.Fill(ds, "result_ " + ds.Tables.Count.ToString());
                }
            }
            catch { }
            CloseConnection(lc);

            return(ds);
        }
Пример #11
0
        public static bool queryUser(string user, string pass)
        {
            DataTable dt        = new DataTable();
            string    queryUser = "******"USER\" u ";

            queryUser += "LEFT JOIN CGS.USER_PASSWORD p ";
            queryUser += "ON p.USER_KEY=u.USER_KEY ";
            queryUser += "WHERE u.USER_ID='" + user + "'";
            ConnectDB2CGS.Open();
            DB2DataAdapter adapter = new DB2DataAdapter(queryUser, ConnectDB2CGS);

            adapter.Fill(dt);
            ConnectDB2CGS.Close();
            string userId            = dt.Rows[0][0].ToString();
            string usuario           = dt.Rows[0][1].ToString();
            string password          = dt.Rows[0][2].ToString();
            string usuarioFormateado = usuario.Replace(" ", "");

            if ((Hash.getHash(pass, password)) && (user == usuarioFormateado))
            {
                return(true);
            }
            else
            {
                return(false);
            }
        }
Пример #12
0
        public DataTable queryToDataTable(string q, DataTable ds)
        {
            DB2DataAdapter sda = new DB2DataAdapter(q, con);

            ds = new DataTable();
            sda.Fill(ds);
            return(ds);
        }
Пример #13
0
 public IBMRecordsUnit(string conn, string cmdtxt)
 {
     strConn    = conn;
     cn         = new DB2Connection(strConn);
     strCmdText = cmdtxt;
     adapter    = new DB2DataAdapter(strCmdText, cn);
     CmdBuilder = new DB2CommandBuilder(adapter);
 }
Пример #14
0
 private void initObject()
 {
     cn = new DB2Connection(strConn);
     //if (!cn.IsOpen)
     //    cn.Open();
     adapter    = new DB2DataAdapter(strCmdText, cn);
     CmdBuilder = new DB2CommandBuilder(adapter);
     cmd        = new DB2Command(strCmdText, cn);
 }
Пример #15
0
        public object showlistbox(string q)
        {
            DB2DataAdapter sda = new DB2DataAdapter(q, con);
            DataSet        ds  = new DataSet();

            sda.Fill(ds);
            object data = ds.Tables[0];

            return(data);
        }
        private void tabControl1_Selected(object sender, TabControlEventArgs e)
        {
            // Move the input focus to the query builder.
            // This will fire Leave event in the text box and update the query builder
            // with modified query text.
            queryBuilder1.Focus();
            Application.DoEvents();


            // Try to execute the query using current database connection

            if (e.TabPage == tabPageData)
            {
                dataGridView1.DataSource = null;

                if (queryBuilder1.MetadataProvider != null && queryBuilder1.MetadataProvider.Connected)
                {
                    DB2Command command = (DB2Command)queryBuilder1.MetadataProvider.Connection.CreateCommand();
                    command.CommandText = queryBuilder1.SQL;

                    // handle the query parameters
                    if (queryBuilder1.Parameters.Count > 0)
                    {
                        for (int i = 0; i < queryBuilder1.Parameters.Count; i++)
                        {
                            if (!command.Parameters.Contains(queryBuilder1.Parameters[i].FullName))
                            {
                                DB2Parameter parameter = new DB2Parameter();
                                parameter.ParameterName = queryBuilder1.Parameters[i].FullName;
                                parameter.DbType        = queryBuilder1.Parameters[i].DataType;
                                command.Parameters.Add(parameter);
                            }
                        }

                        using (QueryParametersForm qpf = new QueryParametersForm(command))
                        {
                            qpf.ShowDialog();
                        }
                    }

                    DB2DataAdapter adapter = new DB2DataAdapter(command);
                    DataSet        dataset = new DataSet();

                    try
                    {
                        adapter.Fill(dataset, "QueryResult");
                        dataGridView1.DataSource = dataset.Tables["QueryResult"];
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "SQL query error");
                    }
                }
            }
        }
Пример #17
0
        /// <summary>
        /// B2返回一个DataSet
        /// </summary>
        /// <param name="sql">SQL语句或命令</param>
        /// <param name="value">参数值列表</param>
        /// <returns>DataSet</returns>
        public override DataSet ExecuteDataSetParams(string sql, params object[] value)
        {
            DB2Command comm = (DB2Command)CreateCommand(sql, value);

            DataSet        ds      = new DataSet();
            DB2DataAdapter adapter = new DB2DataAdapter();

            adapter.SelectCommand = comm;
            adapter.Fill(ds);
            return(ds);
        }
Пример #18
0
        /// <summary>
        /// <para>Create a <see cref="DB2DataAdapter"/> with the given update behavior and connection.</para>
        /// </summary>
        /// <param name="updateBehavior">
        /// <para>One of the <see cref="UpdateBehavior"/> values.</para>
        /// </param>
        /// <param name="connection">
        /// <para>The open connection to the database.</para>
        /// </param>
        /// <returns>An <see cref="DB2DataAdapter"/>.</returns>
        /// <exception cref="ArgumentNullException">
        /// <para><paramref name="connection"/> can not be <see langword="null"/> (Nothing in Visual Basic).</para>
        /// </exception>
        protected override DbDataAdapter GetDataAdapter(UpdateBehavior updateBehavior, IDbConnection connection)
        {
            string         queryStringToBeFilledInLater = String.Empty;
            DB2DataAdapter adapter = new DB2DataAdapter(queryStringToBeFilledInLater, (DB2Connection)connection);

            if (updateBehavior == UpdateBehavior.Continue)
            {
                adapter.RowUpdated += new DB2RowUpdatedEventHandler(OnDB2RowUpdated);
            }
            return(adapter);
        }
Пример #19
0
        /// <summary>
        /// A2返回一个DataSet
        /// </summary>
        /// <param name="sql">SQL语句或命令</param>
        /// <returns>DataSet</returns>
        public override DataSet ExecuteDataSet(string sql)
        {
            DB2Command comm = (DB2Command)CreateCommand(sql);

            DB2DataAdapter adapter = new DB2DataAdapter();
            DataSet        ds      = new DataSet();

            adapter.SelectCommand = comm;

            adapter.Fill(ds);
            return(ds);
        }
Пример #20
0
        public override DbDataAdapter GetAdapter(string selectCommand, DbConnection connection,
                                                 CommandParameterCollection parameters)
        {
            DB2DataAdapter adapter = new DB2DataAdapter(selectCommand, connection as DB2Connection);

            foreach (CommandParameter p in parameters)
            {
                DB2Parameter parameter = adapter.SelectCommand.Parameters.Add(p.Name, (DB2Type)p.DataType, p.Size);
                parameter.Value = p.Value;
            }
            return(adapter);
        }
        /// <summary>
        /// Method to execute a SQL query and return a dataset.
        /// </summary>
        /// <param name="connectionName">Connection name in the configuration file.</param>
        /// <param name="query">Query string to be executed.</param>
        /// <returns>DataSet with the query results.</returns>
        protected DataSet ExecuteQuery(string connectionName, string query)
        {
            var dataset    = new DataSet();
            var connection = GetConnection(connectionName);

            // Verify if number of entities match number of records.
            using (var adapter = new DB2DataAdapter(query, connection))
            {
                adapter.Fill(dataset);
            }

            return(dataset);
        }
Пример #22
0
 public override void dame_dataset_de(String consulta, DataSet data_set)
 {
     try
     {
         adaptador = new DB2DataAdapter();
         adaptador.SelectCommand = new DB2Command(consulta, conexion);
         adaptador.Fill(data_set);
     }
     catch (DB2Exception excepcion)
     {
         MessageBox.Show(excepcion.Message);
     }
 }
Пример #23
0
 public DbDataAdapter CreateAdapter()
 {
     try
     {
         DbDataAdapter dtAdapter = null;
         dtAdapter = new DB2DataAdapter();
         return(dtAdapter);
     }
     catch (Exception)
     {
         throw;
     }
 }
Пример #24
0
        public ICustomActivityResult Execute()

        {
            DB2Connection  con     = null;
            DB2DataAdapter adapter = null;
            DataTable      dt      = new DataTable("resultSet");

            try
            {
                DB2ConnectionStringBuilder cnb = new DB2ConnectionStringBuilder(ConnectionString);
                if (!string.IsNullOrEmpty(UserName))
                {
                    cnb.UserID   = UserName;
                    cnb.Password = Password;
                }

                con = new DB2Connection(cnb.ConnectionString);
                con.Open();

                using (DB2Command command = new DB2Command(Query, con))
                {
                    command.CommandType    = System.Data.CommandType.Text;
                    command.CommandTimeout = Convert.ToInt32(TimeInSeconds);
                    adapter = new DB2DataAdapter(command);

                    adapter.Fill(dt);
                }

                return(this.GenerateActivityResult(dt));
            }
            finally
            {
                if (adapter != null)
                {
                    adapter.Dispose();
                }

                adapter = null;

                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }

                con = null;

                dt.Dispose();
                dt = null;
            }
        }
Пример #25
0
        public override DBDataCollection ExcuteProcedure(string sp_name, bool isReturnDataSet, ref DBOParameterCollection dbp)
        {
            DBDataCollection rtn = new DBDataCollection();

            rtn.IsSuccess = false;

            DataSetStd ds = new DataSetStd();
            DB2Command dc = null;

            if (this._s == DBStatus.Begin_Trans)
            {
                dc = new DB2Command(sp_name, conn, tran);
            }
            else
            {
                dc = new DB2Command(sp_name, conn);
            }
            dc.CommandType = CommandType.StoredProcedure;
            FillParametersToCommand(dc, dbp);
            try
            {
                if (isReturnDataSet)
                {
                    DB2DataAdapter sqlDa = new DB2DataAdapter();
                    sqlDa.SelectCommand = dc;
                    sqlDa.Fill(ds);
                    rtn.ReturnDataSet = ds;
                }
                else
                {
                    dc.ExecuteNonQuery();
                }
                //獲取返回值
                foreach (DB2Parameter sp in dc.Parameters)
                {
                    if (sp.Direction == ParameterDirection.Output || sp.Direction == ParameterDirection.InputOutput || sp.Direction == ParameterDirection.ReturnValue)
                    {
                        rtn.SetValue(sp.ParameterName.Replace("@", ""), sp.Value);
                    }
                }

                rtn.IsSuccess = true;
            }
            finally
            {
                dc.Cancel();
                dc = null;
            }

            return(rtn);
        }
Пример #26
0
        private DataTable GetDB2Table(CustomListData data)
        {
            DB2Connection con = GetConnection(data);

            data.Properties.TryGetValue("SQL Statement", StringComparison.OrdinalIgnoreCase, out var SQLStatement);

            DB2DataAdapter da        = new DB2DataAdapter(new DB2Command(SQLStatement, con));
            DataTable      db2result = new DataTable();

            da.Fill(db2result);
            con.Close();
            da.Dispose();
            return(db2result);
        }
Пример #27
0
        private static string getPNKey(string pn)
        {
            string    pnKey  = "";
            DataTable dt     = new DataTable();
            string    getkey = "SELECT PART_NUMBER_KEY FROM CGS.\"PART_NUMBER\" WHERE PART_NUMBER='" + pn + "'";

            ConnectDB2CGS.Open();
            DB2DataAdapter da = new DB2DataAdapter(getkey, ConnectDB2CGS);

            da.Fill(dt);
            ConnectDB2CGS.Close();
            pnKey = dt.Rows[0][0].ToString();
            return(pnKey);
        }
Пример #28
0
        private static DataTable getItemInfo(string item)
        {
            DataTable dt         = new DataTable();
            string    getItemKey = "SELECT I.ITEM_KEY,P.PART_NUMBER,I.QUANTITY  FROM CGS.\"ITEM\" I";

            getItemKey += " LEFT JOIN CGS.\"PART_NUMBER\" P ON P.PART_NUMBER_KEY = I.PART_NUMBER_KEY";
            getItemKey += " WHERE I.ITEM_ID = '" + item + "'";
            ConnectDB2CGS.Open();
            DB2DataAdapter da = new DB2DataAdapter(getItemKey, ConnectDB2CGS);

            da.Fill(dt);
            ConnectDB2CGS.Close();
            return(dt);
        }
Пример #29
0
        private DataSet innerFillDataSet(string query, IEnumerable <string> tables, int timeout, bool inTransaction)
        {
            try
            {
                var dataSet = new DataSet();
                using (var cmd = new DB2Command(query, inTransaction
                    ? (DB2Connection)TransConnection
                    : (DB2Connection)Connection))
                {
                    if (!IsValidTimeout(cmd, timeout))
                    {
                        throw new ArgumentException("Invalid CommandTimeout value", nameof(timeout));
                    }

                    if (inTransaction)
                    {
                        cmd.Transaction = (DB2Transaction)Transaction;
                    }
                    using (var da = new DB2DataAdapter(cmd))
                    {
                        da.Fill(dataSet);
                        if (tables == null)
                        {
                            return(dataSet);
                        }
                        var tablesArray = tables.ToArray();
                        if (tablesArray.Length <= dataSet.Tables.Count)
                        {
                            for (var i = 0; i < tablesArray.Length; i++)
                            {
                                dataSet.Tables[i].TableName = tablesArray[i];
                            }
                        }
                        else
                        {
                            for (var i = 0; i < dataSet.Tables.Count; i++)
                            {
                                dataSet.Tables[i].TableName = tablesArray[i];
                            }
                        }
                    }
                }
                return(dataSet);
            }
            catch (Exception ex)
            {
                Logger?.LogError(ex, $"Error at FillDataSet; command text: {query}");
                throw new DbDataException(ex, query);
            }
        }
Пример #30
0
 private void tablesandviews(string selecionado)
 {
     try
     {
         Table = new DataTable("TestTable");
         using (DB2Command _cmd = new DB2Command("SELECT * FROM " + selecionado, _con))
         {
             DB2DataAdapter _dap = new DB2DataAdapter(_cmd);
             _dap.Fill(Table);
             dataGridView1.DataSource = Table;
         }
     }
     catch { }
 }