Пример #1
0
        public async Task ShouldIgnoreTables()
        {
            using (var command = new DB2Command("DROP TABLE IF EXISTS Foo; CREATE TABLE Foo (Value INT);", _connection))
            {
                command.ExecuteNonQuery();
                command.CommandText = "DROP TABLE IF EXISTS Bar; CREATE TABLE Bar (Value INT);";
                command.ExecuteNonQuery();
                for (int i = 0; i < 100; i++)
                {
                    command.Parameters.Add(new DB2Parameter("Value", i));
                    command.CommandText = "INSERT INTO Foo VALUES (?);";
                    command.ExecuteNonQuery();
                    command.CommandText = "INSERT INTO Bar VALUES (?);";
                    command.ExecuteNonQuery();
                    command.Parameters.Clear();
                }
                var checkPoint = new Checkpoint()
                {
                    DbAdapter        = DbAdapter.Informix,
                    SchemasToInclude = new[] { "informix" },
                    TablesToIgnore   = new[] { "foo" }
                };
                await checkPoint.Reset(_connection);

                command.CommandText = "SELECT COUNT(1) FROM Foo";
                command.ExecuteScalar().ShouldBe(100);
                command.CommandText = "SELECT COUNT(1) FROM Bar";
                command.ExecuteScalar().ShouldBe(0);
            }
        }
Пример #2
0
        public async Task ShouldHandleRelationships()
        {
            using (var command = new DB2Command("DROP TABLE IF EXISTS Foo; CREATE TABLE Foo (Value INT PRIMARY KEY);", _connection))
            {
                command.ExecuteNonQuery();
                command.CommandText = @"DROP TABLE IF EXISTS Bar; 
                                        CREATE TABLE Bar (
                                            Value INT,
                                            FooValue INT,
                                            FOREIGN KEY (FooValue) REFERENCES Foo(Value)
                                        );";
                command.ExecuteNonQuery();
                for (int i = 0; i < 100; i++)
                {
                    command.Parameters.Add(new DB2Parameter("Value1", i));
                    command.Parameters.Add(new DB2Parameter("Value2", i));
                    command.CommandText = "INSERT INTO Foo VALUES (?);";
                    command.ExecuteNonQuery();
                    command.CommandText = "INSERT INTO Bar VALUES (?, ?);";
                    command.ExecuteNonQuery();
                    command.Parameters.Clear();
                }
                command.CommandText = "SELECT COUNT(1) FROM Foo";
                command.ExecuteScalar().ShouldBe(100);
                command.CommandText = "SELECT COUNT(1) FROM Bar";
                command.ExecuteScalar().ShouldBe(100);

                var checkPoint = new Checkpoint
                {
                    DbAdapter        = DbAdapter.Informix,
                    SchemasToInclude = new[] { "informix" }
                };
                try
                {
                    await checkPoint.Reset(_connection);
                }
                catch
                {
                    _output.WriteLine(checkPoint.DeleteSql ?? string.Empty);
                    throw;
                }

                command.CommandText = "SELECT COUNT(1) FROM Foo";
                command.ExecuteScalar().ShouldBe(0);
                command.CommandText = "SELECT COUNT(1) FROM Bar";
                command.ExecuteScalar().ShouldBe(0);
            }
        }
        private void ingresar_Click(object sender, EventArgs e)
        {
            string ids    = "";
            string values = "";

            for (int i = 0; i < id.Count; i++)
            {
                if (i < (id.Count - 1))
                {
                    ids    += id [i].Text + ",";
                    values += "'" + id_caja [i].Text + "',";
                }
                else
                {
                    ids    += id [i].Text;
                    values += "'" + id_caja [i].Text + "'";
                }
            }
            string query = "INSERT INTO " + arbol_conexiones.SelectedNode.Text + " (" + ids + ") VALUES (" + values + ");";

            PantallaPrincipal pn         = new PantallaPrincipal();
            DB2Connection     connection = pn.obtenerConexion(arbol_conexiones.SelectedNode.Parent.Parent.Text);

            try {
                connection.Open();
                DB2Command cmd = new DB2Command(query, connection);
                cmd.ExecuteNonQuery();
                MessageBox.Show("Registro insertado correctamente");
            } catch (DB2Exception ex) {
                MessageBox.Show("Ha ocurrido un error al insertar\n" + ex.Message);
            }
            connection.Close();
        }
