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();
}
}
}
