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); } }
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(); }
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); } }
//********************************// // 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); } }
//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) { } }
/// <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(); }
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(); }
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); }
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); }
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); }
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); } }
/// <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); }
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); }
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); }
/// <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)); } }
/// <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(); } } } }
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); }
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(); } } }
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); }
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); } }
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); } }
public int NonQueryGlobal(string sql, string dataBase) { DB2Command com = new DB2Command(sql, connection); int affectedRows; affectedRows = com.ExecuteNonQuery(); return(affectedRows); }
private void SetSchema() { string schema = ConfigurationManager.AppSettings["Schema"]; DB2Command sql = new DB2Command(); sql.Connection = connection; sql.CommandText = "SET SCHEMA " + schema; sql.ExecuteNonQuery(); }
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(); } }
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); } } }
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(); } }
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(); } } }
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(); } } }