Пример #4
0
        public void IbmDb2ParameterizedStoredProcedure(string procedureName)
        {
            CreateProcedure(procedureName);

            try
            {
                using (var connection = new DB2Connection(Db2Configuration.Db2ConnectionString))
                    using (var command = new DB2Command(procedureName, connection))
                    {
                        connection.Open();
                        command.CommandType = CommandType.StoredProcedure;

                        foreach (var p in DbParameterData.IbmDb2Parameters)
                        {
                            command.Parameters.Add(p.ParameterName, p.Value);
                        }

                        command.ExecuteNonQuery();
                    }
            }
            finally
            {
                DropProcedure(procedureName);
            }
        }
Пример #5
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);
            }
        }
Пример #6
0
        //public static T GetDataUtil<T>(string query, params object[] paramsList)
        //{
        //    try
        //    {
        //        var val = (IDataObject)Activator.CreateInstance<T>();
        //        using (var informixConn = new DB2Connection(InformixConnectionString))
        //        {
        //            using (var selectCommand = new IfxCommand(query))
        //            {
        //                selectCommand.Connection = informixConn;
        //                if (paramsList != null)
        //                {
        //                    foreach (var param in paramsList)
        //                    {
        //                        switch ((param))
        //                        {
        //                            case string _:
        //                                selectCommand.Parameters.Add(new IfxParameter()).Value = param.ToString();
        //                                break;
        //                            case int _:
        //                                selectCommand.Parameters.Add(new IfxParameter()).Value = int.Parse(param.ToString());
        //                                break;
        //                        }
        //                    }
        //                }
        //                informixConn.Open();
        //                using (var reader = selectCommand.ExecuteReader(CommandBehavior.Default))
        //                {
        //                    while (reader.Read())
        //                    {
        //                        val.FillData(reader);
        //                    }
        //                    reader.Close();
        //                    reader.Dispose();
        //                }
        //            }
        //            informixConn.Close(); informixConn.Dispose();
        //        }
        //        return (T)val;
        //    }
        //    catch (Exception ex)
        //    {
        //        return default(T);
        //    }

        //}
        //public static List<T> GetListData<T>(string query, params string[] paramsList)
        //{
        //    try
        //    {
        //        var respone = new List<T>();
        //        using (var informixConn = new IfxConnection(InformixConnectionString))
        //        {
        //            informixConn.Open();
        //            var selectCommand = new IfxCommand(query) { Connection = informixConn };
        //            if (paramsList != null)
        //            {
        //                foreach (var param in paramsList)
        //                {
        //                    selectCommand.Parameters.Add(param);
        //                }
        //            }

        //            using (var reader = selectCommand.ExecuteReader(CommandBehavior.Default))
        //            {
        //                while (reader.Read())
        //                {
        //                    var val = (IDataObject)Activator.CreateInstance<T>();
        //                    val.FillData(reader);
        //                    respone.Add((T)val);
        //                }
        //                reader.Close();
        //                reader.Dispose();
        //            }
        //            informixConn.Close(); informixConn.Dispose();

        //        }
        //        return respone;
        //    }
        //    catch (Exception ex)
        //    {

        //        return new List<T>();
        //    }

        //}

        //public static List<T> GetListDataUtil<T>(string query, params string[] paramsList)
        //{
        //    try
        //    {
        //        var respone = new List<T>();
        //        using (var informixConn = new IfxConnection(InformixConnectionString))
        //        {
        //            informixConn.Open();
        //            var selectCommand = new IfxCommand(query) { Connection = informixConn };
        //            if (paramsList != null)
        //            {
        //                foreach (var param in paramsList)
        //                {
        //                    selectCommand.Parameters.Add(param);
        //                }
        //            }

        //            using (var reader = selectCommand.ExecuteReader(CommandBehavior.Default))
        //            {
        //                while (reader.Read())
        //                {
        //                    var val = (IDataObject)Activator.CreateInstance<T>();
        //                    val.FillData(reader);
        //                    respone.Add((T)val);
        //                }
        //                reader.Close();
        //                reader.Dispose();
        //            }
        //            informixConn.Close(); informixConn.Dispose();
        //        }
        //        return respone;
        //    }
        //    catch (Exception ex)
        //    {
        //        return new List<T>();
        //    }

        //}

        //public static T GetStoredProcedureData<T>(string query, params KeyValuePair<string, string>[] paramsList)
        //{
        //    try
        //    {
        //        var val = (IDataObject)Activator.CreateInstance<T>();
        //        using (var informixConn = new IfxConnection(InformixConnectionString))
        //        {
        //            var selectCommand = new IfxCommand("", informixConn);
        //            selectCommand.CommandType = CommandType.StoredProcedure;
        //            selectCommand.CommandText = query;

        //            if (paramsList != null)
        //            {
        //                foreach (var param in paramsList)
        //                {
        //                    selectCommand.Parameters.Add(new IfxParameter(param.Key, param.Value));
        //                }
        //            }

        //            informixConn.Open();

        //            using (var reader = selectCommand.ExecuteReader(CommandBehavior.Default))
        //            {
        //                while (reader.Read())
        //                {
        //                    val.FillData(reader);

        //                }
        //                reader.Close();
        //                reader.Dispose();
        //            }
        //            informixConn.Close(); informixConn.Dispose();

        //        }
        //        return (T)val;
        //    }
        //    catch (Exception ex)
        //    {

        //        return default(T);
        //    }

        //}
        //public static void SaveData(string query, params string[] paramsList)
        //{
        //    try
        //    {
        //        using (var informixConn = new IfxConnection(InformixConnectionString))
        //        {
        //            using (var selectCommand = new IfxCommand(query))
        //            {
        //                selectCommand.Connection = informixConn;
        //                if (paramsList != null)
        //                {
        //                    foreach (var param in paramsList)
        //                    {
        //                        selectCommand.Parameters.Add(new IfxParameter()).Value = param;
        //                    }
        //                }
        //                informixConn.Open();
        //                selectCommand.ExecuteNonQuery();
        //                selectCommand.Parameters.Clear();
        //            }
        //            informixConn.Close(); informixConn.Dispose();

        //        }
        //    }
        //    catch (Exception ex)
        //    {

        //        throw;
        //    }

        //}
        //public static void SaveDataWindowApp(string query, params string[] paramsList)
        //{
        //    try
        //    {
        //        using (var informixConn = new IfxConnection(InformixConnectionString))
        //        {
        //            using (var selectCommand = new IfxCommand(query))
        //            {
        //                selectCommand.Connection = informixConn;
        //                if (paramsList != null)
        //                {
        //                    foreach (var param in paramsList)
        //                    {
        //                        selectCommand.Parameters.Add(new IfxParameter()).Value = param;
        //                    }
        //                }
        //                informixConn.Open();
        //                selectCommand.ExecuteNonQuery();
        //                selectCommand.Parameters.Clear();
        //            }
        //            informixConn.Close(); informixConn.Dispose();
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw;
        //    }

        //}
        public static void ExecuteQuery(string query, params string[] paramsList)
        {
            try
            {
                using (var informixConn = new DB2Connection(InformixConnectionString))
                {
                    using (var selectCommand = new DB2Command(query))
                    {
                        selectCommand.Connection = informixConn;
                        if (paramsList != null)
                        {
                            foreach (var param in paramsList)
                            {
                                selectCommand.Parameters.Add(new DB2Parameter()).Value = param;
                            }
                        }
                        informixConn.Open();
                        selectCommand.ExecuteNonQuery();
                        selectCommand.Parameters.Clear();
                    }
                    informixConn.Close(); informixConn.Dispose();
                }
            }
            catch (Exception ex)
            {
            }
        }
Пример #7
0
        /// <summary>
        /// Audita la transaccion en la tabla de auditoria
        /// </summary>
        /// <param name="NumeroSecuencia">Numero de secuencia de la transaccion</param>
        /// <param name="TipoMensaje">E: Enviado - R: Recibido</param>
        /// <param name="CampoDato">Detalle de la transaccion</param>
        public void AuditarTransaccion(string NumeroSecuencia, string TipoMensaje, string CampoDato)
        {
            DB2Transaction _trans        = _myConn.BeginTransaction();
            DB2Command     _myDB2Command = _myConn.CreateCommand();

            string _myQuery = "INSERT INTO CCVAUD VALUES(V7FECH, V7HORA, V7TMSJ, V7NSEQ, V7BUFF) ";

            _myQuery += "(?, ?, '?', ?, '?')";

            _myDB2Command.CommandText = _myQuery;

            _myDB2Command.Parameters.Add("@FechaEnvio", DB2Type.Decimal);
            _myDB2Command.Parameters.Add("@HoraEnvio", DB2Type.Decimal);
            _myDB2Command.Parameters.Add("@Tipo", DB2Type.VarChar);
            _myDB2Command.Parameters.Add("@Secuencia", DB2Type.Decimal);
            _myDB2Command.Parameters.Add("@Mensaje", DB2Type.VarChar);

            _myDB2Command.Parameters["@FechaEnvio"].Value = Convert.ToDecimal(string.Format("{0:yyyyMMdd}", DateTime.Now));
            _myDB2Command.Parameters["@HoraEnvio"].Value  = Convert.ToDecimal(string.Format("{0:HHmmss}", DateTime.Now));
            _myDB2Command.Parameters["@Tipo"].Value       = TipoMensaje;
            _myDB2Command.Parameters["@Secuencia"].Value  = Convert.ToDecimal(NumeroSecuencia);
            _myDB2Command.Parameters["@Mensaje"].Value    = CampoDato;

            _myDB2Command.Transaction = _trans;
            _myDB2Command.ExecuteNonQuery();
            _trans.Commit();
        }
Пример #8
0
        private void button3_Click(object sender, EventArgs e)
        {
            PantallaPrincipal pn         = new PantallaPrincipal();
            DB2Connection     connection = pn.obtenerConexion(arbol.SelectedNode.Parent.Parent.Text);

            try {
                connection.Open();
                if (checkBox2.Checked)
                {
                    DB2Command cmd = new DB2Command("ALTER TABLE " + arbol.SelectedNode.Text + " ALTER COLUMN " + nombre_campo + " DROP NOT NULL;", connection);

                    cmd.ExecuteNonQuery();
                }
                else
                {
                    DB2Command cmd = new DB2Command("ALTER TABLE " + arbol.SelectedNode.Text + " ALTER COLUMN " + nombre_campo + " SET NOT NULL;", connection);

                    cmd.ExecuteNonQuery();
                }
                MessageBox.Show("Campo modificado");
            } catch (DB2Exception ex) {
                MessageBox.Show("Error al modificar\n" + ex.Message);
            }
            connection.Close();
        }
Пример #9
0
        public int NonQueryGlobal(string sql, string dataBase)
        {
            DB2Connection ncc;

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

            DB2Command com = new DB2Command(sql, ncc);

            try
            {
                affectedRows = com.ExecuteNonQuery();

                HistorialSeguimientoTabla(TABLA_SEGUIR, sql);  //Almacenamiento historial de seguimiento a tabla.
            }
            catch (Exception e)
            {
                //AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, sql, e, e.Message);
                //exceptions += e.Message + cambioLinea;
                affectedRows = 0;
            }
            finally
            {
                CloseConnection(ncc);
            }
            return(affectedRows);
        }
Пример #10
0
        public int NonQuery(string sql, byte[] blob)
        {
            DB2Connection ncc = OpenConnection();
            DB2Command    com = new DB2Command(sql, ncc);

            try
            {
                DB2Parameter parm1 = new DB2Parameter();
                parm1.DbType        = DbType.Binary;
                parm1.ParameterName = "@blob";
                parm1.Value         = (byte[])blob;
                com.Parameters.Add(parm1);

                affectedRows = com.ExecuteNonQuery();

                HistorialSeguimientoTabla(TABLA_SEGUIR, sql);  //Almacenamiento historial de seguimiento a tabla.
            }
            catch (Exception e)
            {
                //AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, sql, e, e.Message);
                //exceptions += e.Message + cambioLinea;
                affectedRows = 0;
            }
            finally
            {
                CloseConnection(ncc);
            }
            return(affectedRows);
        }
Пример #11
0
        public bool SaveHashtable(String tableName, Hashtable hash)
        {
            DB2Command com          = null;
            String     sql          = "INSERT INTO {0} ({1}) VALUES ({2});";
            int        affectedRows = 0;

            string cols   = "";
            string values = "";

            foreach (DictionaryEntry de in hash.Keys)
            {
                string col   = de.Key.ToString();
                string value = de.Value.GetType().Equals(typeof(string)) ? "'" + de.Value + "'" : de.Value.ToString();

                if (!cols.Equals(""))
                {
                    cols += ",";
                }
                if (!values.Equals(""))
                {
                    values += ",";
                }

                cols   += col;
                values += value;
            }

            sql = string.Format(sql, tableName, cols, values);

            com          = new DB2Command(sql, connection);
            affectedRows = com.ExecuteNonQuery();

            return(affectedRows == 1);
        }
Пример #12
0
        public void CrearTabla(string script)
        {
            TreeNode node = arbol.SelectedNode;
            DB2ConnectionStringBuilder cn = new DB2ConnectionStringBuilder();

            cn.UserID   = "db2admin";
            cn.Password = "******";
            cn.Database = node.Parent.Text;
            cn.Server   = "localhost";
            DB2Connection connect = new DB2Connection(cn.ToString());
            string        query   = @"CREATE TABLE " + nombre_tabla.Text + "( " + script + ");";

            try {
                connect.Open();
                DB2Command cmd = new DB2Command(query, connect);
                cmd.ExecuteNonQuery();
                TreeNode nodo = arbol.SelectedNode.Nodes.Add(nombre_tabla.Text);
                nodo.ImageIndex         = 2;
                nodo.SelectedImageIndex = 2;
                nodo.ContextMenuStrip   = subMenus [2];
                MessageBox.Show("Su tabla ha sido creada correctamente!");
                this.Hide();
            } catch (DB2Exception e) {
                MessageBox.Show("Ha ocurrido un error al crear su tabla!\n" + e.Message);
            }
            connect.Close();
        }
        private bool Execute(string sql, Dictionary <string, object> parms, out uint count)
        {
            try
            {
                DB2Command cmd = new DB2Command(sql, conn, trans)
                {
                    CommandTimeout = (int)Timeout,
                    CommandType    = CommandType.Text
                };

                if (parms != null)
                {
                    foreach (string key in parms.Keys)
                    {
                        cmd.Parameters.Add(key, parms[key]);
                    }
                }

                count = (uint)cmd.ExecuteNonQuery();

                return(true);
            }
            catch (Exception ex)
            {
                LastError = ex.Message;
                Logger.WriteLogExcept(LogTitle, ex);
                Logger.WriteLog(LogTitle, sql);
                count = 0;

                return(false);
            }
        }
Пример #14
0
        /// <summary>
        /// Execute SQL command and return the number of rows affected
        /// </summary>
        /// <param name="command">Command (Text command or Stored Procedure)</param>
        /// <param name="type">Type of command (text, stored procedure or table-direct)</param>
        /// <returns>Number of rows affected</returns>
        public int ExecuteNonQuery(string command, CommandType type)
        {
            int rowsAffected = 0;

            try
            {
                using (DB2Connection connection = new DB2Connection(connectionString.ConnectionString))
                {
                    using (DB2Command cmd = new DB2Command(command))
                    {
                        cmd.Connection = connection;
                        foreach (DB2Parameter parameter in Parameters)
                        {
                            cmd.Parameters.Add(parameter);
                        }
                        cmd.CommandType = type;
                        cmd.Connection.Open();
                        rowsAffected = cmd.ExecuteNonQuery();
                        cmd.Connection.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                Error aError = new Error(ex.Source, ex.Message, GetCurrentMethod());
                ErrorList.Add(aError);
            }
            return(rowsAffected);
        }
Пример #15
0
        static public int ExeSqlCmd(string sqlTxt)
        {
            int           iResult = 0;
            DB2Connection cn      = new DB2Connection();

            try
            {
                if (Common.strConn == null || Common.strConn.Trim() != String.Empty)
                {
                    strConn = GetConnString(constData.DBName);
                }
                cn = new DB2Connection(strConn);
                DB2Command cmd = new DB2Command(sqlTxt, cn);
                if (cn.State != ConnectionState.Open)
                {
                    cn.Open();
                }
                iResult = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                cn.Close();
            }

            return(iResult);
        }
Пример #16
0
        private bool ExecuteNonQuery(DBRequest request)
        {
            //Controllo
            if (!registered.Contains(request.Controller))
            {
                throw new Exception("Il Controller non è registrato all'inizio dell'esecuzione");
            }

            //Inizio
            DB2Transaction transaction = databaseConnection.GetTransaction();
            DB2Command     command     = databaseConnection.GetCommand();

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

            //Esecuzione
            try {
                command.ExecuteNonQuery();
            }
            catch (Exception e) {
                throw new Exception("Errore: " + 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(true);
        }
Пример #17
0
        /// <summary>
        /// 执行非查询SQL语句
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>受影响的记录数</returns>
        public override int ExecuteNoQuery(string sql, KdtParameterCollection parameters)
        {
            try
            {
                int effected = 0;

                // 执行SQL命令
                using (DB2Command cmd = new DB2Command(ReplaceSqlText(sql, parameters), _db2Cn))
                {
                    InitCommand(cmd); // 初始化

                    // 赋值参数
                    var hasConvertParams = ConvertToSqlParameter(parameters);
                    foreach (var item in hasConvertParams)
                    {
                        cmd.Parameters.Add(item.Value);
                    }

                    effected = cmd.ExecuteNonQuery();

                    cmd.Cancel();
                    cmd.Dispose();
                }

                return(effected);
            }
            catch (Exception ex)
            {
                KdtLoger.Instance.Error(ex);
                throw new DataException(string.Format("执行非查询SQL语句错误,原因为:{0}", ex.Message));
            }
        }
Пример #18
0
        /// <summary>
        /// 执行多条SQL语句,实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">ArrayList</param>
        public static void ExecuteSqlTran(ArrayList sqlList, string ConString)
        {
            bool mustCloseConnection = false;

            using (DB2Connection conn = new DB2Connection(ConString))
            {
                conn.Open();
                using (DB2Transaction trans = conn.BeginTransaction())
                {
                    DB2Command cmd = new DB2Command();
                    try
                    {
                        for (int i = 0; i < sqlList.Count; i++)
                        {
                            string cmdText = sqlList[i].ToString();
                            PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, null, out mustCloseConnection);
                            int val = cmd.ExecuteNonQuery();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                    finally
                    {
                        conn.Close();
                        cmd.Dispose();
                    }
                }
            }
        }
Пример #19
0
        public int DeleteHashtable(string tableName, Hashtable hashPk)
        {
            DB2Command com          = null;
            String     sql          = "DELETE FROM {0} WHERE {1};";
            int        affectedRows = 0;

            string where = "";

            foreach (string key in hashPk.Keys)
            {
                string col   = key;
                string value = hashPk[key].ToString();

                if (!where.Equals(""))
                {
                    where += " AND ";
                }

                where += String.Format("{0} = {1}", col, value);
            }

            sql          = string.Format(sql, tableName, where);
            com          = new DB2Command(sql, connection);
            affectedRows = com.ExecuteNonQuery();

            return(affectedRows);
        }
Пример #20
0
        public static void TransactionTest()
        {
            DB2Connection connection = new DB2Connection(_adonet);

            connection.Open();
            DB2Command cmd = new DB2Command();

            cmd.Connection  = connection;
            cmd.CommandText = "select * from DB2INST1.ACT where actno = 10";

            DB2Command cmd1 = new DB2Command();

            cmd1.Connection  = connection;
            cmd1.CommandText = "insert into DB2INST1.ACT values(182,'DCO1','DDDD')";

            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    //cmd.Transaction = transaction;
                    //cmd1.Transaction = transaction;
                    cmd.ExecuteNonQuery();
                    //cmd1.ExecuteNonQuery();
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    transaction.Rollback();
                }
            }
        }
Пример #21
0
        public int UpdateHashtable(String tableName, Hashtable hashData, Hashtable hashPk)
        {
            DB2Connection ncc          = OpenConnection();
            DB2Command    com          = null;
            String        sql          = "UPDATE {0} SET {1} WHERE {2};";
            int           affectedRows = 0;

            try
            {
                string set = "";
                string where = "";

                foreach (string key in hashData.Keys)
                {
                    string col   = key;
                    string value = hashData[key].ToString();

                    if (!set.Equals(""))
                    {
                        set += ",";
                    }

                    set += String.Format("{0} = {1}", col, value);
                }

                foreach (string key in hashPk.Keys)
                {
                    string col   = key;
                    string value = hashPk[key].ToString();

                    if (!where.Equals(""))
                    {
                        where += " AND ";
                    }

                    where += String.Format("{0} = {1}", col, value);
                }

                sql = string.Format(sql, tableName, set, where);

                com          = new DB2Command(sql, ncc);
                affectedRows = com.ExecuteNonQuery();

                QueryCache.removeData(tableName);             //limpiando caché de la tabla...

                HistorialSeguimientoTabla(TABLA_SEGUIR, sql); //Almacenamiento historial de seguimiento a tabla.
            }
            catch (Exception e)
            {
                AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, sql, e, e.Message);
                return(-1);
            }
            finally
            {
                CloseConnection(ncc);
            }

            return(affectedRows);
        }
Пример #22
0
        public async Task ShouldHandleSelfRelationships()
        {
            using (var command = new DB2Command(@"DROP TABLE IF EXISTS Foo; 
                                                  CREATE TABLE Foo (
                                                      Id INT PRIMARY KEY,
                                                      ParentId INT NULL
                                                  );", _connection))
            {
                command.ExecuteNonQuery();
                command.CommandText = "ALTER TABLE Foo ADD CONSTRAINT (FOREIGN KEY (ParentId) REFERENCES Foo (Id) CONSTRAINT FK_Parent1)";
                command.ExecuteNonQuery();
                command.CommandText = "INSERT INTO Foo (Id) VALUES (?)";
                command.Parameters.Add(new DB2Parameter("Value", 1));
                command.ExecuteNonQuery();
                command.Parameters.Clear();

                for (int i = 1; i < 100; i++)
                {
                    command.CommandText = "INSERT INTO Foo VALUES (?, ?)";
                    command.Parameters.Add(new DB2Parameter("Value1", i + 1));
                    command.Parameters.Add(new DB2Parameter("Value2", i));
                    command.ExecuteNonQuery();
                    command.Parameters.Clear();
                }
                command.CommandText = "SELECT COUNT(1) FROM Foo";
                command.ExecuteScalar().ShouldBe(100);

                var checkPoint = new Checkpoint
                {
                    DbAdapter        = DbAdapter.Informix,
                    SchemasToInclude = new[] { "informix" }
                };
                try
                {
                    await checkPoint.Reset(_connection);
                }
                catch
                {
                    _output.WriteLine(checkPoint.DeleteSql ?? string.Empty);
                    throw;
                }

                command.CommandText = "SELECT COUNT(1) FROM Foo";
                command.ExecuteScalar().ShouldBe(0);
            }
        }
Пример #23
0
        public async Task ShouldIncludeSchemas()
        {
            const string user_1 = "a";
            const string user_2 = "b";

            await ManageUser(user_1);
            await ManageUser(user_2);

            using (var command = new DB2Command($"DROP TABLE IF EXISTS {user_1}.Fooo; CREATE TABLE {user_1}.Fooo (Value INT)", _connection))
            {
                command.ExecuteNonQuery();
                command.CommandText = $"DROP TABLE IF EXISTS {user_2}.Baar; CREATE TABLE {user_2}.Baar (Value INT)";
                command.ExecuteNonQuery();

                for (int i = 0; i < 100; i++)
                {
                    command.Parameters.Add(new DB2Parameter("Value", i));
                    command.CommandText = $"INSERT INTO {user_1}.Fooo VALUES (?)";
                    command.ExecuteNonQuery();
                    command.CommandText = $"INSERT INTO {user_2}.Baar VALUES (?)";
                    command.ExecuteNonQuery();
                    command.Parameters.Clear();
                }

                var checkPoint = new Checkpoint
                {
                    DbAdapter        = DbAdapter.Informix,
                    SchemasToInclude = new[] { user_2 }
                };
                try
                {
                    await checkPoint.Reset(_connection);
                }
                catch
                {
                    _output.WriteLine(checkPoint.DeleteSql ?? string.Empty);
                    throw;
                }

                command.CommandText = $"SELECT COUNT(1) FROM {user_1}.Fooo";
                command.ExecuteScalar().ShouldBe(100);
                command.CommandText = $"SELECT COUNT(1) FROM {user_2}.Baar";
                command.ExecuteScalar().ShouldBe(0);
            }
        }
Пример #24
0
        public int NonQueryGlobal(string sql, string dataBase)
        {
            DB2Command com = new DB2Command(sql, connection);
            int        affectedRows;

            affectedRows = com.ExecuteNonQuery();

            return(affectedRows);
        }
Пример #25
0
        private void SetSchema()
        {
            string     schema = ConfigurationManager.AppSettings["Schema"];
            DB2Command sql    = new DB2Command();

            sql.Connection  = connection;
            sql.CommandText = "SET SCHEMA " + schema;

            sql.ExecuteNonQuery();
        }
Пример #26
0
        private void DropProcedure(string procedureName)
        {
            var statement = string.Format(dropProcedureStatement, procedureName);

            using (var connection = new DB2Connection(Db2Configuration.Db2ConnectionString))
                using (var command = new DB2Command(statement, connection))
                {
                    connection.Open();
                    command.ExecuteNonQuery();
                }
        }
Пример #27
0
        private void HistorialSeguimientoTabla(String tablaSeguir, String sqlRevision)
        {
            sqlRevision = sqlRevision.Replace("'", "");
            sqlRevision = sqlRevision.ToUpper();
            tablaSeguir = tablaSeguir.ToUpper();

            RegexOptions options = RegexOptions.None;
            Regex        regex   = new Regex(@"[ ]{2,}", options);

            sqlRevision = regex.Replace(sqlRevision, @" ");

            if ((sqlRevision.Contains("UPDATE " + tablaSeguir) == true || sqlRevision.Contains("INSERT INTO " + tablaSeguir) == true || sqlRevision.Contains("DELETE FROM " + tablaSeguir) == true) &&
                sqlRevision.Contains("MHISTORIAL_CAMBIOS") == false)
            {
                string usuario   = HttpContext.Current.User.Identity.Name.ToLower();
                string operacion = "";

                if (sqlRevision.Contains("UPDATE"))
                {
                    operacion = "U";
                }
                else if (sqlRevision.Contains("DELETE"))
                {
                    operacion = "D";
                }
                else if (sqlRevision.Contains("INSERT"))
                {
                    operacion = "I";
                }

                DB2Command    command = new DB2Command();
                DB2Connection con     = OpenConnection();
                command.Connection = con;

                DB2Transaction trans = con.BeginTransaction();
                command.Transaction = trans;

                string sqlHistorial = "INSERT INTO MHISTORIAL_CAMBIOS VALUES (DEFAULT,'" + tablaSeguir + "','" + operacion + "','" + sqlRevision + "','" + usuario + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "');";

                AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sqlHistorial, null, string.Empty);
                command.CommandText = sqlHistorial;
                try
                {
                    command.ExecuteNonQuery();
                    trans.Commit();
                    exceptions += "ejecutando: " + tablaSeguir + cambioLinea;
                }
                catch (Exception ex)
                {
                    exceptions += String.Format("error ejecutando: {0} \n {1} \n", tablaSeguir, ex.Message);
                }
            }
        }
Пример #28
0
        private void CreateProcedure(string procedureName)
        {
            var parameters = string.Join(", ", DbParameterData.IbmDb2Parameters.Select(x => $"IN {x.ParameterName} {x.DbTypeName}"));
            var statement  = string.Format(createProcedureStatment, procedureName, parameters);

            using (var connection = new DB2Connection(Db2Configuration.Db2ConnectionString))
                using (var command = new DB2Command(statement, connection))
                {
                    connection.Open();
                    command.ExecuteNonQuery();
                }
        }
Пример #29
0
 public void Delete(int id)
 {
     using (conn = new DB2Connection(connectionString))
     {
         conn.Open();
         using (DB2Command cmd = conn.CreateCommand())
         {
             cmd.CommandText = $"DELETE FROM citizen WHERE id = {id}";
             cmd.ExecuteNonQuery();
         }
     }
 }
Пример #30
0
        private static async Task ManageUser(string userName)
        {
            using (var connection = new DB2Connection("Server=127.0.0.1:9089;Database=dummyifx;UID=informix;PWD=in4mix;Persist Security Info=True;Authentication=Server;"))
            {
                await connection.OpenAsync();

                using (var command = new DB2Command($@"SELECT username 
                                                      FROM sysusers
                                                      WHERE username = '******';", connection))
                {
                    if (command.ExecuteScalar() != null)
                    {
                        command.CommandText = $"DROP USER {userName};";
                        command.ExecuteNonQuery();
                    }
                    command.CommandText = $"CREATE USER {userName} WITH PROPERTIES USER ifxsurr;";
                    command.ExecuteNonQuery();
                    command.CommandText = $"GRANT DBA TO {userName}";
                    command.ExecuteNonQuery();
                }
            }
        }